Grand Total Problem in Excel

I found a problem when I use Excel 2003 with MSAS 2005. The problem occurs when I use the Hide Levels feature on a row dimension and select the grand total for columns option. I noticed that when I apply filters to the selection, the Grand Total does not reflect the filtered members. If I Show Details then the Grand Total is properly displayed. However, this problem does not happen on every diminsion.

I was able to duplicate the problem using Adventure Works DW. In Excel, put the Product Categories in the Row, Source Currency Code in the Column, Sales Amount as the Data, and Filter on Weight (2.12) and Date.Calendar Year (CY 2004). Ensure that Grand Total for Columns is checked. Show Details down to the Product level; notice that the Grand Total amount is correct. On the Sub Category column, select Hide Levels. Notice that the Grand total shows the incorrect amount. If I do a Hide Levels on the Category, the Grand Total is also correct. I was wondering if this would be considered an Excel bug, a MSAS bug, or if this is the way it is.

Having traced the MDX queries generated by the Excel pivot table, and rerun them in Management Studio, they return the correct totals, regardless of which levels are hidden in the query. So some further processing seems to occur in Excel - maybe someone else knows more about this phase?|||I was tracing a similar issue in the OWC component today for a client and I can confirm that it appears to be some post processing that Excel is doing. Interestingly I found that if you turn on the "Include Hidden items in totals" button (the one just to the right of the refresh button) that it appears to have the opposite effect with this particular query and causes it to display the correct totals - really strange.|||

Hi Darren,

That strangeness may help identify the culprit - it may be the "Visual Totals" connection mode which Excel employs, when the user elects not to include hidden items in the total. So it may be an AS 2005 issue after all - try the following Adventure Works queries, the 2nd one attempts to emulate the "Visual Totals" connection mode :

-- Query submitted by Excel, which works correctly without Visual Totals mode:

SELECT NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) )
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

-- Equivalent Visual Totals Query, which doesn't work correctly:

SELECT NON EMPTY VisualTotals(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

|||

I forgot to mention that I also ran the MDX statements generated by Excel into the Management Studio.And as Deepak said, the Grand Totals are correct.

In my situation, the problem only occurs when one particular dimension is used with a certain filter.In SQL Server, I have a table called Project_d.Each record in this table also has the following fields: Grand Parent Project, Project Link, and Responsible Department.Every Project will roll up to a Grand Parent Project.So my Project dimension hierarchy is Grand Parent Project <- Project.A Project may or may not have a Project Link and/or a Responsible Department.The Project Link and Responsible Department are not defined as individual hierarchies, they just attributes under the Project level with the AttributeHierarchyVisible = True.

In Excel, I can filter on Project Link with other dimensions okay.I only seem to get the Grand Total problem when the Project is placed in the Row and the Project Link or Responsible Department is used as the filter and if I Hide Levels (hide the Grand Parent Projects).I tried putting the Project in the filter and the Project Link in the Row, but the Project Link only has one level so there is nothing to hide.

I cannot separate the Project Link and the Responsible Department from the Project SQL Server table because my fact table does not have a relationship to these values; the relationship is with the Project.I hope this extra information helps and thanks to everyone for your insight.

|||

This could be due to the fact that your attributes are related. Mosha has written a very detailed blog post on the interaction between the WHERE clause and related attributes in MDX which may explain your issue. see http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx

If this is the issue that you are seeing, then it sounds like this might be fixed in SP2 which is due out shortly.

|||

I read Mosha’s blog and it is very informative; thank you!It does seem that I am experiencing the condition described, however, in my case the Excel generated query shows the correct information when run in Management Studio.When the pivot table is viewed in Excel, I get the following totals:

SHOW ALL Levels

CAD 10,306.80

EUR 2,576.70

GBP 5,153.40

USD 57,546.30

HIDE Levels (Only Product level visible)

AUD 3,301,776.20

CAD 3,053,884.94

EUR 1,031,955.40

GBP 2,483,938.44

USD 15,937,407.35

Below are the queries generated by Excel.I see the Hide Levels contain the Except statement so maybe there’s hope that SP 2 will address the problem.Thanks again!

SHOW ALL Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])

HIDE Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) , AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])|||

Darren - you are absolutely right - the root cause here is the fact that related attributes from Product appear both in WHERE and in the axis - which is exactly the example I used in the blog you cited - http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx . I don't beleive Visual Totals play any role here. I confirmed that with SP2, the following query gives correct results:

SELECT NON EMPTY VISUALTOTALS(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(

{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},

[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,

AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

HTH,

Mosha (http://www.mosha.com/msolap)