VisualTotals

Return to: Calculated Member Builder Function List

Dynamically totals child members of parent member specified in a set using a pattern for the total label in the result set.

Syntax

VisualTotals(«Set», «Pattern»)

Remarks

This function totals the values of the child members specified in «Set» only. Child members not specified in «Set» will not be included in the result. «Pattern» specifies the format for the totals label. Text for the pattern is taken literally and the asterisk (*) is the substitution character for the parent member. To display a literal asterisk, use two asterisks (**).

Note The VisualTotals function replaces the parent member of the resulting cell set. Multiple hierarchies of Parent and Child members may be specified in «Set».

Example

Assume that the Product dimension has the level Baked Goods with a child of Bread. Bread has the members Bagels, Muffins, and Sliced Bread.

In the first case, a select statement is done using

 [Product].[All Products].[Food].[Baked Goods].[Bread]

as the parent member and ...[Bagels] and ...[Muffins] for the child members. The results for Bread reflect the precalculated values of all of the children of [Bread] and do not take into account that the Sliced Bread member has not been included in the resulting set.

select

  {[Measures].[Unit Sales]} on columns,

  {[Product].[All Products].[Food].[Baked Goods].[Bread],

   [Product].[All Products].[Food].[Baked Goods].[Bread].[Bagels],

   [Product].[All Products].[Food].[Baked Goods].[Bread].[Muffins]

  } on rows

from Sales

An alternate solution is to use the VisualTotals function to dynamically total the child members in the set and display an accurate value for Bread.

select

{[Measures].[Unit Sales]} on columns,

{VisualTotals({[Product].[All Products].[Food].[Baked Goods].[Bread],

  [Product].[All Products].[Food].[Baked Goods].[Bread].[Bagels],

  [Product].[All Products].[Food].[Baked Goods].[Bread].[Muffins]},

   "**SubTotal - *")

} on rows

from Sales

The string “*SubTotal - Bread” is constructed by substituting the single asterisk substitution character with “Bread” to produce a meaningful name for the dynamically calculated total. The output asterisk is specified by the double asterisks in the substitution string.