Birt Reports on Aim Desktop - Trailing 13 months

One of the great features of Aim is the ability to display Birt reports on the workdesk. This gives report designers the ability to create a reporting dashboard showing one or more charts in a single BIRT report.

I like to show the trailing 13 complete months in chart reports that auto-load on the work desk so that the user can see the annual trend and the same month from the year prior. I riffed of the last second of the month sql discussed in my "nice looking date parameters" article to come up with the where clause for the chart dataset.

First, read up on the dateadd function here. http://www.w3schools.com/sql/func_dateadd.asp

Maybe there's a cleaner way to do this, but it works.

Here's a simple application:

select ph_workorder, ph_date_created
from phase
where ph_date_created > dateadd (m, -13, dateadd
(ss,-1, convert(datetime, convert(varchar,(dateadd (mm,1,getdate() - day (getdate())+2)-1), 101))) )
order by ph_date_created