CODEWITHSUNDEEP
reporting-servicesssrs-2008ssasmdxssrs-2008-r2
Aditya
Aditya

Reputation: 2301

How to Order amount by multiple dimensions in MDX?

In my Cube,I have following Measures,Dimensions.

**Measure:**
Amount

**From Customer dimension:**
Busines Unit Number
Business Unit Description
Cust Number
Cust Name

**From Bucket dimension:**
Bucket

I want Amount to be ordered by all these dimensions/attributes.

What I tried in MDX so far is,

SELECT 
NON EMPTY { 

    [Measures].[Amount] 

} ON COLUMNS, 

NON EMPTY { (
ORDER(
    (
    [Dim Customer].[BU].[BU].MEMBERS*
    [Dim Customer].[BU Description].[BU Description].MEMBERS*
    [Dim Customer].[Cust No].[Cust No].ALLMEMBERS*
    [Dim Address].[Customer Name].[Customer Name].ALLMEMBERS
    ),
[Measures].[Amount],
BDESC
)

* 

[Bucket Dim].[Bucket].[Bucket].ALLMEMBERS 
) }  ON ROWS 

FROM [Cube]

Still I am not getting proper order as I want & also it is taking a too much time to execute.

Upvotes: 0

Views: 813

Answers (1)

whytheq
whytheq

Reputation: 35557

You can nest ORDER functions to get multiple order by criteria.

The outer order is applied first - a little counter-intuitive.

The following will be ordered by the value of cust no, then by the caption of BU then by [Measures].[Amount]

SELECT 
  NON EMPTY 
    {[Measures].[Amount]} ON COLUMNS
 ,NON EMPTY 
    {
        Order
        (
          Order
          (
            Order
            (
                [Dim Customer].[BU].[BU].MEMBERS*
                [Dim Customer].[BU Description].[BU Description].MEMBERS*
                [Dim Customer].[Cust No].[Cust No].ALLMEMBERS*
                [Dim Address].[Customer Name].[Customer Name].ALLMEMBERS
             ,[Measures].[Amount]
             ,BDESC
            )
           ,[Dim Customer].[BU].CurrentMember.Member_Caption
           ,BDESC
          )
         ,[Dim Customer].[Cust No].CurrentMember.MemberValue
         ,BDESC
        )
      * 
        [Bucket Dim].[Bucket].[Bucket].ALLMEMBERS
    } ON ROWS
FROM [Cube];

Upvotes: 1

Related Questions