dcsimg

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.

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

In this Dialog Box:

Type the following:

Name:

pX_PerfThresh

Prompt:

Performance Threshold (%):

Data type:

Float

Available Values:

Non-queried

Default Values:

None (default)

Table 1: Performance Threshold Parameter Details

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:

.60

With the following reference to the parameter we have created:

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.


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:

59.75

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.

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers