Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services

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.

Introduction

In Positing
the Intelligence: Conditional Formatting in the Analysis Services Layer
(a recent member of my Database
Journal
Introduction to MSSQL
Server Analysis Services
series), we introduced conditional formatting in
the Analysis Services layer of the integrated Microsoft business
intelligence solution. We noted, there as well as elsewhere throughout my
series’, that an important consideration, when designing a business intelligence
system within any environment, is “where to put the intelligence” among the various
“layers” within the system.  I have stated many times in the past, in both
articles and presentations, that “multi-layered reporting solutions require
multi-layered architects.” Nowhere is this more apropos than within the design
and implementation of the integrated Microsoft business intelligence
solution
.  A “typical” implementation for me includes the following:

  • MSSQL
    Server:
      An RDBMS
    layer
    , consisting usually of both relational / OLTP data sources and
    warehouse(s) / mart(s);

  • Analysis
    Services:
     
    The OLAP layer, at the heart of which resides one or more OLAP cubes;

  • Reporting
    Services:
     
    The Reporting layer, within which both relational and OLAP reports are
    authored, managed and delivered.

Many of
my articles give examples of layering considerations among the above,
just a few of which include:

  • Housing
    functions and calculations at the MSSQL Server (or any other enterprise-level
    RDBMS) Database layer (be it relational, star-schema, or other), which
    are leveraged in the cubes or reporting system;
  • Building
    structures within the Analysis Services OLAP layer to provide picklist,
    conditional formatting and other support to the Reporting layer of the
    system;
  • Building
    virtually anything we need in the way of calculated fields, parameterization
    support, conditional formatting, and more at the Reporting layer
    This can be seductively easy within the flexible vacuum of the Reporting
    Services
    design environment, but can often be the worst place to house the
    associated structures from an optimization perspective.

Regardless
of whether the enterprise implements the entire solution using in-house talent
or seeks skill / other resource augmentation from consultants, it is critical
to seek a multi-dimensional perspective in the planning and
design stages of the implementation, at a minimum.  Much of the time,
money and aggravation that are the natural issue of a haphazard approach is
unwarranted, and can haunt the enterprise for a long time. Relying upon
application specialized “gurus” (or, even worse, the placement organizations
that purport to “pre-qualify” such specialists) to meet business requirements,
with an architecture that will scale with the organization, can be a career-limiting
move
.

For a
general summary of my opinions surrounding the importance of thinking
"multi-dimensionally" within the design and implementation of a
business intelligence system, see Multi-Layered
Business Intelligence Solutions … Require Multi-Layered Architects
.

Leverage Conditional Formatting Logic from the Analysis Services Layer

The reporting / presentation layer
of the integrated Microsoft business intelligence solution is often the only
point of interaction for organizational information consumers. It is here that
we often find it desirable to manifest the effects of conditional formatting.
Conditional formatting typically includes the modification of the visual
appearances of report items, such as size, fonts, color or background color to
draw attention to a given condition (such as the assignment of a red background
color to any value representing an inordinately high expense total for a
division), to generally class / group values based upon conditions (for
example, the operating income of all units meeting their expressed goals is
presented in green numerals, with the values for those falling short of the
mark exhibiting red numerals), and so forth. Conditional formatting can serve
many purposes, ranging from simple “highlighting” of conditions of this sort to
more sophisticated functions that go well beyond mere color changes.

Because a reporting tool like MSSQL
Server Reporting Services
(“Reporting Services”) makes conditional
formatting easy to put into place, and because it is within the deployed
reports that the resulting data presentations are manifest, we often assume
that conditional formatting is naturally handled within the Reporting layer.
Complications arise, however, when we have multiple values to which such
formatting is applied, or when our conditional formatting expressions become complex
and resource intensive. Say the expression performs a somewhat lengthy
comparison process between the value under consideration and multiple possible
values, for classifying the resulting value within a bucket, to each of which
we assign a specific formatting attribute. It is easy to see how report
processing time can be adversely affected when the conditional testing and
resulting formatting are applied to, say, every measure in a given column (or
even multiple columns) of the report.

Analysis Services allows us to apply conditional
formatting at the cube level, where the values can be calculated and stored as
a part of structure of the cube, in some respects. This not only might mean
far more efficient report processing (where, for example, the expression in the
report that dictates the conditional formatting is substituted by an expression
does a straightforward “pull” of the result(s) of the conditional test from the
cube (say, a color description), where it has already been determined, versus
performing the logic in the report itself at runtime, and then assigning the
desired attribute for each report value individually. In addition to the often
obvious performance enhancement that results, the logic can be stored (and
consistently maintained and enforced), from a single location within the
integrated solution, instead of hardcoded into every report for which the same
conditional formatting logic is applicable.

In Positing the Intelligence: Conditional Formatting in the Analysis
Services Layer
,
we examined the placement of conditional formatting logic within the cell
properties
of a calculated measure. As examples, we illustrated the use of
the MDX CASE() and IIF() functions in setting the cell
properties
listed in Table 1.

Cell Property

Possible Use in Reporting Layer

Color Expression: Fore Color

Specify / dynamically
set the foreground color (the color of the text) of the value of the
calculated member within the browser or a report.

Color Expression: Back
Color

Specify / dynamically
set the color of the cell background containing the value of the calculated
within the browser or a report.

Font Expression: Font
Flag

Specify / dynamically
set font “weight” attribute (including Normal, Bold, Heavy, etc.) for cells
based upon the calculated member.


Table 1: Sample Cell Properties and
Possible Uses …

NOTE: For detailed information about the MDX CASE() and
IIF() functions, see related articles within my Database Journal MDX Essentials series.

The cell properties to
which we refer above are ignored within client applications that do not support
them. Support for cell properties is not among the multiple (largely
performance related) benefits provided by Reporting Services’ algorithm
for generating a flattened rowset (as defined in the ODBO specification).
While these properties may be accessed in other ways, we will examine an option
for achieving this objective in a very “out-of-the-box” manner that is both
intuitive and easily maintained. A primary advantage gained by embedding
conditional formatting logic within the Analysis Services layer is still
provided: the support of dynamic attribute presentation within the reporting
layer is stored in, and consistently applied from, a central, easily accessible
location.

In this article, therefore, we
will focus upon such an approach, which we will then leverage in Reporting
Services
. We will begin with a scenario similar to the one we used in Positing the Intelligence: Conditional Formatting in the
Analysis Services Layer
, from which we will obtain an identical
end result within Reporting Services, albeit through a different
mechanism within the Analysis Services layer.

Adding Conditional Formatting Support within the Cube

We will again consider an example
of a need for conditional formatting that we might, at least initially,
consider putting in a report: let’s say that a client needs a couple of things
to happen based upon the value of a calculated measure within the Adventure
Works
cube. Not only does the client want the color of the value text
(referred to as the “Fore Color” property for the calculated member in Analysis
Services
) to vary, based upon the magnitude of the value, but the
representatives with which we are working tell us that it is also desirable for
the background color of the cell containing the value (referred to as
the “Back Color” property for the calculated member in Analysis
Services
) to also vary, depending upon the value of the resident measure.

To be specific, the client wants
to enhance an existing report, which deals with measures based upon the reasons
driving Internet Sales, the Sales Reason Comparison report (one
of the samples that accompany the installation of MSSQL Server 2005
Reporting Services
). First, the client wishes to add Internet Gross
Profit Margin
(a calculated member currently existing in the cube) to the
existing measures contained in the report, Internet Orders, Internet
Sales Amount
, Internet Total Product Cost (a calculated field in the
report). Moreover, and the focus of our discussion in this article, the client
representatives make known a requirement for conditional formatting of the
proposed addition, Internet Gross Profit Margin.

Our client colleagues tell us that
the information consumers have expressed a need to see conditional
formatting
of the Internet Gross Profit Margin to alert them, at a
glance, to the margins (related to the expressed reasons that customers made
purchases) that fall above and below certain thresholds, so as to isolate these
occurrences for more in-depth exploration (as to the root causes, etc.).
Specifically, they would like to see any margin falling below forty percent
(40%) to be displayed in an attention-grabbing red font, coupled
with a yellow background color for the cell housing the margin
(producing a “highlighter” effect to further draw attention to the
underperforming margin). Moreover, the margins within the ranges of forty –
to – forty-two percent
(40.0% – 42.0%), inclusive, are to remain in
the standard black text, with the white background that currently
exists in the report. Finally, the group tells us that any Internet Gross
Profit Margin
exceeding forty-two percent (42 %) needs to be
presented with a green font, with an accompanying light green
background
, to once again emphasize its “outlier” nature to information
consumers. As a finishing touch, our client colleagues tell us that they would
prefer a bold font to accompany the green text assigned to the Internet
Gross Profit Margin
exceeding forty-two percent (42 %), so as
to highlight even more the respective superior performance.

We discuss the requirements with
the client representatives, mentioning that, while the conditional formatting
can certainly be managed within various property settings of the report, we
will demonstrate the generation of the desired physical attributes from the Analysis
Services
layer. Positing the intelligence within the cube will, we assert,
mean that the same logic can be carried forward to multiple reports by simply
referencing the calculations in the data source, versus performing the
calculations for every affected measure in the report (and adding to processing
time, etc.). Moreover, we emphasize that maintaining the logic in a single location
in the Analysis Services layer means a single point of maintenance: we
can modify the logic in one place and rely upon the changes to “ripple through”
to all reports that reference that logic, versus having to mechanically modify
each individual report. This also ensures consistency of application of the
logic to all affected reports, as none will be overlooked (or different logic
accidentally applied to different reports) when a change of this nature becomes
necessary.

Conditional Formatting in
Analysis Services

We begin our efforts by opening
the cube within a project in the Business Intelligence Development Studio.
I like to set up a lab environment for each of my client projects where I have
both the respective cubes and reports involved with the engagement within an
integrated solution in Visual Studio, both for ease in testing cube
modifications through to the report layer from a single, central location, as
well as for taking advantage of effective source control and numerous other
conveniences and advantages. For example, in this particular case, I have both
a copy of the sample Adventure Works DW and the AdventureWorks Sample
Reports
projects added into a single solution within the Business
Intelligence Development Studio
, where I can access all member objects from
one point, the Solution Explorer.

1. 
Open the Adventure
Works
cube from within the Solution Explorer.

2. 
Once the Cube
Designer
opens, select the Calculations tab.

Here
we will create an independent calculated member for each of the three
attributes (Fore Color, Back Color and Font Flag, to which
we refer in Table 1 above) for which we set cell properties used in Positing the Intelligence: Conditional Formatting in the Analysis
Services Layer
. As is quite
often the case in meeting business requirements via the tools and features of Analysis
Services
in general, there are multiple ways to achieve our ends. In situations
like the present scenario, I have created derived measures, as well as taken
other approaches, to achieve what we will accomplish with calculated
measures
in this session. (I sometimes use the terms “calculated member
and “calculated measure” interchangeably; in the present session, we
will be working with calculated members that we add to the Measures
dimension, hence I’ll refer to them as “calculated measures” in most
cases.) The needs of the local environment should, of course, dictate the
criteria by which solutions are selected for production systems; we are only
examining one simple approach in this article.

Let’s get
started by adding the conditional formatting for Fore Color to
our cube, via a calculated measure.

3. 
Select (by
clicking) Internet Gross Profit Margin within the Script Organizer
pane in the upper left corner of the Calculations tab, simply to place
the cursor.

4. 
Click the New
Calculated Member
button in the toolbar just above the Script Organizer
pane.

A new
entry is added to the Script Organizer pane, underneath the row labeled Internet
Gross Profit Margin
(we can, of course, move entries up or down via the
buttons / context menu items provided).

5. 
Type the
following into the Name box within the Form View that has
appeared for the new calculated member:

[Internet GPM Fore_Color]

NOTE: Be sure to enclose the name within the brackets as shown,
anytime there are spaces within the string.

6. 
Expand the Expression
section of the form, if necessary, and then type the following MDX expression
into the box:


CASE
WHEN [Measures].[Internet Gross Profit Margin]< .40
THEN “Red”
WHEN [Measures].[Internet Gross Profit Margin]> .42
THEN “Green”
ELSE “Black”
END

All we are
doing here is generating a color description that will be acceptable to Reporting
Services
(as are the appropriate color codes). We are using the MDX CASE()
function to drive conditional formatting. (Another option might have been to
do so with the (appropriately nested) IIF() function, of course; we will
see an example of IIF() at work in the last of the three calculated
measures we create in our practice session.)

7. 
Expand the Additional
Properties
section, if necessary, and leaving all other settings at
default, select Internet Sales Amount within the Non-empty behavior selector.
Click OK to close the selection dialog, once completed.

The form
for the new Internet GPM Fore_Color calculated measure appears, with our
input, as depicted in Illustration
1
.



Illustration
1: Our Input for the New Calculated Measure …

Next, we
will add the logic to support the conditional formatting requirements
for Back Color and Font Flags that we have mentioned.

8. 
In a manner
similar to that performed above, add two additional calculated measures, with
the Name, Expression and Non-empty behavior settings
specified in Table 2.

Name

Expression

Non-empty Behavior

[Internet GPM Back_Color]

CASE

WHEN [Measures].[Internet Gross Profit
Margin]< .40

THEN "Yellow"

WHEN [Measures].[Internet Gross Profit
Margin]> .42

THEN "GreenYellow"

ELSE "White"

END

Internet Sales Amount

[Internet GPM Font_Flags]

IIF([Measures].[Internet Gross Profit
Margin]> .42,

"Bold", "Normal" )

Internet Sales Amount

Table 2: Settings for Two Additional
Calculated Measures

Our
calculated measures appear, within the Script Organizer, as shown
in Illustration 2.



Illustration
2: The New Calculated Measures within the Script Organizer …

At any
point of input, we can check the syntax we have accumulated within the Calculations
tab via the Check Syntax button in the Calculations toolbar. The
next step will be to deploy the project, which we will do in preparation for
using our new conditional logic support structures within Reporting Services.

9. 
Deploy the Analysis
Services
project within the local environment.

10. 
Once
deployment is complete, close the Deployment Progress viewer.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles