Redefines the structure of a hierarchy for value aggregation.
The specified set is used to redefine measures aggregation of a hierarchy. Values are then aggregated using the parent/child relationships of the members present in the specified set resulting in a visual aggregation. Children not present in the set do not contribute to the parent value.
The set can be a set of members or a set of tuples that can be represented as a crossjoin of sets of members. On the former case we have the following logic:
- VisualTotals ( {A} * {B} * {C} ) = VisualTotals({A}) * VisualTotals({B}) * VisualTotal({C})
The new aggregation logic applied is that if two FOLLOWING members have a descendant relationship ( {Member1,Member2}) the first member will have the same value as the second member.
If a new member is added to this set {Member1,Member2,Member3} we have different possibilities :
- Member3 is not a descendant of the Member1, a new chain of visual aggregation begins.
- Member3 is a descendant of Member2, Member2 will be redefined as the aggregation of Member3
- Member3 is brother of Member2, Member1 value will be the aggregation of {Member1,Member2}.
- Member3 is descendant of Member1 and an ascendant of Member2, Member1 will have the value of Member2
Special care should be taken as VisualTotals function redefined the hierarchy impacting the MDX statements outside the visual total function scope.
The 'pattern' parameter allows for redefining the caption (visual name) of the members which are effectively visually aggregated.
Example:
- VisualTotal({ [Europe], [Switzerland], [Lausanne] })
values for [Europe] and [Switzerland] will change as an aggregation of [Lausanne].
- VisualTotal({ [Europe], [Lausanne], [Switzerland] })
will change values for [Europe] as [Switzerland], whole [Switzerland].
- VisualTotal({ [Switzerland], [Europe], [Lausanne] })
will change values for [Europe] as [Lausanne] and [Switzerland] as [Lausanne].
- { [Europe], VisualTotal({ [Europe], [Lausanne] })}
will change also the value of the first [Europe], outside VisualTotal, to [Lausanne].
We use visual totals on a hierarchy with a parent, its child and one of his grandchild. All values should be the same as the one of the grandchild, [Lausanne]. [Europe] and [Switzerland] will be changed to [Europe VT] and [Switzerland VT] as we have defined a pattern.
Query
SELECT
VisualTotals({[Geography].[Geo].[Europe],[Geography].[Geo].[Switzerland],[Geography].[Geo].[Lausanne]}, '* VT' ) ON 0
FROM
[Sales]
Result
| Europe VT |
Switzerland VT |
Lausanne |
| 56 |
56 |
56 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Lausanne])
MEMBER [Geography].[Geo].[Switzerland VT] AS ([Geography].[Geo].[Lausanne])
SELECT
{
[Geography].[Geo].[Europe VT],
[Geography].[Geo].[Switzerland VT],
[Geography].[Geo].[Lausanne]
} ON 0
FROM
[Sales]
VisualTotals works also in a parent, grand child relationship. Here [Europe] will have the same value as its grandchild [Lausanne].
Query
SELECT
{ VisualTotals( { [Geography].[Geo].[Europe], [Geography].[Geo].[Lausanne] }, '* VT' ) } ON 0
FROM
[Sales]
Result
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ( [Geography].[Geo].[Lausanne] )
SELECT
{[Geography].[Geo].[Europe VT], [Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
The VisualTotals function supports a set of tuples if the set can be represented as a crossjoin of member sets.
Query
SELECT
VisualTotals( {[Product].[Prod].[icCube],[Product].[Prod].[icCube].[Personal] } *
{[Geography].[Geo].[Europe],[Geography].[Geo].[Switzerland]}, '* VT' ) ON 0
FROM
[Sales]
Result
| icCube VT |
icCube VT |
Personal |
Personal |
| Europe VT |
Switzerland |
Europe VT |
Switzerland |
| 8 |
8 |
8 |
8 |
Assertion : MDX Visual Equals
SELECT
VisualTotals( {[Product].[Prod].[icCube],[Product].[Prod].[icCube].[Personal]}, '* VT' ) *
VisualTotals({[Geography].[Geo].[Europe],[Geography].[Geo].[Switzerland]}, '* VT' ) ON 0
FROM
[Sales]
Assertion : MDX Visual Equals
WITH
MEMBER [Product].[Prod].[icCube VT] AS ([Product].[Prod].[icCube].[Personal])
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Switzerland])
SELECT
{[Product].[Prod].[icCube VT],[Product].[Prod].[icCube].[Personal]} * {[Geography].[Geo].[Europe VT],
[Geography].[Geo].[Switzerland]} ON 0
FROM [Sales]
Visual Totals can be used on several hierarchies within the same command.
Query
SELECT
VisualTotals( {[Geography].[Geo].[Europe],[Geography].[Geo].[Switzerland]}, '* VT' ) ON 0,
VisualTotals( {[Product].[Prod].[icCube],[Product].[Prod].[icCube].[Personal] } , '* VT' ) ON 1
FROM
[Sales]
Result
|
Europe VT |
Switzerland |
| icCube VT |
8 |
8 |
| Personal |
8 |
8 |
Assertion : MDX Visual Equals
WITH
MEMBER [Product].[Prod].[icCube VT] AS ([Product].[Prod].[icCube].[Personal])
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Switzerland])
SELECT
{[Geography].[Geo].[Europe VT], [Geography].[Geo].[Switzerland]} ON 0,
{[Product].[Prod].[icCube VT],[Product].[Prod].[icCube].[Personal]} ON 1
FROM
[Sales]
If visual totals are redefined, the latest definition is taken.
Query
SELECT
VisualTotals({[Geography].[Geo].[Switzerland],[Geography].[Geo].[Lausanne],[Geography].[Geo].[Zurich],[Geography].[Geo].[Switzerland],[Geography].[Geo].[Zurich]}
,'* VT' ) ON 0
FROM
[Sales]
Result
| Switzerland VT |
Lausanne |
Zurich |
Switzerland VT |
Zurich |
| 64 |
56 |
64 |
64 |
64 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Switzerland VT] AS ([Geography].[Geo].[Zurich])
SELECT
{[Geography].[Geo].[Switzerland VT], [Geography].[Geo].[Lausanne], [Geography].[Geo].[Zurich],
[Geography].[Geo].[Switzerland VT], [Geography].[Geo].[Zurich]} ON 0
FROM
[Sales]
Visual totals are only applied if there is a descendant relation between an element of the set and the following ones. Here first [Switzerland] has no visual total as [Europe] is after and not just before. The second [Switzerland] has again no Visual Total as [Lausanne], the preceding member, is not a descendant (it's its child). Value of [Europe] is [Switzerland] as it takes precedence over [Lausanne] (higher level).
Query
SELECT
VisualTotals({[Geography].[Geo].[Switzerland],[Geography].[Geo].[Europe],[Geography].[Geo].[Lausanne],[Geography].[Geo].[Switzerland]},
'* VT' ) ON 0
FROM
[Sales]
Result
| Switzerland |
Europe VT |
Lausanne |
Switzerland |
| 248 |
248 |
56 |
248 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Switzerland])
SELECT
{[Geography].[Geo].[Switzerland],[Geography].[Geo].[Europe VT],[Geography].[Geo].[Lausanne],[Geography].[Geo].[Switzerland]} ON 0
FROM
[Sales]
Once VisualTotals has changed a member of a hierarchy (e.g., [Europe] into [Europe VT]),
the change is applied to the whole MDX select scope (including calculated members).
Query
WITH
MEMBER [Measures].[Amount for Europe] AS ([Geography].[Geo].[Europe],[Measures].[Amount])
SELECT
{[Geography].[Geo].[Europe],VisualTotals({[Geography].[Geo].[Europe],[Geography].[Geo].[Lausanne]}, '* VT' )} ON 0,
{[Measures].[Amount for Europe],[Measures].[Amount] } ON 1
FROM
[Sales]
Result
|
Europe VT |
Europe VT |
Lausanne |
| Amount for Europe |
56 |
56 |
56 |
| Amount |
56 |
56 |
56 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Lausanne])
MEMBER [Measures].[Amount for Europe] AS ([Geography].[Geo].[Europe VT],[Measures].[Amount])
SELECT
{[Geography].[Geo].[Europe VT],[Geography].[Geo].[Europe VT],[Geography].[Geo].[Lausanne]} ON 0,
{[Measures].[Amount for Europe],[Measures].[Amount] } ON 1
FROM
[Sales]
The property visualTotalsIntermediateMembers drives how intermediate members defined in the VisualTotals are handled.
If the property is set to true, intermediate members values will be visual.
As an example, VisualTotals( { [Europe], [Lausanne] } will force [Switzerland] as a visual value of [Lausanne] as it is the parent of [Lausanne] and the child of [Europe].
Pattern is used to change the name of the members. Note that MSAS does not change the name of this member.
Query
SELECT
{[Geography].[Geo].[Switzerland],VisualTotals({[Geography].[Geo].[Europe],[Geography].[Geo].[Lausanne]}, '* VT' )} ON 0
FROM
[Sales]
Overridden XMLA Properties
Result
| Switzerland VT |
Europe VT |
Lausanne |
| 56 |
56 |
56 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Lausanne])
MEMBER [Geography].[Geo].[Switzerland VT] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland VT], [Geography].[Geo].[Europe VT], [Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
The property visualTotalsIntermediateMembers drives how intermediate members defined in the VisualTotals are handled.
If the property is set to true, intermediate members values will be visual.
As an example, VisualTotals( { [Europe], [Lausanne] } will force [Switzerland] as a visual value of [Lausanne] as it is the parent of [Lausanne] and the child of [Europe].
Pattern is used to change the name of the members. Note that MSAS does not change the name of this member;
therefore we are using the cell equals semantic for the assertion.
Query
SELECT
{[Geography].[Geo].[Switzerland],VisualTotals({[Geography].[Geo].[Europe],[Geography].[Geo].[Lausanne]}, '* VT' )} ON 0
FROM
[Sales]
Overridden XMLA Properties
Result
| Switzerland VT |
Europe VT |
Lausanne |
| 56 |
56 |
56 |
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Lausanne])
MEMBER [Geography].[Geo].[Switzerland] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland], [Geography].[Geo].[Europe VT], [Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
The property visualTotalsIntermediateMembers drives how intermediate members defined in the VisualTotals are handled.
If the property is set to true, intermediate members values will be visual.
As an example, VisualTotals( { [Europe], [Lausanne] } will force [Switzerland] as a visual value of [Lausanne] as it is the parent of [Lausanne] and the child of [Europe].
Query
SELECT
{ [Geography].[Geo].[Switzerland], VisualTotals( { [Geography].[Geo].[Europe], [Geography].[Geo].[Lausanne] }, '* VT' ) } ON 0
FROM
[Sales]
Overridden XMLA Properties
Result
| Switzerland |
Europe VT |
Lausanne |
| 248 |
56 |
56 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[Europe VT] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland], [Geography].[Geo].[Europe VT], [Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
VisualTotals works also across dimensions of different measure groups. In our example, we use the [Time] dimension which is shared by both measure groups [FX] and [Amount].
Query
WITH
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
VisualTotals({ [Time].[Calendar].[Year].[2010], [Time].[Calendar].[Quarter].[Q2 2010], [Time].[Calendar].[Month].[May 2010] }, 'VT *' ) on 1
FROM
[Sales]
Result
|
amountUSD |
amountEUR |
| VT 2010 |
1023.0 |
1106.886 |
| VT Q2 2010 |
1023.0 |
1106.886 |
| May 2010 |
1023.0 |
1106.886 |
Assertion : MDX Visual Equals
WITH
MEMBER [Time].[Calendar].[VT 2010] AS ([Time].[Calendar].[Month].[May 2010])
MEMBER [Time].[Calendar].[VT Q2 2010] AS ([Time].[Calendar].[Month].[May 2010])
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
{[Time].[Calendar].[VT 2010],[Time].[Calendar].[VT Q2 2010],[Time].[Calendar].[Month].[May 2010]} on 1
FROM
[Sales]
VisualTotals works also across measures of different measure groups. In our example, we use a dimension that is only part of a measure group [Amount]
Query
WITH
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
VisualTotals( {[Geography].[Geo].[Europe],[Geography].[Geo].[Spain],[Geography].[Geo].[Madrid]}, 'VT *' ) on 1
FROM
[Sales]
WHERE
[Time].[Calendar].[May 2010]
Result
|
amountUSD |
amountEUR |
| VT Europe |
1.0 |
1.082 |
| VT Spain |
1.0 |
1.082 |
| Madrid |
1.0 |
1.082 |
Assertion : MDX Visual Equals
WITH
MEMBER [Geography].[Geo].[VT Europe] AS ([Geography].[Geo].[Madrid])
MEMBER [Geography].[Geo].[VT Spain] AS ([Geography].[Geo].[Madrid])
MEMBER amountUSD AS [Measures].[Amount] * 1.0
MEMBER amountEUR AS [Measures].[Amount] * ([Currency].[European Union Currency], [Measures].[FX])
SELECT
{amountUSD} + {amountEUR} on 0,
{[Geography].[Geo].[VT Europe],[Geography].[Geo].[VT Spain],[Geography].[Geo].[Madrid]} on 1
FROM
[Sales]
WHERE
[Time].[Calendar].[May 2010]