fbpx

VisualTotals (MDX)

Redefines the structure of a hierarchy for value aggregation.

Parameters

Name Type Cardinality Default value
  set-expression  one   
pattern  string-expression  zero-one   

Return

set

Description

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].

Examples

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.