Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services

Monday May 21st 2007 by William Pearson
Share:

Leverage Analysis Services – based Conditional Formatting in Reporting Services. BI Architect Bill Pearson demonstrates a simple alternative to reliance upon Cell Properties.

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”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

Introduction

In Positing the Intelligence: Conditional Formatting in the Analysis Services Layer (a recent member of my Database Journal Introduction to MSSQL Server Analysis Services series), we introduced conditional formatting in the Analysis Services layer of the integrated Microsoft business intelligence solution. We noted, there as well as elsewhere throughout my series’, that an important consideration, when designing a business intelligence system within any environment, is “where to put the intelligence” among the various “layers” within the system.  I have stated many times in the past, in both articles and presentations, that “multi-layered reporting solutions require multi-layered architects.” Nowhere is this more apropos than within the design and implementation of the integrated Microsoft business intelligence solution.  A “typical” implementation for me includes the following:

  • MSSQL Server:  An RDBMS layer, consisting usually of both relational / OLTP data sources and warehouse(s) / mart(s);
  • Analysis Services:  The OLAP layer, at the heart of which resides one or more OLAP cubes;
  • Reporting Services:  The Reporting layer, within which both relational and OLAP reports are authored, managed and delivered.

Many of my articles give examples of layering considerations among the above, just a few of which include:

  • Housing functions and calculations at the MSSQL Server (or any other enterprise-level RDBMS) Database layer (be it relational, star-schema, or other), which are leveraged in the cubes or reporting system;
  • Building structures within the Analysis Services OLAP layer to provide picklist, conditional formatting and other support to the Reporting layer of the system;
  • Building virtually anything we need in the way of calculated fields, parameterization support, conditional formatting, and more at the Reporting layer.  This can be seductively easy within the flexible vacuum of the Reporting Services design environment, but can often be the worst place to house the associated structures from an optimization perspective.

Regardless of whether the enterprise implements the entire solution using in-house talent or seeks skill / other resource augmentation from consultants, it is critical to seek a multi-dimensional perspective in the planning and design stages of the implementation, at a minimum.  Much of the time, money and aggravation that are the natural issue of a haphazard approach is unwarranted, and can haunt the enterprise for a long time. Relying upon application specialized “gurus” (or, even worse, the placement organizations that purport to “pre-qualify” such specialists) to meet business requirements, with an architecture that will scale with the organization, can be a career-limiting move.

For a general summary of my opinions surrounding the importance of thinking "multi-dimensionally" within the design and implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

Leverage Conditional Formatting Logic from the Analysis Services Layer

The reporting / presentation layer of the integrated Microsoft business intelligence solution is often the only point of interaction for organizational information consumers. It is here that we often find it desirable to manifest the effects of conditional formatting. Conditional formatting typically includes the modification of the visual appearances of report items, such as size, fonts, color or background color to draw attention to a given condition (such as the assignment of a red background color to any value representing an inordinately high expense total for a division), to generally class / group values based upon conditions (for example, the operating income of all units meeting their expressed goals is presented in green numerals, with the values for those falling short of the mark exhibiting red numerals), and so forth. Conditional formatting can serve many purposes, ranging from simple “highlighting” of conditions of this sort to more sophisticated functions that go well beyond mere color changes.

Because a reporting tool like MSSQL Server Reporting Services (“Reporting Services”) makes conditional formatting easy to put into place, and because it is within the deployed reports that the resulting data presentations are manifest, we often assume that conditional formatting is naturally handled within the Reporting layer. Complications arise, however, when we have multiple values to which such formatting is applied, or when our conditional formatting expressions become complex and resource intensive. Say the expression performs a somewhat lengthy comparison process between the value under consideration and multiple possible values, for classifying the resulting value within a bucket, to each of which we assign a specific formatting attribute. It is easy to see how report processing time can be adversely affected when the conditional testing and resulting formatting are applied to, say, every measure in a given column (or even multiple columns) of the report.

