Your SQL questions, answered

Setting row color in SSRS – Querychat

While developing SSRS reports, we strive to build a report that is visually more attractive to a user. In case we have table incorporated within a report, we can boost the attractiveness and readability of a report table by changing default row colors.

In order to explain how to set row color in a SSRS report table, we will  use the data provided in the table below. First we will change table header color, and then we will programatically change table row colors as well, in a way that only rows having even ordinal numbers will be affected by the background color change.

Table: Students

A report which displays the data from Students table is presented in the picture below.

 Figure 1: Basic report structure

Setting table header color

If we want to set a table header color, the steps are the following:

          1. Position on table report and select entire table header

Figure 2: Selecting table header

          2. Go to Properties tab and locate Fill section

Figure 3: Locating Fill section within the Properties tab

          3. As displayed, we have the possibility to set the background color of the selected table part in two ways: changing the color of it by setting BackgroundColor property or by setting BackgroundImage property. In this example, we will change the BackgroundColor property from “No color” to “Silver”

Figure 4: Setting BackgroundColor property

          4. If we make a preview of the report, we will see that the header color has been changed

Figure 5: Preview of new table header design

Setting table row color

In this example we will demonstrate how to change row color related to rows which have even ordinal numbers. The steps are the following:

          1. Position on table report and select the table row that will display table data

Figure 6: Selecting table row that will display data

          2. Go to Properties tab and find Fill section

Figure 7: Locating Fill section within the Properties tab

          3. We will change here BackgroundColor property as well, but unlike the previous example, we will define the expression within this property

Figure 8: Setting BackgroundColor property by using Expression

          4. Define the expression so that rows with even ordinal numbers have different color from the rows with odd ordinal numbers which will remain white

Figure 9: Setting the Expression for BackgroundColor property

The expression is defined in the following way:

  • We use IIF statement to define two different ways of action for rows with odd and even numbers. IIF(condition, action1, action2) has three parts and whenever the condition is true (or) 1, then action1 will be executed. Otherwise, action2 will get executed.
  • ROWNUMBER(NOTHING) function will return row number for each data row
  • ROWNUMBER(NOTHING) MOD 2 defines if the row number is odd or even: if the result of the expression is 0, the row number is even, otherwise the row number is odd
  • If the row number is even, row color will be set to “White”, otherwise it will be “PaleTurquoise”

          5. If we make a preview of the report, we will see that the table row colors are different, as defined in the expression in the previous step

Figure 10: Preview of new design of table rows

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc