Greetings! Today in this SSRS Tutorial, I thought I’d chat a bit about three SSRS Reporting features that may be helpful to you when creating reports. I am going to review the Switch function, creating green bar reports, and how to even do multiple colors!

One popular request for large landscape reports that have a lot of columns (especially numeric columns) is to give every other row a different background color. For example, here is a screenshot of a landscape report that has a white background for all the rows.

SSRS Tutorial 1

Admittedly, this report may not need a green bar effect to see the data across the row. Nonetheless, we decide to enable it anyway. In Design View, we select the entire Detail row and edit the BackgroundColor property by setting it to an expression.

SSRS Tutorial 2

We can use an “Immediate IF” statement to set the background color to “No Color” or light green. We use the Mod function to determine if the row number is an even number. Mod is used to divide one number by another number. However, it only returns the remainder. If we Mod the row number by two and it returns 0, it is an even row.

Since the first row is row 1, it is an odd number. I prefer to make the first row white. That is why I am making the even rows light green.

SSRS Tutorial Note: Remember to use quotes around the color names.

SSRS Tutorial Note: Even though SSRS uses the name “No Color” when not assigned a color, you can’t use it. Use Nothing without quotes.

SSRS Tutorial 3

Strangely, if you select the color from the list of standard colors, you will see that it has a space between “Light” and “Green”. However, if you want to set the color to light green, you must assign it without a space in its name – go figure!

Now the report can be considered easier to read!

SSRS Tutorial 4

This is usually all you need. However, in this SSRS Tutorial, I wanted to switch between four colors – white, light green, pink, and light blue. I could do a bunch of nested IIF statements but that would be a maintenance nightmare. As well, I wanted to use the cool Switch statement instead!

The way a Switch statement works is by checking a list of expressions, one at a time. As soon as it finds a statement that evaluates to true, it returns the value listed just after the expression. It doesn’t even look at the remaining expressions.

First, let’s convert the IIF to a Switch statement. We need to hard code the value “True” for the second expression so it resets the color to “Nothing”, like this:

SSRS Tutorial 5

I tested it and it still works – great! Now I want to add two more colors. I thought it would be easy to write the expressions for the Switch block using “Mod 4”, “Mod 3”, and “Mod 2”, like this:

SSRS Tutorial 6

However, this didn’t work! I ended up with quite a mix of colors that weren’t evenly distributed. What a mess. Pink was barely used while light blue was used quite a bit.

SSRS Tutorial 7

It comes down to how the Switch statement was running. Based on the math, some of the Switch statements were evaluating to True much sooner than I was expecting.

I noticed that the white ones were showing up every four bars. Hmm. I decided to base the other three colors off of Mod 4 since that never miscalculated. I changed the Switch block to look like the following:

SSRS Tutorial 8

Note that I had to add parenthesis when subtracting 1, 2, or 3 so it would be evaluated before the Mod statement. Now everything looked correct.

SSRS Tutorial 9

All colors are now predictable and evenly distributed. Thanks for reading my SSRS Tutorial and good luck on your reports!