Tackling Matrix Report Conditional Formats in SSRS

During an engagement with a customer of mine, I discovered a solution to a challenge that I found to be more easy than a lot of solutions that I have seen posted in various forums. This concerns the challenge of conditional formatting in SQL Server 2005 Reporting Services particularly when it comes to matrix reports. In almost every case, the recommendations that I've seen involved very cumbersome InScope functions. The solution that I am presenting is by no means perfect but it definitely solved my immediate problem. Until Microsoft updates this short-coming in (hopefully) in SQL Server 2008, this blog shows what I did.

My customer had a desire of setting up a scorecard at a summary level to measure the timeliness of when its engineers were providing their rolling forecasts. Since this was at a summary report, larger programs that required more resources obviously have a larger coefficient. If the program managers are late summitting their data, then the entire product line gets dinged. However rather than use the InScope function to address my problem, SSRS provide custom formats with expressions within the indivdual property as shown below.

My suggestion is that you write your code in notepad since the "custom" feature does not bring up its own expressions window.



=IIF(=FormatPercent(sum(Fields!HC.Value)/Sum(Fields!HC.Value, "matrix1_Product_Line")>.85,"Green", IIF((=FormatPercent(sum(Fields!HC.Value)/Sum(Fields!HC.Value, "matrix1_Product_Line"),etc., etc. etc.)

In the end, it provided me with a much more forgiving solution.

Hope it helps and happy solving!