Introduction SQL Server 2008 Reporting Services (SSRS 2008) features an on-demand report processing engine. This on-demand architecture has a number of key advantages over the processing engine design that existed in previous major releases. The most significant benefits are vast improvements to report engine scalability and performance (you can read a bit more about it here). Because of this fundamental change from previous versions, there are some specific design patterns that have changed. This post is a discussion of one scenario that, due to the new processing engine, requires a different design pattern in 2008 than was required in 2005 and 2000.
Special thanks to my esteemed colleagues Chris Baldwin and Chris Hays, who helped with the contents of this posting. Note: screenshots and how-to steps in this post are based on the currently available SQL Server 2008 RC0 release of Report Builder 2.0. Future releases of this product may change. The attachment at the bottom of this posting contains both, a 2005 version and a 2008 version of the final reports. The reports are based on the Northwind sample database (download link).
On-Demand Report Processing The new processing engine in Reporting Services 2008 still retrieves datasets upfront, but only pre-computes certain invariants, such as grouping, sorting, filter expressions, aggregates, subreport parameters and queries. Everything else are "on-demand" evaluated expressions; most notably, textbox values, and style expressions.
Furthermore, the processing engine now exposes a cursor-based report structure as so-called RenderingObjectModel. Rendering extensions, responsible for translating the processed report to the desired output format, traverse the report using a hierarchy of RenderingObjectModel cursors. This is in contrast to the processing engine in 2005 and 2000 in which the entire report was fully processed upfront. A couple implications of this on-demand model are that a) objects are evaluated hierarchically throughout the report, b) hidden textboxes are not evaluated, and c) the concept of Report and Group Variables has been introduced
Report and Group Variables
In Reporting Services 2008 / RDL 2008/01 namespace, one can declare variables that are global throughout the report or local to particular group scopes and refer to them in expressions. Report and group variables can only be set/initialized once and have a read-only semantics.
Typical use cases for variables include:
The latter use case of group variables will be discussed in more detail in the remainder of this blog posting to implement custom aggregation in a Reporting Services 2008 report.
Custom Aggregate Scenario The scenario discussed is one where a report author implements a custom aggregate, illustrated by an implementation of a Median function. A common pattern for implementing a custom aggregate such as Median in Reporting Services 2005 is like this.
With the custom code for GetMedian and AddValue as follows:
Dim values As System.Collections.ArrayList
Function AddValue(ByVal newValue As Decimal)
If (values Is Nothing) Then
values = New System.Collections.ArrayList()
Function GetMedian() As Decimal
Dim count As Integer = values.Count
If (count > 0) Then
GetMedian = values(count / 2)
What happens here in SSRS 2005 is that for each instance of the detail row, the value gets passed to AddValue() and then added to the values ArrayList. A textbox in the Table header, then, makes a call to GetMedian() which performs a calculation on the values in the ArrayList, and displays it. It's important to note that this wasn't exactly supported in SSRS 2005 and it wouldn't even work properly in most cases. For example, if you were to add end-user sorting to the table, then the processing would go through a different code path that would evaluate the headers before the details. This would mean that the GetMedian() function would be called before AddValue has a chance to add any values are added to the ArrayList. It just so happens that in this particular case, when there is no end-user sort, the details are processed first. Whether or not it was officially supported, a number of people got this to work and are relying on this behavior. In order for the same pattern to work in SSRS 2008, the report needs to be slightly redesigned. Detailed, step-by-step, instructions are provided below. Note that the pattern of using group variables outlined below is not limited to custom aggregation, but can be expanded into more complex solutions. We can show you the path and the pattern, but you will have to apply it to your unique situation. YMMV (your mileage may vary).
Implementing this in SSRS 2008: Step-by-Step The report needs to be slightly revamped in 2008 in order for this to work. The custom code itself, however, doesn't have to change at all. This is going to be a step-by-step procedure by which you can port this pattern from your 2005 report to 2008. Note that the attachment at the bottom of this post contains a 2005 report and a 2008 report, both implementating this custom aggregation approach.
Note: this posting provides an overview of when to consider using Report Variables and/or Group Variables,
You may have heard that the report processing engine of Reporting Services 2008 works fundamentally different