Prologika Forums
Making sense of data
Maintaining State in Reporting Services 2008

Blogs

Prologika (Teo Lachev's Weblog)

Books

Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling)Learn PowerPivot and Analysis Services-Tabular at your own pace with our latest book Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling). It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Training

Applied Microsoft SQL Server 2008 Reporting Services

We offer onsite and online Business Intelligence classes!  Contact us about in-person training for groups of five or more students.

For more information or to register click here! 

Syndication

Archives

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


Posted Tue, Jun 30 2009 9:10 AM by tlachev

Comments

samsonfr wrote re: Maintaining State in Reporting Services 2008
on Thu, Jul 2 2009 1:13 PM

Thanks for sharing this with us. I was not aware of the multi-paging behavior.

Shouldn't you protect yourself against multithreaded issues? Multiple threads can access your static hashtable at the same time, shouldn't you use locks?

tlachev wrote re: Maintaining State in Reporting Services 2008
on Thu, Jul 2 2009 4:19 PM

Samsonfr,

Yes, it turns out that even if the hashtable key is user-specific and we do only adds and deletes (not updates to items from other users), we do need to make access to the hashtable collection thread-safe. I updated the blog. Thanks for pointing this out!

joftingson wrote re: Maintaining State in Reporting Services 2008
on Wed, Aug 26 2009 4:53 AM

Hi,

Is there also a special method like On_Unload that can be used inside a custom code?

Thanks,

Joefer

tlachev wrote re: Maintaining State in Reporting Services 2008
on Wed, Aug 26 2009 7:51 AM

Nope, no such method

ASP808 wrote re: Maintaining State in Reporting Services 2008
on Tue, Jun 22 2010 4:10 PM

Hi, need some further detail.

What should the expression be for the text box that is storing the data?

And what should the expression be for the text box that is retrieving the data?

What is id and column ?

Where should the rest of the function be called? such as the onInit() OR is it called automatically on each render?