Filter User Functions

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.