Wednesday, January 14, 2015

Sort date names order in a matrix report based on SSAS Data Source

 

I created a report based on a simple SSAS cube with a fact table containing a value per day per division. The report has a year parameter and needs to show the sum per month in a pivot table, with 12 months in the columns and a row for each division.

The report is easy to generate with the report wizard, but it results in columns where months are sorted by name: April, August, December, February, …

image

Problem

The Report designer sorts the columns alphabetically by the name of the month.

Solution

The months are supplied by the SSAS MDX in correct order, it is the report definition that sorts the resulting columns alphabetically.

The solution is quite easy: remove the sort order from the Month Column Group.

image

in the Group properties of the Month column group, go to the Sorting options and remove the sort on the Month:

image

So that no sorting is set:

image

The report shows the months in chronological order:

image