In Reporting Services (SSRS) 2005 there are a few tricks to being able to display data items other than report parameters in the header of your report. For some reason if we return values in a dataset and want to use them in the header of the report, the values we want to use in that dataset must appear somewhere in the body of the report. Well, that’s easy enough. We can simply
- Put a textbox in the body of the report.
- Set its visibility to hidden
- Drop in our dataset result
- Reference the textbox in the header of our report like so: “=ReportItems!TextboxName.Value”.
This works great if your report is < 1 page. However if your report becomes more than 1 page, someone will eventually notice that your report header only displays those values on the first page of the report. If you need those values to appear on all pages of your report you’ll need to make a few additional tweeks to get there….namely:
- Add a function to our report to keep track of what our value is.
- Add that function call to the header of our report.
**UPDATE: The solution provided in this example only works while viewing reports online. I later noticed that while printing, or in “Print Preview” mode, items in my header were once again appearing as blank. Ultimately to work around this problem, I converted any item I needed to reference in my report header into a parameter, and set it to “internal” or “Hidden”.
To add code to our report, we’ll need to navigate to the report properties by clicking on “ReportàReport Properties…” (Remember the menu options in the report designer are context sensitive, so make sure you’re on the “Layout” tab of your report). This should open a new window, you’ll want to click on the “Code” tab and add the following function:
Dim lastDivision As String = Nothing
Function GetDivision(ByVal currentDivision As String) As String
If Not currentDivision = Nothing Then
lastDivision = currentDivision
If all has gone well, you should be looking at something similar to the image above. Click “OK” back to the designer.
Next we need to call our new function in our header to get the results. To do so all we need to type is “=Code.FunctionName(ReportItems!YourTextbox.Value)”. So in my example I’m getting a Division name that I’m looking up based on one of the report parameters, thus I named the function in this example “GetDivision”. The textbox that’s storing the value in the body of my report is named “DivisionName”……put that all together and my call in the header looks like this:
Now my division name will show up on all headers of all pages of my report….schweet.
You’ll probably want to change the VB we entered in the code window to more accurately reflect whatever value you’re retrieving (or more accurately, keeping track of across pages if you examine the code). Also note that if you want to do this for multiple values, you’ll need to do a bit more customization to the code, and add new variables.
Hope this helps.