SSRS: Another way to use DataSet fields in page header/footer

I previously blogged about a method to refer to DataSet fields in page header/footer here. This time, I will present another way to achieve this.

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.

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.

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