Working with the RollupChildren Function
The use of the RollupChildren function in Multidimensional Expressions (MDX) statements is simple to explain, but the impact of this function on MDX queries can be wide-ranging.
The RollupChildren function rolls up the children of a member, applying a different unary operator to each child, and returns the value of this rollup as a number. The unary operator used can be supplied by a member property associated with the child member, or it can be a string expression provided directly to the function.
The impact of the RollupChildren function occurs in MDX queries designed to perform selective analysis on existing cube data. For example, the following table contains a list of child members for the Net Sales parent member, with their unary operators (represented by the UNARY_OPERATOR member property) shown in parentheses.
| Parent member | Child member |
|---|---|
| Net Sales | Domestic Sales (+) Domestic Returns (-) Foreign Sales (+) Foreign Returns (-) |
The Net Sales parent member currently provides a total of net sales minus the gross domestic and foreign sales values, with the domestic and foreign returns subtracted as part of the rollup.
Now, if you want to provide a quick and easy forecast of domestic and foreign gross sales plus 10%, ignoring the domestic and foreign returns, there are two ways to perform this action using the RollupChildren function.
Custom Member Properties
If this is to be a commonly performed operation, one method is to create a member property that stores the operator to be used for each child for a given function. For example, a member property called SALES_OPERATOR is created, and the following unary operators are assigned to it, as shown in the following table.
| Parent member | Child member |
|---|---|
| Net Sales | Domestic Sales (+) Domestic Returns (~) Foreign Sales (+) Foreign Returns (~) |
With this new member property, the following MDX statement performs the gross sales estimate operation quickly and efficiently:
RollupChildren([Net Sales], [Net Sales].CurrentMember.Properties("SALES_OPERATOR")) * 1.1
When the function is called, the value of each child is applied to a total using the operator stored in the member property. The following table displays valid unary operators and describes the expected result.
| Operator | Result |
|---|---|
| + | total = total + current child |
| - | total = total - current child |
| * | total = total * current child |
| / | total = total / current child |
| ~ | Child is not used in the rollup. Its value is ignored. |
The tilde (~) unary operator indicates that this member is to be ignored when generating rollups totals. The members for domestic and foreign returns are ignored and the rollup total returned by the RollupChildren function is multiplied by 1.1.
IIf Function
However, if the example operation is not commonplace or if it applies only to one MDX query, then the IIf function can be used with the RollupChildren function to provide the same result. The following MDX query provides the same result as the earlier MDX example, but does so without resorting to the use of a custom member property:
RollupChildren([Net Sales], IIf([Net Sales].CurrentMember.Properties("UNARY_OPERATOR") = "-", "~", [Net Sales].CurrentMember.Properties("UNARY_OPERATOR))) * 1.1
The MDX statement checks the unary operator of the child member; if it is used for subtraction (as with the domestic and foreign returns members), the tilde (~) unary operator is substituted by the IIf function. Otherwise, the unary operator of the child member is used. Finally, the returned rollup total is then multiplied by 1.1 to provide the domestic and foreign gross sales forecast value.