Global Subsets in OLAP Rules

Depending on the scenario, subsets can be referenced in either the rule target definition or in dynamic cell reference. Subsets cannot be used inside static cell references. The general syntax for referencing a subset in a rule is:

'DimensionName'::'SubsetName'
'DimensionName'::'SubsetName'(<optional_Subset_parameters>)

Using parameters in subsets

If the subset definition uses any custom expressions, each unique expression must be named:

_1, _2, _3, ...

When the subset is used in a rule, these expressions must be passed as parameters, similar to function parameters. For example:

['Months'::'Subset'(<value-of_1>,<value-of_2>,…)]

The following global subset definitions will be used in rule examples below:

AttributeOf:

=PALO.Subset("<server>/<database>","<dimension>",1,,,,,PALO.AFILTER(_1,0,{true,true,true,true}),,PALO.SORT(1,0,,0,,0,1,,))

ChildrenOf:

=PALO.Subset("<server>/<database>","<dimension>",1,,PALO.HFILTER(_1,0,false,,,,,1,1,true),,,,,PALO.SORT(1,0,,0,,0,1,,))

Consolidated:

=PALO.Subset("<server>/<database>","<dimension>",1,,PALO.HFILTER(,0,false,1,,,,,,),,,,,PALO.SORT(0,2,,0,,0,0,,))

Rule syntax examples

The rule below sets all consolidated elements of dimension Months to null:

['Months'::'Consolidated']=null

This rule creates a picklist with the element names delimited by comma. No additional spaces should be between the element names.

['Months'::'PicklistOf'("Jan,Feb,Mar")]=1

Below element "Product A" is in escaped double quotes (doubled), because it contains space in the name.

['Products'::'PicklistOf'("""Product A"",All")]=null 

If parameters for the attribute query are passed and multiple attributes are filtered, they have to be specified in pairs <attribute_name>,<attribute_value> delimited by colon. In the example below, products with "Blue" color AND "Brand A" have been selected.

['Products'::'AttributeOf'("Product Color,Blue:Brand,Brand A")]

Using Subsets in rule target definition

Subsets can be used in the rule target definition (the "left side" of the rule). In this scenario, the rule target definition is dynamically set to cover all elements that are returned by the subset. In the following example, the rule target is covering all elements in the "Months" dimension that are returned by the stored subset "Children", given the string "Q1" as value for the subset parameter _1:

['Month'::'Children'("Q1")]

In this context, a current member expression like !'dimension_name' cannot be used in Subset parameter, because it is undefined in the target definition.

Subsets in rule formulas

Subsets can be used in rule formulas (the "right side" of the rule) as a source of the aggregation functions SUM, AVERAGE, COUNT, MAX, MIN, MEDIAN.

In this case, a dynamic cell reference expression referencing a Subset must be the only parameter of the aggregation function. Such a dynamic cell reference expression, which uses a Subset, cannot be used with additional parameters (such as static numbers).

SUM is the implicit default aggregation function if the global Subset is referenced in dynamic cell reference . Therefore

['Month':'Q1'] = SUM(''['Month'::'Children'("Q1")])

is identical to

['Month':'Q1'] = ''['Month'::'Children'("Q1")]

You can also use a current member expression in the rule formula (right side) of the rule. For example, to calculate the value of the consolidated elements in the Months dimension as an average of the children of the current element:

[] = C: AVERAGE(''['Month'::'ChildrenOf'(!'Month')])

In another example, the rule below averages sales of a product with specific color:

['Measure':'Average Red Revenue'] = AVERAGE(['Products'::'AttributeOf'("Color,Red"),'Measure':'Revenue'])

Using stored Subsets in rules

It is also possible to use stored Subsets in rules. For example, you can use a stored Subset with an attribute filter to apply a rule to products with a specific color. Or you could use a data filter for retrieving the top 10 products and use these for any KPI calculation via rule. Stored Subsets can be used in the source area of the rule too. This enables model builders to use a dynamic number of values in rule aggregation functions like SUM or AVERAGE.

In general, stored Subsets in rules reduce the need for template rules and enable new possibilities.

Using stored subsets designed for rules in a spreadsheet

To use parametrized subsets designed for rules (with parameter names _1, _2, ...) in a spreadsheet, a named range must be defined for each parameter.

It is recommended to set the scope of each named range to a single sheet. Due to the fixed parameter names (_1, _2,...), only one parametrized subset designed for rules can be used per sheet.

Updated March 9, 2026