Examples of Triggers

Using MaxL and MDX together, create and manage triggers that enable you to track state changes to an Essbase cube area.

The following examples are based on the Sample.Basic database.

Note:

You cannot define a trigger that requires data from Dynamic Calc members or members from another partition.

Example 1: Tracking Sales for January

Example 1 tracks the Actual, Sales value for the following month, product, and region:

January (Year dimension member Jan)
Colas (Product dimension member 100)
In the Eastern region (Market dimension member East)

When the current member being calculated is Jan, and when the Actual, Sales value of Colas for January exceeds 20, the example logs an entry in the file Trigger_jan_Sales.

create or replace trigger Sample.Basic.Trigger_Jan_20
Where 
 {(Jan,Sales,[100],East,Actual)}
When 
 Jan > 20 AND Is(Year.CurrentMember, Jan) 
then spool Trigger_Jan_20
end;

Example 2: Tracking Sales for Quarter 1

Example 2 tracks the Actual, Sales value for the following months, product, and region:

January, February, March (The children of Year dimension member Qtr1)
Colas (Product dimension member 100)
In the Eastern region (Market dimension member East)

When the current member being calculated is Jan, Feb or Mar, and when the Actual, Sales value of Colas for any of the the months January, February, or March exceeds 20, the example logs an entry in the file Trigger_Jan_Sales_20, Trigger_Feb_Sales_20, or Trigger_Mar_Sales_20.

create or replace trigger Sample.Basic.Trigger_Qtr1_Sales
Where 
Crossjoin(
 {Qtr1.children}, 
 {([Measures].[Sales], [Product].[100], [Market].[East], [Scenario].[Actual])}
 )
When 
 Year.Jan > 20 and is(Year.currentmember, Jan) 
then spool Trigger_Jan_Sales_20
When 
 Year.Feb > 20 and is(Year.currentmember, Feb) 
then spool Trigger_Feb_Sales_20
When
 Year.Mar > 20 and is(Year.currentmember, Mar) 
then spool Trigger_Mar_Sales_20
end;

Example 3: Tracking Inventory Level

Example 3 tracks the inventory level for the following product, region, and months:

Colas (product 100)
In the eastern region (market East)
For January, February, and March (the children of Qtr1)

If the inventory of Colas in the eastern region falls below 500,000, the example trigger sends an email to recipient@example.com.

create or replace trigger Sample.Basic.Inventory_east
where CrossJoin(
 {[Qtr1].children},
 {([East],[100],[Ending Inventory])}
)
when [Ending Inventory] < 500000 then
mail ([smtp_server.example.com],[sender@example.com],
        [recipient@example.com], 
[Subject of E-Mail])
end;