Hello, I am trying to sort on a calendar period. The period is the number 1 through 12. I would like to change them to be the abbreviations for the months... Like Jan, Feb, Mar... Is there a good way to do this?
I have done a case statement on the period and am able to get the abbr. to show up, but when it sorts it sorts alphabetically. and Not in a calendar year order.
Any ideas would be great.
Thanks in advance.
I am using cognos 8.4
You want to sort on the Calender Year order or Calender Period order?
In any column in the report, you can assign a different data item to the sort property (use Advanced sorting option). So, if you are displayig Jan, Feb..., in the sort property you can assign the Period Number which is 1 thru 12 . That way it will display Jan Feb but sort on 1 thru 12.
If you are displaying years and periods and want to sort on every period of every year then you can create a calculated column with the expression year + period and assign that to the sort key.
2011 2012
Aug Dec Jul Jun Nov Oct Sep Feb Jan Mar
that is what is happening now on my columns on my cross tab. Years are good, but months sort on a-z.
I did the advanced sort. by year, then period. I tried changing the format to be Date for period. but didn't work.
So I take it that the Months are nested in the Years. So Months are the 2nd level in the rows?
If that is the case, you don't need to sort on years and periods. Just sort on Months. In the 2nd level of rows (which is displaying Months/Periods), assign the Month No/Period No. (1,2,3...) using the Advanced sort options.