SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

MDX – Putting Visual Totals on top or at the bottom

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:

VisualTotals (MDX)

 


Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

Comments

Leave a comment on the original post [bisherryli.com, opens in a new window]

Loading comments...