Page History: Printing Forms using Report Parameters - SSRS

Compare Page Revisions

2 1 0 Current 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Fri, Jun 25, 2010, 9:12 AM

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

(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

    ...