.NET Playground

Reporting Services (SSRS) - Get data value into page header/footer

In reporting services (ssrs), there are times where you need to get a value from a data source and put it into your page header/footer. The catch here is that ssrs does not allow you to access data sources within your header/footer. This is one way around it.

First the easy but inflexible way.

1. Create a textbox in the body of the report & set the value to what you want appearing in your header/footer
2. Set the name of your textbox to txtVarStore
3. In the properties of this textbox, set Visibility -> Hidden to True (ie: hide the textbox)
4. Create a textbox in your header/footer & set the value to the expression: "=ReportItems!txtVarStore.Value"

This will set the value of the textbox in your header/footer; but will only do it for the page that the hidden textbox is on.

The second way is a bit more involved, but persists across pages:

1. Go to Properties of your report
2. Go to the Code tab
3. Create a new public shared variable for your value, ie:
   1. Public Shared Dim MyVar as String
4. Create a new function to set the value of this variable, ie:
   1. Public Function SetMyVar ( ByVar var as String)
   2.    MyVar = var
   3. End Function
5. Create a new textbox in the body of your report & set the value to the expression:  "=Code.SetMyVar(First(Fields!colVar.Value, "DataSourceName"))"
6. Create a new textbox in your header/footer, set the value to the expression: "=Code.MyVar"

What this does is set up a new static variable (shared variable for you VB ppl), which persists once across the entire report. In SSRS, the body content gets generated first, which is why we can set the static variable using the SetMyVar function.

When it comes time for the header/footer to render, it simply reads out the value of the static variable.

Bit of a sleazy hack, but such are the quirks of SSRS

posted @ Thursday, November 06, 2008 11:46 AM