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.
Sales Territory Country
|
Desired Label
|
France
|
EU-FR
|
Germany
|
EU-GM
|
United Kingdom
|
EU-UK
|
Canada
|
NA-CA
|
United States
|
USA
|
Australia
|
PC-AU
|
Table 1: The Proposed
Sales Territory Country Labels