Archiv

Posts Tagged ‘MDX’

HowTo sort Date-Dimension (SSAS) parameter values in SSRS ?

12. April 2011 1 Kommentar

Challenge:
Cube has a time-dimension where members (years,quarters,month) are ordered in ascending order. In SSRS Report the members should be ordered in descending order…

Orginal Query (produced by SSRS-wizard):

WITH
  MEMBER [Measures].[ParameterCaption] AS
    [Time].[Calendar].CurrentMember.Member_Caption
  MEMBER [Measures].[ParameterValue] AS
    [Time].[Calendar].CurrentMember.UniqueName
  MEMBER [Measures].[ParameterLevel] AS
    [Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS

,[Time].[Calendar].ALLMEMBERS
ON ROWS
FROM [myCube]

image

Change Order of Years:

WITH
  MEMBER [Measures].[ParameterCaption] AS
    [Time].[Calendar].CurrentMember.Member_Caption
  MEMBER [Measures].[ParameterValue] AS
    [Time].[Calendar].CurrentMember.UniqueName
  MEMBER [Measures].[ParameterLevel] AS
    [Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS
 ,ORDER
     ( [Time].[Calendar].ALLMEMBERS
      ,[Time].[Year].CurrentMember.MemberValue, DESC)
ON ROWS
FROM [myCube]

image

Change Order of all levels (Year,Quarter,Month):

WITH
  MEMBER [Measures].[ParameterCaption] AS
    [Time].[Calendar].CurrentMember.Member_Caption
  MEMBER [Measures].[ParameterValue] AS
    [Time].[Calendar].CurrentMember.UniqueName
  MEMBER [Measures].[ParameterLevel] AS
    [Time].[Calendar].CurrentMember.Level.Ordinal
SELECT
  {
    [Measures].[ParameterCaption]
   ,[Measures].[ParameterValue]
   ,[Measures].[ParameterLevel]
  } ON COLUMNS

,Order
  ( [Time].[Calendar].ALLMEMBERS
   ,[Time].[Calendar].CurrentMember.Properties("ID",TYPED)
   ,DESC   )
ON ROWS
FROM GBMS

image

Hint: Use Mosha’s free Online-MDX-Studio  to format SSRS-generated MDX !

Kategorien:Allgemein Schlagwörter: ,