MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports - Page 2

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.

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).