Positing the Intelligence: Conditional Formatting in the Analysis Services Layer - Page 2

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.

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.

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.

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

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.

CASE
   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*/
END

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

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.

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

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.

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

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

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

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.