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"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

As I have stated since the charter article of the series, published about the time Reporting Services was first publicly released, my conviction is that Reporting Services will commoditize business intelligence, particularly in its role as a component in an integrated Microsoft BI solution. Having been impressed from my first exposure to this exciting application, when it was in early beta, my certainty in its destiny grows stronger by the day, as I convert formerly dominant enterprise Business Intelligence systems, such as Cognos, Business Objects, Crystal, and others, to the Reporting Services architecture. I receive constant requests to conduct strategy sessions about these conversions with large organizations in a diverse range of industries – the interest grows daily as awareness of the solution becomes pervasive. Indeed, the five- to six-plus figures that many can shave from their annual IT budgets represent a compelling sweetener to examining this incredible toolset.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including current service packs, along with the applications upon which Reporting Services relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

About the Mastering OLAP Reporting Articles...

As I have noted in many articles and presentations, one of the first things that becomes clear to "early adopters" of Reporting Services is that the "knowledgebase" for OLAP reporting with this tool is, to say the least, sparse. (I recently heard an internal "reporting guru" say, during a BI strategy session with a major soft drink manufacturer in Atlanta, that "we didn't evaluate Reporting Services because it doesn't do cubes ...") As most of us are aware, minimal, if any, attention is given to using Analysis Services cubes as data sources for reports in the handful of books that have been published on Reporting Services to date. All are written from the perspective of relational reporting, as if with existing popular tools for that purpose. One Reporting Services book discusses OLAP reporting with Reporting Services, and then performs illustrative exercises with Office Web Components (OWC), instead. Another depicts an MDX snippet at the end of the book, as if as an afterthought. All of the early books focus entirely on relational reporting, and most make heavy use, typically enough, of the Books Online and other scraps of documentation that we already have anyway. (I could go on, but my overall opinion of the technical book industry is already well known.)

As I stated in my article, Mastering OLAP Reporting: Cascading Prompts, the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of the reporting solutions of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost. The vacuum of documentation in this arena, to date, represents a serious "undersell" of Reporting Services from an OLAP reporting perspective. I hope to contribute to making this arena more accessible to everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, rest assured that the OLAP potential in Reporting Services will be yet another reason that the application "commoditizes" Business Intelligence.

For more information about the Mastering OLAP Reporting articles, see the section entitled "About the Mastering OLAP Reporting Articles" in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

In an earlier article, Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports, as well as within other articles of the Reporting Services series, we have discussed conditional formatting in general, and have set out to examine approaches to meeting specific business requirements based upon conditions. While the Immediate If - or IIF() - function is a popular means of mechanizing conditional formatting, we will introduce a somewhat more powerful function in this session, the SWITCH() function. We will perform a practice exercise where we leverage SWITCH() to achieve a desired conditional result that is a part of a business requirement of a group of hypothetical information consumers.

We will use SWITCH() to enact conditional formatting in a couple of different ways. First, we will use it to drive formatting of a value to meet requirements that are based upon the magnitude of the value itself. As a part of our practice with SWITCH() to achieve our ends, we will delve into a popular formatting convention for negative (and other "outlier") numbers. Next, we will extend our examination of SWITCH() to include its use to enforce the default drilldown states of members of a given group level within a sample matrix report, to achieve another presentation objective.

Virtually all major OLAP reporting solutions on the market provide for capabilities to meet simple conditional formatting needs. Many, including Cognos PowerPlay, make selection and application of these presentation attributes easy for even novice users. Our focus in this article, as it is within many of the articles of this series, will be to demonstrate that these features, and far more, are easily replicated within Reporting Services.

In this article we will:

