SQL Reporting "How to" - Conditional Color 1/4: The Basics. Report Expressions & Custom Code

This "How to" guide provides ideas & code samples on using color to improve your users understanding of the data in your reports. It includes using gradients of color in your tables & charts. It also covers using color to represent a second dimension &/or multiple levels of data in a hierarchy.

Applies to: SQL Server Reporting Services 2005 & SQL 2008 (I’ve not tested it on SQL 2000, nor the version to follow SQL2008, but I expect it will work ok, too.)

Part 1: Starts at the basics of Report Expressions & adding code to your reports.

Part 2: Shows the example code, to create ranges of color & shades within a color.

Part 3: Shows the example code, changing a single color from Bright to Light or Dark.

Part 4: Shows how to use them in charts & with multiple dimensions.

 

Contents - Part 1: Conditional Formatting: the Basics

1. Using a Report Expression.
      Handy Report Expressions
      Simple Conditional Coloring
2. Using a Custom Code
3. Using a other .NET Classes in your Custom Code
4. Using a Custom Assemblies
Appendix: Getting yourself started. 

Overview

This 3 part blog is intended to show you how to create conditional formatting within your reports. Specifically changing the background colors of table cells to highlight ranges of values in cells. And to use Charts to display multiple dimensions ie: The length of the bar shows 1 measure (eg: Revenue) & the color a different measure (eg: Profitability).

 

Samples of what you can do formatting Fonts, Tables & Chart elements.

 image161[1]_thumbimage13_thumb    image131_thumb[1][1]

 

Conditional Formatting: the Basics.

