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

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

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: ,
  1. 20. Juni 2011 um 11:56

    Here is the MDX.WITH MEMBER Measures . Days Since Last Incident AS IIf check at day level Not Time . Calendar .CurrentMember.Level IS Time . Calendar . Date NA Case check we are not at the beginning of the time dim When IsEmpty Time . Calendar .CurrentMember.PrevMember Then 0 check if the current time member has incidents measure that is empty or 0 When IsEmpty Time . Calendar .CurrentMember Measures . Incident Count Or Time . Calendar .CurrentMember Measures . Incident Count 0 if so we need to calculate how many days since the last incident Then Sum Time . Calendar .CurrentMember.PrevMember Measures . Days Since Last Incident 1 if not there is at least one incident for the current day so 0 Else 0 End SELECT Measures . Incident Count Measures . Days Since Last Incident ON 0 Descendants Time . Calendar . Year . Calendar 2005 Time . Calendar . Date ON 1FROM Recursive Mdx Sproc In most cases this would probably work fine. Here is the script.CALCULATE CREATE MEMBER CURRENTCUBE. Measures . Days Since Last Incident AS NA SCOPE Measures . Days Since Last Incident Time . Date .Members 1 Null Case check if the current time member has incidents measure that is empty or 0 When IsEmpty Time . Calendar .CurrentMember Measures . Incident Count Or Time . Calendar .CurrentMember Measures . Incident Count 0 if so calculate how many days since the last incident using a recursive call that increments by 1 for each call Then Sum Time . Calendar .CurrentMember.PrevMember Measures . Days Since Last Incident 1 if not there is at least one incident for the current day so 0 Else 0 End when at the beginning of time evolve Time . Date .Members 0 Null END SCOPE I was flabbergasted to see that the MDX scripting approach takes 1 second for the same query!!! Here is the MDX that uses it.WITHMEMBER Measures . Days Since Last Incident AS IIf check at day level Not Time . Calendar .CurrentMember.Level IS Time . Calendar . Date NA call the sproc and let it do the work ClassLibrary1.DaysSinceLastIncident Time . Date . All .Children Measures . Incident Count Rank Time . Date .CurrentMember Time . Date . All .Children – 1 taking away 1 because 1-based SELECT Measures . Incident Count Measures . Days Since Last Incident ON 0 Descendants Time . Calendar . Year . Calendar 2005 Time . Calendar . Date ON 1FROM Recursive Mdx Sprocs As you can see in order to pass in currentDayIndex we use the Rank function.

  1. No trackbacks yet.

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: