<p>How do you shade alternating rows in a SQL Server Reporting Services report?</p>

<hr>

<p><strong>Edit:</strong> There are a bunch of good answers listed below--from <a href="https://stackoverflow.com/questions/44376/add-alternating-row-color-to-sql-server-reporting-services-report#44378">quick</a> and <a href="https://stackoverflow.com/questions/44376/add-alternating-row-color-to-sql-server-reporting-services-report#345935">simple</a> to <a href="https://stackoverflow.com/questions/44376/add-alternating-row-color-to-sql-server-reporting-services-report#83832">complex and comprehensive</a>. Alas, I can choose only one...</p>

[{'answer_id': 44378, 'author': 'Michael Haren', 'author_id': 29, 'author_profile': 'https://Stackoverflow.com/users/29', 'pm_score': 9, 'selected': True, 'text': '<p>Go to the table row\'s BackgroundColor property and choose "Expression..."</p>\n\n<p>Use this expression: </p>\n\n<pre><code>= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")\n</code></pre>\n\n<p>This trick can be applied to many areas of the report.</p>\n\n<p>And in .NET 3.5+ You could use:</p>\n\n<pre><code>= If(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")\n</code></pre>\n\n<p>Not looking for rep--I just researched this question myself and thought I\'d share.</p>\n'}, {'answer_id': 83832, 'author': 'Catch22', 'author_id': 15428, 'author_profile': 'https://Stackoverflow.com/users/15428', 'pm_score': 7, 'selected': False, 'text': '<p>Using <code>IIF(RowNumber...)</code> can lead to some issues when rows are being grouped and another alternative is to use a simple VBScript function to determine the color.</p>\n<p>It\'s a little more effort but when the basic solution does not suffice, it\'s a nice alternative.</p>\n<p>Basically, you add code to the Report as follows:</p>\n<pre class="lang-vb prettyprint-override"><code>Private bOddRow As Boolean\n\'*************************************************************************\n\' -- Display green-bar type color banding in detail rows\n\' -- Call from BackGroundColor property of all detail row textboxes\n\' -- Set Toggle True for first item, False for others.\n\'*************************************************************************\nFunction AlternateColor(ByVal OddColor As String, _\n         ByVal EvenColor As String, ByVal Toggle As Boolean) As String\n    If Toggle Then bOddRow = Not bOddRow\n    If bOddRow Then\n        Return OddColor\n    Else\n        Return EvenColor\n    End If\nEnd Function\n</code></pre>\n<p>Then on each cell, set the BackgroundColor as follows:</p>\n<pre><code>=Code.AlternateColor(&quot;AliceBlue&quot;, &quot;White&quot;, True)\n</code></pre>\n<p><strong>Further Reading</strong>: <a href="https://web.archive.org/web/20200223132623/http://www.wrox.com/WileyCDA/Section/Report-Solution-Patterns-and-Recipes-Greenbar-Reports.id-291857.html" rel="nofollow noreferrer">Report Solution Patterns and Recipes: Greenbar Reports | Wrox</a></p>\n'}, {'answer_id': 345935, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<p>for group headers/footers:</p>\n\n<pre><code>=iif(RunningValue(*group on field*,CountDistinct,"*parent group name*") Mod 2,"White","AliceBlue")\n</code></pre>\n\n<p>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: </p>\n\n<pre><code>=IIF(RunningValue(Fields![Name].Value, CountDistinct, "NameOfPartnetGroup") Mod 2, "White", "Wheat")\n</code></pre>\n\n<p>See: <a href="http://msdn.microsoft.com/en-us/library/ms159136(v=sql.100).aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms159136(v=sql.100).aspx</a></p>\n'}, {'answer_id': 1224661, 'author': 'Beska', 'author_id': 57120, 'author_profile': 'https://Stackoverflow.com/users/57120', 'pm_score': 4, 'selected': False, 'text': '<p>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.</p>\n\n<p>The basic concept was to reset the toggle when each group starts, so I added a bit of code:</p>\n\n<pre><code>Private bOddRow As Boolean\n\'*************************************************************************\n\' -- Display green-bar type color banding in detail rows\n\' -- Call from BackGroundColor property of all detail row textboxes\n\' -- Set Toggle True for first item, False for others.\n\'*************************************************************************\nFunction AlternateColor(ByVal OddColor As String, _\n         ByVal EvenColor As String, ByVal Toggle As Boolean) As String\n    If Toggle Then bOddRow = Not bOddRow\n    If bOddRow Then\n        Return OddColor\n    Else\n        Return EvenColor\n    End If\nEnd Function\n\'\nFunction RestartColor(ByVal OddColor As String) As String\n    bOddRow = True\n    Return OddColor\nEnd Function\n</code></pre>\n\n<p>So I have three different kinds of cell backgrounds now:</p>\n\n<ol>\n<li>First column of data row has =Code.AlternateColor("AliceBlue", "White", True)   (This is the same as the previous answer.)</li>\n<li>Remaining columns of data row have =Code.AlternateColor("AliceBlue", "White", False)  (This, also, is the same as the previous answer.)</li>\n<li>First column of grouping row has =Code.RestartColor("AliceBlue")  (This is new.)</li>\n<li>Remaining columns of grouping row have =Code.AlternateColor("AliceBlue", "White", False)  (This was used before, but no mention of it for grouping row.)</li>\n</ol>\n\n<p>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.</p>\n\n<p>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.</p>\n'}, {'answer_id': 1478089, 'author': 'Sarah Vessels', 'author_id': 38743, 'author_profile': 'https://Stackoverflow.com/users/38743', 'pm_score': 3, 'selected': False, 'text': '<p>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 <em>all</em> 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 <code>RowNumber</code> and <code>bOddRow</code> (of Catch22\'s solution) pay attention to my column group instead of ignoring that and only alternating with a new row.</p>\n\n<p>What I wanted is for all the <em>columns</em> 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 <code>Nothing</code> to <code>RowNumber</code>, I passed the name of my column group, e.g.</p>\n\n<pre><code>=IIf(RowNumber("MyColumnGroupName") Mod 2 = 0, "AliceBlue", "Transparent")\n</code></pre>\n\n<p>Thought this might be useful to someone else.</p>\n'}, {'answer_id': 3425830, 'author': 'Matt', 'author_id': 413281, 'author_profile': 'https://Stackoverflow.com/users/413281', 'pm_score': 2, 'selected': False, 'text': '<p>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...</p>\n\n<pre><code>=IIf(RowNumber("DataSet1")  Mod 2 = 1, "White","Blue")\n</code></pre>\n'}, {'answer_id': 5563007, 'author': 'nonetaku', 'author_id': 528957, 'author_profile': 'https://Stackoverflow.com/users/528957', 'pm_score': 3, 'selected': False, 'text': '<p>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\n<a href="http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c9d8bd59-311c-4d8d-be71-1309296f7334/">Setting BackgroundColor of Report elements in SSRS</a>. Use Nothing instead of "Transparent"</p>\n\n<pre><code>= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", Nothing)\n</code></pre>\n'}, {'answer_id': 6777983, 'author': 'ahmad', 'author_id': 856187, 'author_profile': 'https://Stackoverflow.com/users/856187', 'pm_score': 6, 'selected': False, 'text': '<p>I got the chess effect when I used Catch22\'s solution, I think because my matrix has more than one column in design.\nthat expression worked fine for me :</p>\n\n<pre><code>=iif(RunningValue(Fields![rowgroupfield].Value.ToString,CountDistinct,Nothing) Mod 2,"Gainsboro", "White")\n</code></pre>\n'}, {'answer_id': 6941946, 'author': 'Michael Eakins', 'author_id': 437301, 'author_profile': 'https://Stackoverflow.com/users/437301', 'pm_score': 4, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>\'*************************************************************************\n\' -- Display alternate color banding (defined below) in detail rows\n\' -- Call from BackgroundColor property of all detail row textboxes\n\'*************************************************************************\nFunction AlternateColor(Byval rowNumber as integer) As String\n    Dim OddColor As String = "Green"\n    Dim EvenColor As String = "White"\n\n    If rowNumber mod 2 = 0 then \n        Return EvenColor\n    Else\n        Return OddColor\n    End If\nEnd Function\n</code></pre>\n\n<p>Noticed that I have change the function from one that accepts the colors to one that contains the colors to be used.</p>\n\n<p>Then in each field add:</p>\n\n<pre><code>=Code.AlternateColor(rownumber(nothing))\n</code></pre>\n\n<p>This is much more robust than manually changing the color in each fields\' background color.</p>\n'}, {'answer_id': 9510135, 'author': 'misha', 'author_id': 1241734, 'author_profile': 'https://Stackoverflow.com/users/1241734', 'pm_score': 1, 'selected': False, 'text': '<p>Could someone explain the logic behind turning rest of the fields to false in below code (from above post)</p>\n\n<p>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.</p>\n\n<p>The basic concept was to reset the toggle when each group starts, so I added a bit of code:</p>\n\n<pre><code>Private bOddRow As Boolean\n\'*************************************************************************\n\'-- Display green-bar type color banding in detail rows\n\'-- Call from BackGroundColor property of all detail row textboxes\n\'-- Set Toggle True for first item, False for others.\n\'*************************************************************************\n\'\nFunction AlternateColor(ByVal OddColor As String, _\n                  ByVal EvenColor As String, ByVal Toggle As Boolean) As String\n         If Toggle Then bOddRow = Not bOddRow\n         If bOddRow Then \n                Return OddColor\n         Else\n                 Return EvenColor\n         End If\n End Function\n \'\n Function RestartColor(ByVal OddColor As String) As String\n         bOddRow = True\n         Return OddColor\n End Function\n</code></pre>\n\n<p>So I have three different kinds of cell backgrounds now:</p>\n\n<ol>\n<li>First column of data row has =Code.AlternateColor("AliceBlue", "White", True) (This is the same as the previous answer.)</li>\n<li>Remaining columns of data row have =Code.AlternateColor("AliceBlue", "White", False) (This, also, is the same as the previous answer.)</li>\n<li>First column of grouping row has =Code.RestartColor("AliceBlue") (This is new.)</li>\n<li>Remaining columns of grouping row have =Code.AlternateColor("AliceBlue", "White", False) (This was used before, but no mention of it for grouping row.)</li>\n</ol>\n\n<p>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.</p>\n\n<p>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.</p>\n'}, {'answer_id': 13692016, 'author': 'Kyle Hale', 'author_id': 32458, 'author_profile': 'https://Stackoverflow.com/users/32458', 'pm_score': 3, 'selected': False, 'text': '<p>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  </p>\n\n<p><a href="http://ankeet1.blogspot.com/2009/02/alternating-row-background-color-for.html" rel="noreferrer">http://ankeet1.blogspot.com/2009/02/alternating-row-background-color-for.html</a> </p>\n\n<p>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:</p>\n\n<ol>\n<li>Create a new column within the RowGroup1. Rename the textbox for this to something like RowGroupColor.</li>\n<li><p>Set the Value of RowGroupColor\'s textbox to </p>\n\n<p><code>=iif(RunningValue(Fields!<strong>[RowGroupField]</strong>.Value\n,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White")</code></p></li>\n<li><p>Set the BackgroundColor property of all your row cells to</p>\n\n<p><code>"=ReportItems!RowGroupColor.Value"</code></p></li>\n<li>Set the width of the the RowGroupColor column to 0pt and set CanGrow\nto false to hide it from clients.</li>\n</ol>\n\n<p>Voila! This also solves a lot of the problems mentioned in this thread:</p>\n\n<ul>\n<li>Automatic resets for subgroups: Just add  a new column for that\nrowgroup, performing a  <em>RunningValue</em> on <strong>its</strong> group values.</li>\n<li>No need to worry about True/False toggles.</li>\n<li>Colors only held in one place for easy modification.</li>\n<li>Can be used interchangeably on row or column groups (just set height to 0 instead of width)</li>\n</ul>\n\n<p>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.</p>\n\n<p>Ahh well, we can dream ...</p>\n'}, {'answer_id': 20255792, 'author': 'CodeRedick', 'author_id': 17145, 'author_profile': 'https://Stackoverflow.com/users/17145', 'pm_score': 0, 'selected': False, 'text': '<p>Just because none of the answers above seemed to work in my matrix, I\'m posting this here:</p>\n\n<p><a href="http://reportingservicestnt.blogspot.com/2011/09/alternate-colors-in-matrixpivot-table.html" rel="nofollow">http://reportingservicestnt.blogspot.com/2011/09/alternate-colors-in-matrixpivot-table.html</a></p>\n'}, {'answer_id': 20336718, 'author': 'FistOfFury', 'author_id': 606725, 'author_profile': 'https://Stackoverflow.com/users/606725', 'pm_score': 0, 'selected': False, 'text': '<p>My matrix data had missing values in it, so I wasn\'t able to get ahmad\'s solution to work, but <a href="http://www.sqlgirl.com/blog/2012/07/02/alternating-row-background-color-for-ssrs-matrix-with-1-row-group/" rel="nofollow">this solution worked for me</a></p>\n\n<p>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.  </p>\n'}, {'answer_id': 23693973, 'author': 'Aditya', 'author_id': 2819400, 'author_profile': 'https://Stackoverflow.com/users/2819400', 'pm_score': 2, 'selected': False, 'text': '<h2>I think this trick is not discussed here. So here it is,</h2>\n\n<p>In any type of complex matrix, when you want alternate cell colors, either row wise or column wise,\nthe working solution is,</p>\n\n<p><strong>If you want a alternate color of cells coloumn wise then,</strong></p>\n\n<ol>\n<li>At the bottom right corner of a report design view, in "Column\nGroups", create a fake parent group on 1 (using expression), named\n"FakeParentGroup".</li>\n<li>Then, in the report design, for cells that to be colored\nalternatively, use following background color expression</li>\n</ol>\n\n<blockquote>\n  <p>=IIF(RunningValue( Fields![ColumnGroupField].Value, countDistinct, "FakeParentGroup" ) MOD 2, "White", "LightGrey")</p>\n</blockquote>\n\n<p>Thats all.</p>\n\n<p>Same for the alternate color row wise, just you have to edit solution accordingly.</p>\n\n<p>NOTE: Here, sometimes you need to set border of cells accordingly, usually it vanishes.</p>\n\n<p>Also dont forget to delete value 1 in report that came into pic when you created fake parent group.</p>\n'}, {'answer_id': 23768811, 'author': 'rpyzh', 'author_id': 2664868, 'author_profile': 'https://Stackoverflow.com/users/2664868', 'pm_score': 2, 'selected': False, 'text': '<p>@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).</p>\n\n<p>@Aditya\'s solution cleverly leverages <code>countDistinct</code> result of <code>runningValue</code> function to identify row numbers within a tablix (row) group. If you have tablix rows with missing value in the first cell, <code>runningValue</code> will not increment <code>countDistinct</code> 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 <code>countDistinct</code> value. My take was to check the first running value in the row group itself (see line 3 of the snippet below):</p>\n\n<pre><code>=iif(\n    (RunningValue(Fields![RowGroupField].Value, countDistinct, "FakeOrRealImmediateParentGroup")\n    + iif(IsNothing(RunningValue(Fields![RowGroupField].Value, First, "GroupForRowGroupField")), 1, 0)\n    ) mod 2, "White", "LightGrey")\n</code></pre>\n\n<p>Hope this helps.</p>\n'}, {'answer_id': 27914710, 'author': 'Jeremy Thompson', 'author_id': 495455, 'author_profile': 'https://Stackoverflow.com/users/495455', 'pm_score': 1, 'selected': False, 'text': '<p>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! </p>\n\n<p>Here is the function I wrote that worked for me using a Column Count:</p>\n\n<pre><code>Private bOddRow As Boolean\nPrivate cellCount as Integer\n\nFunction AlternateColorByColumnCount(ByVal OddColor As String, ByVal EvenColor As String, ByVal ColCount As Integer) As String\n\nif cellCount = ColCount Then \nbOddRow = Not bOddRow\ncellCount = 0\nEnd if \n\ncellCount  = cellCount  + 1\n\nif bOddRow Then\n Return OddColor\nElse\n Return EvenColor\nEnd If\n\nEnd Function\n</code></pre>\n\n<p>For a 7 Column Tablix I use this expression for Row (of Cells) Backcolour:</p>\n\n<pre><code>=Code.AlternateColorByColumnCount("LightGrey","White", 7)\n</code></pre>\n'}, {'answer_id': 38316880, 'author': 'Soy Sauce Johnson', 'author_id': 4342077, 'author_profile': 'https://Stackoverflow.com/users/4342077', 'pm_score': 0, 'selected': False, 'text': '<p>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 </p>\n\n<pre><code>= Iif ( RunningValue (Fields!description.Value + Fields!name.Value, CountDistinct, Nothing) Mod 2 = 0,"#e6eed5", "Transparent")\n</code></pre>\n'}, {'answer_id': 40557573, 'author': 'Eneerge', 'author_id': 5003522, 'author_profile': 'https://Stackoverflow.com/users/5003522', 'pm_score': 0, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>Public Dim BGColor As String = "#ffffff"\n\nFunction AlternateColor() As String\n  If BGColor = "#cccccc" Then\n    BGColor = "#ffffff"\n    Return "#cccccc"\n  Else\n    BGColor = "#cccccc"\n    Return "#ffffff"\n  End  If\nEnd Function\n</code></pre>\n\n<p>Now, in the <strong>first</strong> column of the row you want to alternate, set the color expression to:</p>\n\n<blockquote>\n  <p>=Code.AlternateColor()</p>\n</blockquote>\n\n<p>-</p>\n\n<p>In the remaining columns, set them all to:</p>\n\n<blockquote>\n  <p>=Code.BGColor</p>\n</blockquote>\n\n<p>This should make the colors alternate only after the first column is drawn.</p>\n\n<p>This may (unverifiably) improve performance, too, since it does not need to do a math computation for each column.</p>\n'}]
['https://Stackoverflow.com/questions/44376', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/29/']