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.
10. Click the Layout tab.
11. Select Report --> Report Parameters from the main menu, as depicted in Illustration 43.
Illustration 43: Select Report --> Report Parameters ...
The Report Parameters dialog appears.
12. Click Add to begin a new report parameter.
A new parameter with default name appears.
13. For the dialog boxes shown in Table 1, type the corresponding items (replacing defaults where required):
Table 1: Performance Threshold Parameter Details
In this Dialog Box:
Type the following:
Performance Threshold (%):
The completed Report Parameters dialog appears as depicted in Illustration 44.
Illustration 44: Completed Report Parameters Dialog ...
14. Click OK to accept input and close the dialog, returning to the Layout tab.
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.)
15. Click the Profit Margin textbox, within whose Properties settings we worked in our last section, to select it once again.
16. Leaving the Profit Margin textbox selected, open the Properties window for the textbox.
17. Select <Expression...> from the selector in the Color property box, as we did in the previous section.
The Edit Expression dialog appears.
18. Replace the following part of the expression:
With the following reference to the parameter we have created:
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.
Illustration 45: Parameterized Conditional Formatting Logic in the Edit Expression Dialog (Modification Circled)
19. Click OK to accept the modification, and to return to the Layout tab.
Let's execute the report, once again, to verify the effectiveness of our work.
20. Click the Preview tab.
21. Select Food from the parameter picklist that appears atop the report on the Preview tab.
22. Type the following into the Performance Threshold (%) box, which now appears to the right of the original picklist prompt:
23. Click the View Report button to execute the report.
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.
Illustration 46: Report (Partial View) with Parameterized Conditional Formatting in Place
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.
24. Select File --> Save All to save our work so far.
25. Exit Reporting Services when desired.
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.
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.