none
Displaying Total in the last row of result set with Sort RRS feed

  • Question

  • Hi Experts,

    I want to display Measures.Amounts with respect to two members of the same dimension like below...

    SELECT [Measures].[AMOUNTS] ON COLUMNS,
           {[Customer].[Group ID].[Group ID].ALLmembers *
           [Customer].[Group Name].[Group Name].ALLmembers} ON ROWS
    FROM [Cube];

    Now, I want to show Total in the last low of the result set as well ORDER BY amount Desc.

    Could you please help in achieving this.

    Thanks in advance.

    --NoorBi

    Tuesday, October 8, 2013 7:24 AM

Answers

  • Hi NoorBi,

    I'm tried to write MDX query to get expected output format for your requirement, please see what I have done.

    Fisrtly, I'm consider using VisualTotals() function. Please refer to the MDX query below:

    SELECT
    	{
    		[Measures].[Internet Sales Amount],
    		[Measures].[Reseller Sales Amount]
    	} ON COLUMNS,
    	Order(
    		VisualTotals(
    			{
    				[Product].[Category].ALLMEMBERS
    			},
    			'* - Total Of Selected'
    		), 
    		[Product].[Category].CurrentMember.Level.Ordinal,
    		BDESC 
    	) ON ROWS
    FROM [Adventure Works]
    

    But, If I use "[Product].[Category].ALLMEMBERS*[Date].[Calendar Year].ALLMEMBERS". The MDX query return the following error:
    The VisualTotals function may only be called with a single-hierarchy set parameter

    Maybe we should consider order by amount without grand total, please refer to the following MDX query:

    SELECT Measures.[Reseller Order Count] ON COLUMNS,
    
     Order([Product].[Category].MEMBERS*[Date].[Calendar Year].MEMBERS
     ,Measures.[Reseller Order Count] ,DESC
    
     ) ON ROWS
    
    FROM [Adventure Works] 

    I'm also look forward other community members have good ideas for this issue.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 10, 2013 11:12 AM
    Moderator