Add alternating row color to SQL Server Reporting services report

Question

How do you shade alternating rows in a SQL Server Reporting Services report     Edit  There are a bunch of good answers listed below--from quick and simple to complex and comprehensive  Alas  I can choose only one

User · Answer

Michael Haren s solution works fine for me  However i got a warning saying that  Transparent  is not a valid BackgroundColor when Preview  Found a quick fix from Setting BackgroundColor of Report elements in SSRS  Use Nothing instead of  Transparent     IIf RowNumber Nothing  Mod 2   0   Silver   Nothing

User · Answer

Just because none of the answers above seemed to work in my matrix  I m posting this here   http   reportingservicestnt blogspot com 2011 09 alternate-colors-in-matrixpivot-table html

User · Answer

My problem was that I wanted all the columns in a row to have the same background   I grouped both by row and by column  and with the top two solutions here I got all the rows in column 1 with a colored background  all the rows in column 2 with a white background  all the rows in column 3 with a colored background  and so on   It s as if RowNumber and bOddRow  of Catch22 s solution  pay attention to my column group instead of ignoring that and only alternating with a new row   What I wanted is for all the columns in row 1 to have a white background  then all the columns in row 2 to have a colored background  then all the columns in row 3 to have a white background  and so on   I got this effect by using the selected answer but instead of passing Nothing to RowNumber  I passed the name of my column group  e g    IIf RowNumber  MyColumnGroupName   Mod 2   0   AliceBlue    Transparent     Thought this might be useful to someone else

User · Answer

My matrix data had missing values in it  so I wasn t able to get ahmad s solution to work  but this solution worked for me  Basic idea is to create a child group and field on your innermost group containing the color   Then set the color for each cell in the row based on that field s value

User · Answer

One thing I noticed is that neither of the top two methods have any notion of what color the first row should be in a group  the group will just start with the opposite color from the last line of the previous group   I wanted my groups to always start with the same color   the first row of each group should always be white  and the next row colored   The basic concept was to reset the toggle when each group starts  so I added a bit of code   Private bOddRow As Boolean                                                                              -- Display green-bar type color banding in detail rows   -- Call from BackGroundColor property of all detail row textboxes   -- Set Toggle True for first item  False for others                                                                             Function AlternateColor ByVal OddColor As String             ByVal EvenColor As String  ByVal Toggle As Boolean  As String     If Toggle Then bOddRow   Not bOddRow     If bOddRow Then         Return OddColor     Else         Return EvenColor     End If End Function   Function RestartColor ByVal OddColor As String  As String     bOddRow   True     Return OddColor End Function   So I have three different kinds of cell backgrounds now    First column of data row has  Code AlternateColor  AliceBlue    White   True     This is the same as the previous answer   Remaining columns of data row have  Code AlternateColor  AliceBlue    White   False    This  also  is the same as the previous answer   First column of grouping row has  Code RestartColor  AliceBlue     This is new   Remaining columns of grouping row have  Code AlternateColor  AliceBlue    White   False    This was used before  but no mention of it for grouping row     This works for me   If you want the grouping row to be non-colored  or a different color  it should be fairly obvious from this how to change it around   Please feel free to add comments about what could be done to improve this code  I m brand new to both SSRS and VB  so I strongly suspect that there s plenty of room for improvement  but the basic idea seems sound  and it was useful for me  so I wanted to throw it out here

User · Answer

When using row and column groups both  I had an issue where the colors would alternate between the columns even though it was the same row   I resolved this by using a global variable that alternates only when the row changes   Public Dim BGColor As String     ffffff   Function AlternateColor   As String   If BGColor     cccccc  Then     BGColor     ffffff      Return   cccccc    Else     BGColor     cccccc      Return   ffffff    End  If End Function   Now  in the first column of the row you want to alternate  set the color expression to       Code AlternateColor     -  In the remaining columns  set them all to       Code BGColor   This should make the colors alternate only after the first column is drawn   This may  unverifiably  improve performance  too  since it does not need to do a math computation for each column

User · Answer

