Thursday, December 31, 2009

Database fields in SSRS page header

Scenario
A financial report that I'm writing has columns for current year, prior year, etc.  The dates that define the columns are calculated based on the users entry of a report parameter date.  (i.e. current year start = January 1st of the year of the report parameter date).  These dates were declared and calculated in a dataset query and then used as parameters in the TSQL query.  This worked fine.  I ran into some trouble when I wanted to display the calculated dates in the header of the report.  We're using SQL Server 2005 and Visual Studio 2005.

What DID work
1. Create a dataset to calculate the dates based on the report parameters.  Select the calculated values into a table.


2. Create an internal report parameter for each calculated date and set the available values and default value based on the dataset above.


3. These internal report parameters can be used in other dataset queries and in the report header.
What I could have done but didn't want to

What I could have done but didn't want to
  • I could have rewritten the TSQL that calculated the dates in an expression within the report...but didn't want duplicate code.
What didn't work
  • The date variables that I declared in TSQL were not accessible to the report fields
  • I tried declaring internal and/or hidden report parameters and setting their values in TSQL. I couldn't find a combination of settings that allowed me to set the value of the report parameters
  • I tried creating a second dataset that calculated my dates and returned them as a single-row. This seemed promising, but for some reason SSRS restricts referencing fields in the report header. That's a strange one. I read several articles indicating that you can get around this issue by putting a hidden field in the body of the report...and then doing 4 other goofy workarounds to make it consistently available on multi-page reports...yuk.

No comments:

Post a Comment