Alternative Row Background Color in an SSRS Tablix/Matrix

Original URL…

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
Anuncios

SSRS ERROR: Failed to register url=http://+:80/ReportServer/ for endpoint 2, error=5

Error que se puede dar al ejecutar SQL Server Reporting Services.

  1. Reporting Services: Error creating HTTP endpoint – Access is Denied (Solución del error HTTP Error 503. The service is unavailable)
  2. Y la solución del otro error “Los permisos otorgados al usuario ‘Dominio\usuario’ son insuficientes para realizar esta operación. (rsAccessDenied)” (Solucion).
    1. Start IE as an administrator and load your SSRS site
    2. Go to Folder Settings and add your user name and assign relevant Role(s)
    3. Start IE or any other browser for that matter and the SSRS site comes up good