Positing the Intelligence: Conditional Formatting in the Analysis Services Layer

Monday May 14th 2007 by William Pearson

Support conditional formatting for enterprise reports from the Analysis Services layer of the integrated Microsoft business intelligence solution.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article see Usage-Based Optimization in Analysis Services 2005, another article within this series.


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:  The RDBMS layer, consisting usually of both relational / OLTP data sources and warehouse(s) / mart(s);
  • Analysis Services:  The OLAP layer, consisting of multiple OLAP cubes;
  • Reporting Services:  The Reporting layer, within which both relational and OLAP reports (as well as combinations of these types) are authored, managed and delivered.

(It is not uncommon to find other reporting applications within the Reporting layer, or to encounter non-Microsoft RDBMS’ underneath the OLAP layer, particularly in the initial phases of conversions to the integrated Microsoft business intelligence solution. Conversions of this sort are becoming very common, and staging of the components that occupy the primary layers is approached in many ways.)

In the search for a qualified Business Intelligence Architect, particularly in what is becoming the common backdrop of an integrated solution combining end-to-end relational, OLAP and reporting strata, corporate decision-makers and implementation teams need to rely upon architects that understand all the layers.  The practice of seeking a “reporting guru,”  “OLAP architect,”  or “data warehouse designer” to meet business needs often leads to a suboptimal solution, because the “stratum specialist,” often working in a vacuum, tends to build functionality into the level he / she knows, and not necessarily into the level that best serves the integrated mechanism.  When we add the inherent inability of the “placement industry” (for organizations that cannot or will not do their own research), to grasp even the requirements of the discrete layers, the odds of anything but a suboptimal outcome become small, indeed, when the enterprise relies upon “layer specialists.”

Many of my articles give examples of considerations of this sort, 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. 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.

Conditional Formatting in the Analysis Services Layer

The Reporting / Presentation layer of the integrated Microsoft business intelligence solution is quite 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 dynamic 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 and font changes.

Because a reporting tool like MSSQL Server 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 most naturally to be 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 complicated and resource intensive. Say the expression performs a somewhat lengthy comparison process between values under consideration and multiple possible values, to support the classification of the values within buckets, 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 value in a given column 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 many 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 that does a straightforward “pull” of the result(s) of the conditional test from the cube (such as a color description). In such a scenario, the outcomes of the test have already been determined, so the testing, specification of format properties, and so forth, do not need to be performed in the report itself at runtime, with the reporting application 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 this session, we will examine the process of embedding the logic to support conditional formatting within a cube for a calculated measure. While we will be examining a relatively simple set of circumstances in the interest of time and space, we should keep in mind that the concepts involved (the fact that we do, indeed, have options, for placing support of such capabilities among the multiple layers of the architecture) can become extremely important in enterprise-level design and implementation. Perhaps one of the most important considerations I hope to make plain is that, as I have mentioned many times, determining the placement of components among the layers requires far more than expertise in Analysis Services or Reporting Services alone, an excellent grasp of SQL or MDX, an outstanding capability to generate relational reports from MSSQL Server, in general, or mastery of any of the other discrete layers of the integrated solution. “Multi-level awareness” – and integrated expertise - is required to optimize a multi-level solution.

Adding Conditional Formatting Support within the Cube

Let’s 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 measure in Analysis Services) to vary, based upon the magnitude of the value, but the representative with which we are working tells 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, the Sales Reason Comparison report (one of the samples that accompany the installation of MSSQL Server 2005 Reporting Services), which presents measures grouped by the reasons driving Internet Sales. First, the client wishes to add Internet Gross Profit Margin (a calculated measure currently existing in the cube) to the existing measures contained in the report, Internet Orders, Internet Sales Amount, and 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 their 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: None will be overlooked for prospective modifications in the logic, for instance, when this becomes necessary, nor will different logic be accidentally applied to different reports.

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 or research projects where I have both the respective cubes and reports involved with the engagement within an integrated solution in Visual Studio. This ensures ease in testing cube modifications through to the report layer from a single, central location, as well as providing the advantage of effective source control and numerous other conveniences. 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.

3.  Select Internet Gross Profit Margin within the Script Organizer pane in the upper left corner of the Calculations tab.

The various properties of the calculation appear upon the face of the Calculations tab (the Form View). It is here that we will posit the logic to support conditional formatting of the calculated measure.

4.  Click the downward pointing arrow symbols to the immediate left of the Color Expressions label in the lower half of the tab, as shown in Illustration 1.

Illustration 1: Expanding the Color Expressions Properties

This exposes two textboxes, Fore color and Back color, within which we can add the logic to support conditional formatting of the associated color properties.

5.  Type, or cut and paste, the following syntax into the Fore color box:

   WHEN [Measures].[Internet Gross Profit Margin]< .40 
      THEN 255 /*Red*/
   WHEN [Measures].[Internet Gross Profit Margin]> .42 
      THEN 32768 /*Green*/
   ELSE  0 /*Black*/

Here we are using the MDX CASE() function to drive conditional formatting. (Another option might have been to do so with the more basic IIF() function, of course.) The color codes are obtained by clicking the Color Palette button that appears outside the upper right corner of the Fore and Back color input boxes. Clicking the desired color from the graphical selector that appears will cause the corresponding color code, together with a commented color description, to be inserted at the end of coding within the respective input box upon selection. (For example, 32768 /*Green*/ appears above when we click the corresponding color within the color palate selector).

Next, we will add the logic to support the conditional formatting requirement for Back color.

6.  Type, or cut and paste, the following syntax into the Back color box:

   WHEN [Measures].[Internet Gross Profit Margin]< .40 
      THEN 65535 /*Yellow*/
   WHEN [Measures].[Internet Gross Profit Margin]> .42
      THEN 8454016 /*R=128, G=255, B=128*/
   ELSE 16777215 /*White*/

The Color Expressions input boxes, together with our input, appear as depicted in Illustration 2.

Illustration 2: Color Expression Input Boxes with Our Input ...

Finally, we will deal with the conditional bolding of the font requested by our client colleagues. Here we will use an IIF() function for the simple requirement.

7.  Click the downward pointing arrows to the immediate left of the Font Expressions label, just beneath the Color Expressions section within which we have been working, to expand the three font-related expression input boxes.

8.  Type, or cut and paste, the following syntax into the Font flags box (the bottom of the three Font Expressions input boxes):

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

9.  Leaving the cursor to the right of the comma (“,”) in the expression above, click the Font button that appears to the right of the Font flags input box.

The Font dialog appears, affording us the opportunity to make easy selection of font flagging predicates for our syntax, much as we are able to do via the Color Palette button we discussed earlier.

10.  Select Bold by clicking it within the Font style selection list within the middle, upper portion of the Font dialog.

The associated code for the selected combination of font flags appears within the Font flags input box.

11.  Click OK to accept the font selection, to dismiss the Font dialog, and to append the appropriate syntax into the Fore flags box.

12.  Place a comma (“ , ”) to the right of the string as it currently exists in the Font flags box.

13.  Leaving the cursor to the right of the newly added comma, click the Font button to the right of the Font flags input box, once again.

14.  Select Regular by clicking it within the Font style selection list, to add the Regular outcome to our syntax (for the Internet Gross Profit Margin values that do not exceed forty-two percent (42.0 %).

The associated codes for the selected combination of font flags will next appear within the Font flags input box.

15.  Click OK to accept the font selection, to dismiss the Font dialog, and to append the appropriate syntax into the Fore flags box.

16.  Place a right parenthesis ( “)” ) at the end of the input string to close the IIF() function properly.

The completed string within the Font flags input box should appear as shown in Illustration 3.

Illustration 3: Syntax within the Font Flags Input Box ...

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 of verifying the adequacy of our input via the Cube Browser.

17.  Deploy the Analysis Services project within which you're working.

18.  Once deployment is complete, click the Browser tab within the Cube Designer.

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

Examine Conditional Formatting at Work in the Browser

Let’s set up the browser axes to approximate the proposed report display, so as to verify the intended results of our conditional formatting handiwork.

1.  Click the Reconnect button in the Browser toolbar.

2.  Expand the Sales Reason dimension in the cube metadata tree, and drag the Sales Reason attribute hierarchy to the empty data grid, dropping it in the area marked Drop Row Fields Here.

3.  Expand the Sales Territory dimension in the cube metadata tree, and drag the Sales Territory Group attribute hierarchy into the area marked Drop Column Fields Here.

4.  Expand Measures, and then the Internet Sales Measure Group. Drag the following measures / calculations (preferably in the order shown) to the area of the grid marked Drop Totals or Detail Fields Here:

  • Internet Order Quantity
  • Internet Sales Amount
  • Internet Gross Profit Margin

A partial view of our newly assembled and populated browser grid is depicted in Illustration 4.

Illustration 4: Reconnect: The Browser with our Viewer Settings (Partial View) ...

And so we see that our conditional formatting logic appears effective.

We can leverage cube-based conditional formatting, and enjoy the multiple benefits we have enumerated with ease, assuming a client application that exposes cell properties. While Reporting Services does not expose them directly, there are other ways to make cube-based formatting just as accessible to report authors there, as well. (I introduce variations on this theme within articles of my MSSQL Server Reporting Services series at Database Journal).


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 mentioned 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 often 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. We examined three examples of setting cell properties within Analysis Services with conditional logic to support this. Finally, we verified the effectiveness of our approach via the Cube Browser, and discussed how we might leverage our conditionally determined cell properties within the Reporting layer.

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

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Mobile Site | Full Site