Mastering OLAP Reports: Extend Reporting Services with Custom Code

February 19, 2007

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (“Reporting Services”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to “early adopters” of Reporting Services is that the “knowledgebase” for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions (such as various applications offered by Cognos, Business Objects, and the like) can be met, and exceeded in most respects, by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious “undersell” of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

For more information about the Mastering OLAP Reporting articles, see the section entitled “About the Mastering OLAP Reporting Articles” in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

As I have emphasized throughout the articles of the series, the most powerful characteristic of Reporting Services is the unprecedented flexibility it offers us in creating reports specifically tailored to our business environments. It affords us the capability to innovatively employ one or more datasets in supporting myriad options, to use data groups and report items in all manner of combinations, and to extend data reporting with many features, from basic to advanced, including calculations, conditional formatting, and other options.

Because our reports are expression-based, we have a great deal of control in getting the precise operation and presentation that we need. Moreover, when the business requirements call for even greater horsepower, we can design reports to process more complex logic through the introduction of custom functions, which we can leverage from within property expressions to obtain just the results that we desire.

In this article, we will explore one approach to adding custom code to our reports. We can embed Visual Basic .NET functions that we define to control a large number of report items in the manner that we will explore. Along with the expanded capabilities that this option offers us, the benefits of reusability also accrue: we can reference embedded code from multiple places in the report. (Even more extensive options are available when we access .NET assemblies: these external custom assemblies can be shared by multiple reports via references that we add to the report properties. .NET assemblies can also be built with any .NET language option, and are thus not limited to Visual Basic .NET. We explore the use of .NET assemblies within other articles of this series.)

In this article we will gain some familiarity with using embedded custom code – how and why we might turn to this option - and then get some hands-on exposure to adding custom code within a sample report that is available to anyone who installs Reporting Services 2005, along with the supporting Analysis Services 2005 Adventure Works DW sample database. As a part of our examination of embedded code in this article, we will:

• Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
• Create a clone of an existing sample OLAP report, containing a Matrix data region, with which to perform our practice exercise;
• Make structural modifications to the clone report, including direct modifications to the MDX query underlying the primary dataset, to prepare for our practice exercise session with custom code within the Reporting Services development environment;
• Create, within the Code tab of the Report Properties dialog, two custom function definitions to meet the business requirements of hypothetical information consumers;
• Reference the new custom functions from within properties of report items on the Layout tab;
• Preview the report to observe the conditional logic of the custom functions in action;
• Discuss the interaction of the various components in supporting the runtime application of conditional logic resulting in effects that the end consumer sees;
• Discuss the results obtained with the development techniques that we exploit.