Trying to sort months in a chronological order in a pivot table graph.

ReportServer (EN)
1

Hi,

I am trying to do a few month on month graphs based on the pivot function in reportserver. A date column is one of my dimensions and I am obtaining the month name field by using the “to_char” function in a computed field. The problem here is, that the months get sorted alphabetically (either ascending or descending) and when I’m doing an august to december report, I am getting august-december-november-october-september (or the reverse) as the order.
If i give the numerical month values as the field..or simply dates..the order is correct..but not when the month names (strings) are used .

Is there a custom sort i can use here ? Can you suggest to me a workaround for this issue ?

AFAIK, a month on month report is a very common and important report and there should be a way to do this…

Thanks in advace.

Zach

2

Hi Zach,

you can try the following. Generate two computed fields one which maps to the numerical value of months (say MONTH) and one that maps to the name (say MONTHNAME). Now when defining your dimension and measures put both MONTH and MONTHNAME in the same dimension (say M). You need to ensure that MONTH (i.e., the numerical value) comes before MONTHNAME in the list. Now if, in your report, you only work with MONTHNAME and you do not specify a sorting direction it will per default be sorted after the natural sort direction of its parent dimension (in this case after MONTH) which should give the desired effect.

As for a general custom sort, these are currently not possible with Pivot mode. In dynamic lists you can, however, simply use hidden columns for this.

Hope this helps,
-Arno