SQL Server Reporting Services does not allow you to refer to DataSet fields in page header/footer. Sometimes, you really need this feature. For example, You are displaying some data related to a Project and the Project is to be selected from the user via a report parameter. Now typically, you want to display the Project Name in the header of every page. Here’s a hack on accomplishing this:
- Create a dataset
dsProjectNamein which you select the desired information (in this case, theProjectNamecolumn) of the selected Project. A simple query for this could be:
Select ProjectName from Project where ProjectID = @ProjectID - Create a new internal parameter
ProjectName, set its default value to get the data from datasetdsProjectNameand then set the value field toProjectName. Also, make sure this new parameter is below theProjectIDparameter to allow cascading.
You can now use this new ProjectName paramater anywhere in your page header textbox expressions.
December 7, 2008 at 2:50 PM
[...] Mehroz’s Experiments My computational experiments to be posted here « SSRS: Using DataSet fields in page header/footer [...]
March 23, 2009 at 1:04 PM
thanks.
September 27, 2010 at 3:15 PM
hi I have one query here: How can we repeat this header on every page on the report? by default report header and footer is displayed only on the first page. I want to display header on each page with different project names.
Please suggest.
Thanks
April 15, 2011 at 1:34 AM
Thank You!!!!!
May 30, 2011 at 7:01 PM
Although SSRS does not allow us to use DataSet fields in page headers but it allows us to refer to report items. So we could place a textbox (that takes its value from a DataSet field) anywhere in our report’s body and set its Hidden property to true. Then, we could easily refer to that textbox in the page header with an expression like: =ReportItems!TextBox1.Value and we are done. Note that the textbox that is being referred should be present on every page, or otherwise the header will print empty value.
June 11, 2011 at 3:11 AM
excellent solution..cannot believe I never thought of it.. have been struggling for 2 days!
August 12, 2011 at 9:03 AM
as m said: You can refer to the ReportItems. I needed a solution to show a couple of text boxes in the first page header only. I included a hidden column in the report called RowNum that was simply the ROW_NUMBER() in my SQL. I called that cell RowNum, and then in the property of my page header text box I put “=(First(ReportItems!RowNum.Value)>1)”
The above formula picks up the first Rownum on the page, which is only 1 on the first page. You can use this to add the word ‘cont…’ noto groups/headings going over more than 1 page
January 12, 2012 at 1:47 PM
to m, thank you so much… you have just save my day… God bless you…
February 10, 2012 at 6:31 PM
Has anyone solved this problem when your report contains a sub-report that extends to muliple pages? For example I have a report with the field “FULL_NAME” in the body, and a subreport in the body. In the page footer I reference ReportItems!FULL_NAME.Value. This displays fine when the sub-report does not push to multiple pages, but when it does it returns an empty string (on all pages, even the first).
March 12, 2012 at 4:41 PM
Hey Tom, did you ever find a solution?
March 12, 2012 at 5:17 PM
Yes, had to use “custom code”. Eg if you want to display something called FullName in the page footer, add this to custom code (right-click the yellow background of the report, select Report Properties, Code):
Public Shared Dim FullName As String
Public Function SetFullName(ByVal val As String)
fullName = val
End Function
In report body place an invisible textbox, and give it an expression that calls the above method, passing it the field you want to display, eg “=Code.SetFullName(Fields!FULL_NAME.Value)”.
In page footer refer to “Code.FullName” to display it.
March 12, 2012 at 5:59 PM
Does it look like I’m doing it right?
http://imgur.com/a/Xjq2X
I’m generating about 9 pages worth of reports (The master report has a CSV parameter of guids, which gets parsed through the database and send to the sub-report).
All of the sub-reports data returns great, I just need this one field to come up right in the footer and it’s not!
Using the method in the blog above (the hidden parameter), the first record of the dataset shows on each report footer.
Using the code you showed me, the last record of the dataset is showing!
I know I’m really close.
March 12, 2012 at 6:05 PM
When using this (without the quotes) it’s showing the last record in the dataset:
=Code.SetName(Fields!EmployeeName.Value)
My screen shot shows quotes, I’ve been trying different things.
March 12, 2012 at 6:47 PM
I figured it out!
I ended up adding 2 new rows to the table (1 containing the field which sets the Employee Name, 1 which displays it but is hidden).
Then in the Footer, I make my textbox refer to the hidden field in the table row:
March 12, 2012 at 6:47 PM
Last Picture:
http://imgur.com/a/Xjq2X
July 27, 2012 at 12:10 PM
Thanks u very much. this post is helpful to me….
April 5, 2013 at 8:04 PM
T.Bow, I am having a similar dilemma. I have a report with multiple subreports that are all tied to a store number and grouped as such. I need the store number to print on the top of each page. When I try your solution it displays fine when viewing on SSRS but when I export to PDF all the headers are blank except for the last page in the store grouping. Anyone have any thoughts?
April 24, 2013 at 1:59 PM
Excellent solution Mr. Tom Regan. Thanks a lot. worked great.