Using IIF RowNumber     can lead to some issues when rows are being grouped and another alternative is to use a simple VBScript function to determine the color   It s a little more effort but when the basic solution does not suffice  it s a nice alternative   Basically  you add code to the Report as follows     Private bOddRow As Boolean                                                                              -- Display green-bar type color banding in detail rows   -- Call from BackGroundColor property of all detail row textboxes   -- Set Toggle True for first item  False for others                                                                             Function AlternateColor ByVal OddColor As String             ByVal EvenColor As String  ByVal Toggle As Boolean  As String     If Toggle Then bOddRow   Not bOddRow     If bOddRow Then         Return OddColor     Else         Return EvenColor     End If End Function   Then on each cell  set the BackgroundColor as follows    Code AlternateColor  AliceBlue    White   True    Full details are on this Wrox article

User · Answer

I think this trick is not discussed here  So here it is   In any type of complex matrix  when you want alternate cell colors  either row wise or column wise  the working solution is   If you want a alternate color of cells coloumn wise then    At the bottom right corner of a report design view  in  Column Groups   create a fake parent group on 1  using expression   named  FakeParentGroup   Then  in the report design  for cells that to be colored alternatively  use following background color expression       IIF RunningValue  Fields  ColumnGroupField  Value  countDistinct   FakeParentGroup    MOD 2   White    LightGrey     Thats all   Same for the alternate color row wise  just you have to edit solution accordingly   NOTE  Here  sometimes you need to set border of cells accordingly  usually it vanishes   Also dont forget to delete value 1 in report that came into pic when you created fake parent group

User · Answer

Go to the table row s BackgroundColor property and choose  Expression      Use this expression      IIf RowNumber Nothing  Mod 2   0   Silver    Transparent     This trick can be applied to many areas of the report   And in  NET 3 5  You could use     If RowNumber Nothing  Mod 2   0   Silver    Transparent     Not looking for rep--I just researched this question myself and thought I d share

User · Answer

The only effective way to solve this without using VB is to  store  the row grouping modulo value within the row grouping  and outside the column grouping  and reference it explicitly within your column grouping  I found this solution at    http   ankeet1 blogspot com 2009 02 alternating-row-background-color-for html   But Ankeet doesn t the best job of explaining what s happening  and his solution recommends the unnecessary step of creating a grouping on a constant value  so here s my step-by-step process for a matrix with a single row group RowGroup1    Create a new column within the RowGroup1  Rename the textbox for this to something like RowGroupColor  Set the Value of RowGroupColor s textbox to    iif RunningValue Fields  RowGroupField  Value  CountDistinct Nothing  Mod 2   LightSteelBlue    White   Set the BackgroundColor property of all your row cells to    ReportItems RowGroupColor Value  Set the width of the the RowGroupColor column to 0pt and set CanGrow to false to hide it from clients    Voila  This also solves a lot of the problems mentioned in this thread    Automatic resets for subgroups  Just add  a new column for that rowgroup  performing a  RunningValue on its group values  No need to worry about True False toggles  Colors only held in one place for easy modification  Can be used interchangeably on row or column groups  just set height to 0 instead of width    It would be awesome if SSRS would expose properties besides Value on Textboxes  You could just stuff this sort of calculation in a BackgroundColor property of the row group textboxes and then reference it as ReportItems RowGroup BackgroundColor in all of the other cells   Ahh well  we can dream

User · Answer

Slight modification of other answers from here that worked for me  My group has two values to group on  so I was able to just put them both in the first arg with a   to get it to alternate correctly     Iif   RunningValue  Fields description Value   Fields name Value  CountDistinct  Nothing  Mod 2   0   e6eed5    Transparent

User · Answer

Could someone explain the logic behind turning rest of the fields to false in below code  from above post   One thing I noticed is that neither of the top two methods have any notion of what color the first row should be in a group  the group will just start with the opposite color from the last line of the previous group  I wanted my groups to always start with the same color   the first row of each group should always be white  and the next row colored   The basic concept was to reset the toggle when each group starts  so I added a bit of code   Private bOddRow As Boolean                                                                             -- Display green-bar type color banding in detail rows  -- Call from BackGroundColor property of all detail row textboxes  -- Set Toggle True for first item  False for others                                                                               Function AlternateColor ByVal OddColor As String                      ByVal EvenColor As String  ByVal Toggle As Boolean  As String          If Toggle Then bOddRow   Not bOddRow          If bOddRow Then                  Return OddColor          Else                  Return EvenColor          End If  End Function     Function RestartColor ByVal OddColor As String  As String          bOddRow   True          Return OddColor  End Function   So I have three different kinds of cell backgrounds now    First column of data row has  Code AlternateColor  AliceBlue    White   True   This is the same as the previous answer   Remaining columns of data row have  Code AlternateColor  AliceBlue    White   False   This  also  is the same as the previous answer   First column of grouping row has  Code RestartColor  AliceBlue    This is new   Remaining columns of grouping row have  Code AlternateColor  AliceBlue    White   False   This was used before  but no mention of it for grouping row     This works for me  If you want the grouping row to be non-colored  or a different color  it should be fairly obvious from this how to change it around   Please feel free to add comments about what could be done to improve this code  I m brand new to both SSRS and VB  so I strongly suspect that there s plenty of room for improvement  but the basic idea seems sound  and it was useful for me  so I wanted to throw it out here

