Dynamically totaling child members
Putting the dynamic totals on top or at the bottom can be a trivial or a big decision. Very often, visual tools reply on MDX queries to get the aggregation correct in the MDX query so that the need to aggregate in the visual tool is eliminated.
Suppose you need to display the sales for just Accessories and Clothing, and on the top you just want to show the aggregated sales value for Accessories and Clothing, as shown in the following example.
| Internet Sales Amount | |
| Total – All Products | $1,040,532.57 |
| Accessories | $700,759.96 |
| Clothing | $339,772.61 |
VisualTotals() function can be used to get visual totals
VisualTotals() function is a good choice to display the aggregated values for the result set (“visual” here implies that the aggregation will be only based on the visually seen results, not based on the entire cube space). The VisualTotals () function will allow you to show the totals on top or at the bottom, depending on where you put the All member.
This query using the VisualTotals () function will display the visual total on top. This is because the All Product member was the first in the named set.
WITH SET [Visual Total] AS
VisualTotals(
{ [Product].[Category].[All Products],
[Product].[Category].[Accessories],
[Product].[Category].[Clothing] }
,
'Total - *'
)
SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS,
[Visual Total] ON ROWS
FROM
[MDXBook]
Let’s put the All member as the last member in the named set as the following:
WITH SET [Visual Total] AS
VisualTotals(
{ [Product].[Category].[Accessories],
[Product].[Category].[Clothing],
[Product].[Category].[All Products] }
,
'Total - *'
)
Now the visual total is shown at the bottom.
| Internet Sales Amount | |
| Accessories | $700,759.96 |
| Clothing | $339,772.61 |
| All Products | $1,040,532.57 |
Now the visual total is shown at the bottom.
As a matter of fact, you can display the totals anywhere. Try putting the All member in the middle, you will get the totals in the middle of the result set.
WITH SET [Visual Total] AS
VisualTotals(
{ [Product].[Category].[Clothing],
[Product].[Category].[All Products],
[Product].[Category].[Accessories] }
,
'Total - *'
)
Hierarchize doesn’t seem to be necessary if there is only one level
I’ve seen MDX queries that used the Hierarchize() function for the only purpose to put the visual totals at the bottom, using the POST options.
Hierarchize ( [Visual Total], POST ) ON ROWS
When using the POST option, the Hierarchize function sorts the members in a level using a post-natural order. In other words, child members precede their parents. Therefore the dynamic totals are at the bottom. If there is only one level in the result set, I don’t see the need to use the Hierarchize function since we can position the dynamic totals at the bottom by simply rearranging the set.
Check out this MSDN page to see the description of the VisualTotals() function:
