Mastering OLAP Reports: Extend Reporting Services with Custom Code - Page 2

Extending Reporting Services with Embedded Custom Code

Objective and Business Scenario

As any regular reader of my MSSQL Server Reporting Services series is aware, I often evangelize about the highly flexible capabilities of Reporting Services, and about how the myriad options exposed within the application make it the leader among enterprise reporting tools. While many of the once-dominant applications robustly supported either relational (for example, Cognos Impromptu) or OLAP (such as Cognos PowerPlay / Transformer) reports, none provided for both types of data sources (much less several of both types) within a single report. Among many other limitations, these expensive solutions were often limited in the types of custom coding that could be easily added into the mix to obtain a precise result to meet specific information consumer needs. Although most allowed the addition of some external functions, the number of properties exposed within their report development environments – individual properties to which such external coding might be applied - did not approach the number of accessible properties within even the simplest Reporting Services scenarios.

The expression-based nature of the majority of report items in Reporting Services (and “accessible” is the key concept here – that is, “individually exposed for control by custom code”), means we are able to meet myriad needs within simply selected value expressions, with which we can combine large libraries of functions especially designed to meet specific needs. In cases where our needs exceed even the wide range of expressions available within the commonest reports, however, Reporting Services can be extended yet further in a couple of ways. In short, we can add custom functions to our report or we can add references to functions in external assemblies. In both cases, this extended code can be used to perform complex functions or functions that need to be performed multiple times within a given report. Moreover, access to code assemblies can mean sharing sophisticated functions across multiple reports – code that can be managed centrally, and which shares a single point of maintenance.

Our focus in this article will be upon the former option, the use of embedded code. To set the stage for our practice session, we offer the following hypothetical scenario and business requirement: Let's say that representatives of the Controller's Group of our client, the Adventure Works organization, have asked us to assist them in gathering business requirements to meet a new report presentation need. Various members of management have asked that a clone of the existing Sales Reasons report (with which many consumers are already pleased) be modified to make it more compact and focused. They have asked that the new report reflect only the single measure, Internet Sales Amount, and that the columns for the report, currently reflecting Sales Territory Groups, display the underlying Sales Territory Countries instead.

Moreover, because they realize that the “drill down” of the column headings to Countries will mean expanded report width, the managers have asked that the labeling for the associated Sales Territory Countries be shortened to a code – a simple two-letter abbreviation. In addition, management agrees that it would like to see a two-letter abbreviation for the respective Sales Territory Group prefixing each Country code, separated from it by a single hyphen (“-“). The representatives of the Controller's Group tell us that they will produce a list of the codes as they are typically used within other presentations.

Part of the reason that we wish to generate the proposed codes within the modified report is to support the confirmation of the business requirement from management. To assist in gaining their concurrence with the appearance of the column headers within the report, we propose that we add logic to the report to generate the new labels. More specifically, we suggest that we use embedded code within this OLAP report as a means of generating a prototype to confirm with management, as well as to demonstrate the process for using embedded code to apply this conditional logic within the report.

We emphasize to the client representatives that, once we have determined that the codes meet the requirement, we should place the codes themselves, or perhaps the logic that generates them, at a lower level within the integrated business intelligence solution. While embedding code is great for generating conditional outcomes such as this at the report level, especially for prototypical purposes (or in scenarios where we have no choice but to work within the report layer, such as cases where we might not be able to access the database or OLAP layers), report processing would almost certainly be more optimal were we to install the logic or the name in a lower layer. Upon making our convictions clear, in this specific case, the client assures us that, once they ascertain that the representative Country codes fit the requirement, they will seek our assistance in embedding the mechanics for the generation of those codes in a lower level.

Another consideration that arises, and for which we have a convenient opportunity to demonstrate an approach, will be will be a need to add more than one function via embedded code within the relevant report properties: one exception to the general assignment of the specified number of characters to each Sales Territory Country, our client colleagues tell us, is that management prefers to see the label for home operations (Sales Territory Country United States) as simply “USA.” This means that we can provide conditional logic to assign Country codes to all Countries, except the United States, within the first function we create. Within our first function, we do not make allowance for Country “United States,” in effect leaving it to be treated as an exception, for which its initial value is returned. In the second method / function, we will apply the different Country code format of three letters for that specific exception. Finally, having assigned names to both functions, we will reference them within Layout view, where we will apply the action of the embedded code to our Country labels, as we shall see.

Once we obtain a verbal description of the labeling needs contained within the immediate report specification, we seek to confirm our understanding by requesting that the client produce a quick list of the Sales Territory Countries, alongside their corresponding proposed labels. The result of this exercise is presented within Table 1.