Friday, June 11, 2010

SSRS-Backgorund Colors...

I was recently working on creating a SSRS report in SQL Server 2008 which required alternating background colors. Initially it seemed a easy requirement but as the report got built out it had a row group and a column group which made the getting background colors tricky. Initially I went with the RowNumber and Running value functions, it did not get the required effect. I was researching this and found a a discussion on MSDN where in one of the moderators had suggested the USE of DENS_RANK() function in the sql being used to create the DataSet. In my dataset there are multiple records appearing for a single state spanning different months. The Use of the DENSE_RANK() fuction allowed me to persist the Rank across multiple occurences of months for a singlke state. The Row_num column was created as part of the result set and sent it back to the DataSet in SSRS. For example: SELECT a,b,c,DENSE_RANK() OVER (ORDER BY state) AS Row_Num.
In the Report on the row of the tablix control, in the background property I used the expression =iif(Fields!Row_Num.Value Mod 2, "Gainsboro", "White"). This gave me the required alternate color effect on a tablix report with a Row and Column group.

No comments:

Post a Comment