Wednesday, August 15, 2007

Reports Server subscriptions and Dates

Ahh, the joys of SQL reporting services. While it is a very powerful piece of software, it has some obvious (in my opinion) features missing.

Problem: User wants to schedule a report to run, the dates need to move on every time it runs but the different parameters mean that they dont move in a consistent way, ie on the same report they want to run for one customer for Monday to Wednesday, for another they want just one day etc.

The subscriptions in SQL server reporting services lack the ability to move dates on within the subscription. You can run it for the default, or specify specific dates. When you want to run the same reports for across different ranges for different customer this is not very helpful, so we came up with a clever solution.

Within the sql on the report, instead of the sql reading something like:

startDate >= @par_StartDate AND EndDate <= @par_EndDate

We have create two parameters, one called par_StartOffSet and one called par_EndOffset both being integer data types. We then change our SQL to look like this:

startDate >= CONVERT (datetime, DATEADD(dd, CAST(@par_StartOffset AS int), DATEDIFF(dd, 0, GETDATE())), 103) AND
endDate <= CONVERT (datetime, DATEADD(dd, CAST(@par_EndOffset AS int), DATEDIFF(dd, 0, GETDATE())), 103)

So, what does this allow us to do. Well, the SQL we have now always start from the current date, it then takes our parameter and adds/subtracts this from the date today. We do this on both the start and end date.

This means that now, when we create a subscription we dont have any date parameters, just two offset ones. So, if you scheduled your report to run on a Friday, and wanted it to run for Monday to Wednesday of the current week you would set the startDate param to be -4, and you endDate param to be -2. Now with the same report you can make it run for any date ranges you can think of!

Just a quick side note, you might notice in the sql we are specifically casting the 2 params to be ints. Even though we told the parameter to be an integer in the parameters menu, i got this error when trying to run the report

I'm not sure if this is a bug, but in the IDE (I cant imagine it is), but I dont see why I specifically cast the parameter when it's already an int?

That tripped me up for a bit but I guess the fix is kind of obvious.

No comments: