Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Alternate Row Background Color in SQL Server Reporting Services Tablix and Matrix


By:   |   Read Comments (11)   |   Related Tips: > Reporting Services Formatting

Problem

You want to make your SQL Server Reporting Services (SSRS) Tablix's and Matrix's more attractive by configuring an alternative row background color. Unfortunately, you cannot find any out of box properties in SSRS to do this. How do you configure alternative row background colors in a Tablix or Matrix?

Solution

The alternative row background color improves the readability of data in your SQL Server Reporting Services (SSRS) Reports. In this tip I will show you how to configure alternative row background colors in a Tablix and Matrix each with one simple example.

Set Alternative Row Background Color in an SSRS Tablix

I have a Tablix in my report, but the Tablix does not have alternative row background colors. Here is an example:

Tablix Preview without background color

To set alternative row background color, please follow the below steps.

1. Select the Details Row group from the Tablix.

2. Then press the F4 key or manually open Properties window.

3. In the Properties window select BackgroundColor Property and then click on the arrow button which is on the right side. Next, click on Expression as shown in the below screenshot.

Tablix Row Group Property

4. Once you clicked on Expression in step 3, an Expression Window will open. In the Expression Window paste the below expression.

=IIF(ROWNUMBER(NOTHING) MOD 2, "LIGHTBLUE", "SILVER")

This expression contains the alternative row background color; you can choose the color of your choice. You can also use hexadecimal values for colors. You can find hexadecimal values for colors at here. Next click on the OK button. You can refer to the below screenshot.

Tablix Row Group Background Color Expression

We have made all necessary changes, now let's preview the report. As you can see from the image below, each Tablix row has alternative background color.

Tablix Preview After Background color

Set an Alternative Row Background Color in an SSRS Matrix

I have a Matrix in my report, but the Matrix does not have alternative row background colors. Here is an example:

Matrix Report Preview without background color

We can't set alternative row background color in a Matrix similar to Tablix.  Please follow the steps below to set an alternative row background color in an SSRS Matrix.

1. Select the innermost row group (in my case it is City). Right click on selected innermost row group and click on Add Group and then click on Child Group. You can refer to the below image.

Matrix add new dummy group

2. Once you clicked on Child Group in step 1, it will open the Tablix group window. Please provide the Group by value as 1 and click on the OK button. You can refer to the below image.

Matrix dummy group property

3. As you can see from the below image, a new child group named as Group1 was created in the previous step. Now right click on the Group1 data field text box which was automatically added when Group1 was created and click on Text Box Properties. You can refer to the below image.

Matrix Group1 Row Textbox Selection

4. Please provide the Text box name as ROWCOLOR and then click on the Value's Expression button. Once you click on expression an Expression window will open. In that window paste the below expression.

=RunningValue(Fields!City.Value,countDistinct,Nothing)

You can refer to the image below.

Matrix Group1 Row Texbox Properties

5. Select the RowColor and MaritalStaus data field text box and then press the F4 key or manually open the Properties window. In the Properties window select BackgroundColor Property and then click on arrow button which is on right side and select Expression as shown in the below screenshot.

Matrix Group1 and Marital Status TextBox Background Color Properties

6. Once you clicked on expression in step 5, an Expression window will open. In that window paste the below expression.

=IIF(VAL(ReportItems!ROWCOLOR.Value) MOD 2,"LIGHTBLUE","SILVER")

This expression contains the alternative row background color.  You can choose color according to your choice. You can also pass hexadecimal values for colors. You can find hexadecimal values for colors at here. Next click on the OK button. You can refer to the below screenshot.

Matrix Group1 and Marital Status Background Color Expression

7. Select the City data field text box and then press the F4 key or manually open the Properties window. In the Properties window select BackgroundColor Property and then click on the arrow button which is on right side then click on the Expression as shown in the screenshot below.

Matrix City Column Properties

8. Once you clicked on the Expression button in step 7, an Expression window will open. In that expression window paste the below expression.

=iif(RunningValue(Fields!City.Value,CountDistinct,Nothing) Mod 2, "LIGHTBLUE", "SILVER")

This expression contains the same alternative row background color you provided in step 6 then click on the OK button. You can refer to the screenshot below.

Matrix City Column Background Color Expression