User · Answer

for group headers footers    iif RunningValue  group on field  CountDistinct   parent group name    Mod 2  White   AliceBlue     You can also use this to    reset    the row color count within each group  I wanted the first detail row in each sub group to start with White and this solution  when used on the detail row  allowed that to happen     IIF RunningValue Fields  Name  Value  CountDistinct   NameOfPartnetGroup   Mod 2   White    Wheat     See  http   msdn microsoft com en-us library ms159136 v sql 100  aspx

User · Answer

I tried all these solutions on a Grouped Tablix with row spaces and none worked across the entire report  The result was duplicate colored rows and other solutions resulted in alternating columns    Here is the function I wrote that worked for me using a Column Count   Private bOddRow As Boolean Private cellCount as Integer  Function AlternateColorByColumnCount ByVal OddColor As String  ByVal EvenColor As String  ByVal ColCount As Integer  As String  if cellCount   ColCount Then  bOddRow   Not bOddRow cellCount   0 End if   cellCount    cellCount    1  if bOddRow Then  Return OddColor Else  Return EvenColor End If  End Function   For a 7 Column Tablix I use this expression for Row  of Cells  Backcolour    Code AlternateColorByColumnCount  LightGrey   White   7

User · Answer

I got the chess effect when I used Catch22 s solution  I think because my matrix has more than one column in design  that expression worked fine for me     iif RunningValue Fields  rowgroupfield  Value ToString CountDistinct Nothing  Mod 2  Gainsboro    White

User · Answer

Aditya s answer is great  but there are instances where formatting will be thrown off if the very first cell of the row  for row background formatting  has a missing value  in complex tablixes with column rows groups and missing values     Aditya s solution cleverly leverages countDistinct result of runningValue function to identify row numbers within a tablix  row  group  If you have tablix rows with missing value in the first cell  runningValue will not increment countDistinct result and it will return the previous row s number  and  therefore  will affect the formatting of that cell   To account for that  you will have to add an additional term to offset the countDistinct value  My take was to check the first running value in the row group itself  see line 3 of the snippet below     iif       RunningValue Fields  RowGroupField  Value  countDistinct   FakeOrRealImmediateParentGroup         iif IsNothing RunningValue Fields  RowGroupField  Value  First   GroupForRowGroupField     1  0        mod 2   White    LightGrey     Hope this helps

User · Answer

I have changed  Catch22 s solution A bit as I do not like the idea of having to go into each field if I decide I want to change one of the colors   This is especially important in reports where the are numerous fields that would need to have the color variable changed                                                                                -- Display alternate color banding  defined below  in detail rows   -- Call from BackgroundColor property of all detail row textboxes                                                                            Function AlternateColor Byval rowNumber as integer  As String     Dim OddColor As String    Green      Dim EvenColor As String    White       If rowNumber mod 2   0 then          Return EvenColor     Else         Return OddColor     End If End Function   Noticed that I have change the function from one that accepts the colors to one that contains the colors to be used   Then in each field add    Code AlternateColor rownumber nothing     This is much more robust than manually changing the color in each fields  background color

User · Answer

If for the entire report you need an alternating color  you can use the DataSet your Tablix is bound to for a report-wide identity rownumber on the report and use that in the RowNumber function      IIf RowNumber  DataSet1    Mod 2   1   White   Blue

[sql-server] Add alternating row color to SQL Server Reporting services report

Examples related to sql-server

Examples related to reporting-services

Examples related to formatting