Load SQL Function in SSRS Header or Footer

Today I had an interesting problem that I believe is common with SSRS so I wanted to share my solution.  I had a new requirement to display the result of a SQL function into the header of a report.  Quickly, I found out that you cannot drag a dataset item into the header.  To solve this problem I created a new internal parameter that obtains its default value from the dataset I wanted to display on the header.

In this example I have an Id that is passed into the report as a parameter. I call a SQL function in a dataset that displays a different identifier based on the criteria inside the SQL function.    Below is an image that displays the setting I used for the internal parameter.

image

Once this parameter exist I was able to use the following code in the header section to display your data returned from the SQL function.

Parameters!ConflictReportId.Value