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

Monday Dec 20th 2004 by William Pearson
Share:

Parameterize conditional formatting within an OLAP report. MSAS Architect Bill Pearson provides hands-on practice in parameterizing conditional formatting at run time in Reporting Services.

About the Series ...

This article is a member of the series MSSQL Server 2000 Reporting Services. The series is designed to introduce MSSQL Server 2000 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.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including Service Pack 1, along with the applications upon which it 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.

This article also relies upon MSSQL Server Analysis Services ("MSAS") for a sample cube that will be used as a data source by the report with which we work in our practice exercise. For information surrounding the installation of MSAS and the sample OLAP databases and cubes that accompany the typical installation of MSAS, see the associated Books Online.

About the BlackBelt Articles ...

As we have stated in earlier BlackBelt articles, one of the greatest challenges in writing tutorial / procedural articles is creating each article to be a freestanding document that is complete unto itself. This is important, because it means that readers can complete the lesson without reference to previous articles or access to objects created elsewhere. When our objective is the coverage of a specific technique surrounding one or more components of a report, a given administrative function surrounding all reports, and other scenarios where the focus of the session is not the creation of reports, per se, can be challenging because a report or reports often has to be in place before we can begin to cover the material with which the article concerns itself.

The BlackBelt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) under consideration. We will attempt to use existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are readily accessible to virtually any organization that has installed the application. While we will often have to make modifications to the sample involved (we will actually create a copy, to allow the original sample to remain intact), to refine it to provide the backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will still have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

For more information about the BlackBelt articles, see the section entitled "About the BlackBelt Articles" in BlackBelt Components: Manage Nulls in OLAP Reports.

Overview

Over the years that I have spent implementing enterprise Business Intelligence applications, I have found many uses, ranging from "simple to sublime," for conditional formatting. At its most basic, conditional formatting can be used to identify exceptional data in a report. Examples (in the world of the sample FoodMart organization) might include Products that sell over or below certain thresholds, or Employees who have been around for a period exceeding a certain number of years.

Conditional formatting is obviously more intelligent than standard, or "absolute," formatting, where the format that is added to a text object, a field of numbers, or other report object remains fixed regardless of the values being printed or displayed. With conditional formatting, Reporting Services evaluates the object to which it is applied, and formats the object based upon its value. The most common example that comes to mind is to print or display negative numbers in red, or enclosed in parentheses / brackets (something with which I have a more than passing familiarity as a recovering CPA). However, Reporting Services can do far more sophisticated conditional formatting with numbers, strings, and a host of other objects.

In Reporting Services, like any of the other enterprise reporting packages on the market, we create what is conceptually a condition, or conditions, to which we assign rules of behavior, consisting most often of a presentation style, but certainly not limited to that. When a value, or other report object, meets the criteria of the condition(s) we define, Reporting Services applies the formatting or other behavior to the object.

In this article, we will introduce conditional formatting in a basic application, and then add a twist: We will practice the hands-on creation of parameterized conditional formatting that will allow users to make runtime decisions surrounding the formatting in their reports. Subsequent BlackBelt articles will focus on more advanced conditional formatting, often in combination with parameters and other components, and especially within the context of providing a "real world" solution that I can share from my own experiences with my readers, who can then implement the concepts in custom-fit manner in their own implementations. Whether my articles show you how to do something you always wanted to do, and your searches of various keywords led you here, or if you're a casual reader who obtains an idea that had not even occurred before exposure to the article, I am confident that you will find that Reporting Services stands ready to meet your needs - probably with multiple options and means of approach.

While we will deal in this article with an OLAP report, ad hoc conditional formatting can be accomplished in similar ways in reports based upon relational sources. (As I have noted in other articles, my tendency is to focus on OLAP reporting, because, while references abound that teach reporting for relational sources in Reporting Services, virtually no references to OLAP reporting exist, at the time of this writing, in any of the new books that have recently appeared. Articles on the subject are also universally sparse - as is often the case for those topics surrounding a new application that are not specifically spoon-fed from its online documentation.) In this session, we will:

  • Make structural changes to a copy of a sample report, based upon a sample cube, to meet the business requirements of a hypothetical group of information consumers;
  • Create a condition whose criteria will be applied to report objects at run time;
  • Create a parameter, with which we will associate conditional formatting, to provide information consumers the ad hoc capability to apply conditional formatting;
  • Discuss the results obtained with the development techniques that we exploit;
  • Preview the report to ascertain the effectiveness of our solution.

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

