x

Want a Totals column in MDX

I have the following query that gives me an extract (I am putting this into a CSV from my cube).

I want to show the Total of all NRx in a column as the first column NRx and the rest of the NRx columns should come up as they do now. I tried to add a SUM (Measures.NRx) to this, but it did not work

How do I get a total NRx column?

SELECT NON EMPTY CrossJoin (  [Time Period].[Calendar].[Cycle Dt],  {  [Measures].[NRx]  } )  ON COLUMNS, NON EMPTY {(  [Prescriber].[Prescriber Name].[Prescriber Name].Members  , [Prototype Alignment].[Tier Code].[Lilly Tier Code].Members  , [Prototype Alignment].[Territory Name].[Territory Name].Members  , [Prototype Alignment].[District Name].[District Name].Members  , [Prototype Alignment].[Division Name].[Division Name].Members  , [Prototype Alignment].[Area Name].[Area Name].Members  , [Market Product].[Product Group Nbr].[Product Group Nbr].Members  , [Market Product].[Product G Name Name].[Product G Name Name].Members )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM (  SELECT  ({  [Prescriber].[Ims Prescriber Id].&[011111]  }  ,  {  [Time Period].[Cycle Dt].&[2008-10-03T00:00:00],  [Time Period].[Cycle Dt].&[2008-10-10T00:00:00],  [Time Period].[Cycle Dt].&[2008-10-17T00:00:00],  [Time Period].[Cycle Dt].&[2008-10-24T00:00:00],  [Time Period].[Cycle Dt].&[2008-10-31T00:00:00],  [Time Period].[Cycle Dt].&[2008-11-07T00:00:00],  [Time Period].[Cycle Dt].&[2008-11-14T00:00:00],  [Time Period].[Cycle Dt].&[2008-11-21T00:00:00],  [Time Period].[Cycle Dt].&[2008-11-28T00:00:00],  [Time Period].[Cycle Dt].&[2008-12-05T00:00:00],  [Time Period].[Cycle Dt].&[2008-12-12T00:00:00],  [Time Period].[Cycle Dt].&[2008-12-12T00:00:00],  [Time Period].[Cycle Dt].&[2008-12-19T00:00:00],  [Time Period].[Cycle Dt].&[2008-12-26T00:00:00],  [Time Period].[Cycle Dt].&[2008-12-26T00:00:00]
} ) ON COLUMNS FROM [Mart] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
more ▼

asked Nov 23, 2009 at 11:04 AM in Default

Raj More gravatar image

Raj More
1.7k 80 82 84

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

A total column is basically one that is against the 'All' level of the various dimensions you're slicing by.

So, if you're wanting the total against all dates (but still split up by all the other things you have on the rows), try:

WITH MEMBER [Meaures].[TotalNRx] as ([Measures].[NRx], [Time Period].[Cycle Dt].[All]) SELECT NON EMPTY  {  [Measures].[NRx],  [Measures].[TotalNRx]  }  ON COLUMNS, NON EMPTY [Time Period].[Calendar].[Cycle Dt].Members * {(  [Prescriber].[Prescriber Name].[Prescriber Name].Members  , [Prototype Alignment].[Tier Code].[Lilly Tier Code].Members  , [Prototype Alignment].[Territory Name].[Territory Name].Members  , [Prototype Alignment].[District Name].[District Name].Members  , [Prototype Alignment].[Division Name].[Division Name].Members  , [Prototype Alignment].[Area Name].[Area Name].Members  , [Market Product].[Product Group Nbr].[Product Group Nbr].Members  , [Market Product].[Product G Name Name].[Product G Name Name].Members )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ... 
more ▼

answered Nov 23, 2009 at 11:30 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

I get an error saying The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
Nov 23, 2009 at 11:39 AM Raj More
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x174
x69

asked: Nov 23, 2009 at 11:04 AM

Seen: 2164 times

Last Updated: Nov 23, 2009 at 11:04 AM