9. Select the newly added Group1 column in the Matrix and then press F4 or manually open the Properties window.

Matrix Group1 Column Selection

In the Properties window, change Right BoderStyle to None and set Width to 0in.

Matrix Group1 Properties

10. Select the MaritalStatus column in the Matrix and then press F4 or manually open Properties window.

Matrix Marital Column Selection

In the Properties window, Change Left BoderStyle to None.

Matrix Marital Properties

11. We have not given any background color to the first two columns in the Matrix. If you don't want to give any background color then you are all set to preview the report. If you want to include a background color then update. I am using LIGHTBLUE as background color for first two columns in the Matrix.

Matrix Region and State Background Color

12. We have made all necessary changes, now let's preview the report. As you can see from the image below the Matrix detail and first innermost row group has an alternative background color.

Matrix Report Preview
Next Steps


Last Update:






About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, October 07, 2016 - 2:21:52 AM - Anwer jamal Back To Top

This article helped me in very simpler and short way.

Very nice tip.


Wednesday, August 10, 2016 - 4:36:25 PM - Dmac Back To Top

 Hi, 

Thank you so much for posting this article, it is excellent and has been very helpful. I am running into an issue though, I have tried setting the width of my "Group1" column to 0 but by default it always goes back to .03125. When I run my report in report builder everything looks exactly how I want it to, however, when I upload this into our production server that column shows up. I have tried setting it to hidden and everything else I can think of but nothing works. Any idea why this may be occurring? Thanks!

 


Friday, July 29, 2016 - 8:56:43 AM - Amruta R Back To Top

 Hi Ghanesh,

I find this post very useful and I am trying to implement something similar in matrix. But facing an issue after 11 rows the runningvalue is not getting incremented properly i.e. it shows 12 for next 5 rows and then 13 for all the remaining 70 rows. Hence in intial 11 rows I see the color getting alternate properly but from 12 row this doesnt work. My inner most row group is Month that has format something like Jul 2015, Aug 2015, Sep 2015, etc.

Can you please suggest how can this be corrected?

Thanks

Amruta

 


Wednesday, December 16, 2015 - 12:12:48 AM - Ali Back To Top

Thanks Ganesh,

I found this post very usefull and your description is very clear. I made it more simple by adding a coulmn for RowColor outside the most inner row group, then hide the column by setting Column Visibility to false.

 

Cheers,

Ali


Friday, July 24, 2015 - 5:25:58 AM - Min Back To Top

Excellent very clear and works a treat, thank you


Friday, July 17, 2015 - 3:35:26 PM - Jeff Back To Top

I'm struggling with a slight variation on this and would appreciate any thoughts. In your example, what if I wanted to alternate the color everytime the State Province Name changed. So, Alberta would be blue, British Columbia would be white (all rows), Ontario would be blue. My IIF statement needs to say: If the State Province Name changes AND the Previous row BackgroupColor = "Blue" then "", else "Blue". The first part of that is trivial, but I can't seem to figure out how to check the BackgroundColor.

= IIf(Previous(Fields!BackgroundColor.Value)) = "Blue", "", "Blue")

Doesn't not work. Any thoughts?


Thursday, July 02, 2015 - 11:17:04 AM - Ghanesh Back To Top

Thank you Ivo Novak for your comments.

Regards,

Ghanesh


Wednesday, May 13, 2015 - 9:48:03 AM - Ivo Novak Back To Top

Perfect issue, very useful. It helped me.

Thank you.


Thursday, May 07, 2015 - 2:54:23 PM - Ghanesh Prasad Back To Top

Hi Kris Maly, 

Thanks for your comments, I will keep writing day to day problem solution. 

Coming future you will get some more tips on SSRS, I do have experience in SSAS. I will write on SSAS too.

Keep Learning, It’s never ending processing.

Regards,

 

Ghanesh Prasad


Thursday, May 07, 2015 - 2:13:33 PM - Kris Maly Back To Top

Do you have experience in SSAS if so how about writing some tips on that topic


Thursday, May 07, 2015 - 2:10:52 PM - Kris Maly Back To Top

Awesome.

I read the article and steps explained very well.

I have done this earlier and they are fine.

Recommend others to read.

 

Please keep publishing articles with your day to day experiences in the field

 

Thanks a lot


Learn more about SQL Server tools