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

December 20, 2004

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.

The Report Parameters dialog appears.

A new parameter with default name appears.

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

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

The Edit Expression dialog appears.

.60

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.

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

59.75

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.

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.

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.