Databases Reference
In-Depth Information
Figure A-39:
Results of two VisualTotals () using same parent mem-
ber.
You can see two different VisualTotals () calls, with the same parent member
in each. This results in that member appearing twice in the query result. The
aggregated values are calculated by the second call's set.
Note that placement of members is more flexible in AS2005 than in AS2000.
In particular, you can have visual totals parents following their children in-
stead of only preceding them, which enables more display requirements to be
fulfilled.
In Analysis Services 2000, VisualTotals () returns a set that includes dynamic-
ally created calculated members that total up the given descendants for an
ancestor. When a parent member is followed by one or more of its children in
the given set, or an ancestor by one or more of its descendants, the function
replaces that parent or ancestor member with a synthesized member that
totals the values taken only from the children or descendants that follow it in
the set. The name of the synthesized member is formed from the pattern giv-
en in the pattern argument. The order of the appearance of members is im-
portant; a parent that is to be replaced by a synthetic visual total must appear
immediately before its children. The sets created by the DrillDownXXX func-
tions are likely to fit VisualTotal ()'s member ordering requirements.
The synthesized members are named using the text from the pattern string,
per the rules described earlier. Consider the following VisualTotals () expres-
sion, which contains numerous parents and ancestors (its results are shown
in
Figure A-40
).
WITH
MEMBER [Measures].[AvgPrice] AS '[Measures].[Total] /
[Measures].[Qty]', FORMAT_
SET [Rowset] AS 'VisualTotals (
{
[Time].[All Time].[2001].[Q1, 2001],
[Time].[All Time].[2001],
[Time].[All Time].[2001].[Q1, 2001].[January],
[Time].[All Time].[2001].[Q1, 2001].[February],
[Time].[All Time].[2001].[Q2, 2001],
Search WWH ::
Custom Search