MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 2

August 15, 2005

Extending Conditional Formatting: SWITCH and Drilldown Defaults

Introduction and Business Scenario

A large portion of the requests for assistance that I receive, from direct clients, or via e-mail, forums, and elsewhere, involve the need to perform conditional formatting of some sort upon a value, based upon the amount / quantity that the value represents. In addition, I often receive requests for approaches to replicate "tried and true" presentation effects that have long been the standard within a given environment or within larger disciplines, such as accounting and finance in general. Finally, and more commonly within the settings of my work with pioneering clients, I receive requests for approaches to other conditional formatting scenarios that extend the uses for various functions into another realm: the control of the default and other behavior of reporting objects in a more mechanical way, beyond mere formatting in the routine sense, and into leveraging more "mechanical" attributes within a report.

In this article, we will examine a formatting technique for meeting a common requirement, particularly when working with Accounting and Finance practitioners, but with other functional groups as well. These information consumers often like to see negative values presented in parentheses, as opposed to carrying a negative sign in front. Moreover, such audiences often prefer to see negatives in red, to have their attention drawn to the negative numbers. This preference might, based upon the financial statement or report, extend to the "positive" numbers, too: credits, formatted with negative signs in many cases, are often a "good" thing. A report that focuses, say, on revenues, which are stored in an accounting system as negatively-signed credits, might seek to draw attention to debits (expenses, for example), which in cases like this might actually be presented as "positive" numbers.  My point here is that the logic we apply in Reporting Services to "highlight" a negative value (say, with a red font) can just as easily be used to highlight a positive value, as circumstances dictate.
 

In addition to the conditional formatting of values, we will examine another presentation nuance that I have found to be popular recently among multiple clients with various reporting needs: a feature within a report that presents a given level in one default drilldown state in one way (say, defaults a line item to "drilled down") while presenting another, or a group of other, line items, in another default state ("rolled up.") As we shall see, this capability will be useful in various scenarios where we wish to focus on the details of a given activity, while providing summarized information about areas that are not typically the focus, but upon which we might still want to perform ad hoc examination of underlying details for various reasons.

We will illustrate the aforementioned needs within our usual context of a business scenario. We will assume that we have received a request from a group of information consumers in the Finance Department of the FoodMart 2000 organization. The request is for additional support in the presentation of some data, housed within the Sales sample cube,

The information consumers tell us that the existing FoodMart Sales report (for operating year 1997, their current year), with a few modifications, would serve well in presenting the data they need for a monthly presentation to management. The group states, first, that the report will be intended for a single use, and will not require parameterization (FoodMart Sales parameterizes on Product Family). Product Family will become a key grouping in the report, however, and will appear to the left of the currently left-most column, Product Department.

Values presented in the report will need to reflect conditional formatting. Specifically, the information consumers, as members of the Accounting and Finance fraternity, wish to see "negative" Store Profit numbers, within the context of this report, as "bracketed" (enclosed in parentheses), with a red font, so as to drive attention to these outliers. Moreover, the information consumers wish the Drink and Food categories, which will form the focus of the new report, to present Product Families with a "drilled down" state as the default. In contrast, the consumers tell us that Non-Consumable products, while not in themselves a focus of the report, need to be included in summary, to provide the "tie" to the totals, which must agree to the existing FoodMart Sales report. We ask the group if they would prefer a summary line that could be drilled down upon should the need arise, and they concur that this might be a useful attribute.

We listen carefully to the requirements, and, as a confirmation of our understanding, present a spreadsheet mockup of the report, as shown in Illustration 1, with the specifications we have discussed to the consumers.

The consumers agree that the draft reflects the business requirements, and so we begin the desired enhancements, as we shall see, in the steps that follow.

Considerations and Comments

For purposes of our practice session, we will create a copy of the Foodmart Sales sample report. Creating a clone of the report means we can make changes to our report while retaining 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 Services in general.

Before we can work with a clone of the Foodmart Sales report, we need to create a Reporting Services project in the Visual Studio.Net 2003 Report Designer environment. Streamlining, and then making the enhancements to the report to add the requested functionality, can be done easily within the Reporting Services Report Designer environment. 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, to which we can refer in our individual business environments. This approach also preserves the original sample in a pristine state – for the reasons we have already mentioned. If you already have a project within which you like to work with training or development objects, you can simply skip the Create a Reporting Services Project section.

If the sample FoodMart 2000 Analysis Services database or the Foodmart Sales report was not created / installed as part of the initial installation of the associated application, if either was removed prior to your beginning this article, or if either or both applications have yet to be installed, etc., see the respective Books Online or other documentation for the necessary procedures to prepare for the exercises that follow.