With the release of Office 2007, Excel introduced cool new conditional formatting rules that gave you the ability to show dashboard-esque icons inside cells. With these icons, you can represent performance using different shapes and colors.
As usual, Access gets the short end of the visualization stick. No such functionality exists in your cache of Access tools. But today, I'll show you how to hack your way into showing dashboard-esque icons in Access queries.
Let's go through an exercise to get some trend icons. I'll start with this query that compares Actual performance versus Forecast. As you can see, the Variance column is in the garden-variety percent format. This is fine, but it takes a moment to pick out the markets that have negative variances. It would be nice to make those Pop out more quickly.
At this point, you may be thinking, "I'll just use the old Wingdings to give the negative variances a symbol of some sort".
No dice there. It's an all or nothing formatting situation in Access Queries. So using Wingdings font simply makes your entire query look like the bathroom wall of some ancient Egyptian bar.
One creative alternative is to use the ChrW function. The ChrW function returns Unicode characters based on a character number. For instance, ChrW(9608) returns a block character. These characters allow you to mimic Excel's icon sets, using symbols to create visual representations of performance.
I've listed the codes for Unicode characters resembling the icons you often seen on dashboard reports. Again, the idea is to pass the character number through the ChrW function. For example, ChrW(9650) would return an up arrow symbol.
Now that I know the codes to use, I can implement them in my query. As you can see here, I'm updating my query to include a 'Trend Icon' column. This column will evaluate the Variance column to see if the Variance column is greater than or equal to zero. If it is, it does nothing. If the Variance column is less than zero, the character ChrW(9660) is used.
The effect we get is pretty nifty.
There is a new Trend Icon column that shows a down arrow for each market whose Variance is less than 0. This helps our audience to quickly point out the problem Markets.
I'll be back soon with another blindingly awesome post.
