Removing Excel Pivot-table columns but retaining grand total

Question

I'm using Excel pivot-tables to produce a report. The pivot-table connects to a SSAS cube. I have 2 measures- measure 1 is a 'real' measure, measure 2 is calculated based upon measure 1. Measure 1 must be shown broken out by dimB members across the columns. WIth Measure 2 I just want the totals column.

I've hidden the measure 2 columns as a workaround but this is less than ideal as when users expand or contract the dimension B members the pivot-table moves relative to the hidden columns and the report becomes a mess. It's also returning extra data which can't help performance.

Here is what I have:

                Measure 1   Measure 2       Measure 1 Total Measure 2 Total
                a   b   c   a   b   c   
DimA- member1   2   3   4   2   3   4        9              9
DimA- member2   1   4   5   1   2   5        10             8

This is what I want:

                Measure 1   Measure 1 Total Measure 2 Total
                a   b   c       
DimA- member1   2   3   4   9               9
DimA- member2   1   4   5   10              8

Is there a way to achieve the second option? Either with perhaps some mdx on the calculated measure (scope/custom rollup etc) or with the pivottable itself?

Basically I want the total without the dimension B breakdown for measure 2.

Alex Peshik Alex Peshik · Accepted Answer · 2015-03-03T10:38:38

I don't know that technique, and afraid, that it's impossible.

What if to NULLify all members in scope at least for the lowest levels? Something like this:

SCOPE ([Measures].[Measure 2 Hide]);
THIS = IIF(Axis(1).Item(0).Item(0).Hierarchy.Level.Ordinal=1
,[Measures].[Measure 2],null);
END SCOPE;

Or <2 instead of =1. Measure 2 will be at least empty for users.

Here is my Excel example (measure is called Measure 2 Hide):

CREATE MEMBER CURRENTCUBE.[Measures].[Measure 2 Hide]
 AS 
[Measures].[Count]-1,
VISIBLE = 1;

Measure2Hide

I understand that it's not a solution, but maybe will help somehow.

Removing Excel Pivot-table columns but retaining grand total

1 Answers