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 Aug 15, 2005

MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 6

By William Pearson


Establish Conditional Formatting for the Store Profit Value

Having created a copy of the functional report, we are now ready to make the enhancements requested by the FoodMart information consumers. Our first modification will establish conditional formatting of the Store Profit value, to meet the requirement that, when negative, it 1) is contained in parentheses and 2) appears in a red (versus the standard black) font. (We will go one step further, as well, and color the value green, if it equals zero, to establish a scenario with yet an additional possible outcome). The purpose, again, is to attract reader attention to line items that did not render a profit. Obviously, the procedures that we undertake to conditionally format the Store Profit value could be applied to other values in the report, as well.

While there are multiple approaches to bringing about the conditional formatting we desire, we will accomplish it using modifications within the basic and advanced textbox properties settings for the Store Profit value. We will begin our procedure from our current position on the Layout tab, within the Report Designer. Here we will apply expressions to properties of the Store Profit value.

1.  Right-click the textbox underneath the Store Value column title, currently displaying =Sum(Fields!Store_Profit.Value).

2.  Select Properties from the context menu that appears, as shown in Illustration 20.

Illustration 20: Accessing the Field Properties

The Textbox Properties dialog for the Store_Profit textbox opens,

3.  In the right, lower portion of the dialog, below the FormatStandard list box, and to the immediate right of the Custom box (whose radio button is selected) replace the current format code (C0) with the following expression:


The Textbox Properties dialog appears, with our modification, as depicted in Illustration 21.

Illustration 21: Custom Formatting Syntax in the Textbox Properties Dialog

4.  Click OK to accept our changes, and to close the Textbox Properties dialog.

We are returned to the Layout tab.

The format pattern we have entered into the Custom Format box of the Textbox Properties dialog will, as we shall see, handle the enclosing of negative numbers in parentheses, the first half of the formatting requirement we face. Next, we will use an expression, within the font Color property, to make negative values appear red.

5.  With the Store Profit value textbox still selected, select View --> Properties Window from the main menu (if required), as shown in Illustration 22.

Illustration 22: Select View --> Properties Window from the Main Menu (Partial View) ...

The Properties Window appears (I typically "dock" mine to the right side of the Report Designer environment, for easy access).

6.  Ensure that the selector atop the Properties Window displays Store_Profit Textbox, as depicted in Illustration 23.

Illustration 23: Properties Window for the Store_Profit Textbox (Partial View) ...

7.  Click the Color box in the upper half of the Properties Window, to activate its downward pointing selector button.

8.  Click the selector button to enable the Color selection list (the color currently in place is Black).

9.  Select Expression from the partially expanded colors list, as shown in Illustration 24.

Illustration 24: Select Expression from Atop the Colors List (Partially Expanded)

The Expression Editor appears. Here, as with myriad other object properties throughout Reporting Services, we can use an expression to enact conditional formatting, as we shall see next.

10.  Replace "Black" in the Expression box (right half of the Expression Editor)with the following expression:

=Switch(Sum(Fields!Store_Profit.Value) > 0, "Black",
Sum(Fields!Store_Profit.Value) = 0, "Green",
Sum(Fields!Store_Profit.Value) < 0, "Red")

The Expression box of the Expression Editor appears, with our expression, as depicted in Illustration 25.

Illustration 25: Expression Appears in the Expression Box ...

11.  Click OK to accept our changes, and to close the Textbox Properties dialog.

We are returned to the Layout tab, where we can preview the effects of our handiwork surrounding the Store Profit value.

12. Click the Preview tab, atop the design environment.

The report executes. The values section of the report matrix appears as partially shown in Illustration 26.

Illustration 26: The Values Section of the Matrix (Partial View)

We now see that the ("cosmetically calculated") negative values of the Store Profit column appear to have been successfully formatted based upon the conditions we have supplied. This meets the expressed needs of the information consumers in that the negative numbers now appear in red fonts, and enclosed by parentheses, for easy identification.

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