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..
Just calculate the year and month of the date then concatenate them to give you your field. Then bring in your measures as usual.
Thanks Chris.. Will try to implement...If possible can you be elaborate on "Just calculate the year and month of the date" ?
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)
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 !!!
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
Excellent....Thank you much.