SQL Reporting Services is incredibly flexible, there are many ways that it can be extended, enhanced &/or embedded. This post will limit itself to techniques commonly used by the average Report Author.
To really tap into its power I suggest reading up on Using Custom Assemblies with Reports & Extending Reporting Services.
You might also want to consider writing your own Custom Report Item, this lets you display anything you want in a highly efficient manner. Examples are the Dundas Add-ins, for Map, Calendar, Gauge, Charts, Other have written Barcode generators. See Readme_Custom Report Item Sample & the Reporting Services code samples (they shipped with the product in 2005 & are downloadable from Codeplex in SQL2008 Microsoft SQL Server Community Projects & Samples. You can also use an XSLT or other XML manipulation techniques to modify the Report Definition Language (RDL) as it is just XML constrained by a well documented schema.

1. Using a Report Expression.

Nearly every property of any report can be controlled using Report Expressions. They are the basic building block for the rest of this article.

How do you use one?

This sample will show how to display negative numbers as Red & in Brackets ie ($ -20.00)

Step 1: Open the Properties window. (If it isn’t visible, hit F4)

Step 2: Click the item (ie: textbox, table, chart) you want to modify.

Step 3: Select the Property you'd like to control.
             First we need to change the Format property.

Step 4: Select the <Expression...> option

image

Step 5: Set the Format property to: $ #,##0.00;($ -#,##0.00);Zero

  • This can have 3 groups separated by semicolons. The 1st Group is for Positive values, The 2nd Group for negative & the last for the Zero Value.
  • Formats use the very powerful .NET Formatting strings. See  .NET Format String 101 & .NET Format String 102: DateTime Format String for a nice summary or the MSDN Reference Formatting Overview.
  • TIP: Unlike COBOL you do not need to specify groups of 3 numbers. Putting a Comma between two "#" chars will comma separate groups of 3 numbers. Also makes it easy to Round to the nearest Thousand or Million. Just terminate the string with commas. Eg: "$#, K" will round to the nearest thousand & put the letter K on the end. “$#,,” will remove the left most 6 digits (for Millions) & so on.   This is really handy when you are charting big numbers & don’t want lots of zeros in the Y axis labels.
    image

Step 6: Set the Color Property to: =iif( Fields!myField.Value < 0, “Red”, “Black”)

image

The Result: This should display as.

image

WARNING: This example may be a “Worst Practice”. If you care about International Audiences, you should take advantage of SSRS’s use of the default .NET settings. It looks at the culture settings on the client computer & renders appropriately. Eg: Many Europeans prefer a comma as a decimal point & use a period to separate the Thousands. But be careful of this "Internationalisation" assistance, especially with currency amounts. You may need to guard against a change in the Dollar sign. If the currency really does represent an amount in dollars just swapping the Dollar sign to a Yen or Pound symbol is a very inaccurate way to do foreign currency conversion.

 

Handy Report Expressions

The SQL Books Online team did a good job at collecting many of the really useful report tips. See this article Using Expressions (Reporting Services) & use index to grab nearby articles. Also Bob Meyer's Blog Using RDL expressions in Report Builder

Tip 1: It is possible to display multiple distinct fields in one cell (textbox).
To display a date range "12:45 AM - 3:34 PM"  Set the Value Property to
=FORMAT(Fields!StartDate.Value, "t") & " – " & FORMAT(Fields!EndDate.Value,"t")
If you provide formatting instructions as part of the Value Property, you don't need to set the Format Property

Tip 2: It is possible to use the value of one textbox as part of a calculation in another.
To refer to another TextBox use the prefix "ReportItems! " collection, this lets you refer to its value.
eg: If TextBox3 was "YOY Delta" with Value: "= Fields!2008Sales.Value - Fields!2007Sales.Value" then rather than Textbox4 "YOY Change" being "= (Fields!2008Sales.Value - Fields!2007Sales.Value) / Fields!2007Sales.Value".
You can reuse the calculation like:
         "=ReportItems! textbox3.Value / Fields!Sales2007.Value"

NB: Be careful with not to get too complex in your use of this. If you have a huge number of fields dependant on fields, that depend on other fields, with partial calculations combining multiple other fields etc. You should test this carefully as the calculation order is not guaranteed. If your cross report references change scope or aren’t yet calculated at the time your cell property is being calculated. You might not get the answers you expect.

Tip 3: Avoid "Divide by Zero Errors", but only check the denominator.
In the above example "=ReportItems! textbox3.Value / Fields!Sales2007.Value", if I had zero sales in 2007, I'll see "NaN" (Not a Number) or "Infinity".  This expression would be better written to include a test to ensure I do not attempt to divide by zero ie: =iif( Fields!Sales2007.Value <> 0, ReportItems!textbox88.Value / Fields!Sales2007.Value , ""). I often see some very creative formulas that attempt to create the divide by zero error in the test & then provide a different result. eg: =iif( iserror(value/0), "-", value/0) this is a very sub-optimal approach & quite unnecessary.

Note: The above test works fine even if the denominator is NULL. Most .NET datatypes do not handle tri-state logic (True, False, Unknown), so NULL's are converted to 0 in most calculations.

 

Simple Conditional Coloring

Tip 2b: It is possible to use the value of one textbox to control the color of another.
In Part 2 of this "How to" guide you will see many different code alternatives that change the background color of a textbox. Remember the value you pass as a parameter doesn't have to be the value of the current text box. This is handy for flagging errors. eg: One manufacturer I worked with had a machine that created & packed 7 items at a time. They had a business rule that permitted a Sales Rep to give up to 20% discount if the order quantity was divisible by 7. If not, the max discount was 12%, as humans would need to unpack & repack the order.

Option 1: Nested IF statements 
=iif( (Fields!Discount.Value <= 0.12), "Green", ( iif(Fields!Discount.Value > 0.2, "Red", iif(Fields!Qty.Value mod 7 = 0, "Yellow", "Red") ) ) )

Option 2: Switch Statement

=Switch( (Fields!Discount.Value <= 0.12), "Green", Fields!Discount.Value > 0.2, "Red", Fields!Qty.Value mod 7 = 0, "Yellow", true, "Red" )
This consists of multiple pairs of values each separated by a comma. The first value in the pair is a Boolean expression, the second value is what will be returned if the boolean is true. Each pair is evaluated left to right. Evaluation stops at the first pair that returns true. To simulate an "ELSE" clause ensure that the last pair always evaluates to true. Some people use "1=1" but I prefer to simply put "true",

 

2. Using a Custom Code

As powerful as Report Expressions are, they become inadequate if you require many lines of code or the same code is called from many different places within the same report. Even though the tool lets you enter vast amounts of code, its not something I’d recommend. You will create a maintenance nightmare. I’ve found that if it is more than a few lines of code, you will probably use it in other areas of your report. So it is cleaner to put it in one location & call it. This location is called Custom Code. SQL Books Online has good coverage on this see Using Custom Code References in Expressions (Reporting Services) 

Steps to using Custom Code:

Step 1. Create your functions in the Custom Code window.

Step 1a. Click on the “Report” Menu, “Report Properties” submenu.

             image71_thumb

Note: This menu is context sensitive. In SQL2008, the Design Tab should have focus. In SQL2005 either the Data or Layout tabs need to have focus.
If the Preview Tab is selected you will have problems. The “Report Properties” submenu will be disabled <see graphic below>

If you are in Edit Mode for one of the Report Items, the entire “Report” menu will not be visible. Hit enter to finish editing the property that has focus & it should reappear.

             image4_thumb

Step 1b. Select the Code Tab & enter or paste your code.

              image1011_thumb

Tip: Only Public Functions can be called from Report expressions. Any private functions are only visible to other Functions within the window. You can create module level variables to share state &/or be constants. You can’t use the IMPORTS statement, but if you add a reference correctly it seems to create it for you.

Tip: You will rapidly discover this dialog is nothing more than a Multi-Line TextBox. It is far from an ideal development environment, but does the job. For anything decent, I open a separate VB.NET code project & write my code there, I also do my preliminary testing by passing static values to it. Once it is stable then I paste it into the code window. For smaller routines, I’ve developed them as Report Expressions, then lifted them out & turned them into a function.

Obvious tip: The Report properties dialog is resizable. It starts off quite small. I only mention to save embarrassment when you complain about coding thru a letterbox & someone asks “why didn’t you don’t drag it bigger?”.

 

Step 2. Use it from within your report expressions.

All your public functions are made available in a Code collection. To refer to a public function, just prefix it with the word “Code.”,
eg: =code.ColorRYG( Fields!Number.Value, 0, 255, 127)
Of course it can be used anywhere a function can be used & combined in any way you see fit.
eg: =4 / sin( code. myFunction(Fields!myfield.value) )

 

Custom Code example - selecting colors from a preset list.

If you had a field that with values from 1 to n. Perhaps you could use a report expression to map between it (ie: RegionID) and a color to display.  eg:

=Choose(Fields!RegionID.Value, "Brown", "Blue", "GoldenRod", "Olive", "MediumTurquoise","Red", "Green", "DeepSkyBlue", "Yellow", "Chocolate", "Purple", "DarkOrange")
NB: If (Value <= 0) or (Value > number of items in the array), the color returned is "Transparent"
If RegionID was 2 then it would return “Blue”.

 

But often you just have a string value, ie: RegionName. So you could use Switch statement to create “Value – Color” pairs.

=Switch("Africa", "Brown", "Americas", "Blue", "ANZ","GoldenRod", "Asia","Olive", "Europe","MediumTurquoise", True, "Red")

 

The problem with that is readability & reuse. Many people prefer each pair to be spaced out on a separate line. So the custom code alternative would be.

 Step 1: Paste this into the Custom Code window.

  Public Function ColorRegion(ByVal RegionName As String) As String
Select Case RegionName
Case"Africa"
               Return"Brown"
           Case"Americas"
               Return"Blue"
           Case"ANZ"
               Return"GoldenRod"
           Case"Asia"
               Return"Olive"
           Case"Europe"
               Return"MediumTurquoise"
           Case Else
Return"Red"
       End Select
End Function

 Step 2: Use it in a report Expression to assign a value to a Backcolor property.

=code.ColorRegion(Fields!Region.Value)

 

3. Using a other .NET Classes in your Custom Code

If you want to call .NET classes other than the ones exposed by default you will need to create a reference to them. The graphics below are from SQL2005. The dialogs in SQL2008 are similar, but slightly nicer to use.

Step 1: Click the “References” Tab.

image16_thumb

Step 2. Click the “…” button & Use the Add a reference dialog to select the libraries you want.
This is exactly the same process you do if you add your own custom assembly.

image191[1]_thumb

Reporting services preregisters the VB Library, System.Maths & System.Convert libraries. Using using additional .NET classes can be handy. Beware, most of the Report element properties just need a string or numeric value. So if you return some special object eg: a System.Drawing.Color object to a Color or BackColor property, it is an error. The Color property takes text representing a color name or RGB “0xFFFFFF” format.

So this is mainly useful if you want some more powerful processing within your function, perhaps using Regex for complex String manipulation or Matrix Transforms.

 

4. Using a Custom Assemblies

Just as Custom code lets you reuse your “special” routines in many data elements within the same report. It too can be criticised in the same way. The same code sprayed throughout multiple reports could become a maintenance issue. So if your report authors all start using the code examples I’ve provided below, consider compiling them into a .NET assembly & calling that from your reports. 

The benefit is that the assembly code then be incorporated into your application lifecycle management (ie Source code control, bug tracking etc). Just by deploying a new version of your assembly enhancements can be quickly incorporated  into all relevant production reports.

The downside to this is you need to deploy an assembly onto a production server. Not a problem if you own the server or can tell central IT what to do, more likely to be an issue if you are but a humble Report Author stuck at the wrong end of an MIS fiefdom.

To do this, see these sections of SQL Books Online

 

Appendix: Getting yourself started. 

If you don’t have a suitable datasource & want to quickly try the functions provided in this article. Below is a query you can just paste into your datasource

Step 1: Create a Dataset.

If you have none, then cut/paste this into the TSQL area of your data set. As this is completely self-contained, it should work on any SQL Server you have permission to access.

Step 1a: Create a Data Source.

If you have SQL installed on your machine, set Server = “.” or “.\Instance name”

Set the database of the connection to any DB including master as it will not affect the database.

Step 1a: Create a Data Query.

Change the mod values in the WHERE clause to get more or less numbers.

Do some algebra in the Select List if you want a different range of values. eg: SELECT Number, (Number –128) * 10 as NegNumber FROM …

 -- Brute Force approach to creating sequential numbers.
-- But hopefully avoids distracting anyone not comfortable with TSQL.
SELECT Number FROM ( 
    SELECT 0 AS Number
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
    UNION ALL SELECT 11
    UNION ALL SELECT 12
    UNION ALL SELECT 13
    UNION ALL SELECT 14
    UNION ALL SELECT 15
    UNION ALL SELECT 16
    UNION ALL SELECT 17
    UNION ALL SELECT 18
    UNION ALL SELECT 19
    UNION ALL SELECT 20
    UNION ALL SELECT 21
    UNION ALL SELECT 22
    UNION ALL SELECT 23
    UNION ALL SELECT 24
    UNION ALL SELECT 25
    UNION ALL SELECT 26
    UNION ALL SELECT 27
    UNION ALL SELECT 28
    UNION ALL SELECT 29
    UNION ALL SELECT 30
    UNION ALL SELECT 31
    UNION ALL SELECT 32
    UNION ALL SELECT 33
    UNION ALL SELECT 34
    UNION ALL SELECT 35
    UNION ALL SELECT 36
    UNION ALL SELECT 37
    UNION ALL SELECT 38
    UNION ALL SELECT 39
    UNION ALL SELECT 40
    UNION ALL SELECT 41
    UNION ALL SELECT 42
    UNION ALL SELECT 43
    UNION ALL SELECT 44
    UNION ALL SELECT 45
    UNION ALL SELECT 46
    UNION ALL SELECT 47
    UNION ALL SELECT 48
    UNION ALL SELECT 49
    UNION ALL SELECT 50
    UNION ALL SELECT 51
    UNION ALL SELECT 52
    UNION ALL SELECT 53
    UNION ALL SELECT 54
    UNION ALL SELECT 55
    UNION ALL SELECT 56
    UNION ALL SELECT 57
    UNION ALL SELECT 58
    UNION ALL SELECT 59
    UNION ALL SELECT 60
    UNION ALL SELECT 61
    UNION ALL SELECT 62
    UNION ALL SELECT 63
    UNION ALL SELECT 64
    UNION ALL SELECT 65
    UNION ALL SELECT 66
    UNION ALL SELECT 67
    UNION ALL SELECT 68
    UNION ALL SELECT 69
    UNION ALL SELECT 70
    UNION ALL SELECT 71
    UNION ALL SELECT 72
    UNION ALL SELECT 73
    UNION ALL SELECT 74
    UNION ALL SELECT 75
    UNION ALL SELECT 76
    UNION ALL SELECT 77
    UNION ALL SELECT 78
    UNION ALL SELECT 79
    UNION ALL SELECT 80
    UNION ALL SELECT 81
    UNION ALL SELECT 82
    UNION ALL SELECT 83
    UNION ALL SELECT 84
    UNION ALL SELECT 85
    UNION ALL SELECT 86
    UNION ALL SELECT 87
    UNION ALL SELECT 88
    UNION ALL SELECT 89
    UNION ALL SELECT 90
    UNION ALL SELECT 91
    UNION ALL SELECT 92
    UNION ALL SELECT 93
    UNION ALL SELECT 94
    UNION ALL SELECT 95
    UNION ALL SELECT 96
    UNION ALL SELECT 97
    UNION ALL SELECT 98
    UNION ALL SELECT 99
    UNION ALL SELECT 100
    UNION ALL SELECT 101
    UNION ALL SELECT 102
    UNION ALL SELECT 103
    UNION ALL SELECT 104
    UNION ALL SELECT 105
    UNION ALL SELECT 106
    UNION ALL SELECT 107
    UNION ALL SELECT 108
    UNION ALL SELECT 109
    UNION ALL SELECT 110
    UNION ALL SELECT 111
    UNION ALL SELECT 112
    UNION ALL SELECT 113
    UNION ALL SELECT 114
    UNION ALL SELECT 115
    UNION ALL SELECT 116
    UNION ALL SELECT 117
    UNION ALL SELECT 118
    UNION ALL SELECT 119
    UNION ALL SELECT 120
    UNION ALL SELECT 121
    UNION ALL SELECT 122
    UNION ALL SELECT 123
    UNION ALL SELECT 124
    UNION ALL SELECT 125
    UNION ALL SELECT 126
    UNION ALL SELECT 127
    UNION ALL SELECT 128
    UNION ALL SELECT 129
    UNION ALL SELECT 130
    UNION ALL SELECT 131
    UNION ALL SELECT 132
    UNION ALL SELECT 133
    UNION ALL SELECT 134
    UNION ALL SELECT 135
    UNION ALL SELECT 136
    UNION ALL SELECT 137
    UNION ALL SELECT 138
    UNION ALL SELECT 139
    UNION ALL SELECT 140
    UNION ALL SELECT 141
    UNION ALL SELECT 142
    UNION ALL SELECT 143
    UNION ALL SELECT 144
    UNION ALL SELECT 145
    UNION ALL SELECT 146
    UNION ALL SELECT 147
    UNION ALL SELECT 148
    UNION ALL SELECT 149
    UNION ALL SELECT 150
    UNION ALL SELECT 151
    UNION ALL SELECT 152
    UNION ALL SELECT 153
    UNION ALL SELECT 154
    UNION ALL SELECT 155
    UNION ALL SELECT 156
    UNION ALL SELECT 157
    UNION ALL SELECT 158
    UNION ALL SELECT 159
    UNION ALL SELECT 160
    UNION ALL SELECT 161
    UNION ALL SELECT 162
    UNION ALL SELECT 163
    UNION ALL SELECT 164
    UNION ALL SELECT 165
    UNION ALL SELECT 166
    UNION ALL SELECT 167
    UNION ALL SELECT 168
    UNION ALL SELECT 169
    UNION ALL SELECT 170
    UNION ALL SELECT 171
    UNION ALL SELECT 172
    UNION ALL SELECT 173
    UNION ALL SELECT 174
    UNION ALL SELECT 175
    UNION ALL SELECT 176
    UNION ALL SELECT 177
    UNION ALL SELECT 178
    UNION ALL SELECT 179
    UNION ALL SELECT 180
    UNION ALL SELECT 181
    UNION ALL SELECT 182
    UNION ALL SELECT 183
    UNION ALL SELECT 184
    UNION ALL SELECT 185
    UNION ALL SELECT 186
    UNION ALL SELECT 187
    UNION ALL SELECT 188
    UNION ALL SELECT 189
    UNION ALL SELECT 190
    UNION ALL SELECT 191
    UNION ALL SELECT 192
    UNION ALL SELECT 193
    UNION ALL SELECT 194
    UNION ALL SELECT 195
    UNION ALL SELECT 196
    UNION ALL SELECT 197
    UNION ALL SELECT 198
    UNION ALL SELECT 199
    UNION ALL SELECT 200
    UNION ALL SELECT 201
    UNION ALL SELECT 202
    UNION ALL SELECT 203
    UNION ALL SELECT 204
    UNION ALL SELECT 205
    UNION ALL SELECT 206
    UNION ALL SELECT 207
    UNION ALL SELECT 208
    UNION ALL SELECT 209
    UNION ALL SELECT 210
    UNION ALL SELECT 211
    UNION ALL SELECT 212
    UNION ALL SELECT 213
    UNION ALL SELECT 214
    UNION ALL SELECT 215
    UNION ALL SELECT 216
    UNION ALL SELECT 217
    UNION ALL SELECT 218
    UNION ALL SELECT 219
    UNION ALL SELECT 220
    UNION ALL SELECT 221
    UNION ALL SELECT 222
    UNION ALL SELECT 223
    UNION ALL SELECT 224
    UNION ALL SELECT 225
    UNION ALL SELECT 226
    UNION ALL SELECT 227
    UNION ALL SELECT 228
    UNION ALL SELECT 229
    UNION ALL SELECT 230
    UNION ALL SELECT 231
    UNION ALL SELECT 232
    UNION ALL SELECT 233
    UNION ALL SELECT 234
    UNION ALL SELECT 235
    UNION ALL SELECT 236
    UNION ALL SELECT 237
    UNION ALL SELECT 238
    UNION ALL SELECT 239
    UNION ALL SELECT 240
    UNION ALL SELECT 241
    UNION ALL SELECT 242
    UNION ALL SELECT 243
    UNION ALL SELECT 244
    UNION ALL SELECT 245
    UNION ALL SELECT 246
    UNION ALL SELECT 247
    UNION ALL SELECT 248
    UNION ALL SELECT 249
    UNION ALL SELECT 250
    UNION ALL SELECT 251
    UNION ALL SELECT 252
    UNION ALL SELECT 253
    UNION ALL SELECT 254
    UNION ALL SELECT 255
    UNION ALL SELECT 256
) AS Numbers
WHERE (Number % 5 = 0)

Of course if you just want to load a lot of rows into a Table & then select from it, try something simpler , but change the select into an Insert into your table.

 DECLARE @iCount INT
SET @iCount = 0
WHILE @iCount < 256 BEGIN
    SELECT @iCount as Number;
    SET @iCount = @iCount + 1
END

If you are using SQL2008, don’t overlook the new code enhancements. Declare & Set in one statement, & the “+=” operator.

 DECLARE @iCount INT = 0;
WHILE @iCount < 256 BEGIN
    SELECT @iCount as Number;
    SET @iCount += 1;
END

 

Please give feedback. Is this series of articles useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.

Technorati Tags: SQL Server 2008,SQL 2008,SQL Reporting Services,SQL Server 2005,SQL 2005,SSRS,Color Coding,Colour Coding,Conditional Formating,Charts,Graphs