mssqltips logo

Conditional Formatting for SQL Server Reporting Services Reports

By:   |   Updated: 2018-09-04   |   Comments   |   Related: > Reporting Services Formatting

Problem

Reports are useful to organize data into summaries and grouping to quickly visualize the data.  Sometimes having additional visual cues can be helpful to zoom on key sections of the report.  In this tip, we will look at how to add conditional formatting to a SQL Server Reporting Services SSRS report to make reports even more useful.

Solution

Let’s take the following report as the basis for this tip.  This report shows disk space for 2 servers, nothing elaborate, just the drives on each server and the space usage:

Sample report with sample data to illustrate when to use conditional formatting

What if we could highlight certain areas of the data with different colors based on the free space? Let's take a look at how this can be done.

Quick Overview to Add Conditional Formatting to a SSRS Report

Below is the sample report in Design view.

put our report on design mode

If you right click on an object you can look at the properties for the object as shown below. The properties window has an fx button next to almost all of the different properties. This indicates that we can set these values using formulas.

almost every property can be set using a formula

If you click one of the fx buttons, a new window appears where you enter the desired formula.

window where we can set the formula for any given property

SSRS Conditional Formatting - Bold Text

For our first example, we will set the [Drive] field bold when the space is under 20%.

First, we right click the [Drive] field and select Text Box Properties:

will set drive properties

Then navigate to Font and click fx next to the Bold property:

looking the bold property

In the formula window, we will put the following formula:

=IIf(Fields!pctFree.Value<0.2,"Bold","Default")

We use the IIf function that returns 2 values depending on the evaluation of an expression. In this case, our expression is to evaluate if the free space of a drive is under 20%. If true, it will return “Bold”, if false, it will keep the “Default” value:

formula to calculate the bold property for the drive field

Let's see it in action.  As you can see below, the drives under 20% of free space (F:, D: and Z:) are marked in bold:

SSRS Conditional Formatting - Text Background Color

For the next example, we will set the background color of the PctField, based on its value. A yellow color for values between 20% and 10% and a red color for values under 10%.

We select our [PctFree] field and open the properties. Navigate to the Fill tab and click the fx button next to the Fill color property:

we will set the backgroud color of the PCT free based on the value

In the formula window, put the following:

=Switch(Fields!pctFree.Value<0.1,"Tomato",Fields!pctFree.Value<0.2,"Gold")

Since there are multiple validations, we use the SWITCH function. This returns the value next to the evaluation if this is true, so if the first validation is true (space under 10%) it will return the “tomato” value and will exit. If false, it will evaluate the next expression (space under 20%) and if true, will return the “gold” value and will exit, if none of the evaluations are true, no background color is set:

formula to set the background color for the pctFree field

Let's see it in action. We can see the percent free field is highlighted based on its value:

fill color results for the % Free field based on its value

SSRS Conditional Formatting - Chart Bar Colors

As you have seen, it is possible to set any property based on any value or formula, so setting properties for charts is also relatively easy.

Keep in mind that some of the fields for charts are summarized, so be careful with this so you do not have undesired results.  Always check first if you need to summarize the fields in your formula.

So, for example if we want a color warning for the bar next to the value we will apply it to the Series Properties:

series properties location

We select the fill color property and apply a formula like this:

=IIf(Fields!pctFree.Value<0.2,"Orange","Blue")

If the value is less than 20% it will be Orange otherwise it will be Blue.

Here are the results:

Fill color results for free space bar

The last thing we want to do is to apply formatting to the other chart in our report.

We want the bar color as follows:

  • Drives with space under 10% - Red
  • Drives with space between 10% and 20% - Yellow
  • Everything else – Green

We select the Series Properties for the Used Space:

series properties location for charts

Then select the Fill tab and for the color property, click the fx button and enter the following formula:

=Switch(Fields!pctFree.Value<0.1,"Tomato",Fields!pctFree.Value<0.2,"Gold",true,"Green")

You can see the formula is the same as what we used for the second example, the only difference is that in the last check we put the constant true and a value of green.  This is the equivalent of the ELSE sentence, it must be put at the end, because if you put it anywhere else, it will stop the evaluation.

Final Formatted Report

Below we can see the final report with all the formats we applied.

final report with custom formatting so we can see different calculated properties in action.
Next Steps
  • SQL Server Data Tools (SSDT) is the tool provided by Microsoft to develop Reporting Services, Analysis Services and Integration Services solutions in a Visual Studio like environment, you can download the latest version from here.
  • This custom formatting is only available for .RDL paginated reports. These features are not available in Power BI. Please take this into consideration if you plan to migrate your reports from SSRS to Power BI.
  • We focused mostly on color properties, but you can customize any property that has an fx button next to it, so create a test report with dummy data and try to modify and apply formulas to different properties and on different controls. This will help you create professional looking reports.


Last Updated: 2018-09-04


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools