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

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:
        Public Shared Dim MyVar as String
  4. Create a new function to set the value of this variable, ie:
        Public Function SetMyVar ( ByVal var as String)
           MyVar = var
        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

8 Comments

  • Vijay Arunraj
    Posted 23 March, 2011 at 7:50 pm | Permalink

    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)

  • bal
    Posted 26 April, 2011 at 11:36 pm | Permalink

    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

  • Denis Abramov
    Posted 23 August, 2011 at 3:53 am | Permalink

    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

  • raj
    Posted 15 March, 2012 at 6:18 am | Permalink

    this would not work when two values are selected…is there any way to get this work when more than two values are selected for a parameter…

  • sam
    Posted 15 March, 2012 at 10:12 pm | Permalink

    Could you please help on this. I have Inv Num as Multivalue parameter, now one can print 10 Inv at times selecting values from Parameter. If I use the above approach I stumble upon an issue. Let say I am going to print 5 Invs. INv could have Two pages.. when a Inv have one page, it prints fine.. Inv 1 INv 2 INv3, now if INv4 has two page, then it prints Inv 4 on first page and prints Inv 5 on the next page of Inv 4…why this happens could you please help…

  • sdf
    Posted 27 March, 2012 at 4:40 am | Permalink

    Just set your header textbox to =First(Fields!ColName.Value, “ReportDataSetName”).
    Should work.

  • kranthi
    Posted 24 May, 2012 at 6:54 pm | Permalink

    Thnqks for code
    i want same code for image type

  • kranthi
    Posted 7 June, 2012 at 7:28 pm | Permalink

    I have to display the Signature (Image) and the name (text) of the one who has signated in the footer of my report.
    text coming fine. for Images its mot working any solution ??