• Discuss presentation nuances to meet a common business need within the reporting environment, the requirement to present one of multiple possible formats (or to "conditionally format"), based upon the value of a given report measure;
• Discuss briefly the SWITCH() function, and how it can be leveraged to meet the presentation needs of a hypothetical group of information consumers to meet a conditional formatting need;
• Prepare for our practice session by creating a project within Reporting Services, and by creating a "clone" report (based upon an existing sample OLAP report to save time), within which we will perform our exercises;
• Present, as a part of our examination of conditional formatting, a technique for formatting negative values as enclosed within parentheses, using red characters to draw attention;
• Present an approach for conditionally controlling the presentation of the default drilldown state of a given line item within a matrix report region;
• Preview the report in Report Designer, to verify the operation of the calculations that form the scope of our practice exercise.

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.

Hands-On Procedure

Preparation

First, let's create a new Reporting Services project from which to conduct our practice session.

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

The equivalent on my PC appears as depicted in Illustration 2.

Visual Studio .NET 2003 opens at the Start page.

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

RS020

The New Project dialog appears, with our input, as depicted in Illustration 4.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 5.

Having created a Report Project, we are ready to proceed with creating the new report clone.

Create a Copy of the Foodmart Sales Report

As we have noted, we will be working with a copy of the Foodmart Sales report, to keep the original sample intact for easy access and use at another time.

The Add Existing Item - RS020 dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

An example of the Add Existing Item - RS020 dialog, having been pointed to the sample Reports folder (which contains the Foodmart Sales report we seek), appears as partially shown in Illustration 7.

A copy of the Foodmart Sales report appears within the dialog.

SWITCH_CondFormat.rdl

The renamed copy of the Foodmart Sales sample report appears as depicted in Illustration 10.

SWITCH_CondFormat.rdl appears in the Reports folder, within the RS020 project tree in the Solution Explorer, as shown in Illustration 11.

Modify the Foodmart Sales Report to Prepare for Our Procedural Steps

The sample objects that come along with an installation of Reporting Services are often the best to use in a practice example such as the one that follows, primarily because virtually everyone who has installed Reporting Services has access to these samples. The use of copies of pre-fabricated reports, among other sample objects, in this and other of my articles has saved significant amounts of time in preparing for practice sessions, allowing us to focus upon the specific techniques which the article is contrived to address. One of the disadvantages of working with readily available sample objects is that they are just that – samples - and, as most of have become aware, the samples provided with even enterprise-level applications can be quite simplistic with regard to their reflection of business reality.

We have before us an example of this that we will need to manage with a minor adjustment: one of the specifications of the information consumers includes the conditional formatting of the existing Store Profit values. The consumers have stated that they wish to have negative values formatted in a manner that attracts attention. To perform the steps necessary to demonstrate an approach to doing this requires one thing that is not found in the sample data – a negative Store Profit number! It would seem that FoodMart never meets with anything but a profit, even at a granular level, with any product it sells.

We will adjust the Store Cost value, within the Store Profit calculated field, with a simple multiplier to generate a few negative numbers, to make the conditional formatting portion of our practice session possible. To do this, as well to prepare the report further for our exercises, we will take the following steps:

The report opens within the Report Designer, and the Layout View appears, as depicted in Illustration 12.

The Edit Field dialog opens.

The Expression Editor opens.

=Fields!Store_Sales.Value - 2.5*(Fields!Store_Cost.Value)

The Expression box appears, with the new expression, as partially shown in Illustration 15.

We have now adjusted the Store Profit calculated field to generate some negative numbers in the report. We will, of course, be forced to "suspend disbelief" in the accuracy of the Store Profit values (no longer simply Store Sales minus Store Cost), but this will be a simple sacrifice to allow anyone with the standard samples to perform the exercises that follow. (We can be confident that calculated fields can generate accurate values in simple subtraction scenarios like this in the real world – our focus here is conditional formatting, and this is simply a way to make the process possible).

We are returned to the Layout view for the report. To conclude our preparation steps, we will eliminate the existing Product Family parameter from the report, in accordance with the expressed wishes of the information consumers.

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 17.

