Customized Sort

[Tony]

I am new in Cognos BI tools. I just created couple of reports in Report studio. But I am unable to display all 12 months names (Jan, Feb ,Mar.....Dec) in calendar order. I saw that Report Studio has sort features either A>Z or Z>A or do not sort. Any idea how to display all months in Calendar order?

[abu_a]

Hi Tony

The problem your getting is because cognos see's the month names as characters therefore sorting them in alphabetical order, to resolve the problem you need to sort your months numerically.

Does your calendar have any month dates which are displayed numerically e.g. '200904'

If this is not possible you will need to create your own data item to control the sorting where you apply a number to a month and sort by the new data item.

e.g  if (month='Jan')
      then (1)
      else if (month='Feb')
      then (2)
      ....... etc

Hope this helps
Abu

[vij]

see if u have column as Month number then add that in your Query
or if u don't have column as Month number then create it with ur Month Name as explain by Abu ....

e.g  if (month='Jan')
      then (1)
      else if (month='Feb')
      then (2)

then In the report page select that particular Query associated List and go to  the properties pane the property called DATA---->Grouping & Sorting in that
add that month number data item in Detail Sort List folder and RUN the Report

u will get Month Name in the List sorted on Month number ....


hope it will help u


vij
:)



     

[dumbell]

First data item is the case statement for your month

case
when [Month]='Jan' then then 1
when [Month]='None' then 0
||
||
||
end


Second data item that works in the background. Do not place it in the layout.
If ([Month] starts with '0') then (1)
else if ([Month] starts with '1') then (2)
else if ([Month] starts with '2') then (3)
||
||
||
else (13)


There is an article on this in knowledge base. Dont remember the title...

Good Luck

Dumbell

[sunnysis]

Thanks!  I've been trying to figure this technique out.

[dumbell]

Glad that this thread was helpful for you.