Conditional formatting with Reporting Services

Anyone who has created reports with SQL Server Reporting Services has tweaked the presentation of a table or matrix to enhance its visual appeal. However in most cases, you do this once and for all, to produce a static result. The row or table cell is set to fixed and final presentation, with background colors, font attributes and borders defined once and for all. This is great when you want a uniform presentation, but in these days of information overload, you often need to allow certain pieces of data to stand out and to draw the reader's attention. Even better, you want to automate this process, and have certain values draw attention to themselves when specific thresholds are met. What you need is conditional formatting - just like Access and Excel have had for years.

Fortunately Reporting Services allows you to vary almost any aspect of a report's presentation based on the underlying data. So when a sales figure shoots up - or down - you can have the report draw attention to this fact by:

* changing the colour cell backgrounds;
* changing font colour and attributes;
* adding or altering cell borders;

Indeed, you can combine most of these techniques in many ways to ensure that your reports deliver analysis as well as data to their users.

Just to be clear, I am not pretending that this is some wildly useful but undiscovered feature of Reporting Services. Far from it. I am merely attempting to draw your attention to useful ways of enhancing your reports and reaching further levels of automation in report creation using Reporting Services expressions.

Example conditional formatting

A report example

Firstly, we are going to need some data. I suggest using the Adventureworks database to return a few rows about sales people and their sales figures, as this will give us a set of data which can then be used to highlight the best- (and the worst-) performing sales staff.

So here is the stored procedure that will return data to Reporting Services. If you have not already done so, I suggest that you download the AdventureWorks database from the CodePlex web site, install it, and then create the following procedure in the AdventureWorks database:

CREATE PROCEDURE [dbo].[pr_GetSalesData]
AS
DECLARE @Topseller INT
DECLARE @Bottomseller INT

SELECT @Topseller = MAX(Bonus) FROM Sales.SalesPerson
SELECT @Bottomseller = MIN(Bonus) FROM Sales.SalesPerson

SELECT
CT.FirstName + N' ' + CT.LastName AS PersonName,
SP.Bonus,
SP.SalesQuota,
SP.CommissionPct * SP.SalesYTD AS Commission,
SP.SalesYTD,
SP.SalesLastYear,
SP.SalesYTD - SP.SalesLastYear AS SalesIncrease,
CASE
WHEN (SP.CommissionPct * SP.SalesYTD) > 75000 THEN 5
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 50001 AND 75000 THEN 4
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 25001 AND 50000 THEN 3
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 1 AND 25000 THEN 2
ELSE 1
END AS CommissionFlag
FROM
Sales.SalesPerson SP
INNER JOIN HumanResources.Employee EMP ON SP.SalesPersonID = EMP.EmployeeID
INNER JOIN Person.Contact CT ON EMP.ContactID = CT.ContactID AND EMP.ContactID = CT.ContactID

The first thing to note here is that I am Using T-SQL to perform all calculations, rather than use Reporting Services. This is a personal choice, but I consider that this approach is fundamental to efficient and easy conditional formatting with Reporting Services.

You will then need to create a new Reporting Services project. Then create a new data source which connects to the AdventureWorks database. Finally add a new report (Right-click Reports, then Add/New Item/Report), and create a new dataset for the report, being careful to use pr_GetSalesData as the query string, and "Stored Procedure" as the command type.

Then switch to the layout tab, and drag a table object from the toolbox onto the report body. Add another four columns (making seven in all), and drag the data fields, one by one, into the table details row. You should end up with something like Person Name,Sales Last Year,Sales YTD,Sales Increase,Sales Quota, Bonus, Commission

Applying basic conditional formatting

Suppose that you wish for a series of colors to be applied to the cell background of the "Commission" column, to draw attention to the lowest and highest commissions, as well as indicating the lower and higher of the intermediate commissions.

For argument's sake, I suggest the following requirement:

Color Thresholds Amount Color
0 red
1 - 25,000 yellow
25,001 - 50,000 light green
50,001 - 75,000 dark green
75,001 + blue

The commission column will change color according to where in the range of data the figure lies.

At this point, we need to do some strategic thinking, as we are trying to do two things here:

1. Apply a set of conditions, and not just a simple IF..ELSE.
2. Test a range of figures, and not just carry out a simple comparison,

Now we could use the IIF function that we used above have negative numbers appear in red. However using more than two IIF functions can get very difficult to get right, so I suggest using the Reporting Services SWITCH function to apply the color coding. However, while this solves the first problem, it does not help with the second problem at all. Therefore I think that using T-SQL to apply range-based conditional analysis is a much better idea than attempting this with Reporting Services.

CASE
WHEN (SP.CommissionPct * SP.SalesYTD) > 75000 THEN 5
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 50001 AND 75000 THEN 4
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 25001 AND 50000 THEN 3
WHEN (SP.CommissionPct * SP.SalesYTD) BETWEEN 1 AND 25000 THEN 2
ELSE 1
END AS CommissionFlag

The above snippet will calculate the commission for each salesperson, and categories it according to the amount of commission received. This is then returned as a figure between 1 and 5.

You will need to add the new field that the stored procedure is returning to your reporting services dataset. To do this:

1. Click on the "Data" tab of your report.
2. Click the "Edit Selected Dataset" ellipsis on the tab toolbar.
3. The "Dataset" dialog will appear. Click the "Fields" tab.
4. Scroll to the bottom of the list of fields and click in a blank row.
5. Add "CommissionFlag" as field name to both the "Field Name" and "Value" cells.
6. Select "Type" as "DatabaseField"

You can then Click "OK" and right=click on "Report Datasets" in the "Datasets" pane, and select "Refresh" to make the new field appear in the fields collection.

Now that you have fed the analytical flag for commissions through to the report, you can use it to apply some conditional formatting.

1. Click on the table cell for the detail row in the "Commission" column.
2. Display the properties window (pressing F4 will do this if it is not already visible).
3. Click the popup box to the right of the "Background Color" attribute, and select "Expression". The "Edit Expression" dialog will be displayed.
4. Enter the following code: =switch(Fields!CommissionFlag.Value=1,"Red",Fields!CommissionFlag.Value=2,"Yellow",Fields!CommissionFlag.Value=3,"LawnGreen",Fields!CommissionFlag.Value=4,"DarkGreen",Fields!CommissionFlag.Value=5,"Blue")
5. Click "OK"
6. Preview the report.

That's it Enjoy