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, the
ProjectNamecolumn) 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
dsProjectNameand then set the value field to
ProjectName. Also, make sure this new parameter is below the
ProjectIDparameter to allow cascading.
You can now use this new
ProjectName paramater anywhere in your page header textbox expressions.