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

Monday Aug 15th 2005 by William Pearson
Share:

Join Business Intelligence Architect Bill Pearson as he introduces the SWITCH function, along with popular number formatting options, and then extends conditional treatment to default drilldown presentation.

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.


Illustration 1: A Draft (Using MS Excel) of the Desired End Report ...

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.

1.  Click Start.

2.  Navigate to, and click, the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

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

Click for larger image

Illustration 2: Beginning in Microsoft Visual Studio .NET 2003 ...

Visual Studio .NET 2003 opens at the Start page.

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 3.


Illustration 3: Selecting a New Project

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

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS020

8.  Navigate to a location in which to place the Report Project files.

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


Illustration 4: The New Projects Dialog, with Addition

9.  Click OK.

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


Illustration 5: The New Project Appears in the Solution Explorer

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.



1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Add Existing Item from the cascading context menus that appear, as depicted in Illustration 6.



Click for larger image

Illustration 6: Adding the Report to the Project ...



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

3.  Using the Add Existing Item - RS020 dialog, navigate to the location of the sample reports in your own environment.

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.


Illustration 7: Navigating to the Sample Reports Folder ...

4.  Right-click the Foodmart Sales report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 8.


Illustration 8: Performing a Quick Copy of the FoodMart Sales Report

6.  Within the Add Existing Item - RS020 dialog, navigate to the RS020 folder we created earlier, when we added the new RS020 project.

7.  Right-click somewhere in the white space inside the RS020 folder, within the dialog.

8.  Select Paste from the context menu that appears, as shown in Illustration 9.


Illustration 9: Select Paste within the New Folder ...

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

9.  Right-click the new file.

10.  Select Rename from the context menu that appears.

11.  Type the following name in place of the highlighted existing name:

SWITCH_CondFormat.rdl

NOTE: Be sure to include the .rdl extension in the file name.

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


Illustration 10: The New Report File, SWITCH_CondFormat.rdl

12.  Click the white space to the right of the file name, to accept the new name we have assigned.

13.  Re-select the new file by clicking it.

14.  Click Open on the dialog box to add the new report to report project RS020.

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


Illustration 11: The New Report Appears in Solution Explorer – Report Folder

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:

1.  Within the RS020 project tree in the Solution Explorer, double-click the new SWITCH_CondFormat report, to open it.

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


Illustration 12: The Report Clone – Layout View

2.  Right-click the Store_Profit field in the Report Designer Field List. (If the Field List does not appear, resurrect it by selecting View --> Fields from the main menu).

3.  Select Edit... from the context menu that appears, as shown in Illustration 13.


Illustration 13: Select Edit ... from the Context Menu

The Edit Field dialog opens.

4.  Click the Expression Editor (Fx) button that appears at the immediate right of the Calculated field box, in the lower portion of the dialog, as depicted in Illustration 14.


Illustration 14: Click the Function Button ...

The Expression Editor opens.

5.  Replace the expression within the Expression box with the following:

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

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


Illustration 15: The Replacement Expression in the Expression Box (Partial View)

6.  Click OK to accept our modification, and to close the Expression Editor.

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

7.  Click OK to close the Edit field dialog.

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.

8.  Select Report --> Report Parameters (click a point within the Layout view of the report to enable the Report menu item, if it does not already appear) from the main menu atop the Report Designer, as depicted in Illustration 16.


Illustration 16: Select Report --> Report Parameters from the Main Menu

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


Illustration 17: The Report Parameters Dialog

9.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

10.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

11.  Click OK to accept removal of the parameter.

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.

12.  Click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

13.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

14.  Select Properties from the context menu that appears, as depicted in Illustration 18.


Illustration 18: Accessing the Matrix Properties

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

15.  Click the Filters tab.

16.  Click the Value field of the single occupied row to select it.

17.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 19.


Illustration 19: Select and Delete the Parameter Reference

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

18.  Click OK to accept our changes and to close the Matrix Properties dialog.

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.

1.  Right-click the textbox underneath the Store Value column title, currently displaying =Sum(Fields!Store_Profit.Value).

2.  Select Properties from the context menu that appears, as shown in Illustration 20.


Illustration 20: Accessing the Field Properties

The Textbox Properties dialog for the Store_Profit textbox opens,

3.  In the right, lower portion of the dialog, below the FormatStandard list box, and to the immediate right of the Custom box (whose radio button is selected) replace the current format code (C0) with the following expression:

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

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


Illustration 21: Custom Formatting Syntax in the Textbox Properties Dialog