Analysis Services allows us to apply conditional formatting at the cube level, where the values can be calculated and stored as a part of structure of the cube, in some respects. This not only might mean far more efficient report processing (where, for example, the expression in the report that dictates the conditional formatting is substituted by an expression does a straightforward “pull” of the result(s) of the conditional test from the cube (say, a color description), where it has already been determined, versus performing the logic in the report itself at runtime, and then assigning the desired attribute for each report value individually. In addition to the often obvious performance enhancement that results, the logic can be stored (and consistently maintained and enforced), from a single location within the integrated solution, instead of hardcoded into every report for which the same conditional formatting logic is applicable.

In Positing the Intelligence: Conditional Formatting in the Analysis Services Layer, we examined the placement of conditional formatting logic within the cell properties of a calculated measure. As examples, we illustrated the use of the MDX CASE() and IIF() functions in setting the cell properties listed in Table 1.

Cell Property

Possible Use in Reporting Layer

Color Expression: Fore Color

Specify / dynamically set the foreground color (the color of the text) of the value of the calculated member within the browser or a report.

Color Expression: Back Color

Specify / dynamically set the color of the cell background containing the value of the calculated within the browser or a report.

Font Expression: Font Flag

Specify / dynamically set font “weight” attribute (including Normal, Bold, Heavy, etc.) for cells based upon the calculated member.

Table 1: Sample Cell Properties and Possible Uses ...

NOTE: For detailed information about the MDX CASE() and IIF() functions, see related articles within my Database Journal MDX Essentials series.

The cell properties to which we refer above are ignored within client applications that do not support them. Support for cell properties is not among the multiple (largely performance related) benefits provided by Reporting Services’ algorithm for generating a flattened rowset (as defined in the ODBO specification). While these properties may be accessed in other ways, we will examine an option for achieving this objective in a very “out-of-the-box” manner that is both intuitive and easily maintained. A primary advantage gained by embedding conditional formatting logic within the Analysis Services layer is still provided: the support of dynamic attribute presentation within the reporting layer is stored in, and consistently applied from, a central, easily accessible location.

In this article, therefore, we will focus upon such an approach, which we will then leverage in Reporting Services. We will begin with a scenario similar to the one we used in Positing the Intelligence: Conditional Formatting in the Analysis Services Layer, from which we will obtain an identical end result within Reporting Services, albeit through a different mechanism within the Analysis Services layer.

Adding Conditional Formatting Support within the Cube

We will again consider an example of a need for conditional formatting that we might, at least initially, consider putting in a report: let's say that a client needs a couple of things to happen based upon the value of a calculated measure within the Adventure Works cube. Not only does the client want the color of the value text (referred to as the “Fore Color” property for the calculated member in Analysis Services) to vary, based upon the magnitude of the value, but the representatives with which we are working tell us that it is also desirable for the background color of the cell containing the value (referred to as the “Back Color” property for the calculated member in Analysis Services) to also vary, depending upon the value of the resident measure.

To be specific, the client wants to enhance an existing report, which deals with measures based upon the reasons driving Internet Sales, the Sales Reason Comparison report (one of the samples that accompany the installation of MSSQL Server 2005 Reporting Services). First, the client wishes to add Internet Gross Profit Margin (a calculated member currently existing in the cube) to the existing measures contained in the report, Internet Orders, Internet Sales Amount, Internet Total Product Cost (a calculated field in the report). Moreover, and the focus of our discussion in this article, the client representatives make known a requirement for conditional formatting of the proposed addition, Internet Gross Profit Margin.

Our client colleagues tell us that the information consumers have expressed a need to see conditional formatting of the Internet Gross Profit Margin to alert them, at a glance, to the margins (related to the expressed reasons that customers made purchases) that fall above and below certain thresholds, so as to isolate these occurrences for more in-depth exploration (as to the root causes, etc.). Specifically, they would like to see any margin falling below forty percent (40%) to be displayed in an attention-grabbing red font, coupled with a yellow background color for the cell housing the margin (producing a “highlighter” effect to further draw attention to the underperforming margin). Moreover, the margins within the ranges of forty – to – forty-two percent (40.0% - 42.0%), inclusive, are to remain in the standard black text, with the white background that currently exists in the report. Finally, the group tells us that any Internet Gross Profit Margin exceeding forty-two percent (42 %) needs to be presented with a green font, with an accompanying light green background, to once again emphasize its “outlier” nature to information consumers. As a finishing touch, our client colleagues tell us that they would prefer a bold font to accompany the green text assigned to the Internet Gross Profit Margin exceeding forty-two percent (42 %), so as to highlight even more the respective superior performance.

We discuss the requirements with the client representatives, mentioning that, while the conditional formatting can certainly be managed within various property settings of the report, we will demonstrate the generation of the desired physical attributes from the Analysis Services layer. Positing the intelligence within the cube will, we assert, mean that the same logic can be carried forward to multiple reports by simply referencing the calculations in the data source, versus performing the calculations for every affected measure in the report (and adding to processing time, etc.). Moreover, we emphasize that maintaining the logic in a single location in the Analysis Services layer means a single point of maintenance: we can modify the logic in one place and rely upon the changes to “ripple through” to all reports that reference that logic, versus having to mechanically modify each individual report. This also ensures consistency of application of the logic to all affected reports, as none will be overlooked (or different logic accidentally applied to different reports) when a change of this nature becomes necessary.

Conditional Formatting in Analysis Services

We begin our efforts by opening the cube within a project in the Business Intelligence Development Studio. I like to set up a lab environment for each of my client projects where I have both the respective cubes and reports involved with the engagement within an integrated solution in Visual Studio, both for ease in testing cube modifications through to the report layer from a single, central location, as well as for taking advantage of effective source control and numerous other conveniences and advantages. For example, in this particular case, I have both a copy of the sample Adventure Works DW and the AdventureWorks Sample Reports projects added into a single solution within the Business Intelligence Development Studio, where I can access all member objects from one point, the Solution Explorer.

1.  Open the Adventure Works cube from within the Solution Explorer.

2.  Once the Cube Designer opens, select the Calculations tab.

Here we will create an independent calculated member for each of the three attributes (Fore Color, Back Color and Font Flag, to which we refer in Table 1 above) for which we set cell properties used in Positing the Intelligence: Conditional Formatting in the Analysis Services Layer. As is quite often the case in meeting business requirements via the tools and features of Analysis Services in general, there are multiple ways to achieve our ends. In situations like the present scenario, I have created derived measures, as well as taken other approaches, to achieve what we will accomplish with calculated measures in this session. (I sometimes use the terms “calculated member” and “calculated measure” interchangeably; in the present session, we will be working with calculated members that we add to the Measures dimension, hence I’ll refer to them as “calculated measures” in most cases.) The needs of the local environment should, of course, dictate the criteria by which solutions are selected for production systems; we are only examining one simple approach in this article.

Let’s get started by adding the conditional formatting for Fore Color to our cube, via a calculated measure.

3.  Select (by clicking) Internet Gross Profit Margin within the Script Organizer pane in the upper left corner of the Calculations tab, simply to place the cursor.

4.  Click the New Calculated Member button in the toolbar just above the Script Organizer pane.

A new entry is added to the Script Organizer pane, underneath the row labeled Internet Gross Profit Margin (we can, of course, move entries up or down via the buttons / context menu items provided).

5.  Type the following into the Name box within the Form View that has appeared for the new calculated member:

[Internet GPM Fore_Color]

NOTE: Be sure to enclose the name within the brackets as shown, anytime there are spaces within the string.

6.  Expand the Expression section of the form, if necessary, and then type the following MDX expression into the box:


CASE
   WHEN [Measures].[Internet Gross Profit Margin]< .40 
      THEN "Red"
   WHEN [Measures].[Internet Gross Profit Margin]> .42 
      THEN "Green"
   ELSE "Black"
END

All we are doing here is generating a color description that will be acceptable to Reporting Services (as are the appropriate color codes). We are using the MDX CASE() function to drive conditional formatting. (Another option might have been to do so with the (appropriately nested) IIF() function, of course; we will see an example of IIF() at work in the last of the three calculated measures we create in our practice session.)

7.  Expand the Additional Properties section, if necessary, and leaving all other settings at default, select Internet Sales Amount within the Non-empty behavior selector. Click OK to close the selection dialog, once completed.

The form for the new Internet GPM Fore_Color calculated measure appears, with our input, as depicted in Illustration 1.


Illustration 1: Our Input for the New Calculated Measure ...

Next, we will add the logic to support the conditional formatting requirements for Back Color and Font Flags that we have mentioned.

8.  In a manner similar to that performed above, add two additional calculated measures, with the Name, Expression and Non-empty behavior settings specified in Table 2.

Name

Expression

Non-empty Behavior

[Internet GPM Back_Color]

CASE

WHEN [Measures].[Internet Gross Profit Margin]< .40

THEN "Yellow"

WHEN [Measures].[Internet Gross Profit Margin]> .42

THEN "GreenYellow"

ELSE "White"

END

Internet Sales Amount

[Internet GPM Font_Flags]

IIF([Measures].[Internet Gross Profit Margin]> .42,

"Bold", "Normal" )

Internet Sales Amount

Table 2: Settings for Two Additional Calculated Measures

Our calculated measures appear, within the Script Organizer, as shown in Illustration 2.


Illustration 2: The New Calculated Measures within the Script Organizer ...

At any point of input, we can check the syntax we have accumulated within the Calculations tab via the Check Syntax button in the Calculations toolbar. The next step will be to deploy the project, which we will do in preparation for using our new conditional logic support structures within Reporting Services.

9.  Deploy the Analysis Services project within the local environment.

10.  Once deployment is complete, close the Deployment Progress viewer.

Leverage Analysis Services - Based Conditional Formatting in Reporting Services

At this point, we are ready to leverage our new calculated measures within Reporting Services to present the conditional formatting our client colleagues have requested. Had we established the pre-designed formatting options within the Color Expressions and Font Expressions sections of the associated Internet Gross Profit Margin, as we did in Positing the Intelligence: Conditional Formatting in the Analysis Services Layer, and assuming that we were using a reporting solution that exposed these cell properties, we might simply be able to reference the properties within the associated formatting properties within the .rdl file that comprises the respective report.

The difference we experience in using independent calculated measures (due to the absence of support for cell properties within Reporting Services) is simply that we reference both the Internet Gross Profit Margin (as the report value) and the calculated measures (via the associated value properties) independently within our report. The only complication, and it is a minor one, is that each calculated measure must return the respective format setting in the proper style, as we shall see.

Use Analysis Services Calculated Measures to Support Formatting in Reporting Services

A convenient report within which to leverage the new cube-based conditional formatting is the sample Sales Reason Comparisons report, which we have used in past practice sessions. (As usual, I recommend that you make a copy of the Sales Reason Comparisons.rdl file with which to practice, so as to preserve the original report for work with the online tutorials, and so forth).

1.  Once inside the report (whether you have added it to the already open Adventure Works project within Business Intelligence Development Studio, or have opened it independently), the first thing to do will be to modify the existing ProductData dataset (on the Data tab) to include the following calculated measures from the Adventure Works DW Analysis Services database, with which we worked in the previous section:

  • Internet Gross Profit Margin
  • Internet GPM Fore_Color
  • Internet GPM Back_Color
  • Internet GPM Font_Flags

NOTE: I created a named set within the cube, into which I placed the above-calculated measures. I then simply added the named set to the column axis of the query. While it is quite all right to add the calculated measures individually, of course – I often use named sets in this manner for various reasons when creating Analysis Services-based datasets within Reporting Services, as I discuss in many articles within this series.

2.  Click the Refresh button in the Data tab toolbar, above the Query pane.

3.  Click the Execute the Query button in the Data tab toolbar to repopulate the Data grid.

4.  Click the Refresh Fields button to refresh the fields within the ProductData report dataset within the Datasets pane, which will appear, at this stage, similar to that depicted in Illustration 3.


Illustration 3: The Fields Appearing within the ProductData Report Dataset

A quick review of the Data grid allows us to see that the information generated by the calculated members’ conditional formatting logic is returned, as expected, for use within our report. A sample of the respective data appears within the dataset partially shown in Illustration 4.


Illustration 4: A Sample of the Output of the Calculated Members ...

Having entrained conditional formatting “instructions” for each value of the Internet Gross Profit Margin into the report dataset, we can now associate these instructions with the respective properties in the Layout tab.

5.  Click the Layout tab.

Here we will add the new value upon which we wish to report, Internet Gross Profit Margin, to the existing matrix data region. Once this new field is in place, we will apply our conditional formatting “instructions” to its property settings.

6.  Click the Internet Gross Profit Margin field, within the ProductData report dataset in the Datasets pane, and then drag the field to the right edge of the (currently) rightmost column within the matrix data region (until the pointer highlights the right edge of the text box containing Internet Total), as depicted in Illustration 5.


Illustration 5: Adding Internet Gross Profit Margin to the Matrix ...

7.  Drop Internet Gross Profit Margin into the matrix.

A new column, whose heading label reads “Internet Gross Profit Margin,” appears.

8.  Click the newly created textbox containing the label Internet Gross Profit Margin to select it.

9.  Click the Center button in the formatting group of the toolbar atop the design environment, as shown in Illustration 6.


Illustration 6: Centering the New Column Label ...

10.  Right-click the newly added Internet Gross Profit Margin value textbox (underneath the label we have centered), and then select Properties from the context menu that appears.

11.  Click the Format tab in the Textbox Properties dialog that opens.

12.  Type “P1” within the Format code box (in the upper left-hand corner of the Format tab), to format the Internet Gross Profit Margin value as a percentage.

13.  Click OK to accept the new property setting and close the Textbox Properties dialog.

Internet Gross Profit Margin has now become a member of the report.

14.  Begin execution of the report by clicking the Preview tab at this point. Select any Product Category within the parameter dropdown selector, and then click the View Report button, to ascertain that the newly added Internet Gross Profit Margin value appears as expected.

Next, we will apply the conditional format property settings we have retrieved from Analysis Services to the associated property settings surrounding our new data field.

15.  Return to the Layout tab, and select the Internet Gross Profit Margin value textbox, once again, if necessary.

16.  Within the Properties pane, (its default location is below the Solution Explorer pane on the right side of the report design environment), click the downward-pointing selector arrow within the box labeled Color, and select the top entry, <Expression...>.

17.  Type the following into the input box (replacing any pre-existing text) atop the expression editor that appears next:

=FIRST(Fields!Internet_GPM_Fore_Color.Value)

The relevant portion of the expression editor appears with our input as depicted in Illustration 7.


Illustration 7: Referencing the Fore Color Calculated Measure in the Respective Textbox Property (Partial View)

Here we are referencing the appropriate field from the underlying dataset, which supplies the color value as conditionally generated by the Internet GPM Fore_Color calculated measure we added within the Analysis Services layer.

18.  Click OK to accept our input and to close the expression editor.

19.  Again, within the Properties pane for the Internet Gross Profit Margin value textbox, click the downward-pointing selector arrow within the box labeled Background Color. Select the top entry, <Expression...>, as before.

20.  Replace any pre-existing text within the input box atop the expression editor with the following string:

=FIRST(Fields!Internet_GPM_Back_Color.Value)

Here we are referencing the field supplying the color value as conditionally generated by the cube – based Internet GPM Back_Color calculated measure.

21.  Click OK to accept our input and to close the expression editor.

22.  Finally, and once again within the Properties pane for the Internet Gross Profit Margin value textbox, expand the Font properties group, just under the Color property with which we worked earlier.

23.  Click the downward-pointing selector arrow within the Font properties box labeled FontWeight. Select the top entry, <Expression...>, once again, to launch the expression editor

24.  Type the following (replacing any pre-existing text, as before) into the input box atop the expression editor:

=FIRST(Fields!Internet_GPM_Font_Flags.Value)

We have now referenced the calculated measures which we have entrained into the report dataset for purposes of driving the required format properties settings.

Verification: Preview Conditional Formatting in Reporting Services

Now that we have associated the conditional formatting logic with the respective properties of the value to which they are to be applied, we are ready to preview the report to ascertain expected results.

1.  Click the Preview tab, once again.

2.  Select Bikes within the Product Category parameter selector, and click the View Report button to execute the report.

The report executes, generating the expected formatting for the values contained within the new Internet Gross Profit Margin column. A portion of the Preview results appears in Illustration 8.


Illustration 8: Analysis Services – based Conditional Formatting at Work ... (Partial View)

As we can see, all three properties of the newly added Internet Gross Profit Margin value appear to be functioning as specified and expected. The conditional formatting which we have put into place within the Analysis Services calculated measures, and which we have entrained into our supporting report dataset, and then referenced within the respect properties of the value under consideration, appear to be supporting both the color (text and background) and the font weight properties that our client colleagues have requested.

Because of the flexible, expression-based nature of Reporting Services, we can support other format-related report properties after a manner similar to that of our basic example. In so doing, we can leverage cube-based conditional formatting, and enjoy the multiple benefits we have enumerated, with ease. Through options such as those we have explored, it becomes obvious that, although Reporting Services does not expose cell properties in Analysis Services directly, there are other ways to make cube-based formatting just as accessible to report authors. (I introduce variations on this theme within various other articles of my MSSQL Server Reporting Services series at Database Journal).

Conclusion

In this article, we resumed a discussion that is no doubt familiar to anyone who regularly reads my articles about the integrated Microsoft business intelligence solution. We once again examined considerations surrounding the “placement of the intelligence” within the layers of the solution, and then focused upon another specific example of such a consideration here: the placement of the logic to support conditional formatting in the Reporting layer. We emphasized the fact that selection among the various layers for positing business intelligence support can become extremely important in enterprise-level design and implementation, and that “multi-level awareness” – and expertise - within the team involved is required to optimize a multi-level solution.

We noted that, because most enterprise reporting tools (such as MSSQL Server Reporting Services) make conditional formatting easy to put into place, and because it is within the deployed reports that the resulting data presentations are manifest, it is often assumed that conditional formatting is naturally handled within the Reporting layer. Because conditional formatting expressions can become complicated and resource intensive, processing time and other attributes of report processing can be adversely affected, and report maintenance can become overly cumbersome.

As a possible means of optimizing the overall business intelligence solution, we proposed that the Analysis Services layer might serve as an optimal home for conditional formatting support, even though cell properties which can be specified with conditional formatting in mind are not directly supported by Reporting Services. We proposed, as an alternative approach, that calculated members within the cube can contain logic to support the conditional formatting of report item properties. We examined three examples of creating calculated measures within Analysis Services with conditional logic to support the conditional formatting needs of a hypothetical client. Finally, we verified the effectiveness of our approach via a modified sample report within Reporting Services, where we enacted Analysis Services – based conditional formatting within the Reporting layer.

» 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