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 theNormaldefault 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.