It’s the second Tuesday of the month, and you know what time it is! That’s right, another installment of T-SQL Tuesday which is hosted this month by Rick Krueger (blog | twitter). The topic is about that one time we did a hack to get something sorted out, because of time pressure, budget, sheer laziness or whatever the reason was.

My story is not about a hack in its purest definition, but it is about a nice alternative way to get something done more efficiently. I’m talking about Indicators in a SSRS report. For those non-BI folks who just fell off their chair: an indicator is a visual component introduced in Reporting Services 2008R2. They are very tiny gauges that display the state of a single data value at a glance. They come in various shapes and colors and they are pretty useful for KPIs and dashboards.

Once upon a time I had a fairly large matrix report at a client. There were a lot of rows and a lot of columns in that matrix, and the client wanted an indicator in every cell of the matrix. The development went pretty OK using BIDS, although it’s quite a pain to configure a separate indicator for every column. However, when the report was deployed to the server, it became nerve wreckingly slow. This was caused by the sheer amount of indicators, as the query behind the report finished in less than 1 second. Time to look for an alternative.

After some Google fu, I came across this MSDN topic: SSRS 2008 R2 Rendering Performance. In this thread, the alternative of using Wingdings was suggested, instead of using actual indicators. You know Wingdings, the Windows font with all the funny symbols.

I replaced every indicator of an expression of the following type:

=iif((Fields!Result) < 70,”O”,”P”))

The letter O in Wingdings-2 corresponds with a cross, the letter P corresponds with a checkmark. I placed a similar expression on the font color, making the cross red and the checkmark green. The resulting matrix looked like this:

Since the report now just had to evaluate a simple expression and display a simple text for each cell instead of a single chart for each cell, the report performance went through the roof.

In conclusion: using something “goofy” can sometimes lead to extraordinary results.