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.
- Create a textbox in the body of the report & set the value to what you want appearing in your header/footer
- Set the name of your textbox to txtVarStore
- In the properties of this textbox, set Visibility -> Hidden to True (ie: hide the textbox)
- 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:
- Go to Properties of your report
- Go to the Code tab
- Create a new public shared variable for your value, ie:
- Public Shared Dim MyVar as String
- Create a new function to set the value of this variable, ie:
- Public Function SetMyVar ( ByVar var as String)
- MyVar = var
- End Function
- Create a new textbox in the body of your report & set the value to the expression: “=Code.SetMyVar(First(Fields!colVar.Value, “DataSourceName”))”
- 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
Thanks for the code. Was really useful.
One small typo: “ByVar” is supposed to be “ByVal”
Create a new function to set the value of this variable, ie:
1. Public Function SetMyVar ( ByVar var as String)
Hi Andrew:
What am I doing wrong? I am getting the following error:-
[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox50’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
Thanks
If you want your field value to cross multiple pages before it changes to the next value the best approach is a combination of the two approaches listed here. Put in a TextField as described in #1 and then use that value of the text field to set a global variable so you can transition from the previous value to the next if your header is spanning multiple pages. (SSRS has trouble when you are grouping a tablix and starting a group on a new page). Something like this in your code section:
Public Shared LastBook As String
Public Function GetLastBook(ByVal book as String) As String
if Len(book) > 0 Then
LastBook = book
End If
return LastBook
End Function