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.
Aleem's Weblog 
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
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)
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.
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
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
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.
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.
I doubted where i must declare
=Code.getReportNameHeader(ReportItems)?
thanks.
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?
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
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
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
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.
@Natalie
=code.GetItemValue(ReportItems, “your item name here with quotes”)
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! 🙂
Justin, Thanks. Although it is coming three years after you posted the solution, this works really well.