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]) = 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'   

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.