Want a Totals column in MDX

Question

question

Raj More asked Nov 23, '09

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

ssas mdx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-23T11:30:04Z

Rob Farley answered Nov 23, '09

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 
...

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Raj More commented · Nov 23, 2009 at 11:39 AM

I get an error saying *The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.*

0 ·

question