If the predefined driver formulas do not accurately reflect the required calculation flow for your model, you can create a custom driver formula using the Formula Editor. The formula created to calculate the driver value may be simple, or it may be a complex formula that includes IF statements.
The assignment for which the custom driver is used automatically provides the FIX context in the calculation script. The formula should be defined considering the FIX context. The FIX context always FIXes on assignment blocks for the assignment. For information on the FIX command, see the Oracle Essbase Database Administrator's Guide.
A custom formula can include unlimited variables, both custom and predefined, such as Volume or Rate. The custom variables must be defined in Performance Management Architect in the Measures dimension.
You can additionally specify a location for a variable used within the custom formula to enable the custom formula to be used in different stages. The Location (Global, Source, Destination or Assignment) must be defined with the variable using curly brackets ({}), even though curly brackets are not used in Essbase. The location is resolved dynamically during calc script generation.
Note: | If a location for a variable is not specified, it is considered to be an Assignment. |
You can use functions (operands) between elements to control the formula calculation, including simple operands such as the following examples:
Add (+)
Subtract (-)
Multiply (*)
Divide (/)
Each formula must end with a semicolon(;).
This example displays a generic format for a custom driver formula:
“CalculatedDriverValue” = {Custom Variable —> Source} * {Custom Variable —> Destination};
The following example displays a custom driver formula which does not use any location syntax:
“CalculatedDriverValue” = “Variable1” * “Variable2” —> “[GL Departments].[NoMember]”;
Custom formulas must be mathematically correct, and syntactically correct according to Essbase syntax, with the following exceptions for Profitability and Cost Management:
Custom Variables (driver measure) and Locations (Global, Source, Destination or Assignment) must be enclosed in curly brackets, even though curly brackets are not used in Essbase.
Variables specified using the Profitability and Cost Management syntax should not be surrounded by double quotes.
Profitability and Cost Management resolves and translates the location syntax into Essbase syntax. After resolving the correct dimensional references on any variables with locations that use Profitability and Cost Management syntax, the formula is copied into Essbase calculation scripts and verified for Essbase syntax within the scripts.
For instructions on using the Formula Editor to create custom formulas, see the Oracle Essbase Database Administrator's Guide
The custom driver formula examples assume the following values:
Stage 1 Dimensions: GL_ Department x GL_Account
Stage 2 Dimensions: ACT_Department x ACT_Activity
The calculation script generation process in Profitability and Cost Management inserts the script defined for a custom driver for any source that uses this driver. The system-generated script creates the FIX statements to pinpoint the source and destination combinations defined by the assignment logic attached to the source.
The custom driver script need not provide this FIX, but it can take advantage of it, or modify the FIX to reference data from different locations that is normally defined for the DESTINATION, SOURCE, ASSIGNMENT, or GLOBAL measure locations.
Common uses for custom drivers rely on the ability to reference data that is stored in locations other than the normal four, enabling the user to enter driver measures at higher levels in the hierarchy or at fewer intersections.
The default FIX statement that the CalculatedDriverValue measure computed reflects the link between source and destination. Using the example stages, the FIX created by the system-generated script is shown below:
GL_Department.member x GL_Account.member x ACT_Department.member x ACT_Activity.member
where the dimension members for each dimension reflect the source intersection and destination intersection for the allocation being performed.
The examples below modify this default FIX to retrieve driver measures from another location. Remember that if the member referenced by the default FIX is correct for your purposes you need not override it.
Example 1: Referencing a driver measure located at only one of the destination dimensions cross NoMember in the other (in this case ACT_Department)
“CalculatedDriverValue” = “DriverMeasure” ->”[ACT_Activity.NoMember] ->”[ GL_Department.NoMember] ->”[ GL_Account.NoMember];
Use this formula when there is a single driver value for the entire department; for example, square feet, meters, or headcount.
Example 2: Referencing a driver measure located at the parent of one of the destination dimensions cross NoMember in the other dimension:
“CalculatedDriverValue” = “DriverMeasure” ->”[ACT_Activity.NoMember]”->”(@PARENT(ACT_Department)” ->”[ GL_Department.NoMember] ->”[ GL_Account.NoMember];
Alternatively, you can reference the Generation of the ancestor from which you want to retrieve the value using @ANCSET function. In this example, the driver pulls the DriverMeasure value from the Generation 2 ancestor of the destination’s Department member:
“CalculatedDriverValue” = “DriverMeasure” ->”[ACT_Activity.NoMember]”->”(@ANCEST(ACT_Department, 2)” ->”[ GL_Department.NoMember] ->”[ GL_Account.NoMember];
Other common uses are to key the driver value calculation on the nature of the source/destination combination. This option enables the driver to adapt to specific circumstances based on characteristics of the intersections involved in the allocation.
Example 3: Referencing a different measure for driver calculation based on the UDA of one destination member:
IF(@ISUDA(Activity,"UDA1"))
"CalculatedDriverValue" = {Measure1->Destination};
ELSE IF (@ISUDA(Activity,"UDA2"))
"CalculatedDriverValue" = {Measure2->Destination};
ELSE IF (@ISUDA(Activity,"UDA3"))
"CalculatedDriverValue" = {Measure3->Destination};
ENDIF;
Use this formula to key the driver formula from a characteristic of the destination, such as product form factor or customer classification Note that the syntax in the example: ‘{Measure1->Destination}’ does not match usual Essbase calc Script syntax. The use of the curly brackets ({ }) enables Profitability and Cost Management to interpret the ‘->Destination’ shorthand, and to replace it with the actual destination being targeted. When the script is deployed to Essbase, Profitability inserts the correct member references and syntax.