Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Wednesday, August 20, 2008 5:29 AM | Filed Under [ Reporting Services ]

Feedback

Gravatar

# 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.
8/20/2008 8:55 AM | wally
Gravatar

# 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"))
8/20/2008 4:31 PM | Darin
Gravatar

# 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
8/20/2008 9:54 PM | Joe Webb
Gravatar

# re: Alternating background colors in Reporting Services

how do you alternate rows in a matrix?
1/9/2009 1:37 PM | ange
Gravatar

# re: Alternating background colors in Reporting Services

how about alternating rows if u have a grouping?
1/12/2009 12:19 PM | ricciomb
Gravatar

# re: Alternating background colors in Reporting Services

You wish to alternate colors within the group? I'll see if I can set that up.

1/15/2009 5:01 PM | Joe Webb
Gravatar

# 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
1/30/2009 10:45 AM | R. Linder
Gravatar

# 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

1/30/2009 11:32 AM | R. Linder
Gravatar

# re: Alternating background colors in Reporting Services

Thanks for sharing this!

1/30/2009 1:28 PM | Joe Webb
Gravatar

# 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)
:-)
4/15/2009 9:15 AM | Claudio
Gravatar

# 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.
4/27/2009 2:11 PM | dtradio
Gravatar

# 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
6/4/2009 6:03 AM | Paggi
Gravatar

# 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
6/10/2009 5:07 AM | Mehrab
Gravatar

# 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
8/4/2009 6:51 AM | Rupa
Gravatar

# 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
8/21/2009 10:38 AM | Praveen
Gravatar

# 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
8/25/2009 11:54 PM | Paggi
Gravatar

# re: Alternating background colors in Reporting Services

Great post. Very helpful and easy to understand. Nice job!
10/16/2009 12:16 PM | lds1ph
Gravatar

# re: Alternating background colors in Reporting Services

Hi,

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

Kind regards

RickyP
2/10/2010 5:24 AM | RickyP
Gravatar

# 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?
3/25/2010 9:56 AM | Stac
Gravatar

# re: Alternating background colors in Reporting Services

This was really nice article on simple and more robust methods used in ssrs.............good work.
4/22/2010 4:51 PM | Bharath Reddy VasiReddy
Gravatar

# 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....
5/3/2010 1:14 AM | sites de jeux de societe
Gravatar

# 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
5/3/2010 6:49 AM | Joe Webb
Gravatar

# re: Alternating background colors in Reporting Services

very good article..very useful too
5/23/2010 11:37 PM | Abhilash Sunkara
Gravatar

# re: Alternating background colors in Reporting Services

Thanks for this tidbit. It's all coming back to me know, thanks for jarring my memory.
7/26/2010 8:22 AM | RonNasty64
Gravatar

# re: Alternating background colors in Reporting Services

this works great Thanks!!
9/27/2010 9:39 AM | Selwyn
Gravatar

# re: Alternating background colors in Reporting Services

Let's consider an example. We have an employee phone list report as shown below.
10/15/2010 12:46 AM | columbia jackets
Gravatar

# 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
10/19/2010 4:41 AM | fur boots
Gravatar

# 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.
12/10/2010 11:14 AM | Steve D.
Gravatar

# 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")
1/12/2011 1:29 PM | NickB-UK
Gravatar

# 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.
2/23/2011 10:30 AM | Tejas
Gravatar

# re: Alternating background colors in Reporting Services

Merci Joe!
2/24/2011 3:39 AM | Jibe84
Gravatar

# 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

3/1/2011 3:12 AM | NIRAJ Kumar
Gravatar

# 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!!!
3/7/2011 3:21 PM | kalpesh
Gravatar

# re: Alternating background colors in Reporting Services

Could you please explain about (Nothing)?
6/26/2011 1:35 AM | Rams
Gravatar

# 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
8/3/2011 1:58 AM | INM
Gravatar

# 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
8/22/2011 12:55 PM | tStat
Gravatar

# re: Alternating background colors in Reporting Services

You Saved My life!!!!! Thank You!!!!
12/29/2011 10:18 AM | Erick
Gravatar

# re: Alternating background colors in Reporting Services

Thanks!!!!
2/15/2012 4:54 AM | Filippo
Gravatar

# 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!!!!!!!!!!!!!!!!!
8/17/2012 12:15 AM | Ubaid ur Rahman
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET