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.


(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.


(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)


=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