Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

Alternating background colors in Reporting Services

One common question from many of those who are relatively new to Microsoft SQL Server Reporting Services is how to alternate the background color of detail rows in a table data region.

Although I'm sure samples of this have been posted elsewhere on the Internet, I thought I'd share a common technique in the hopes that those searching for a solution will find it, either here or elsewhere.

Let's consider an example. We have an employee phone list report as shown below.

SSRS_alternating_bgcolor2-2008-08-19

To make the report a little easier to read horizontally, we'd like to change the background color of every other row. To do so, let's highlight the detail row of the data table in the layout tab.

SSRS_alternating_bgcolor3-2008-08-19

In the properties window, find the BackgroundColor property for the highlighted row and choose <Expression…>. Add the following conditional formatting statement in the Edit Expression window.

SSRS_alternating_bgcolor4-2008-08-19

Click Ok, and preview the report.

SSRS_alternating_bgcolor1-2008-08-19

And there you go, a report that alternates the background color for each row.

Thanks to the folks in Cincinnati earlier this week! Great questions and comments during the class.

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


wally
2008-08-20
re: Alternating background colors in Reporting Services
Cool. Can you tell us how to put in a third critera. Let say you wanted to continue toggling between silver and white but also turn the row to red if another condition is met. ie, if the phone number starts with 206 for instance, that row will turn to Red instead of white or silver. That would be very cool.

Darin
2008-08-20
re: Alternating background colors in Reporting Services
You just need to interrogate Field!Phone.Value

e.g.

=IIF(Left(Fields.Phone.Value, 3) = "206", "Red", "White")

or if you want to still do the alternating row then nest the statement above

e.g.
=IIF(Left(Fields!Phone.Value, 3) = "206", "Red", IIF(RowNumber(nothing) Mod 2, "Silver", "White"))

Joe Webb
2008-08-20
re: Alternating background colors in Reporting Services
Thanks, Darin!

In additional to the IIF function, we can use the Switch function, too. I just posted a blog about it.

Cheers!

Joe

ange
2009-01-09
re: Alternating background colors in Reporting Services
how do you alternate rows in a matrix?

ricciomb
2009-01-12
re: Alternating background colors in Reporting Services
how about alternating rows if u have a grouping?

Joe Webb
2009-01-15
re: Alternating background colors in Reporting Services
You wish to alternate colors within the group? I'll see if I can set that up.


R. Linder
2009-01-30
re: Alternating background colors in Reporting Services
If you have a list of grouped items, then the row numbers are not contiguous, and the colors are not alternated,but bunched up, because RowNumber is not consecutive (odd even), so in place of the (Nothing), you need to put a "data region". If you use "table" or "dataset" it still does not alternate because some row numbers are combined in the group. So the question is do you continue to use ROWNUMBER and if so what data region do you use? Is it even possible to alternate using Rownumber when you are using groups.

R. Linder

R. Linder
2009-01-30
re: Alternating background colors in Reporting Services
Well I found this:
<http://www.wrox.com/WileyCDA/Section/Report-Solution-Patterns-and-Recipes-Greenbar-Reports.id-291857.html>
and it works, only tricky part is the first column background color boolean is True, the rest of the columns to the left are boolean False, see Figure 2.

Thanks for providing this blog Joe.

R. Linder


Joe Webb
2009-01-30
re: Alternating background colors in Reporting Services
Thanks for sharing this!


Claudio
2009-04-15
re: Alternating background colors in Reporting Services
en una matriz you can alternate colors with
=IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2)
:-)

dtradio
2009-04-27
re: Alternating background colors in Reporting Services
An matrix solution (2005 and later):
1. Create a color value column/field in the dataset for each group using DENSE_RANK
Example: For [postal code] values, the column will be
bgcolor = CASE dense_rank() OVER (ORDER BY [postal code]) WHEN 1 THEN 'White' ELSE 'LightGrey' END

2. on the "postal code" group on the matrix, set each textbox bgcolor property to "=First(Fields!bgcolor.Value)"

This works for most cases provided each group contains a unique value.

Paggi
2009-06-04
re: Alternating background colors in Reporting Services
Hi ,

I wish to have my coloumns colored in alternate coloring schema ..
is there anything similar to rownumber ..
or may be you can point me to someplace where i can look up at user defined code

Mehrab
2009-06-10
re: Alternating background colors in Reporting Services
I have faced a problem. The result is not reflected on the first column , the other columns are ok. I need some help

Rupa
2009-08-04
re: Alternating background colors in Reporting Services
=IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2)
This works fine when this expression is used in matrix with groups also but when the calculation field is not zero if calcuation field is zero it is getting wrong colours

Praveen
2009-08-21
re: Alternating background colors in Reporting Services
Hi Guys,

