I have an SSRS report in which I had been using the standard expression for alternating row colors:
=IIF(RowNumber(Nothing) Mod 2 = 0, "LightGrey", "White")
But this particular report shows customers with duplicate SSNs so I added a DENSE_RANK windows function so each SSN would be assigned the same DENSE_RANK value - I am hoping this will allow me to base the row color on the DENSE_RANK instead of a simple alternating row color expression.
For example,
CustomerID : 1, SSN : 123456789, DENSE_RANK : 1
CustomerID : 2, SSN : 123456789, DENSE_RANK : 1
CustomerID : 3, SSN : 987654321, DENSE_RANK : 2
In the report, my goal is to make DENSE_RANK 1 vales set to one color while DENSE_RANK 2 is set to a different color - and obviously this would continue on for as many duplicate SSNs as were in the report. I tried to modify the original expression as follows:
=IIF(RowNumber(Fields!SSNRanking.Value) Mod 2 = 0, "LightGrey", "White")
SSNRanking is the alias I gave the DENSE_RANK function and I am receiving the following error message when i try preview the report:
The Background Color expression for the text box "CustomerID" has a scope parameter that is not valid for an aggregate function. The scope parameter much be set to a string constant that is equal to either the name of the containing group, the name of a containing data region, or the name of a dataset.
Is there a way to build the expression to accomplish my goal?
Thanks,
