My database contain timestamp 'Dec 18, 2010 12:00:00 AM'
i want to display my chart in month wise like Dec 1, 2010 12:00:00 AM to Dec 31, 2010 12:00:00 AM is in DEC month.
JAN 1, 2011 12:00:00 AM to JAN 30, 2011 12:00:00 AM is in Jan month.
how do i create a query item so that i will display the report month wise.
CASE extract(month,[timestamp_dataitem])
when 1 then 'Jan'
when 2 then 'Feb'
....
....
when 12 then 'Dec'
else null
END
Blom - That would accumulate data for all years in the month buckets for Jan, Feb ... so the chart would have lot more data than he needs.
Create a data item in FM with this -
Case
when (extract(month,[timestamp_dataitem]) = 1) then ('Jan ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 2) then ('Feb ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 3) then ('Mar ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 4) then ('Apr ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 5) then ('May ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 6) then ('Jun ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 7) then ('Jul ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 8) then ('Aug ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 9) then ('Sep ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 10) then ('Oct ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
when (extract(month,[timestamp_dataitem]) = 11) then ('Nov ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
Else ('Dec ' + cast(extract(year, [timestamp_dataitem]),char(4) ))
End
Indeed, but I expect a developer to understand that combining this with the year creates the right 'buckets' ;D
There are multiple ways to achieve this. Your example or coding the year as seperate dataitem and using this combined with the expression for month.
Your solution too - by the way - will still need additional dataitem to get the right sorting..
Hi ,
Use the expression _first_month(timestamp_dataitem]) .
this would convert all the dates in a month to first day of the month 'DEC 1, 2010 00:00:00'. After using this calcualted data item as dimensional attribute on chart axis define the format in data format properties set the pattern MON-YYYY.
Hope this would help.
Regards,
Mohan.