The ProductFamily parameter is removed from the list.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report. We must now delete a reference to the parameter we have removed, which we can access via the Properties dialog for the matrix.

The Matrix Properties dialog opens, defaulted to the General tab.

The remaining reference to the now-deleted parameter is itself deleted.

We are now ready to proceed with modifications to our report to meet the conditional formatting and other presentation requirements of the information consumers.

Procedure

Establish Conditional Formatting for the Store Profit Value

Having created a copy of the functional report, we are now ready to make the enhancements requested by the FoodMart information consumers. Our first modification will establish conditional formatting of the Store Profit value, to meet the requirement that, when negative, it 1) is contained in parentheses and 2) appears in a red (versus the standard black) font. (We will go one step further, as well, and color the value green, if it equals zero, to establish a scenario with yet an additional possible outcome). The purpose, again, is to attract reader attention to line items that did not render a profit. Obviously, the procedures that we undertake to conditionally format the Store Profit value could be applied to other values in the report, as well.

While there are multiple approaches to bringing about the conditional formatting we desire, we will accomplish it using modifications within the basic and advanced textbox properties settings for the Store Profit value. We will begin our procedure from our current position on the Layout tab, within the Report Designer. Here we will apply expressions to properties of the Store Profit value.

The Textbox Properties dialog for the Store_Profit textbox opens,

 $#,###;($#,###)

The Textbox Properties dialog appears, with our modification, as depicted in Illustration 21.

We are returned to the Layout tab.

The format pattern we have entered into the Custom Format box of the Textbox Properties dialog will, as we shall see, handle the enclosing of negative numbers in parentheses, the first half of the formatting requirement we face. Next, we will use an expression, within the font Color property, to make negative values appear red.

The Properties Window appears (I typically "dock" mine to the right side of the Report Designer environment, for easy access).

The Expression Editor appears. Here, as with myriad other object properties throughout Reporting Services, we can use an expression to enact conditional formatting, as we shall see next.

=Switch(Sum(Fields!Store_Profit.Value) > 0, "Black",

Sum(Fields!Store_Profit.Value) = 0, "Green",

Sum(Fields!Store_Profit.Value) < 0, "Red")

The Expression box of the Expression Editor appears, with our expression, as depicted in Illustration 25.

We are returned to the Layout tab, where we can preview the effects of our handiwork surrounding the Store Profit value.

The report executes. The values section of the report matrix appears as partially shown in Illustration 26.

We now see that the ("cosmetically calculated") negative values of the Store Profit column appear to have been successfully formatted based upon the conditions we have supplied. This meets the expressed needs of the information consumers in that the negative numbers now appear in red fonts, and enclosed by parentheses, for easy identification.

Establish "Conditional Drilldown Defaults" for Presentation Purposes

Having established conditional formatting of the Store Profit value, we are now ready to enhance the report further to meet the second of the business requirements. We recall that the information consumers, who intend to use this report to focus upon the Drink and Food categories of the FoodMart product offerings, have asked that the report present these two Product Families with a "drilled down" state as the default. Moreover, they want the Non-Consumable products to appear, by default, in a "rolled up" state, providing a single line item for Non-Consumables on the face of the report. This drill down presentation will allow emphasis upon the analysis upon the Product Families, Drink and Food, while at the same time providing the Non-Consumables total to allow them to agree total product sales to corresponding summary values in other system reports. The consumers have agreed that the flexibility of ad hoc drilldown on the summary Non-Consumables line would add value to the report, as well.

Providing the "conditional default drill downs" as the information consumers have requested will afford us an opportunity to extend the concept of conditional formatting, once again, to meet a business need in a relatively sophisticated way. Such opportunities are legion, as many of us are learning, within Reporting Services and the integrated Microsoft BI Solution in general. We will undertake the requirement in the steps that follow, beginning with the establishment of Product Family as a group within the existing matrix data region.

The Matrix Properties dialog opens, defaulted to the General tab.

