Sometimes, more advanced reporting needs may require maintaining state using custom code. Recently I had to implement a rather involved report consisting of two sections: a detail section that used a recursive sum, Sum(Fields!SomeField.Value, , True), and a summary section with additional calculations which would reference some of the aggregated values in the detail section. Since Reporting Services is not Excel, you cannot reference arbitrary fields on a report. So, I had to cache the values from the detail section in a hashtable using custom code so the summary section could obtain these values when needed. The following embedded code gets the job done:
Friend Shared _hashtable As New System.Collections.Hashtable()
Function GetKey(ByVal id as Long, ByVal column as String) as String
return Report.User!UserID & id & column
End Function
Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object
Dim key as String = GetKey(id, column)
If _hashtable.ContainsKey(key)=False Then
_hashtable.Add(key, value)
End If
Return value
End Function
Function GetValue(ByVal id as Long, ByVal column as String) As Object
Dim key as String = GetKey(id, column)
If _hashtable.ContainsKey(key) Then
Return _hashtable(key)
End If
Return Nothing
End Function
Protected Overrides Sub OnInit()
Dim keys As String() = New String(_hashtable.Keys.Count – 1) {}
_hashtable.Keys.CopyTo(keys, 0)
For Each key as String In keys
If key.StartsWith(Report.User!UserID) Then
_hashtable.Remove(key)
End if
Next key
End Sub
In my scenario, tablix cells in the detail section would call the AddValue function to cache the dataset field values. Then, cells in the summary section would call GetValue to obtain these cached values. This technique is not new and you’ve probably used it to solve similar challenges.
What’s new is that moving to Reporting Services 2008 you may need to revisit your custom code and make changes. First, in prior version of Reporting Services, you probably declared the hashtable variable as an instance variable and that worked just fine. However, Reporting Services 2008 introduced a new on-demand processing engine where each page is processed separately. Consequently, you may find that the report “loses” values. Specifically, if the report fits on one page, everything will work as expected. But if the report spills to more pages, the hashtable collection will lose the values loaded on the first page. The solution is to declare the hashtable object as a static (Shared in Visual Basic) object so it survives paging.
Friend Shared _hashtable As New System.Collections.Hashtable()
But because a static object is shared by all users running the report, you need to make the hashtable key user-specific, such as by using the User!UserID variable which returns the user identity.
Function GetKey(ByVal id as Long, ByVal column as String) as String
return Report.User!UserID & id & column
End Function
Are we done? Well, there is a nasty gotcha that you need to be aware of. If the report takes parameters, you will surprised to find out that the custom code returns the cached values from the first report run and changing the parameters (report data) doesn’t change the values in these cells that call the GetValue function. To make the things even more confusing, testing the report in the BIDS Report Designer or Report Builder 2.0 will work just fine. But you will get the above issue after you deploy and run the server report, such as when you request the report in Report Manager or SharePoint.
What’s going on? As it turns out, the hashtable object survives report requests as a result of parameter changes. Consequently, the code that checks if the hashtable key exists will find the key when the report is reposted and it will not add the new value.
If _hashtable.ContainsKey(key)=False Then
_hashtable.Add(key, value)
End If
The solution is to clear the user-specific hashtable items each time the report is run. This takes place in the OnInit method. The OnInit method is a special method which gets executed before the report is processed. You can use this method to do initialization tasks or, in this case, clear state held in global variables.
Are we there yet? Almost. As the reader samsonfr pointed out in a blog comment, we’ll need to make this code thread-safe because the chances are that multiple users may be running the report at the same time so concurrent threads may need to write and read to/from the static hashtable variable at the same time. As the Hashtable documentation explains “Hashtable is thread safe for use by multiple reader threads and a single writing thread. It is thread safe for multi-thread use when only one of the threads perform write (update) operations, which allows for lock-free reads provided that the writers are serialized to the Hashtable. To support multiple writers all operations on the Hashtable must be done through the wrapper returned by the Synchronized method, provided that there are no threads reading the Hashtable object. Enumerating through a collection is intrinsically not a thread safe procedure. Even when a collection is synchronized, other threads can still modify the collection, which causes the enumerator to throw an exception. To guarantee thread safety during enumeration, you can either lock the collection during the entire enumeration or catch the exceptions resulting from changes made by other threads.”
Although the code adds and removes user-specific values only (the collection key uses User!UserID), Robert Bruckner from the SSRS dev team clarified
“Everytime you have code like this in a multi-threaded environment, there is potential for a race-condition:
if (!_hashtable.ContainsKey(“abc”)) _hashtable.Add(…)
In this case you have to take a lock to ensure ContainsKey and Add are run as an atomic piece of code.”
Keeping this in mind, my proposal for making the code thread-safe code follows (concurrent experts, please comment if you find anything substantially wrong with my attempt to make a hashtable access thread-safe).
Friend Shared _hashtable As New System.Collections.Hashtable()
Dim _sht as System.Collections.Hashtable = System.Collections.Hashtable.Synchronized(_hashtable)
Function GetKey(ByVal id as Long, ByVal column as String) as String
return Report.User!UserID & id & column
End Function
Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object
If id = -1 or id = -2 or id=-3 Then
Dim key as String = GetKey(id, column)
If _sht.ContainsKey(key)=False Then
_sht.Add(key, value)
End If
End If
Return value
End Function
Function GetValue(ByVal id as Long, ByVal column as String) As Object
Dim key as String = GetKey(id, column)
If _sht.ContainsKey(key) Then
Return _sht(key)
End If
Return Nothing
End Function
Protected Overrides Sub OnInit()
SyncLock _hashtable.SyncRoot
Dim keys As String() = New String(_hashtable.Keys.Count – 1) {}
_hashtable.Keys.CopyTo(keys, 0)
For Each key as String In keys
If key.StartsWith(Report.User!UserID) Then
_hashtable.Remove(key)
End if
Next key
End SyncLock
End Sub
