In this post I am going to show how to handle alternate row coloring in three scenarios
- Simple Tablix with static columns that render set of rows
- Tablix with static columns and contains row grouping
- Tablix with dynamic columns and contains row grouping
Scenario 1: Simple Tablix with static columns that render set of rows
The following is the expected tablix output:
Given: The columns “Hiring Month”,”Name”,”Department” are static columns
Then: we can achieve alternate row coloring by setting all rows cell property “BackgroundColor” to “=iif(RowNumber(Nothing) mod 2,"White","Gray")”
RunningValue Function
Before starting the next scenario I would like to have a little peek about the RunningValue function which returns running aggregate values for an expression within scope let’s have an example to make things more clear
Given that we have a data table with columns “HiringMonth”, “HiredCount”, “Department” that is rendered in tablix like the following image
The first 3 columns is mapped directly from the DataTable
Column “Count”
Value: =RunningValue(Fields!HiredCount.Value,count,Nothing)
Description: this column contains the accumulated number of rows from the table till the current row so as we can see the first row is 1,second row we used 2 rows so count equals 2 ,…..
Column “Count Distinct”
Value:
=RunningValue(Fields!HiredCount.Value,countdistinct,Nothing)
Description: this contains the accumulated distinct count of HiredCount column i.e. this value is the result of calling the SQL statement “select count(distinct hiredcount) from table” till the current so we will find row 1 value equals 1,row 2 equals 2,row 3 equals 3 and row 4 equals 3 because this is distinct and row 2 value is 2 so the result still 3 ,…
Column “Summation”
Value: is =RunningValue(Fields!HiredCount.Value,sum,Nothing)
Description: the accumulated summation of the HiredCount column till the current running row
Row 1 = 10
Row 2 = Row1+2 = 10
Row3 = Row2+1 = 13
Scenario 2: Tablix with static columns and contains row grouping
The following image contains the expected output:
Given: the columns “HiringMonth”, “Name”, “Department” are static columns and we have group on the column “HiringMonth”
Then: we can achieve alternate row coloring by setting all rows cell property “BackgroundColor” to “=iif(RunningValue(Fields!HiringMonth.Value ,countdistinct,"table1") mod 2,"LightGrey","Gray")”
Scenario 3: Tablix with dynamic columns and contains row grouping
Scenario 3.1
The first column is row group on column HiringMonth, the rest of the columns is dynamic
Scenario 3.2
The first column is row group on column HiringMonth columns .Net and Java is column group on column Department the sub columns “Male Count” and “Female Count” is static columns inside the department column group
To achieve alternate row coloring for the previous scenario we will do the following steps
- Add column to your query to hold number of dynamic columns for example “NumberOfColumns”
- Create Report Variable “DynamicColumnsCount” with value equals to the total number of columns inside the row
Scenario 3.1
Value = The Number of the columns defined in the previous step + 1
Value = “=Fields!NumberOfColumns.Value + 1”
Value = 2 + 1 = 3
Scenario 3.2
Value = ( Number of Dynamic Columns defined in previous step * Static Columns inside the Dynamic Columns (Male Count, Female Count) + Static Columns (Hiring Month)
Value = (Fields!NumberOfColumns.Value * 2 ) + 1
Value = (2 * 2)+ 1 = 5
- Define 2 static variables
rowStatus: this variable will hold either “Even” or “Odd” which represent if we are in even or odd row so we can alternate row color
Counter: this variable will hold the accumulated number of rendered cells
- Define function called ToggleRowStatus which toggle the value of static variable rowStatus from Even to Odd and vice versa
Public Function ToggleRowStatus() As String
If rowStatus = "Even" Then
rowStatus = "Odd"
Else
rowStatus = "Even"
End If
Return rowStatus
End Function
- Define function called GetRowStatus which accept the number of dynamic columns defined in step 2
Public Function GetRowStatus(columnCount As Integer) As String
If Counter Mod columnCount = 0 Then
ToggleRowStatus()
End If
Counter = Counter + 1
Return rowStatus
End Function
- Update the BackgroundColor property of all row cells to “
=iif(Code.GetRowStatus(Variables!DynamicColumnsCount.Value)="Even","LightGrey","Grey")”by setting this property, the function GetRowStatus will be called with rendering each cell incrementing the counter and toggling its value in case counter mode columns count equals zero
The following is the full report code
Public Shared rowStatus As String = "Even"
Public Shared Counter As Integer = 0
Public Function GetRowStatus(columnCount As Integer) As String
If Counter Mod columnCount = 0 Then
ToggleRowStatus()
End If
Counter = Counter + 1
Return rowStatus
End Function
Public Function ToggleRowStatus() As String
If rowStatus = "Even" Then
rowStatus = "Odd"
Else
rowStatus = "Even"
End If
Return rowStatus
End Function
You can download sample project from here , all you have to do is to change the connection string to a valid connection and test the reports.
Enjoy.
