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]
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]
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
Hint: Use Mosha’s free Online-MDX-Studio to format SSRS-generated MDX !
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.