Question

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

Answer 1

What if you made a calculated member before the SELECT query with something like this:

SUM ([Measures].[NRx], [Time Period].[Calendar].[Cycle Dt])?

Then just do:

SELECT NON EMPTY [MYCALCMEMBER_SUM] * CrossJoin([Time Period].[Calendar].[Cycle Dt], {[Measures].[NRx]}) ON COLUMNS,

友情链接