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:
- 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.
- 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…>.
- 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 3rd 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:
- Left-click the row and column that you’d like use dynamic formatting on.
- 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.
- 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
=iif(Fields!JobStatus.Value
= "Failed", "Bold", "
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.