Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Printing Forms using Report Parameters - SSRS

RSS
Modified on Fri, Jun 25, 2010, 2:11 PM by Administrator Categorized as SSRS (SQL Server Reporting Services)

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.

  • Make the parameter Hidden.
  • Set the Default Value from the header dataset.
  • Make the parameter's name the same as the dataset's field name.
  • Be sure to set the data type appropriately.

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.

  • In your textbox, specify a value of =Code.MyFunction(Parameters)
  • In your custom code, declare your function to accept a parameter of type Parameters (See below).
  • Retrieve the parameter value using the form p("paramName").Value, where p is the Parameters function parameter (See below).

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

    ...

    Dim s As String = p(fieldName).Value

    ...

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.