MDX Function and Operator Reference - Professional SQL Server Analysis Services 2005 with MDX - page 1131

VisualTotals ( set , pattern ) Returns set
Extension: AS2005, AS2000
The function accepts a set that can contain members at any level from within
one dimension. (The set can only include members from one dimension.)
Typically, the set contains members with some ancestor/ descendant relation-
ship. For the set that is returned, aggregate data values for the ancestor data
values are calculated as aggregates of the children or descendants provided
in the set instead of using all children from the dimension. (When the set cor-
responds to children visible in the GUI, the parents are totaled according to
the visible members, which is the origin of the "visual totals" name). The pat-
tern is a string that is used to identify visual-total members "visually" totaled
members are identified in the results using this pattern string. Wherever an
asterisk appears in the string, the name (the simple name, not the unique
name) of that parent member is inserted. A double asterisk ( ⊛⊛ ) causes an
asterisk character to appear in the name.
While this function exists in both AS 2005 and AS 2000, its behavior has
changed substantially between the releases. In AS2005, the function
works with all measure aggregation types, in contrast with AS2000 in
which it did not work with DISTINCT COUNT measures. We will describe
the AS 2005 behavior first and then the AS 2000 behavior.
In Analysis Services 2005, VisualTotals () effectively redefines the parent
members listed in the set to have only children/descendants as they appear
in the set, changing the display caption of the parent members to match the
naming pattern as well. This affects all uses of the members in the entire
query, not just within the set. In terms of calculations, it is similar to, but not
the same as defining a subcube consisting of just the lowest-level members
in the set.
Consider the following query, whose results are shown in Figure A-38 :
WITH
SET [VT1] AS
VisualTotals (
{ [Product].[ByCategory].[Category].&[2],
{[Product].[ByCategory].[Subcategory].&[12],
[Product].[ByCategory].[Subcategory].&[15]}