SSRS 2008 - Multiple Groupings For Date Range

Question

A record in a table contains a range of valid dates, say: *tbl1.start_date* and *tbl1.end_date*. So to ensure I get all records that are valid for a specific date range, the selection logic is: <...> WHERE end_date >= @dtFrom AND start_date < @dtTo (the @dtTo parameter used in the SQL statement is actually the calculated next day of the *@prmDt_To* parameter used in the report).

Now in a report I need to count the number of records for each day within the specified data range and include the days, if any, for which there were no valid records. Thus a retrieved record may be counted in several different days. I can do it relatively easily with a recursive CTE within the data set, but my rule of thumb is to avoid the unnecessary load on the SQL database and instead return just the necessary raw data and let the Report engine handle groupings. So is there a means to do this within SSRS?

Thank you, Sergey

Answer 1

You might be able to do something in SSRS with custom code, but I recommend against it. The place to do this is in the dataset. SSRS is not designed to fill in groups that don't exist in the dataset. That sounds like what you are trying to do: SSRS would need to create the groups for each date whether or not that date is in the dataset.

If you don't have a number or date table in your database, I would just create a recursive CTE with a record for every date in the range that you are interested as you mention. Then outer join this to your table and use COUNT(tbl1.start_date) to find the appropriate days. This shouldn't be too painful a query for SQL server.

If you really need to avoid the CTE, then I would create a date or number table to use to generate the dates in your range.