Printing Forms using a Shared Dictionary - SSRS

The technique documented below has been shown to suffer from intermittent problems
in a multi-user environment. It is therefore NOT recommended for such cases.

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 a shared Dictionary variable.

Walkthrough¶

(1) Add the code from the Source Code section below into your report.

In the Source Code section below, click the Copy Code button.
In your report, navigate to the Report menu > Report Properties > Code tab.
Paste the code into the Custom code textbox.

(2) Create a Table control in the Body of your report, setting its properties as follows. This table will be used to cache field values.

DataSetName = the name of the dataset from which every field value in the table will be pulled. Typically, this is a header table.
Visibility > Hidden = True

(3) For each field value you want to use in the Page Header or Page Footer of the report, add a cell in the table with the following code.

=Code.CacheSet("SessionId", "Key", Fields!FieldName.Value)

A typical value for SessionId is User!UserID+Globals!ExecutionTime or Parameters!UserID.Value+Globals!ExecutionTime. (This parameter is necessary to keep different users/sessions from overwriting each other's values in the shared Dictionary object.)
Note that Key and FieldName are typically the same, but don't have to be.
FieldName is CASE-SENSITIVE (it must match the SQL statement), but Key is case-insensitive.
To format field values (e.g., a date), use the following code instead.

=Code.CacheSet("SessionId", "Key", Format(Fields!FieldName.Value, "MM/dd/yyyy"))

(4) For each field value desired within the Page Header or Page Footer, place a TextBox control with the following code.

=Code.CacheGet("SessionId", "Key")

(5) Anywhere in your custom code you want to retrieve the value of any of the cached field values, imitate the following code sample.

Dim x As String = CacheGet("Key")

Source Code¶

{copytext|div1}

Private Shared _dict As System.Collections.Generic.Dictionary(Of String, String)
'--------------------------------------------------------------------------------------------------
Public Shared Function CacheSet(ByVal sessionId As String, ByVal name As String, ByVal value As _
String) As Boolean

    Dim result As Boolean = False
    Try
        name = sessionId.ToUpper() & "|" & name.ToUpper()
        If value IsNot Nothing Then
            CreateDictionary()
            If _dict.ContainsKey(name) Then
                _dict.Item(name) = value
                result = True
            Else
                _dict.Add(name, value)
                result = True
            End If
        End If
    Catch ex As Exception
        ' ignore exceptions
    End Try

    Return result

End Function
'--------------------------------------------------------------------------------------------------
Public Shared Function CacheGet(ByVal sessionId As String, ByVal name As String) As String

    Dim result As String = ""

    Try
        name = sessionId.ToUpper() & "|" & name.ToUpper()
        CreateDictionary()
        If _dict.ContainsKey(name) Then
            result = _dict.Item(name)
        End If
    Catch ex As Exception
        ' ignore exceptions
    End Try

    Return result

End Function
'--------------------------------------------------------------------------------------------------
Private Shared Sub CreateDictionary()
    If _dict Is Nothing Then
        _dict = New System.Collections.Generic.Dictionary(Of String, String)
    End If
End Sub