Dinesh Asanka
Final output of alternate row colors in SSRS

Alternate Row Colors in SSRS

September 20, 2019 by

Setting alternate row colors in SSRS (SQL Server Reporting Services) is an important visualization configuration for end-users so that they can easily view their reports. However, setting alternate colors in SSRS is not a click of a button configuration like in the Microsoft Excel.

The following screenshot shows the output in Microsoft Excel when the Alternate row color is set for a table.

How alternate Row colors are set in Microsoft Excel.

However, in SSRS this is not straight forward as in Microsoft Excel shown in the above screenshot.

Let us configure a report in SSRS.

SSRS Sample Report

Following is the SSRS project solution.

SSRS Project Solution to demostrate alternate row colors.

First, the data source is created for the AdventureWorks sample database in any SQL Server instance. After the data source creation, the next step is to create a data set with the following t-SQL code.

Please note that only six orders are used for demonstration purposes. Also, the data set is sorted by the order by OrderID for the demonstration purposes.

Next is to create a table in the SSRS report and link with the data set and you will see the following report.

Defaut SSRS report

No major formatting was done to the report except for the rounding the Line total to the two decimal points.

Alternate Row Colors in SSRS for Table

The next task is to set alternate row colors in SSRS in the above SSRS Report.

Right-click the data row as shown in the below screenshot, click F4 or properties window in the View menu.

Configuring alternate row colors in SSRS Report.

When selecting this, you will see the BackgroundColor option. By default, it is No Color, which means that there is no color for the background and use can select any colors. However, those options will provide a report with the same colors for all rows, not for alternate row colors in SSRS Report as required.

You can select the Expression… option in the listed options which will give you a screen when you can enter an expression.

Expression for the alternate row colors in SSRS.

In the expression, ROWNUMBER function is used. ROWNUMBER will be the row number for the row. The method used in this case is that the odd row numbers are light blue while the even row numbers are blue. Odd rows are found by for the rows which has reminder 1 when the row number is divided by 2 and similarly, the even rows are the rows which will be the reminder 0 when the row number is divided by 2. This entire logic is used with the IIF and ROWNUMBER functions as shown in the above screenshot.

Then the SSRS report is shown as following which has alternate row colors.

Final output of alternate row colors in SSRS

Now this will be same as what you get in Microsoft Excel.

Alternate Row Colors in SSRS When Grouping

In SSRS, typically you add groups to the detail records. For example, in the above report, the Sales Order ID is repeated in the above data set. This means that the report can be grouped by the Sales Order ID and when the grouping is done, the report will be looked like the following screenshot.

Invalid alternate row color in SSRS when the grouping is done for the tablix report.

You will observe that background color has gone wrong for the grouping rows. This is because an additional row is introduced to the grouping column. To avoid this, the background color of the grouping row should be modified accordingly.

Modify back ground for color in the grouped report.

Then the report will look like the following screenshot.

Alternate row colors in SSRS for the grouping in SSRS reports.

Alternate Row Colors in SSRS Matrix Control

Matrix is an SSRS control from which you can have dynamic columns and rows. This means that unlike in the previous example of tables, in the matrix control, columns will grow.

In the following report, order numbers are in the columns while the product names are in the rows. In case you get a new order number that will appear in the next column.

Sample Matrix report is SSRS.

Due to this dynamic nature of the report, the previous simple rule will not work for matrix. If you apply the same rule, the alternate color will occur not at the row level but for every value in the matrix.

This means we need a new approach for the reports with matrix control.

  1. Add a column outside the dynamic columns which are shown in the following screenshot and highlighted column is the newly added column

    Add a column outside the dynamic columns

  2. Then add an expression to the newly added column as shown in the below screenshot. This column is Textbox10

    Adding a value to the newly added column.

    Expression is : =IIF(RUNNINGVALUE (Fields!ProductName.Value,CountDistinct,Nothing) Mod 2, “LightBlue”, “Blue”)

    It is similar to the previous expression, but only thing is, RUNNINGVALUE for the grouped column which is the Product Name is used.

    Now you will get the color into the text value as shown in the below screenshot:

    Color will be displayed in in the newly added column in the SSRS matrix report.

  3. Next step is to get the background color from the value of the newly added column. Since the color is available the newly added column, that color can be set to the background of the matrix row

    Seeting up value of the nwely column to the background color of the row.

  4. Next is to hide the newly added column since this column is used only as an internal column to the report. This can be done by setting the Hidden option to True

    Hide the newly added column

Finally, the report will look like the following screenshot.

Final output for alternate row colors in SSRS Matrix control.

In both the modes, a new the column is added, and it will automatically get the necessary background color so that it does not need any additional configurations. Also, instead of the name of the color, you can use the color code which can be taken from the RGB Color Codes Chart

Dinesh Asanka

Dinesh Asanka

Dinesh Asanka is MVP for SQL Server Category for last 8 years. He has been working with SQL Server for more than 15 years, written articles and coauthored books. He is a presenter at various user groups and universities. He is always available to learn and share his knowledge.

View all posts by Dinesh Asanka
Dinesh Asanka

Latest posts by Dinesh Asanka (see all)

151 Views