4.  Click OK to accept our changes, and to close the Textbox Properties dialog.

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.

5.  With the Store Profit value textbox still selected, select View --> Properties Window from the main menu (if required), as shown in Illustration 22.


Illustration 22: Select View --> Properties Window from the Main Menu (Partial View) ...

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

6.  Ensure that the selector atop the Properties Window displays Store_Profit Textbox, as depicted in Illustration 23.


Illustration 23: Properties Window for the Store_Profit Textbox (Partial View) ...

7.  Click the Color box in the upper half of the Properties Window, to activate its downward pointing selector button.

8.  Click the selector button to enable the Color selection list (the color currently in place is Black).

9.  Select Expression from the partially expanded colors list, as shown in Illustration 24.


Illustration 24: Select Expression from Atop the Colors List (Partially Expanded)

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.

10.  Replace "Black" in the Expression box (right half of the Expression Editor)with the following expression:

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


Illustration 25: Expression Appears in the Expression Box ...

11.  Click OK to accept our changes, and to close the Textbox Properties dialog.

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

12. Click the Preview tab, atop the design environment.

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


Illustration 26: The Values Section of the Matrix (Partial View)

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.

1.  Return to the Layout view by clicking the Layout tab.

2.  Click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

3.  Right-click the upper left corner of the matrix, as we did in the earlier section.

4.  Select Properties from the context menu that appears.

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

5.  Click the Groups 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.

6.  Click the Add button to the right of the Rows list box.

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

7.  Type the following into the Name box on the General tab, replacing the existing name:

BrandSales_Product_Family

8.  Select Fields!Product_Family.Value within the dropdown selector of the Expression list, in the Group on section.

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


Illustration 27: The Grouping and Sorting Properties Dialog – New Product Family Group

9.  Click OK to accept changes and close the Grouping and Sorting Properties dialog for the BrandSales_Product_Family group.

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.

10.  Click the new group, BrandSales_Product_Family, in the Rows list box to select it, if necessary.

11.  Click the Up button to the right of the Rows list box enough times to raise the BrandSales_Product_Family to the top of the 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).


Illustration 28: The New Product Family Group Appears Atop the Rows List Box

12.  Click the OK button to accept the rearrangement of the groups, and to close the Matrix Properties dialog.

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.

13.  Right-click the new Product Family textbox (the leftmost of the label textboxes).

14.  Select Properties from the context menu that appears, as shown in Illustration 29.


Illustration 29: The Select Properties for the New Product Family Label Textbox

The Textbox Properties dialog appears.

15.  Type the following into the Name box of the dialog.

Product_Family

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


Illustration 30: The Textbox Properties Dialog for Product_Family

16.  Click OK to accept changes and close the dialog.

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.

17.  From the Layout view, once again, click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible, as we did earlier.

18.  Right-click the upper left corner of the matrix.

19.  Select Properties from the context menu that appears.

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

20.  Click the Groups tab.

21.  Click the BrandSales_Product_Department group in the Rows list box to select it.

22.  Click Edit.

The Grouping and Sorting Properties dialog for the group opens.

23.  Click the Visibility tab.

24.  Under Initial Visibility, click the radio button to the immediate left of the Expression field, to select it.

25.  Type the following expression into the Expression field:

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

26.  Click the checkbox the immediate left of the label "Visibility can be toggled by another report item" below the Expression field.

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

27.  Select the Product_Family item in the selector.

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.


Illustration 31: The Visibility Tab – with our Input

28.  Click OK to accept changes and to return to the Groups tab of the Matrix Properties dialog.

29.  Click OK to accept modifications and close the Matrix Properties dialog.

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.

30.  Right-click the textbox containing the report title FoodMart Sales.

31.  Select Properties from the context menu that appears, as we did in an earlier step for the Product Family textbox.

The Textbox Properties dialog appears.

32.  Type the following into the Value box of the dialog:

FoodMart Consumables Analysis

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


Illustration 32: The Textbox Properties Dialog for the Report Title

33.  Click OK to accept changes and close the dialog.

34.  Right-click the Product Family (leftmost) label textbox, once again.

35.  Select Subtotal from the context menu that appears, as shown in Illustration 33.


Illustration 33: Creating a Subtotal for the Product Family Level – and the Report Itself ...

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.

1.  Click the Preview tab, atop the design environment.

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

Click for larger image

Illustration 34: The Report Appears in Preview, Reflecting Our Changes

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.

2.  Select File --> Save All to save all work to this point.

3.  Select File --> Exit when ready to leave the Reporting Services development environment.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved