Most medium to large sized businesses have a reporting solution that provides timely financial information in a user-friendly format for sales people, managers and customers. Usually, the “solution” is a dedicated reporting employee who manually queries source databases for statistical data.
This paper offers two software specific solutions to overcome limitations exposed in Microsoft SQL Server’s Reporting Services. These solutions offer the benefit of providing employees with the ability to quickly generate dynamic report templates and focus their energy on future enhancements instead of continuous maintenance.
(Note: This article applies to Microsoft SQL Server Reporting Services 2000 w/SP4.)
The Framework
Microsoft SQL Server Reporting Services provides the development environment needed to develop report templates and to parse, retrieve and display the data through Internet Information Server (IIS).
The Reporting Services development environment is a Visual Studio (VS) application that provides report specific methods and functions, including two-dimensional tables, multi-dimensional matrices, list boxes, dropdown selection boxes and other visual report tools.
Reports are created with VS, uploaded to the Reporting Services server and then accessed through IIS by end-users.
Management of developed reports is restricted to the Reporting Services Web site, which lets you configure scheduled report deliveries through subscriptions, manage the security of individual reports and display basic information. Bug fixes, enhancements or further development must take place within the development environment.
The Limitations
To understand the restrictions inherent in Reporting Services, one must have an understanding of the customer’s report requirements. In most cases, the tools and methods provided by Reporting Services are sufficient to author typical reports. However, in specific circumstances the report requirements can eclipse the built-in tools and methods. One of these specific circumstances occurs when the source data on which a report depends consists of multidimensional data, such as the data retrieved from OLAP cubes. This data is often horizontally dynamic, which means that the number, format and name of the columns in an explicitly named, two-dimensional result set can be different from any other result set within the same report.
For example, in a standard table format, rows represent individual entities, whereas columns represent characteristics of the rows. In an “Inkjet Product” table, each row may denote an individual hardware component, such as a printer cartridge. Each column in that row would denote a characteristic of that component, such as “manufacturing date,” “size” or “cost.” In a “LaserJet Product” table, there may be similar columns, but there may also be additional columns to denote different characteristics.
In multidimensional structures, we are nesting these separate datasets hierarchically. Each product may belong to a product category, a product line, a sales channel or a geographic region. We are also nesting these datasets within a time hierarchy, which adds the key limitation that my solutions will resolve.
Fig. 1 In this example of a nested product hierarchy, “A” is the top level of the Product Line, “B” is the Product Category, “C” is the Product Sales Motion, “D” is the Product SKU and “E” is the current inventory. Each D-level Product SKU could belong to any, all or none of the C-level Sales Motions.
The limitation of Reporting Services appears when the concept of a “rolling average” or “rolling window” is introduced. This concept is quite simple and very common in financial reports. Basically, the idea is to provide a dataset that encompasses data from an ever-changing range of dates. If I were to ask for a list of the dates included in the “Last 13 weeks of sales,” the result set would be a static list: “2005 Week 50,” “2005 Week 51,” “2005 Week 52,” “2006 Week 1,” “2006 Week 2,” and so on. If I were to ask for the same list a month from now, it would be different: “2005 Week 50” would fall off the list and a new one would be added.
Fig. 2 In this report of the “Last 4 Weeks,” each date in column “A,” “B,” “C” and “D” are valid today. But what about a week from now? Column A would no longer be the oldest, or appear on the report. Each column and its corresponding data rows shift to the left each week.
A Reporting Services template does not allow for this. When a report is requested and rendered through Reporting Services, a dataset is retrieved from the database. At this point, the dataset is a table with statically named columns based on the data available in the OLAP cube at the exact time the report is made, so each time you render the report, the columns could potentially change.
This limitation would make manual authoring imperative for a report with horizontally dynamic datasets. For each time period, an employee must manually edit the report definition to include the latest date and remove the oldest date. Then the employee must re-render and upload the newly modified report.