Four groups appear in the Rows list box, and two added groups, along with the default Static Group, appear in the Columns list box. Here we will add a Product Family group, upon which we will base the default drilldown attributes that the consumers have requested.

The Grouping and Sorting Properties dialog for the new group, named BrandSales_RowGroup5 (or similar) by default, opens to the General tab.

BrandSales_Product_Family

The Grouping and Sorting Properties dialog appears as shown in Illustration 27.

We are returned to the Matrix Properties dialog - Group tab. We need, at this point, to arrange the new group to the left of the report – and thus to move it to the top in the Rows list box.

The BrandSales_Product_Family group appears in the Rows list box as depicted in Illustration 28 (relevant portion of the Matrix Properties dialog – Group tab).

We are leaving the groups momentarily, and returning to the Layout view, to name the textbox that was created by our addition of the Product Family group.

The Textbox Properties dialog appears.

Product_Family

The Textbox Properties dialog appears, with new Name, as depicted in Illustration 30.

Now that we have put the primary group, from which we will effect our default drill down presentation, in place and labeled, we will turn to the control of visibility in the next group level, BrandSales_Product_Department. We will do so from the Matrix Properties dialog – Group tab, to which we will return once again.

The Matrix Properties dialog opens, defaulted to the General tab, once again.

The Grouping and Sorting Properties dialog for the group opens.

= Fields!Product_Family.Value="Non-Consumable"

(Note that the logic behind "visibility" seems reversed. It is as if we have established a drilled down state based upon the return of a "false" Boolean response. As we shall see, we receive a collapsed state for the Non-Consumable Product Family.)

A checkmark appears in the checkbox, and the Report Item selector becomes activated.

This step allows ad hoc drilldown when the default state is collapsed (as will be the case for the Non-Consumable family.).

The Visibility tab - Grouping and Sorting Properties dialog appears as shown in Illustration 31.

Finally, let's modify the title of the report to represent its new use, with a caption similar to the one that appeared in the draft presented at the conclusion of business requirements gathering for the report. In addition, we will add a report total underneath the rolled up "reconciling" line item for added report utility.

The Textbox Properties dialog appears.

FoodMart Consumables Analysis

The Textbox Properties dialog appears, with new Value, as depicted in Illustration 32.

We return again to the Layout view in Report Designer, where we see the new Total in evidence. We are now ready to verify the results of our work.

Verification

Preview the Report to Ascertain that It Meets Business Requirements

Let's preview the report as it currently stands, to ensure that we have met the expressed business requirements of the information consumers.

The report executes, and appears as depicted in Illustration 34.

We see the effects of our handiwork. As we noted earlier, the conditional formatting of the Store_Profit value has the intended presentation effect. Moreover, we note that the Drink and Food families are presented in a "drilled down" state by default, while the Non-Consumable family is presented with a default of "rolled up," with regard to drill down attributes. We find, too, that we can conduct drill down of the Non-Consumable family on an ad hoc basis, should the need arise, adding value to the report though the provision of capabilities to meet needs that the information consumers may not have foreseen at the time they communicated their initial specifications for the report to us.

Conclusion ...

In this article, we extended our exploration of conditional formatting within Reporting Services to address a common need within the reporting environment, the requirement to present one of multiple possible formats, based upon the value of a given report measure. To introduce an approach to meeting such a requirement, we discussed briefly the SWITCH() function, and then embarked upon a practice example within which we address the presentation needs of a hypothetical group of information consumers.

After preparing for our practice session by creating a project within Reporting Services, and by creating a "clone" report (based upon an existing sample OLAP report to save time), we presented a technique for formatting negative values (as enclosed within parentheses, using red characters to draw attention) using a combination of properties settings and expressions, including the SWITCH() function. We then presented an approach for conditionally controlling the presentation of the default drilldown state of a given line item within a matrix report region. Finally, we reviewed the report in Report Designer, to verify the operation of the calculations that formed the scope of our practice exercise.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.