Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

AS2005 and Totals

Posted by thomasivarssonmalmo on June 29, 2007

I continue my discussion of the relation between the AS2005 clients and the rules of MDX. There is an interesting limitation in AS2005 regarding totals. It is mentioned in a whitepaper by the ProClarity team: "Using ProClarity with SQL Server 2005" released at the same time as SQL Server 2005.
You can copy and run this MDX i management studio:

WITH MEMBER [Measures].[ Grand Total] AS ‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] }, [Measures] ) } },

{ [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] } ) )’, SOLVE_ORDER = 1000

MEMBER [Product].[Product Model Categories].[All Products].[ Grand Total] AS ‘AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { [Product].[Product Model Categories].[All Products].CHILDREN }, [Product].[Product Model Categories] ) } }, { [Product].[Product Model Categories].[All Products].CHILDREN } ) )’, SOLVE_ORDER = 1000
SELECT { { [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] }, ( [Measures].[ Grand Total] ) } ON COLUMNS ,{ { [Product].[Product Model Categories].[All Products].CHILDREN }, ( [Product].[Product Model Categories].[All Products].[ Grand Total] ) } ON ROWS FROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL
It is a simple table with product model categories on the rows and two measures on columns.
This MDX is generated from ProClarity Professional 6.3 and I use a feature, Totals(that you see in the two calculated members), that does not work for rows. At the same time it make perfect sense to add Internet Sales Amount and Internet Tax Amount. But the row totals will be empty in ProClarity Professional and in management studio you will se #ERROR in the cells for row totals. In management studio you will also see the error message "Aggregate functions cannot be used on Calculated Members in the Measures dimension" 
If you build the same table in Excel2007 you will see that the row totals are gone. They are usually switched on by default and the pivot tables also have the default of not showing empty rows or columns.

The conclusion in the whitepaper is that this is by design and the workaround is to build a calculated member that sums the Internet Sales Amount with the Internet Tax Amount.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: