Ad Hoc Conditional Formatting in an OLAP Report
Objective and Business Scenario
Because it allows for
more impact in our reports, conditional formatting is a popular topic in
the forums and newsgroups of most enterprise reporting applications. My first exposure
to the concepts of conditional formatting with Cognos, and my continued
application of those concepts within Cognos, Crystal, Business Objects, MicroStrategy,
and a host of other, more specialized applications, has given me a great appreciation
for the opportunities that exist in the business environment for effective
conditional formatting. Whether the reports are to be printed, displayed on
screen, or any of the other options for production / deployment, it's easy to
see the value that conditional formatting can add in making our presentations
more focused and consumer-friendly.
Just
as we discussed with filters in BlackBelt
Components: Manage Nulls in OLAP Reports, working with conditional
formatting has presented challenges to many - particularly to those without
extensive exposure to the concepts in reporting applications other than
Reporting Services. In the following sections, we will perform the steps
required to add conditional formatting to an OLAP report. To provide a report upon
which we can practice our conditional formatting exercises, we will begin with
the FoodMart Sales sample report that is available with the installation
of Reporting Services. This report uses the FoodMart 2000 Sales cube
that comes along with the installation of MSAS; in the event of an
installation of Reporting Services without an installation of MSAS (someone
invariably reminds me that it is, indeed, a plausible scenario), you can find
the cube on the MSAS installation disk, or from numerous other sources. (If you
have installed Reporting Services, you will need it anyway, to make use of the FoodMart
Sales report, in general.)
For purposes of our
practice procedure, we will assume that information consumers within the Operations
department of the FoodMart organization have expressed the need for modifications
of the existing FoodMart Sales report, which drills down to the Product
Brand Name level, as partially shown in Illustration 1.
Illustration 1: Current
FoodMart Sales Report - Partial View
The consumers have
expressed overall satisfaction with the report, but want to enhance it a bit to
make their analytical functions easier. First, they wish to make the structure
simpler, and to eliminate drilldown as it exists in the current FoodMart
Sales report: they wish to present only two levels, Product Subcategory
and Product Name, with both levels visible at run time. In addition,
the consumers want to add a new measure, Profit Margin, to reflect the
profitability percentage of the products and subcategories for which we are
already summarizing profit values. It is upon this measure that the
enhancements that follow will hinge.
Once they have
specified the minor structural changes above, the consumers request a
presentation characteristic that is common among conditional formatting
aficionados everywhere. The consumers want to enhance the presentation of the
data with the addition of a simple profitability indicator: they want the Profit
Margin value on the face of the report to appear with a red font, to
command the attention of the reader of the report, for those products whose
margin is below a certain percentage. The group tells us that they would like
for the intended audience, largely themselves, to be able to supply the
percentage at run time, as their analytical objectives might change at
different points in a given operating period. A prompt for this
information, they conclude, would make the ad hoc input of this
criterion easy enough to apply multiple scenarios quickly, based upon immediate
conditions and analysis needs.
As part of our typical
business requirements gathering process, we listen attentively to the details, formulating,
in the background, an idea of the steps we need to take in modifying a copy of
the report to produce the desired results. Once we grasp the stated need, and
confirm our understanding with the intended audience, we begin the process of modifying
the FoodMart Sales report to satisfy the information consumers.
Considerations and Comments
Before we can work
with the FoodMart Sales sample report, we need to create a
Reporting Services project in the Visual Studio.Net 2003 Report
Designer environment, within which we will make the requested modifications
to a copy of the report. Making the enhancements to the report to add the
requested functionality can be done easily within the Reporting Services Report
Designer environment, and working with a copy of the report will allow us
the luxury of freely exploring our options, and leave us a working example of
the specific approach we took, while preserving the original sample in a
pristine state - perhaps for other purposes, such as using it to accompany
relevant sections of the Books Online, and other documentation, in
learning more about reporting design in general.
If the sample FoodMart
2000 MSAS database was not created as part of the initial MSAS
installation, or was removed prior to your beginning this article, please see
the MSAS Books Online for the procedure to restore the database, together
with the sample cubes. As of this writing, a copy of the samples can be
obtained from the installation CD or via download from the appropriate
Microsoft site(s).