question

Raj More avatar image
Raj More asked

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
ssasmdx
10 |1200

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

1 Answer

·
Rob Farley avatar image
Rob Farley answered

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 comment
10 |1200

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

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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