Data to be displayed based on Month

[Kid_in_Cognos]

Hi ,

I want to display the report data based on the date. Please see the below example.
Can you guide me how can I group the Month column as below I mentioned in the report page?
   
Prompt Page :   
         
From Date :   1/1/2015         
To Date :           31/12/2015         
         
Report Page:
       
      
Month   Count   Premium

Jan 15   80      5400
Feb 15   80      5400         
Mar 15   80      5400         
Apr 15   80      5400         
May 15   80      5400         
Jun 15   80      5400         
Jul 15   80      5400         
Aug 15   80      5400         
Sep 15   80      5400         
Oct 15   80      5400         
Nov 15   80      5400         
Dec 15   80      5400

Thanks..   

[BigChris]

Just calculate the year and month of the date then concatenate them to give you your field. Then bring in your measures as usual.

[Kid_in_Cognos]

Thanks Chris.. Will try to implement...If possible can you be elaborate on "Just calculate the year and month of the date" ?

[BigChris]

Hi - yes, my apologies that was a bit of a throwaway comment. It depends slightly on your setup, but you can probably just create a calculation that looks something like:

Year([DateField) || if(month([DateField]<10) then ('0') else ('') || month([DateField])

If you want to have the month name that takes a little more work, but it's not that bad:

case month([DateField)
when 1 then 'Jan '
when 2 then 'Feb '
...
when 12 then 'Dec '
else 'xxx'
END || year([DateField])

Of course, the best option is to have a calendar table in your database that you maintain once a year with the value for your new field held against each date.

(edited to add trailing spaces after the month name)

[Kid_in_Cognos]

Thank you much Chris. Great Help.

Got the logic. BTW, it should be "if(month([DateField]<12) then ('0')"
                               Instead of "if(month([DateField]<10) then ('0')" . Right?.

Cheers !!!

[BigChris]

Nope, it's definitely 10...what it's doing is adding a zero in so that all the months are two digits. If you don't do that you'll get your months sorted as:

20161
201611
201612
20162
etc.

using that code you'll get:

201601
201602
...
201611
201612

[Kid_in_Cognos]

Excellent....Thank you much.