Click here to Skip to main content
Click here to Skip to main content

SQL Reporting Services Alternating and Varying Row Styles

, 13 Mar 2008 LGPL3
Rate this:
Please Sign up or sign in to vote.
Shows how to set various styles to rows in MS SQL Reporting Services reports based on row sequence or on data.

Introduction

Often we require our data rows / lines to have different colors for various purposes and reasons. The colors may be different purely for improving readability or based on data. While ASP.NET components offer very powerful tools to achieve that, SQL Reporting has only limited functionality. However, even with it, one can get fancy.

For the purpose of simplicity, I will not get fancy, but only demonstrate how to have your row styles changed in batches of 4. I.e., row colors will repeat not each second row, but each fourth. From the solution, you will see that the number doesn't really matter.

Also, rows will change color depending on data. See that the rows with "Owner" in Contact title are yellowish, and other colors repeat each fourth row:

theReport.png

Using the project

Just download the project, unzip and open it in Visual Studio 2005. You must have SQL Server components installed though. The rest is straightforward.

Data

I am using a simple Northwind MS Access *.mdb database. The report is connected to it through an OLEDB MS Jet engine and the name of the data source is: DataSource1.

What we color

  1. Each fourth row will be repeating in color.
    • Row 1: Color 1
    • Row 2: Color 2
    • Row 3: Color 3
    • Row 4: Color 4
    • Row 5: Color 1
    • Row 6: Color 2

    and so on...

  2. Rows where the field "Contact Title" value is "Owner" is painted "PaleGoldenrod".

How we color

It's so simple that I don't believe I am putting an article. I guess, the fact that I couldn't find the solution for some time makes me do this.

In order to set the color of the row, we first select the row in the Design Layout mode:

ProjectWindow.png

Open its properties. For BackgroundColor, instead of a color, we select <Expression...>:

ColorDropDown.png

A new window pops up:

ExpressionWindow.png

where we build our expression which will determine the background color of each row. Keep in mind that we are dealing with VBA here, thus your expression has to be a "one-liner" as in MS Excel, for example.

The code

In order to satisfy our requirement, we put the following code into the BackgroundColor expression:

= IIF(Fields!ContactTitle.Value = "Owner", 
  "PaleGoldenrod",   IIF(RowNumber("DataSource1") Mod 4 = 1, 
  "White",  IIF(RowNumber("DataSource1") Mod 4 = 2, 
  "#f0ffff", IIF(RowNumber("DataSource1") Mod 4 = 3, 
  "#e0eeee", "#c1cdcd"))))

The code does the following:

If the Contact Title value is "Owner", then it paints the field in "PaleGoldenrod", else it checks for the field's relation to the number of fields to skip before repeating color.

The best way to do so is through using mod. While Div (which is represented by DivRem in this version of VBA) might have done the trick, it can't be used for because it requires a variable to receive the remainder, which we don't have and don't need.

Styles

While I only deal with BackgroundColor here, you can change other style properties based on the expression. For example, we could have had rows backgrounds colored only for readability and highlight "Owner" by changing font properties based on data.

Simpler

If you simply want rows to alternate in color, please refer to: http://www.codeproject.com/KB/database/SQLReports.aspx.

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)

Share

About the Author

Alexei Fimine
Software Developer
Canada Canada
"And though I have the gift of prophecy, and understand all mysteries, and all knowledge; and though I have all faith, so that I could remove mountains, and have not charity, I am nothing. And though I bestow all my goods to feed the poor, and though I give my body to be burned, and have not charity, it profiteth me nothing. Charity suffereth long, and is kind; charity envieth not; charity vaunteth not itself, is not puffed up, Doth not behave itself unseemly, seeketh not her own, is not easily provoked, thinketh no evil; Rejoiceth not in iniquity, but rejoiceth in the truth; Beareth all things, believeth all things, hopeth all things, endureth all things."

Comments and Discussions

 
GeneralThanks for the article PinmemberDeema_Sh23-Aug-10 21:46 
GeneralMissing images Pinmemberrobertjb2016-Mar-08 5:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 13 Mar 2008
Article Copyright 2008 by Alexei Fimine
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid