MDX : Ordering by Month in your SSRS Reports

You want your months to display in your SSRS report as Jan, Feb, Mar etc yet you run the report and the ordering is all out of sorts. Adapt the following MDX sampler, to grab the month key, and follow the steps subsequent to set the ordering in your SSRS chart.

The month key will need to be something with a natural sort order, so either 1 through to 12 or the year concatenated with the month i.e. for year 2010, the key for Jan through to March would be 201001, 201002, 201003 etc. The main name can be Jan, Feb, Mar, or your own custom display format.

WITH MEMBER Measures.[MonthNameOrder] AS
[Date].[Month].CurrentMember.MEMBER_NAME

MEMBER Measures.[MonthKeyOrder] AS
[Date].[Month].CurrentMember.MEMBER_KEY

MEMBER Measures.X
as 1

SELECT {
[Measures].[X],
[Measures].[MonthNameOrder],
[Measures].[MonthKeyOrder]
} ON COLUMNS, 

{[Date].[Month].[Month].Members}

ON ROWS
 
FROM [Wruggles_Cube]

Now in your SSRS chart, drag Month to your Categories, right click the Month within Categories, select Category Group properties, select sorting, then choose the MonthKeyOrder column.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s