VisualTotals
Returns a set generated by dynamically totaling child members in a specified set, optionally using a pattern for the name of the parent member in the result set.
Important The VisualTotals function cannot be used in a query to a cube that contains a distinct count measure; such a query will return an error for all measure values. For more information, see Using Aggregate Functions.
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 cellset. Multiple hierarchies of Parent and Child members may be specified in «Set».
Example
Assume that the Product dimension has the member Baked Goods with a child of Bread. Bread has the child 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
[Product].[All Products].[Food].[Baked Goods].[Bread].[Bagels]
[Product].[All Products].[Food].[Baked Goods].[Bread].[Muffins]
for the child members. The results for the parent member reflect the precalculated values of all of its children and do not take into account that other child members have 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 double asterisks in the substitution string specify the output asterisk in the string "*Subtotal - Bread".