Timestamp Month display.

[esanray]

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.

[blom0344]

CASE extract(month,[timestamp_dataitem])
when 1 then 'Jan'
when 2 then 'Feb'
....
....
when 12 then 'Dec'
else null
END

[cognostechie]

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

[blom0344]

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..

[Mpotla]

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.