Adding Conditional Formatting into Reporting Services

Last week, we discussed how to create your first report in Microsoft SQL Server Reporting Services. The report we created was a status report on SQL Server Agent jobs. That article can be found here: http://www.sqlservercentral.com/columnists/bknight/designingyourfirstreportinreportingservices.asp. The report we created was from the template and was very crude looking because of it. In this short article, we’re going to look at improving that report and answer a common newsgroup question I see. This week, we’re going to set the rows of the report to alternate colors. In other words, row 1 will have a grey background and row 2 will have a white background. This makes your report much more readable. We’ll also change the color of data based on jobs failing or succeeding.

Let’s start by first opening the report we worked on in the last tutorial. If you don’t have a copy of it, click on the earlier mentioned link. If you download the RDL file, you’ll only have to copy and paste it into the Solution Explorer side of Visual Studio. Now that you’re caught up on the report, let’s jump into solving the following goals: 1) alternate row colors and 2) change the data to red on jobs that have failed.

 Tutorial 1 – Alternating Colors Between Rows

To make our report more readable, your viewers probably want to have each row an alternate color. To do this, pull open your report and follow these steps:

1. Select one of the fields in the report. Once you select the field, you’ll see a grid surrounding the row. Left-click on the grey column to the left of the data as shown in the below figure. By left-clicking on this row, you can now set the properties for the entire row and not a single column in the table.
   
   
2. The property you want to set in the right-pane is the BackgroundColor property. If you don’t see the Properties window on the right as seen in the above screenshot, select Properties Window under View. The default background color is Transparent. To change this to our dynamic property, click the drop-down box and select <Expression…>.
3. You should not be in the Edit Expression dialog box as shown in the below screenshot. In the Expression box to the right you can type the following syntax to alternate colors (make sure you remove what was there before):
   
   =iif(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")
   
   Essentially this variant of .NET code above means that the first row will be a slightly grey color and the second will be white. Then, the rows will alternate between the colors. If you’d like to change the colors, just type the new color name where WhiteSmoke is in my example. The list of color names can be found where you clicked the drop-down box to select <Expression…> earlier. If you wanted something to occur every 3^rd row, change the 2 in the above code to 3.

After you have typed in the expression, click OK and you’re done! Almost anything can be set dynamically like this in Reporting Services. For example, if you’d like the report to look different based on the UserID that generated the report, you can do that by using the global variable.

 Tutorial 2 – Changing the Color of Text Dynamically

Next, we want to make bad numbers in our report jump out. To do this, we’re going to dynamically look at the data in the Job Status column and if it says Failed, turn it red so it stands out for a casual viewer. To do this, follow these simple steps in Visual Studio:

1. Left-click the row and column that you’d like use dynamic formatting on.
2. You will again want to go to the Properties Window and select the drop-down box under Color in the Appearance group. Select <Expression…> to open the Expression Editor.
3. Replace the word Black (which is the default) with the following text and click OK.
   
   =iif(Fields!JobStatus.Value = "Failed", "Red", "Black")

Now, you can preview the data and see if can see any failed jobs. Essentially, the syntax above says that if the value of the JobStatus field is equal to the word Failed then turn the color to Red. Otherwise, keep it black. Likewise, you could change the color based on profits not being met with the following syntax.

=iif(Fields!Profit.Value < 0 , "Red", "Black")

You can also make the word Failed really stand out by making the font style a bit more dynamic. If you want the text bolded dynamically, then you can change the Normal default in the FontWeight property (under font) to the following syntax:

=iif(Fields!JobStatus.Value = "Failed", "Bold", " Normal ") 

Well hopefully this showed you a bit about how to make two simple but necessary changes to your report to make them more readable. In the next articles in this tutorial series, we’ll begin securing our report that we’ve just created so you’ll need special rights to see the job status.