SSRS: Using DataSet fields in page header/footer

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 dsProjectName in which you select the desired information (in this case, the ProjectName column) 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 dataset dsProjectName and then set the value field to ProjectName. Also, make sure this new parameter is below the ProjectID parameter to allow cascading.

Adding a parameter

You can now use this new ProjectName paramater anywhere in your page header textbox expressions.

About these ads

19 Responses to “SSRS: Using DataSet fields in page header/footer”

  1. SSRS: Another way to use DataSet fields in page header/footer « Mehroz’s Experiments Says:

    [...] Mehroz’s Experiments My computational experiments to be posted here « SSRS: Using DataSet fields in page header/footer [...]

  2. roxy Says:

    thanks.

  3. MJ Says:

    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

  4. JGreen Says:

    Thank You!!!!!

  5. m Says:

    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.

  6. NewSSRSUser Says:

    excellent solution..cannot believe I never thought of it.. have been struggling for 2 days!

  7. Dave Says:

    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

  8. the.wizard Says:

    to m, thank you so much… you have just save my day… God bless you… :)

  9. Tom Regan Says:

    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).

  10. T. Bow. Says:

    Hey Tom, did you ever find a solution?

  11. Tom Regan Says:

    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.

  12. T. Bow. Says:

    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.

  13. T. Bow. Says:

    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.

  14. T. Bow. Says:

    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:

  15. santosh Kumar Says:

    Thanks u very much. this post is helpful to me….

  16. Marty Says:

    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?

  17. Gopal Says:

    Excellent solution Mr. Tom Regan. Thanks a lot. worked great.

  18. Nikhila Says:

    Thanks for your help Tom.. :)


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

Follow

Get every new post delivered to your Inbox.

Join 52 other followers

%d bloggers like this: