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 a Shared Dictionary - SSRS

RSS
Modified on Fri, Jun 25, 2010, 2:07 PM by Administrator Categorized as SSRS (SQL Server Reporting Services)
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

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