Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 20, 2004

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

By William Pearson

Add Parameterized Conditional Formatting to the Report Structure

Parameterized conditional formatting forms the nucleus of our session. There are several steps, and multiple report components, involved in bringing about the capability that the information consumers have requested. These steps can be done in more than one way, and in more than one order. We will first perform standard conditional formatting, to make the process clear, and then we will add parameterization as an independent step, once we have seen the operation of conditional formatting in its simplest form.

Add Simple Conditional Formatting to the Report

First, we will add conditional formatting in a way that might be adequate where formatting is based upon fixed criteria, which is applied each time the report is run. The values in the report change over time, in most cases, and the application of the criteria to the report with each execution means that values meeting the criteria we have designated will be brought to attention of the user of the information.

Let's return to the Layout tab of the Report Designer to get started.

1.  Click the Layout tab.

2.  Click the Profit Margin textbox, within which we made the changes to the calculation in our last section, to select it.

3.  Leaving the Profit Margin textbox selected, open the Properties window for the textbox.

4.  In the Color property box, select <Expression...> with the downward arrow, as shown in Illustration 40.

Illustration 40: Select <Expression...> from the Color Property Selector

The Edit Expression dialog appears.

5.  Change the expression that appears in the Expression box, "Black," to the following:

=IIF(Sum( Fields!Store_Profit.Value)/Sum(Fields!Store_Sales.Value)  < .60 , "Red", "Black")

With this expression, we are simply specifying "if the Store Margin evaluates to less than .60 (sixty-percent), then make the font color red for the value; otherwise leave it black." Remember that this is a simple use of conditional formatting, and although the data values themselves may change with each new report execution, the criteria itself will remain fixed.

The Edit Expression dialog appears as depicted in Illustration 41.

Illustration 41: Simple Conditional Formatting Logic in the Edit Expression Dialog

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

7.  Click the Preview tab.

8.  Select Food from the parameter picklist that appears atop the report on the Preview tab.

9.  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 partially shown in Illustration 42.

Illustration 42: Report (Partial View) with Simple Conditional Formatting in Place

Our expression appears to have been effective in rendering a simple conditional formatting solution. In the next section, we will expand our solution to include parameterization, and thus support the requirement of the information consumers to be able to specify the criteria at runtime, rather then having the conditional formatting apply to a fixed threshold (sixty percent, in our example) that is "hard coded" into the expression.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM