Printing Forms using Report Parameters - SSRS

Overview

SQL Server Reporting Services does not permit placing field values in the Page Header and Page Footer sections of a report, at least not with the Field!FieldName.Value syntax. This article explains a work-around for this in SSRS 2005 by caching the header data in hidden report parameters.

Walkthrough

(1) Modify your SQL statement for the header records. Modifying your SQL this way avoids the error message Parameter 'ParameterName' is missing a value when the original SQL finds no records. Note the addition of the HasData column. You can use this column to easily check if your SQL is returning a legitimate row or the "dummy" row.

From To
SELECT
     OrderId
    ,ShipDate
    ,SoldToId
    ,BillToId
    ...

FROM OrderHeader

WHERE 1=1 AND OrderId = @OrderId
with MyData as (
    SELECT
         OrderId
        ,HasData = 1
        ,ShipDate
        ,SoldToId
        ,BillToId
        ...
    
    FROM
        OrderHeader
    
    WHERE 1=1
        AND OrderId = @OrderId
    )
SELECT * FROM MyData
UNION SELECT
     OrderId  = ''
    ,HasData  = 0
    ,ShipDate = getdate()
    ,SoldToId = 0
    ,BillToId = 0
    ...
WHERE NOT EXISTS (SELECT 2 FROM MyData)

(2) For each header field needed in the Page Header or Page Footer, create a Report Parameter.


Report Parameters Dialog

Report Parameters Dialog


(3) Wherever you need a header field value in the page header or footer, create a textbox and set its value to an expression of the form =Parameters!ParameterName.Value.

(4) To use the value of a report parameter in custom code, you need to submit the Report Parameters collection as a parameter to your custom function.


Private Shared Function MyFunction(ByVal p As Parameters, ByVal fieldName As String) As String

    ...

    Dim s As String = p(fieldName).Value

    ...