Hands-On Procedure

Preparation

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 the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

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


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

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:

RS012

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

The New Project dialog appears, with our additions, as shown 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.



Modify the FoodMart Sales Report



In this section, we will copy the existing FoodMart Sales report, and then open it in Reporting Services' Report Designer, where we will begin the modifications that the information consumers have requested.



Set up a Data Connection for the FoodMart Sales Report

Our next step is to set up a Data Connection. As we have noted numerous times throughout our series, Reporting Services can connect with, and create the DataSets it needs from virtually any ODBC or OLE DB-compliant data source (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise "alternative," scenario on your hands.



Let's set up a Connection to support the DataSet within our practice example.

1.  Right-click the Shared Data Sources folder underneath the new RS012 project tree in the Solution Explorer.

2.  Select Add New Data Source from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Select Add New Data Source from the Context Menu

The Data Link Properties dialog appears, defaulted to the Connection tab.

3.  Click the Provider tab, to select it.

4.  Select Microsoft OLE DB Provider for OLAP Services 8.0 from the Provider list box, as shown in Illustration 7.


Illustration 7: Selecting Microsoft OLE DB Provider for OLAP Services 8.0 - Providers Tab

5.  Click Next, to move to the Connection tab.

6.  In the Data Source box, type the name of the server upon which MSAS resides (mine is MOTHER1 in the illustration that follows).

7.  Type in the server name again in the Location box.

8.  Provide the appropriate authentication information to access the MSAS environment on the server you have selected.

I am using Windows NT Integrated Security, as this is not a production environment. For a production environment, selections here require careful consideration.

9.  Select FoodMart 2000 in the list of data sources that appear when we click the down-arrow selector button at the next box (and thus receive confirmation that our server / authentication information is adequate to display the sources) at the top of the Data tab.

The completed Data Link Properties - Connection tab appears as depicted in Illustration 8.


Illustration 8: Data Link Properties - Connection Tab

10.  Click the Test Connection button to confirm connectivity.

The Microsoft Data Link message box appears, indicating a successful test, as shown in Illustration 9.


Illustration 9: Testing Positive for Connectivity ...

11.  Click OK to close the message box.

12.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

Having established the data source, we are now ready to create a copy of the FoodMart Sales sample report, which we will then modify to meet the expressed requirements of the information consumers.



Create a Copy 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 newly created shared data source, in the Solution Explorer.

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




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

The Add Existing Item - RS012 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 - RS012 dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS012 dialog, having been pointed to the Samples folder (which contains the FoodMart Sales report we seek), appears as shown in Illustration 11.


Illustration 11: 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 12.


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

6.  Right-click somewhere in the white space to the right of the report files within the dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 13.


Illustration 13: Select Paste to Complete Copying ...

A copy of the FoodMart Sales report appears, with "Copy of" appended at the front of the name of the file, within the dialog.

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

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

AdHocCondFormat_Foodmart Sales.rdl

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

The copy of the FoodMart Sales sample report appears as depicted in Illustration 14.


Illustration 14: The New Report, AdHocCondFormat_Foodmart Sales.rdl

11.  Select the new file by clicking it, if necessary.

12.  Click Open on the dialog box to add the new report to report project RS012.

AdHocCondFormat_Foodmart Sales.rdl appears in the Reports folder, within the RS012 project tree in the Solution Explorer, as shown in Illustration 15.


Illustration 15: The New Report Appears in Solution Explorer - Report Folder

Having created a copy of the functional report, we are now ready to make the enhancements requested by the FoodMart information consumers.

Modify the FoodMart Sales Report Structure to Meet Business Requirements

Let's open the new clone of the FoodMart Sales report, and begin our alterations. We will make the layout changes to eliminate the unwanted levels, and then add the new Profit Margin measure. We will then focus upon the conditional formatting filtering considerations that form the nucleus of our session.

1.  Double-click AdHocCondFormat_Foodmart Sales.rdl, within the Reports folder in Solution Explorer, to open it in Report Designer.

The report opens, displaying the report on the Layout tab.

2.  Select the Preview tab, to begin a quick check of connectivity and overall report operation.

3.  Select Food in the parameter selector box that appears atop the Preview area, as depicted in Illustration 16.


Illustration 16: Select the Food Product Family in the Parameter Picklist

4.  Click the View Report button to execute the clone report.

The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. This indicates that our data source is functional, and that the DataSet that underlies the report is operational.

5.  Drill down on Baking Goods (click the "+" sign to its left), which appears near the top left of the report, to expose its underlying groups.

6.  Drill down on Jams and Jellies group that appears to the right of Baking Goods.

7.  Drill down on Jelly, which appears to the right of Jams and Jellies.

At this point we see the lowest level of the row axis, the Product Brand Name, appear. We know that we need to alter the drill down capability to go one level below, and instead of, the Product Brand Name, substituting Product Name, to meet the requirements we have been given. A portion of our view of the report at this stage appears in Illustration 17.


Illustration 17: The AdHocCondFormat_Foodmart Sales Report with Drill-down Example

Our next step involves swapping the Product Name level of the Product dimension with the current lowest level of the report drill down, Product Brand Name. Before we can make the field assignments on the Layout tab, we must ascertain that the needed level is included in the DataSet.

8.  Click the Data tab.

9.  Ensure that the ProductData DataSet is selected.

The DataSet definition (an MDX query) appears as shown in Illustration 18.


Illustration 18: ProductData DataSet - Current Definition

In examining the MDX behind the DataSet, we note a limiting factor in reaching our objectives. The level to which the Descendants() function extends is the Product Brand Name ([Product].[BrandName]) level, as I have circled above. This means that we need to adjust the MDX to include the next lower level, Product Name. Let's make the changes with the following steps.

10.  Modify the second line of the MDX query (the Row Axis definition) from its present form of:

{  Descendants([Product].[All Products], [Product].[Brand Name], LEAVES)  } ON ROWS,

to the following:

{  Descendants([Product].[All Products], [Product].[Product Name], LEAVES)  } ON ROWS,

(The only change is the switch of [Product Name] for [Brand Name] in one place, circled in Illustration 18 above.)

The modified DataSet appears as shown in Illustration 19. (I have circled the change).


Illustration 19: The AdHocCondFormat_Foodmart Sales Report with Modification (Circled)

NOTE: For more information about MDX, see my series MDX Essentials at Database Journal.

11.  Click the Run (!) button atop the Data tab to execute the query.

The query executes. An examination of the returned DataSet confirms the selection of the level element we require to modify the report, Product Name, as depicted in Illustration 20.


Illustration 20: The Needed Field Appears in the Returned DataSet (Partial View)

We are now ready to finish modifications to our report to remove Product hierarchy drill-down capabilities, and to display the Product Subcategory and Product Name levels, removing the others, as they are not useful within the scope of the business requirements for the new report.

12.  Click the Layout tab to switch to the Layout view.

13.  Widen the fourth column (fourth from both left and right, and thus the middle column of the report) enough to expose the full expression in the textbox, =Fields!Brand_Name.Value, as shown in Illustration 21.


Illustration 21: Expanded Column in Layout Tab (Partial View)

We must make modifications in a couple of places, to exchange the Product Name level with the Brand Name level, and to remove the remaining associated drill-down capability.

14.  Click anywhere within the Matrix data region to activate the gray column and row headers.

15.  Right-click the upper right corner of the Matrix data region.

16.  Select Properties from the bottom of the context menu that appears, as shown in Illustration 22.


Illustration 22: Select Properties from the Bottom of the Context Menu ...

The Matrix Properties dialog - General tab for our matrix (named BrandSales) appears, as depicted in Illustration 23.


Illustration 23: Matrix Properties Dialog - General Tab

17.  Click the Groups tab, to access the first area we need to modify, the group that currently points to Brand Name.

18.  Click the BrandSales_Brand_Name item that appears at the bottom of the Rows group list, as shown in Illustration 24.


Illustration 24: Select BrandSales_Brand_Name Row Group ...

19.  Click the Edit button to the right of the highlighted group.

The Grouping and Sorting Properties dialog - General tab appears.

20.  Replace the contents of the Name box atop the tab, BrandSales_Brand_Name, with the following:

Product_Product_Name

21.  In the Group On list, use the selector button to the right of the top line in the list, to modify the existing expression, =Fields!Brand_Name.Value, to the following expression:

=Fields!Product_Product_Name.Value

Illustration 25 depicts scrolling down on the selector to select the replacement expression.

Click for larger image

Illustration 25: Select the New Row Group Expression ...

The Grouping and Sorting Properties dialog - General tab appears, as shown in Illustration 26.


Illustration 26: Grouping and Sorting Properties Dialog - General Tab

Within the Matrix Properties dialog - Group tab, we note that the bottom row group now reflects the name change we made in the Grouping and Sorting Properties dialog.

22.  Click OK to accept modifications, and to close the dialog.

23.  Click OK to close the Matrix Properties dialog.

We return to the Layout tab. We now need to make another modification to continue with the drill-down-related changes requested by the information consumer group.

24.  Right-click the textbox fourth column from the left / right (currently showing "= Fields!Brand_Name.Value" in the Layout view.

25.  Select Properties from the context menu that appears.

The Textbox Properties dialog appears.

26.  Replace "Brand_Name" in the Name box with the following:

Product_Name

27.  Select the following by clicking the selector for Value, just below Name, to replace the current "= Fields!Brand_Name.Value" :

=Fields!Product_Product_Name.Value

The Textbox Properties dialog appears (modifications circled) as depicted in Illustration 27.


Illustration 27: Textbox Properties Dialog - Modifications Circled

28.  Click OK to close the Textbox Properties dialog.

We return to the Layout tab. We have another set of minor modifications to make to complete the structural changes that the intended audience has requested: to remove all except the lowest two current drilldown levels, Product Subcategory and Product Name, which the intended audience wants to see visible together in the new report. For this, we will need to return to the Matrix Properties dialog, Group tab, where we were earlier.

29.  Click anywhere within the Matrix data region to activate the gray column and row headers.

30.  Right-click the upper right corner of the Matrix data region.

31.  Select Properties from the bottom of the context menu that appears, as we did earlier.

The Matrix Properties dialog - General tab appears.

32.  Click the Groups tab.

33.  Click the BrandSales_Product Category item that appears within the Rows group list, as shown in Illustration 28.


Illustration 28: Select BrandSales_Product Category Row Group ...

34.  Click the Delete button to the right of the highlighted group.

The BrandSales_Product Category group disappears.

35.  Click the BrandSales_Product Department item that appears atop the Rows group list.

36.  Click the Delete button to the right of the highlighted group.

The BrandSales_Product Department group disappears.

37.  Click the BrandSales_Product Subcategory item within the Rows group list.

38.  Click the Edit button to the right of the highlighted group.

The Grouping and Sorting Properties dialog appears, defaulted to the General tab.

39.  Click the Visibility tab to access it.

40.  Under Initial visibility, select the Visible option by clicking the radio button to its immediate left.

41.  Uncheck Visibility can be toggled by another report item by clicking the checkbox to its immediate left.

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


Illustration 29: Grouping and Sorting Properties Dialog - Visibility Tab

42.  Click OK.

We return to the Matrix Properties dialog - Groups tab.

43.  Click the Product_Product Name item within the Rows group list.

44.  Click the Edit button to the right of the highlighted group.

The Grouping and Sorting Properties dialog appears, defaulted to the General tab.

45.  Click the Visibility tab to access it.

46.  Under Initial visibility, select the Visible option by clicking the radio button to its immediate left.

47.  Uncheck Visibility can be toggled by another report item by clicking the checkbox to its immediate left.

48.  Click OK.

We return to the Matrix Properties dialog - Groups tab.

49.  Click OK, again.

We return to the Layout tab in Report Designer. We are now ready to test the report to preview the results of our structural changes.

NOTE: Ensure that only the Product Subcategory and Product Name columns remain, from what was previously the Product hierarchy drilldown, before proceeding.

50.  Click the Preview tab.

51.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

52.  Click the View Report button to execute the report.

The report runs and returns the data associated with the Food Product Family that resides in the MSAS Sales cube. At this point, we see that the new lowest level of the row axis, Product Name, appears. We note, too, the appearance of Product Subcategory level, and that the other levels previously in place have disappeared, as the information consumers had requested.

A portion of our view of the report at this stage appears in Illustration 30.


Illustration 30: The AdHocCondFormat_Foodmart Sales Report (Partial View) with Layout Modifications

Add the Profit Margin Calculated Measure

We will next add a calculated field to the report to present the product Profit Margin. While I would probably do this at the cube level in real life, many cases arise where we have to work within Reporting Services to bring about a desired effect, without the luxury of access to the underlying data source. Regardless of its location, the Profit Margin calculation will be the basis, within this article, for conditional formatting. Keep in mind that the concept is the same, regardless of the genesis of the basis.

53.  Click the Layout tab.

54.  Locate the Fields Selector window (I keep mine docked, for easy access), and ensure that ProductData appears in the DataSet selector atop the window.

55.  Right-click any blank area within the Fields Selector window.

56.  Select Add from the context menu that appears, as depicted in Illustration 31.


Illustration 31: Select Add to Create a New Calculated Field

The Add New Field dialog appears.

57.  Type the following into the Name box:

Profit_Margin

58.  Click the radio button to the immediate left of Calculated field to select it.

59.  Click the Expression (fx) button to the right of the Calculated field box.

The Edit Expression dialog opens.

60.  Type the following into the Expression box to the right of the dialog:

=(Fields!Store_Profit.Value/ Fields!Store_Sales.Value)

The Edit Expression dialog appears as shown in Illustration 32.


Illustration 32: Expression for the New Profit Margin Calculated Field

61.  Click OK to accept the expression, and to close the Edit Expression dialog.

The completed Add New Field dialog appears as depicted in Illustration 33.


Illustration 33: The Add New Field Dialog

62.  Click OK.

The Add New Field dialog closes, and the Profit_Margin calculated field is created, as evidenced by its appearance in the Fields Selector window (shown in Illustration 34.


Illustration 34: The New Profit Margin Calculated Field in the Fields Selector Window

Let's place the new calculated field on the report, in accordance with the needs expressed by the intended audience.

63.  Click the Profit Margin calculated field in the Fields Selector window, to select it.

64.  Drag the Profit Margin calculated field onto the Layout area of the report, to the right of the right-most existing value, Store Profit.

65.  Drop the calculated field when the cursor indicates a drop point to the right of Store Profit.

At the drop point, the cursor resembles that depicted in the inset picture in Illustration 35.


Illustration 35: Drop Point is Indicated in the Cursor Change

Profit Margin appears within the layout. We now need to make an adjustment to the expression appearing in the report.

66.  Right-click the Profit Margin calculated text box on the report layout.

67.  Click Expression... from the context menu that appears, as shown in Illustration 36.


Illustration 36: Select Expression ... from the Context Menu

The Edit Expression dialog appears.

68.  Change the expression that appears in the Expression box to the following:

=Sum( Fields!Store_Profit.Value)/ Sum(Fields!Store_Sales.Value)

Note that our change is simply restating the calculated field with its original expression, and inserting a Sum() function around the denominator as well as the numerator. The default expression, created when we dragged the calculated field to the report layout, simply inserted the calculated field wrapped in a single Sum() function, =Sum(Fields!Profit_Margin.Value).

The Edit Expression dialog appears as depicted in Illustration 37.


Illustration 37: The Modified Expression in the Edit Expression Dialog

69.  Click OK to accept the modification, and to return to the Layout tab.

70.  Leaving the Profit Margin textbox selected, open the Properties window (I keep mine docked to the right of the Layout tab) for the text box.

71.  For the time being, replace the "C0" (currency without decimals) in the Format box of the Properties dialog with "P" (percentage with 2-decimals).

We will revisit this setting in the following section. The Properties dialog appears as partially shown in Illustration 38, with our modification circled.


Illustration 38: Format Modification, Properties Dialog

Let's execute the report to ensure all operates correctly, at this point.

72.  Click the Preview tab.

73.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

74.  Click the View Report button to execute the report.

The report runs and returns the data associated with the Food Product Family, once again. We note the appearance of the new Profit Margin calculated field, as depicted in Illustration 39.


Illustration 39: Report (Partial View) with New Profit Margin Calculated Field

We have made the structural changes as requested by the information consumers, and we are now ready to put into place the conditional formatting they have stated that they wish to see.

Add Parameterized Conditional Formatting to the Report Structure

Parameterized conditional formatting forms the nucleus of our session. There are several steps, and multiple report components, involved in bringing about the capability that the information consumers have requested. These steps can be done in more than one way, and in more than one order. We will first perform standard conditional formatting, to make the process clear, and then we will add parameterization as an independent step, once we have seen the operation of conditional formatting in its simplest form.

Add Simple Conditional Formatting to the Report

First, we will add conditional formatting in a way that might be adequate where formatting is based upon fixed criteria, which is applied each time the report is run. The values in the report change over time, in most cases, and the application of the criteria to the report with each execution means that values meeting the criteria we have designated will be brought to attention of the user of the information.

Let's return to the Layout tab of the Report Designer to get started.

1.  Click the Layout tab.

2.  Click the Profit Margin textbox, within which we made the changes to the calculation in our last section, to select it.

3.  Leaving the Profit Margin textbox selected, open the Properties window for the textbox.

4.  In the Color property box, select <Expression...> with the downward arrow, as shown in Illustration 40.


Illustration 40: Select <Expression...> from the Color Property Selector

The Edit Expression dialog appears.

5.  Change the expression that appears in the Expression box, "Black," to the following:

=IIF(Sum( Fields!Store_Profit.Value)/Sum(Fields!Store_Sales.Value)  < .60 , "Red", "Black")

With this expression, we are simply specifying "if the Store Margin evaluates to less than .60 (sixty-percent), then make the font color red for the value; otherwise leave it black." Remember that this is a simple use of conditional formatting, and although the data values themselves may change with each new report execution, the criteria itself will remain fixed.

The Edit Expression dialog appears as depicted in Illustration 41.


Illustration 41: Simple Conditional Formatting Logic in the Edit Expression Dialog

6.  Click OK to accept the modification, and to return to the Layout tab.

Let's execute the report, once again, to verify the effectiveness of our work.

7.  Click the Preview tab.

8.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

9.  Click the View Report button to execute the report.

The report runs and returns the data we have requested. We note the effect of the conditional formatting expression we have installed, as partially shown in Illustration 42.


Illustration 42: Report (Partial View) with Simple Conditional Formatting in Place

Our expression appears to have been effective in rendering a simple conditional formatting solution. In the next section, we will expand our solution to include parameterization, and thus support the requirement of the information consumers to be able to specify the criteria at runtime, rather then having the conditional formatting apply to a fixed threshold (sixty percent, in our example) that is "hard coded" into the expression.

Parameterize the Conditional Formatting in the Report

In adding parameterization to our conditional formatting, we begin by simply creating a parameter in our report, as we shall see. We then change the textbox expression we put into place in the last section to reference the parameter, versus a fixed threshold.

NOTE: For more on parameters in general, see my article Mastering OLAP Reporting: Cascading Prompts.

We will return, once again, to the Layout tab, to begin putting our final solution into place.

10.  Click the Layout tab.

11.  Select Report --> Report Parameters from the main menu, as depicted in Illustration 43.


Illustration 43: Select Report --> Report Parameters ...

The Report Parameters dialog appears.

12.  Click Add to begin a new report parameter.

A new parameter with default name appears.

13.  For the dialog boxes shown in Table 1, type the corresponding items (replacing defaults where required):

In this Dialog Box:

Type the following:

Name:

pX_PerfThresh

Prompt:

Performance Threshold (%):

Data type:

Float

Available Values:

Non-queried

Default Values:

None (default)

Table 1: Performance Threshold Parameter Details

The completed Report Parameters dialog appears as depicted in Illustration 44.


Illustration 44: Completed Report Parameters Dialog ...

14.  Click OK to accept input and close the dialog, returning to the Layout tab.

Having created the Performance Threshold parameter, we can now reference it in the conditional formatting expression we placed in the Color property of the Profit Margin textbox in our last section. (Had we not chosen to explore setup of a simple conditional formatting scenario, with fixed criteria that is "hard coded" into the expression, we could have created the parameter first, and then put the expression in place directly, referencing the parameter from the outset.)

15.  Click the Profit Margin textbox, within whose Properties settings we worked in our last section, to select it once again.

16.  Leaving the Profit Margin textbox selected, open the Properties window for the textbox.

17.  Select <Expression...> from the selector in the Color property box, as we did in the previous section.

The Edit Expression dialog appears.

18.  Replace the following part of the expression:

.60

With the following reference to the parameter we have created:

Parameters!pX_PerfThresh.Value/100

With this expression, we are simply extending the previous logic to reference the ad hoc performance threshold parameter, as input by the individual executing the report. The division by 100 allows the information consumer to input whole numbers, perhaps making it easier for them to input percentages "on the fly."

It is important to note that the parameter Data type must be set to Float to allow for the input of decimal places at runtime, a feature we anticipate that the information consumers would want. (We might make "suitable input" format even more obvious to the consumers by building an example into the parameter Prompt, such as "Performance Threshold (ex. 59.99);" the possibilities here are, of course, legion.)

The Expression box of the Edit Expression dialog, with our modification circled in red, appears as shown in Illustration 45.


Illustration 45: Parameterized Conditional Formatting Logic in the Edit Expression Dialog (Modification Circled)

19.  Click OK to accept the modification, and to return to the Layout tab.

Let's execute the report, once again, to verify the effectiveness of our work.

20.  Click the Preview tab.

21.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

22.  Type the following into the Performance Threshold (%) box, which now appears to the right of the original picklist prompt:

59.75

23.  Click the View Report button to execute the report.

The report runs and returns the data we have requested. We note the effect of the conditional formatting expression we have installed, as shown in Illustration 46.


Illustration 46: Report (Partial View) with Parameterized Conditional Formatting in Place

Our parameterized conditional formatting appears effective, indeed, as we see the Profit Margin values that lie below the threshold of 59.75 appear in red, with the remaining values displayed in black. We have thus met the requirements of the information consumers, providing the specified structural changes, together with the capability to apply conditional formatting based upon an ad hoc parameter that they can vary to meet the specific business need at report runtime.

24.  Select File --> Save All to save our work so far.

25.  Exit Reporting Services when desired.

Far more elaborate uses of conditional formatting can be devised, as most of us can imagine. We will certainly explore some of these options in prospective articles. The basic concepts, however, have been largely presented in our practice exercise, which employs a commonly observed conditional formatting objective in highlighting fonts based upon various criteria. I have used conditional formatting / other properties to perform a wide range of activities, ranging from the ad hoc presentation of subreports to sorting fields that appear in a given report. I am always interested in hearing of innovations in this, and other areas of development, within the exciting Reporting Services realm, and invite correspondence with regard to unique needs in this arena.

Conclusion...

In this article, we introduced conditional formatting in one of its simplest possible incarnations, and then built upon the basics to illustrate how we might approach parameterization of conditional formatting, to allow information consumers to make runtime decisions surrounding the formatting in their reports. After discussing conditional formatting in general, we prepared for our practice example much as we would in a real world scenario, where an existing report meets many business needs, but can be improved upon by making a few modifications and adding conditional formatting.

After making minor structural changes to a copy of a sample report to meet the business requirements of a hypothetical group of information consumers, we created a condition whose fixed criteria was applied, upon execution, to the report value under consideration. We then created a parameter with which we associated our conditional formatting expression, to provide information consumers the ad hoc capability to apply conditional formatting in a manner that flexes to specific needs at runtime. Finally, throughout our practice example, we discussed the results obtained with the development techniques that we exploited, previewing our report at appropriate junctures to ascertain their effectiveness.

» 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