Cascading Date Range Parameters

I've written about date range parameters in an earlier post. In that post I wrote about using a group-by month sql query to dynamically create parameter lists. As I used the technique for a while, I noticed an irritating flaw. Because the two parameters were independent of each other, it was possible to request an end date that was before the start date, which generated 0 records when the report was run. Had to be a better way. I knew about cascading parameters, but found that their use was too limiting for the reports I like to create. But maybe I could use them to make a better date range parameter.

So using the technique described in the earlier post, I made two data sets, one called begin date and one called end date.

Begin date has a query like:

select distinct convert(datetime, convert(varchar,dateadd(day,-datepart(day,ae_s_fnd_a.tranx_date)+1,ae_s_fnd_a.tranx_date), 101)) as starting_date,

right(convert(varchar, ae_s_fnd_a.tranx_date, 106), 8) as display_name

from ae_s_fnd_a

end date has a query like:

Select distinct dateadd(ss,-1, convert(datetime, convert(varchar,(dateadd (mm,1,ae_s_fnd_a.tranx_date - day (ae_s_fnd_a.tranx_date)+2)-1), 101))) as ending_date,

right(convert(varchar, ae_s_fnd_a.tranx_date, 106), 8) as display_name

from ae_s_fnd_a

where ae_s_fnd_a.tranx_date > ?

Then make a cascading parameter, with the first parameter called begin date. Make it a list box, dynamically created, and reference the begin date data set. Set it to order descending by starting date.

Then add another parameter called end date. Reference the end date data set. Everything else same as above.

Then go back to your end date data set. Create a data set parameter referencing the begin date report parameter.

End result is that your end date parameter will be filtered by dates AFTER your start date. So you just cant pick a date BEFORE your start date.

Nice Looking Date Parameters in Birt

When making a Birt report with parameters for a date range, I like to build data set(s) for the date parameter(s) from the same Aim table as the eventual results set. For example, if I'm reporting from the phase status table, (ae_p_pst_e), I'll select the dates for the parameter data set from that table. That way you don't have to build a bunch of static dates and your parameters always will match the data available in the table from which you are reporting.

We generally look at results on a monthly basis, so to make sure I get all records, I have my start date with a datetime of the first second of the month and the ending date with the last second of the month. And because in Birt you can display one column as the display value and have another column as the data value, I also use a shortened date format that is a little more visually pleasing for the display.

In short, create a data set called start date. Here's your query.

select distinct convert(datetime, convert(varchar,dateadd(day,-datepart(day,ae_p_pst_e.status_date)+1,ae_p_pst_e.status_date), 101)) as starting_date,
right(convert(varchar, status_date, 106), 8) as display_name
from ae_p_pst_e
order by date desc

Then create a data set called end date. Here's that query.

Select distinct dateadd(ss,-1, convert(datetime, convert(varchar,(dateadd (mm,1,status_date - day (status_date)+2)-1), 101))) as ending_date,
right(convert(varchar, status_date, 106), 8) as display_name
from ae_p_pst_e

Then create two report parameters called start date and end date. Within the config for each parameter, reference the respective data sets. Set the value column to the data set column with the last/first seconds format, and the display column to the more visually pleasing column. Set the sort to the display column and order descending to show the most recent dates at the top.

Then create your main data set. Maybe its something like

select * from ae_p_pst_e where status_date > ? and status_date < ?.

Create your two dataset parameters referencing the report parameters and let it rip. You'll have nice looking parameters that underneath are very specific in setting the limits of the month.