Understanding Rules, Formulas, and User Functions

This topic lists common elements and discusses rules, formulas, and user functions, filter user functions, and the rule bar display.

Field or Control	Definition
	Click the Plus icon to insert a plus symbol into the rule.
	Click the Minus icon to insert a minus symbol into the rule.
	Click the Multiply icon to insert a multiplication symbol into the rule.
	Click the Divide icon insert a division symbol into the rule.
	Click the Exponent icon to insert an exponent symbol into the rule.
	Click the Left Parenthesis icon to insert a left parenthesis into the rule.
	Click the Right Parenthesis icon to insert a right parenthesis into the rule.
	Click the Less Than icon to insert a less than symbol into the rule.
	Click the Greater Than icon to insert a greater than symbol into the rule.
	Click the Equals icon to insert an equal symbol into the rule.
	Click the AND Operator icon to insert an AND operator into the rule.
	Click the OR Operator icon to insert an OR operator into the rule.
	Click the NOT Operator icon to insert a NOT operator into the rule.
	Click the Paste Build-in Function icon to paste a built-in function and its arguments into the rule.
	Click the Paste Cube icon to paste a data cube name into the rule.
	Click the Paste Dimension icon to paste a dimension name into the rule.
	Click the Paste Member Reference icon to paste a member reference into the rule.
	Click the User Function icon to paste a user function into the rule.
	Click the Exit Formula Mode icon to exit the formula without canceling the changes or validating the formula.

In Analytic Calculation Engine, you use the rule bar to create rules that define the calculation of data. You use rules within formulas and user functions.

Formulas define the calculation of data cubes. You enter the formula within the rule bar of the data cube that you want to calculate.

You can create a formula and save it as a user function, which can be reused with various data cubes by entering the name of the user function in the rule bar of the relevant data cube. You also create user functions to create filters and to define the calculation of aggregates.

Analytic Calculation Engine enables you to create rules that contain references to other parts. When the values of these other parts change, the analytic calculation engine recalculates the rule and stores the results in the field mapped to the calculated part. These kinds of rules can be useful for working with assumption data. When end users work with analytic instance data within an application, they can enter assumption values into one data cube, and then view the results of those assumptions in the values of other calculated data cubes.

For example, suppose an analytic model contains three data cubes called PROFIT, INCOME, and EXPENSE. The PROFIT data cube contains this formula:

INCOME - EXPENSE

When an end user changes a value that is tied to the INCOME or EXPENSE data cube, the analytic calculation engine recalculates the formula and stores the result in the field that is mapped to the PROFIT data cube.

You apply a filter user function to a specific dimension, on the Dimensions tab of the cube collection's properties.

See Defining Additional Cube Collection Dimension Properties.

This topic discusses:

Data filters.
Dimension member filters.

Data Filters

You can create filter user functions to display only the dimension members whose values meet a certain condition. For example, this is the formula for the FILTER_PROD_OVER_2000 filter user function, which is applied to the PRODUCTS dimension:

IF (SALES > 2000, RETURN(1), RETURN(0))

In the analytic model, only the PRODUCTS dimension is attached to the SALES data cube. In the analytic grid, the end user views the SALES data cube but has access only to the products that have sold over 2,000 units.

Here is the formula for the FILTER_RED_PRODUCTS filter user function, which is applied to the PRODUCTS dimension:

IF(PRODUCT_COLOR = "RED", RETURN(1), RETURN(0))

In this example, the end user has access only to the products whose members have the red attribute.

When a filter user function is applied to a dimension that is attached to a multidimensional data cube, the end user has access to a different set of members depending on whether the filtered dimension is in the column axis/row axis or slice bar.

Using the first filter user function example, the PRODUCTS, MONTHS, and REGIONS dimensions are attached to the SALES data cube. When only the PRODUCTS dimension is in the column or row axis—and the other dimensions are in the slice bar—the end user has access to only the PRODUCTS members that have sold over 2,000 units in the currently selected region and month in the slice bar. If the end user changes the region or month selection in the slice bar, the filter is reapplied and the analytic grid may display a different set of PRODUCTS members.

However, when the PRODUCTS dimension plus one or more dimensions are in the slice bar, the end user has access to a different set of dimension members. For example:

If the PRODUCTS dimension is in the slice bar, the end user has access to all PRODUCTS members that have sold over 2,000 units in all regions over the course of all months, regardless of where these dimensions are displayed in the grid.
If the PRODUCTS dimension is in the row headings, the MONTHS dimension is in the column headings and the REGION dimension is in the slice bar, the end user has access to all PRODUCTS members that have sold over 2,000 units in the currently selected region in the slice bar, over the course of all months.
If the PRODUCTS and MONTHS dimensions are in the row headings—and the MONTHS dimension is indented below the PRODUCTS dimension—plus the REGION dimension is in the slice bar, the end user has access to all PRODUCTS members that have sold over 2,000 units in the currently selected region in the slice bar, for the month under which the products are displayed.
This means that the analytic grid may display a different set of products for each month.

Dimension Member Filters

You can create filter user functions to display only the dimension members that are referenced in the filter function. For example, this is the formula for the FILTER_DIGITAL_CAMERAS filter user function, which is applied to the PRODUCTS dimension:

IF(MEMBER(PRODUCTS) = [PRODUCTS:Digital Cameras], RETURN(1), RETURN(0))

In this example, the end user only has access to the Digital Cameras member of the PRODUCTS dimension.

You can also create filter functions that filter data by user ID by using the OPRID built-in function.

See OPRID.

The information that is displayed in the rule bar depends on the selected part. The following table lists the parts and the resulting rule bar display.

Selected Part	Rule Bar Display
Data cube	The data cube's formula (if any).
User function	The user function's rule.
All other parts	Remains blank.
No selected part	Remains blank.

To edit a formula or user function, click in the rule bar. The rule bar displays buttons that enable you to edit rules.

Understanding Rules, Formulas, and User Functions

Common Elements Used in This Product Documentation

Rules, Formulas, and User Functions

Filter User Functions

Data Filters

Dimension Member Filters

Rule Bar Display