Alternating background colors in Reporting Services

Wed Aug 20, 2008 by Joe Webb 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.

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.

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.

Click Ok, and preview the report.

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

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!!

columbia jackets 2010-10-15	re: Alternating background colors in Reporting Services Let's consider an example. We have an employee phone list report as shown below.

fur boots 2010-10-19	re: Alternating background colors in Reporting Services 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. snow boots \| snow boots for women \| columbia sportswear \| columbia sportswear outlet \| cheap north face jackets \| the north face outlet \| mac makeup \| cheap makeup

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!!!!!!!!!!!!!!!!!