In my last post, I showed a dashboard in SSRS 2005 that included sparklines, inline bar charts and bullet charts. As I mentioned there is good MSDN article on how to do inline charts but the Bullet chart took a little more thought.
As one of my colleagues has pointed out, these aren't bullet charts in the truest sense that Stephen Few designed them as they don't show the "bullet's path" running through them, however I think they serve the same purpose albeit in a simplistic way
The charts are made using 100% stacked bars with three data values for each "zone" of your chart:
- The section below your marker;
- The marker itself (which of course only shows if it falls in that zone); and
- The section above your marker.
So in the Speciality Bike Shop row in the example above, the three values that add up to the first zone of 25 are 11, 1 and 13 with the colour set as Dark Grey, Black and Dark Grey respectively. Whereas as Value Added Reseller is made up of three zones with values 0, 0 and 25
Likewise, the 2nd zone for Speciality Bike Shop is made up of 0, 0 and 64 whereas VAR is 42, 1 and 22 this time with light grey, black, light grey
So how do we come with the numbers? It's just a case of calculating the size of the zones as a percentage of the whole and deciding whether the marker should show. Lets follow the MDX query through to explain
First lets define our key measures: the zone limits, and the actual value for our marker. I'm using constants for the zone sizes but you could easily have this coming from your data:
WITH
//replace this constant with your Zone 1 measure
MEMBER [Measures].[Zone1] AS
300000
//replace this constant with your Upper Zone 2 measure
MEMBER [Measures].[Zone2] AS
1067000
//replace this constant with your Upper Zone 3 measure
MEMBER [Measures].[Zone3] AS
1200000
//Actual measure
MEMBER [Measures].[Actual] AS
[Measures].[Reseller Sales Amount]
Next we need to calculate which zone the marker will fall in - we set a boolean for each which is also used as the marker for the chart.
//Are we in the zone 1
MEMBER [Measures].[ShowZone1Marker] AS
IIF(
[Measures].[Actual] < [Measures].[Zone1],
1,
0
)
//Are we in Zone 2
MEMBER [Measures].[ShowZone2Marker] AS
IIF(
([Measures].[Actual] >= [Measures].[Zone1]
AND
[Measures].[Actual] < [Measures].[Zone2]),
1,
0
)
//Are we in Zone 3
MEMBER [Measures].[ShowZone3Marker] AS
IIF(
[Measures].[Actual] >= [Measures].[Zone2],
1,
0
)
Then we need to calculate the size of the lower and upper parts of each zone, lower first:
MEMBER [Measures].[Zone1a] AS
(([Measures].[Actual]) * [Measures].[ShowZone1Marker]
/
[Measures].[Zone3]
*
100)
-
([Measures].[ShowZone1Marker] * [Measures].[ShowZone1Marker])
the logic is:
The actual value subtract the previous zone limit (not required in zone 1), multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart. The bottom line removes one from the value to allow for the marker.
The upper part of zone 1:
MEMBER [Measures].[Zone1b] AS
([Measures].[Zone1] - ([Measures].[Actual] * [Measures].[ShowZone1Marker]))
/
[Measures].[Zone3]
*
100
this time the logic is:
the zone size subtract the actual value multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart.
Repeat the same for subsequent zones and then bring it all together in a select statement on your cube
Just in case I've explained all this as badly as I think I have, you can download the sample project and have a look yourselves (you'll need to sign in/up).
And the scale - errr OK in case you hadn't already guessed, it's an image on the column header - hey what more do you want?the moon on a stick? ;) You can size the chart to fit the scale or create your own image. The image is also included in the sample project.
All criticism/suggestion for improvement welcome.
Here at Adatis we're big fans of Reporting Services. Yep, it has it's quirks and frustrations (don't talk to me about excel export!) but doesn't deserve all the criticism it gets in our opinion. You can create some great looking reports with not a lot of extra effort over the defaults (though how many times have you seen a "slate" style report called "Report1" go into production!)
We're also fans of what some might call the more "trendy" visualisation techniques such as sparklines and bullet charts and with Microsoft acquiring Dundas technology for use in SQL 2008, SSRS has some much improved functionality in this area. I'll be taking a look at some of these new features in a future post.
Whilst SSRS 2005 does have it's limitations, with a little effort you can still create some very effective information visualisations. The dashboard below is built using completely standard functionality
Adding sparklines and in-table bar charts is very simple (and pretty clever IMO). This MSDN Article covers how to do it in detail but in essence you simply add a line or bar chart into a cell in your data table.
Whilst the bullet chart (in the Sales Channel Performance region of the dashboard) may be simplistic compared to those available in SSRS 2008 or MicroCharts from XLCubed (from whom I nicked the styling for our dashboard) it still provides a very clear visualisation of the data. This required a little more trickery in the underlying query but is also just a chart in a table cell - this time a 100% stacked bar. I've explained how this works here