Monday, January 5, 2009

Getting data into a page header in SSRS

As I've mentioned before, we're moving MS Access97 reports to SSRS 2008. Recently I came across another issue, repeating data in a page header. You may or may not know it, but you can't directly add a data field to a page/report headers and footers. If you want your data to repeat you need to employ a workaround. One way is to use parameters and use a second data set for the parameters. I tried this method, but I had a problem when the page changed; the header didn't always mirror what was in my group. I also tried group headers that repeated on each page, but the report I'm working with also has sub reports that may or may not span pages. I found when that happened the group headers didn't repeat properly.

The solution I came up with is to set the values in code. The first step was to declare a public variable for each field I needed on my page header. The second step was to create a function that set the value of the variables to a parameter passed into the function. I then added a hidden text box on the body of my report that called my functioned and passed in the values I needed on the page header. Finally I added the page header section, added a text box for each field I needed, and used an expression to call the public variable I declared in the first step.

This isn't my invention; there are examples of this on the web. I don't have a source handy but I'll cite a few that I used later.

No comments: