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.
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.
- 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