Inconsistent Background Colors in SSRS 2008

I ran across this issue a while back and figured I’d blog about my findings. After running my issue through Google, it turns out that I wasn’t the only one with this same problem.

In SSRS, you can use the following code expression to set alternating background colors on your rows as part of the Background Color property, making the data easier to read:

=IIF(RowNumber(Nothing) MOD 2,"WhiteSmoke","White"
The only problem with this code is this: If your matrix has grouping on the dataset, then there is the possibility that your background colors can be inconsistent. Look at the image below to see an explanation of what happens when you use the above code snippet as your background color:
 
Notice on this example how the first row is White, then the next 2 are WhiteSmoke, the next White, etc. These rows should be presented as shown below (alternating White to WhiteSmoke):
 
 
To accomplish this, I derived the background in SQL Server using a case statement, and then passed that column value as the background image in SSRS.
CASE
    WHEN DENSE_RANK() OVER (ORDER BY LastName) % 2 = 1 then 'WhiteSmoke'
    ELSE 'White'
END as BGColor
I found a great write-up relating to the different ranking functions within SQL Server here; if you’re confused about any of the ranking functions, I would suggest it as a good read. It is written to SQL Server 2005, but is also applicable to SQL Server 2008. The way that I am using the ranking function here, the case statement performs a MOD 2 (% 2) function on the LastName. If the result = 1, then we pick WhiteSmoke as the background color. Else, we choose White.
Now, all that needs to be done is to include the BGColor column as part of your dataset, and then set the Background Color property in the cells that you want to alternate (in my case, I wanted the entire row to alternate colors, so I set BGColor as the Background Color in all of my cells).
Another advantage of this approach is centralized maintenance. Say, for instance, that you have a stored procedure in which you are incorporating this code, and that stored procedure is utilized by 5 different reports. If, for some reason, your user comes back saying that they want the alternating background colors to be different, then you have to physically touch each RDL file to accommodate this change. By determining the background inside SQL Server, you only have to make the change in one place — the stored procedure.
One thing to note: Whatever you use as your order column in your case statement (in my case, I used LastName), you have to ensure that your grouping in SSRS is also set to order by that column. Else, your row colors will once again be out of order.
Hope that this write-up helps someone work around the headache that I spun my wheels working through!
NOTE: If you enable sorting on any of your columnsin SSRS, then this approach will not work. What I have found is that you can use the function that I mentioned above, and all you need to do is to ensure that your data is presented to SSRS sorted by LastName (perform the sort in your dataset query in SSRS, and the also sort your data in the Report).
=IIF(RowNumber(Nothing) MOD 2,"WhiteSmoke","White"