Search code examples
c#reporting-servicesreportsql-server-data-toolsrdl

Two rows having same color in SQL Server reporting

The project is using SQL Server reports with N number of rows on it. My requirement was having a background color of a row in an alternative way.

To achieve this following expression were provided to backgroundcolor property of rows:

=IIf(RowNumber(Nothing) Mod 2 = 1, "RED", "BLUE")

Everything works perfectly, except the second and third line of a second page having the same color:

enter image description here

Considering the following properties of report:

  • Grouping applied on first column of a report. However, entries of first row can be repeated.
  • Not a single column having unique entry hence grouping on any column giving the same issue.

In nutshell, is it possible to give an alternative background color to row where grouping was applied and repeated entries where skiped?

Solution

  • I have no experience concerning your particular issue (so apologies in advance if it this doesn't work); but the way I've done this in regular reports is to have a hidden column using an expression. In your case you would have to put this expression inside your row group as a hidden column:

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

    RunningValue is similar to an identity column in SQL Server, where every new row increments the value +1.

    You should rename this hidden cell to something meaningful like ROWCOLOR. If you right-click the cell and select Text Box Properties- set the Name to ROWCOLOR.

    The background color for the rows is alternated using another expression such as:

    =IIF(VAL(ReportItems!ROWCOLOR.Value) MOD 2, "RED", "BLUE")