Positing the Intelligence: Conditional Formatting in the Analysis Services Layer - Page 2
May 14, 2007
Adding Conditional Formatting Support within the Cube
Lets 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.
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:
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.
6. Type, or cut and paste, the following syntax into the Back color box:
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.
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.
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.