Suppose the basic sample matrix has two columns. 1 is Row group and other is Column Group.
Steps:
1. Right Click on Row Group and add another group - New Row Group.
2. In its row (let say textbox1) expression set "=iif(RunningValue(Fields!RowGroup.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")". If u have more row groups set the expression to "=iif(RunningValue(Fields!RowGroup.Value & Fields!RowGroup1.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")"
3. Set the data cell's (The intersection of row and column) background color to =ReportItems!textbox1.Value
4. Thats it the data cell gets the alternate colors. If the Row Groups also need this alternate color then set the expression "=iif(RunningValue(Fields!RowGroup.Value & Fields!RowGroup1.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")" to background color property.
5.Right Click --> Edit Group on the New Report group and set visiblity to hidden

This worked for me . Hope This works for u too

Paggi
2009-08-25
re: Alternating background colors in Reporting Services
Do we something to color ALTERNATE COLUMNS when the colums are Grouped by.

eg a Column P has 3 subcolomns (A, B, C).

The result should be
First the first Column P0(containing subcolumns A0,B0,C0) should be white , next P1(containing subcolumns A1,B1,C1) should be blue and P2(containing subcolumns A2,B2,C2) should be white and so on

lds1ph
2009-10-16
re: Alternating background colors in Reporting Services
Great post. Very helpful and easy to understand. Nice job!

RickyP
2010-02-10
re: Alternating background colors in Reporting Services
Hi,

Praveen's example doesn't seem to cater for Matrix totals?

Kind regards

RickyP

Stac
2010-03-25
re: Alternating background colors in Reporting Services
How can you alternate row color when a value in a cell in the row is different from the previous row?

Bharath Reddy VasiReddy
2010-04-22
re: Alternating background colors in Reporting Services
This was really nice article on simple and more robust methods used in ssrs.............good work.

sites de jeux de societe
2010-05-03
re: Alternating background colors in Reporting Services
I hope that when you come back we will see your new advanced blogs! As I liked them so much! Yes, you didn't write exactly when that day will come... some kind of a secret! Like that we are going to wait you every day....

Joe Webb
2010-05-03
re: Alternating background colors in Reporting Services
Hey Guys - Thanks for the nice comments on this article!

Just wanted to let you know that I'm now blogging over here - http://www.webbtechsolutions.com/blog.

Thanks!

Joe

Abhilash Sunkara
2010-05-23
re: Alternating background colors in Reporting Services
very good article..very useful too

RonNasty64
2010-07-26
re: Alternating background colors in Reporting Services
Thanks for this tidbit. It's all coming back to me know, thanks for jarring my memory.

Selwyn
2010-09-27
re: Alternating background colors in Reporting Services
this works great Thanks!!

Steve D.
2010-12-10
re: Alternating background colors in Reporting Services
Thanks Joe!

As simple as it is I always have a brain cramp when trying to remember this. Google led me right to you.

NickB-UK
2011-01-12
re: Alternating background colors in Reporting Services
For alternating row highlighting within the group, RowNumber is scope aware, so you can get the RowNumber within the group, eg

BrandX
ProdX-1 RowNumber("group_BrandName") is 1
ProdX-2 RowNumber("group_BrandName") is 2
Total BrandX RowNumber("group_BrandName") is 2


So, for the BackGroundColor for the detail row use : Iif(RowNumber("group_BrandName") Mod 2 = 0, "Silver", "White")

For the group total swap the colours, and use : Iif(RowNumber("group_BrandName") Mod 2 = 0, "White", "Silver")

Tejas
2011-02-23
re: Alternating background colors in Reporting Services
Thanks Joe for very helpful post also Thanks Rupa you solution work for me

=IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2)

my data set does not have any non zero and I am comparing unique row. I suggest if we add a row as unique Row Sequence above solution will work fine.

Jibe84
2011-02-24
re: Alternating background colors in Reporting Services
Merci Joe!

NIRAJ Kumar
2011-03-01
re: Alternating background colors in Reporting Services
Hi Nick I m Not Getting Please Explain
BrandX
ProdX-1 RowNumber("group_BrandName") is 1
ProdX-2 RowNumber("group_BrandName") is 2
Total BrandX RowNumber("group_BrandName") is 2


kalpesh
2011-03-07
re: Alternating background colors in Reporting Services
If the Row Groups also need this alternate color then set the expression "=iif(RunningValue(Fields!RowGroup.Value & Fields!RowGroup1.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")" to background color property.


This really workt for me after tring every thing
thanks Praveen!!!

Rams
2011-06-26
re: Alternating background colors in Reporting Services
Could you please explain about (Nothing)?

INM
2011-08-03
re: Alternating background colors in Reporting Services
Well, we are encoutering a scenario where we would need to take a Go-NoGo decision based on the capability of SSRS.

The requirement is as below:

Based on the resultset (after querying for the parameters selected by user), the various ranges would be decided; and the most acceptable range would be having green background, then yellow etc, finally reaching unacceptable at RED.

The idea is to give a visual impression of various production parameters and how healthy the production system is.

Now, the concern is that these ranges would change based on the parameters selected by the user at runtime, and we would need the charting tool to be able to have that capability.

Frankly, I don't think SSRS can do this. But has anyone tried? Or anyone succeeded? Thanks in advance and "NO" or "not possible" is also a good answer!

Rgds

tStat
2011-08-22
re: Alternating background colors in Reporting Services
INM... this is entirely doable in SSRS. Use nested iff statements in the fomating. Have the logic in thos iif statements utilize the values supplied as parameters from the user running the report

Erick
2011-12-29
re: Alternating background colors in Reporting Services
You Saved My life!!!!! Thank You!!!!

Filippo
2012-02-15
re: Alternating background colors in Reporting Services
Thanks!!!!

Ubaid ur Rahman
2012-08-17
re: Alternating background colors in Reporting Services
thanks for sharing it's nice and very easy code i have solve my problme using this line of code
Thanks!!!!!!!!!!!!!!!!!