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: BulletCharts.zip (7.42 kb)
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.
Meet the Team – Jason Bonello, Senior Consultant
Meet Jason Bonello! Jason has been with us for just over two years and works
Apr
Meet the Team – Matt How, Principal Consultant
Next up in our series of meet the team blogs is Matt How. Matt has
Apr
MLFlow: Introduction to MLFlow Tracking
MLFlow is an open-source MLOps platform designed by Databricks to enable organisations to easily manage
Apr
Adatis are pleased to announce expansion plans into India
Adatis has offices in London, Surrey and Bulgaria – and has some big expansion plans
Mar
Querying and Importing Data from Excel to SSMS
Introduction There are couple of ways to import data from Excel to SSMS – via
Mar
Data Engineering for Graduates and New Starters
This blog aims to give potential graduates and other new starters in the industry some
Mar
Passing DP-900 Azure Data Fundamentals
Back in December, I took the DP-900 Azure Data Fundamentals exam which is one of
Feb
Real-time Dashboards Provide Transparency to Everyone
Real-time dashboards enable data analytics firm Adatis to be agile and transparent with its team
Feb