CODEWITHSUNDEEP
sortingreporting-servicesmdx
PabloCruise
PabloCruise

Reputation: 85

MDX - How to select one column and sort the returned data

For a SSRS report, I'm trying to return a list of sorted data from a dimension to use with a parameter.

My dimension is [Radio].[Radio NO].[Radio NO] where the last Radio NO is a string.

I can find examples of returning one column while sorting on another but I can't figure out how to sort and return just one column.

Upvotes: 1

Views: 1237

Answers (2)

whytheq
whytheq

Reputation: 35557

Without seeing the exact structure of your cube / query an avenue you could explore, if you'd like to order alphabetical, is the following 

ORDER(
   [Radio].[Radio NO].[Radio NO].MEMBERS
  ,[Radio].[Radio NO].CURRENTMEMBER.MEMBER_CAPTION
  ,BDESC
)

If you want to order by a measure in your cube, then something like the following:

ORDER(
   [Radio].[Radio NO].[Radio NO].MEMBERS
  ,[Measures].[Profit]
  ,BDESC
)

This is a possible if you really need to change the column name before hitting SSRS but it has the disadvantage of changing it to a measure:

WITH 
MEMBER [Measures].[thisIsTheNewName] AS
  [Radio].[Radio NO].CURRENTMEMBER.MEMBER_CAPTION
SELECT 
{[Measures].[thisIsTheNewName]} ON COLUMNS,
ORDER(
   [Radio].[Radio NO].[Radio NO].MEMBERS
  ,[Radio].[Radio NO].CURRENTMEMBER.MEMBER_CAPTION
  ,BASC
) On ROWS
FROM [OurCube];

Upvotes: 1

PabloCruise
PabloCruise

Reputation: 85

Thanks whytheq! Based on your answer, here's what I came up with that works:

SELECT {} ON COLUMNS,
ORDER(
   [Radio].[Radio NO].[Radio NO].MEMBERS
  ,[Radio].[Radio NO].CURRENTMEMBER.MEMBER_CAPTION
  ,BASC
) On ROWS
  FROM [OurCube]

Upvotes: 1

Related Questions