SQL Server Reporting Services: Report Header cannot show data fields

It seems such a trivial issue, I can’t imagine why SQL Server Reporting Services Team has not addressed this. It seems that this problem was in SQL Server 2000 Reports and is carried through SQL Server 2005 Reports.

Unfortunately, there is no solution available for this and it becomes so irritating not been able to find a solution for this extremely common requirement. Though there are advantages of using Reporting Services, but just because of small issues/bugs like these I am regretting my decision of using SQL Server Reports in the first place.

One workaround to this suggested on many groups/lists is to bind data values to a report item like TextBox and access this report Item in the Header to show the value. The following funciton can be added as a custom code in the report to retrieve the value of any report item.

Public Function GetItemValue(ByVal Items As ReportItems, ByVal item As String) As String

return Items(item).Value

End Function

However, this solution is fairly useless becuase the ReportItem value is only retrieved in the header if the Report Item is on the smae page. So, for example if the TextBox which contains the value is on the first page, then only first page header will contain the value. This actually negates the actual purpose of using the Header/Footer when it can only be used on only single page.

To get rid of the problem, I had to pass all the values which I had to show in Header as Report Parameters.

Advertisements

16 thoughts on “SQL Server Reporting Services: Report Header cannot show data fields

  1. Slight mod to solutions posted, but uses a static variable. So once the Header data is set, it will not go null. I’m sure that you could make the “If…” logic more complex to detect whether a change has occurred that needs to be reflected on the page.

    Dim ReportNameHeader as String
    Function getReportNameHeader(rptItems AS ReportItems) as String
    If ReportNameHeader = “” then
    ReportNameHeader = rptItems(“REPORT_NAME”).Value
    End if
    return ReportNameHeader
    End Function

  2. Thanx, you save my life 🙂
    Just a little bit more specific:
    Main menu -> Report -> ReportProperties -> Code tab
    Insert this code

    Shared Dim ReportNameHeader as String
    Public Function getReportNameHeader(rptItems AS ReportItems) as String
    If ReportNameHeader = “” then
    ReportNameHeader = rptItems(”REPORT_ITEM_NAME”).Value
    End if
    return ReportNameHeader
    End Function

    ReportNameHeader must be declared as Shared

    Header textbox expression:
    Thanx, you save my life 🙂
    Just a little bit more specific:
    Main menu -> Report -> ReportProperties -> Code tab
    Insert this code

    Shared Dim ReportNameHeader as String
    Public Function getReportNameHeader(rptItems AS ReportItems) as String
    If ReportNameHeader = “” then
    ReportNameHeader = rptItems(”REPORT_ITEM_NAME”).Value
    End if
    return ReportNameHeader
    End Function

    ReportNameHeader must be declared as Shared

    =Code.getReportNameHeader(ReportItems)
    =Code.getReportNameHeader(ReportItems)

  3. Don’t ever store your values in Shared module level values. This data is global and NOT THREAD SAFE! You will have crossed data in your headers when running in a multi user environment.

  4. Any body have idea how to use this in multi user environment? I’m experiencing the same problem which Mark has addresssed. Any suggestions?
    I’m really stuck . any kind of help is appreciated.
    Tx
    Dharmen

  5. I have multi-page lists-within-lists and it only shows the correct value on the first page heading of the report – in my case a Period End date that is the same for all records. I used the following trick to save the value (in this case a date field) for subsequent page headings:
    A)
    In Report – Properties – Code
    Public Function StoreFirstValue_PeriodEndDateFromExtract(aDT as DateTime) as DateTime
    Static dtPeriodEndDateFromExtract as DateTime
    if dtPeriodEndDateFromExtract.Year < 999 then
    dtPeriodEndDateFromExtract = aDT
    end if
    return dtPeriodEndDateFromExtract
    End Function
    B)
    the text box uses the following expression:
    =”Period Ending ” & Code.StoreFirstValue_PeriodEndDateFromExtract(CDate(First(ReportItems(“textboxPeriodEndDateFromExtract”).Value))).ToString(“d MMMM yyyy”)

    HTH
    Brewmanz

  6. Will this work for multi user environment to blank out the shared variable after returning the value?:

    Shared Dim ReportNameHeader as String
    Function getReportNameHeader(rptItems AS ReportItems) as String
    If ReportNameHeader = “” then
    ReportNameHeader = rptItems(“invoiceKey”).Value
    End if
    return ReportNameHeader
    ReportNameHeader = “”
    End Function

    Please let me know.

  7. Slight addition to Aleem’s original solution of passing header values to the report via parameters.

    I still used parameters, but I created “internal” parameters that are not required to generate the report. Supply a query for their default value and forget about having to supply them prior to generating the report.

  8. I want to add a “report header”, not a “page header”, because I want to see some specific info on the first page only but I don’t know how to add it. Can you help me?

  9. I do this by making complex table or group headers and turn of the ability to repeat on each page.

    This is the only way I make report header/footers. Nest or use mutliple table if necessary, you should get it work in most cases.

    Cheers,
    BB

  10. I need to be able to add a “From: ” date and a “To: ” date in my report. I want to be able to print the users selection criteria on the report, but i need it to be in the page header.

    Thanks,
    Philip

  11. Hi I’m not sure how to call

    Public Function GetItemValue(ByVal Items As ReportItems, ByVal item As String) As String

    return Items(item).Value

    End Function

    = GetItemValue (?1 , ?2)

    ?1 – would this be the Textbox containing the value
    ?2 – No idea what this should be

  12. Justin, you left your response a long time ago, but THANK YOU THANK YOU THANK YOU for the “Internal Parameter” solution to this problem.

    Turns out it was exactly what i was looking for, and it works like a charm.

  13. Justin, thanks a lot, Internal parameters helped me too! :-)they can have a default value based on a field in a dataset, which depands on another, normal, parameter, so that I get the info from the database I need for the current user, hippi! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s