Bullet Chart or Graph

[jguevin]

Anyone know how to create a bullet chart or bullet graph in Report Studio?


thanks
Jeff

[CognosPaul]

I do have a way, but it's not exactly the most elegant solution. Because Cognos is not bundled with it, you have to use a third party generator. In this case, I'm using Google charts.

In order to use the Google charts you need to drag in a singleton, set it to your query, drag an image into it, set the source to report expression and paste in the following:

Code Select Expand

'http://chart.apis.google.com/chart?' +
'chs=100x40' +
'&chd=t:' + number2string ([Query1].[Measure] * (100 / ([Query1].[T Measure] * 1.5))) +
'&cht=bhs&chco=000000' +
'&chxt=x&chxl=0:|0|' + number2string (([Query1].[T Measure] * 1.5)/3) + '|' + number2string (([Query1].[T Measure] * 1.5)/2) + '|' + number2string ([Query1].[T Measure] * 1.5) +
'&chm=r,ff0000,0,' + number2string([Query1].[T Measure]/([Query1].[T Measure] * 1.5)-0.01) + ',' + number2string([Query1].[T Measure]/([Query1].[T Measure] * 1.5)+0.01) + ',1' +
'|r,888888,0,0,' + number2string([Query1].[MidValue]/([Query1].[T Measure] * 1.5)) +
'|r,AAAAAA,0,' + number2string([Query1].[MidValue]/([Query1].[T Measure] * 1.5)) + ',' + number2string([Query1].[HighValue]/([Query1].[T Measure] * 1.5)) +
'|r,CCCCCC,0,' + number2string([Query1].[HighValue]/([Query1].[T Measure] * 1.5)) + ',1' +
'&chbh=10' +
'&chxs=0,000000,9' +
'&chf=bg,s,fafafa'

We'll say, for the sake of this example that the following are true:

Code Select Expand

Measure    = 62
Target     = 70
MidValue   = 30
High Value = 60

Which would resolve to:

Code Select Expand

'http://chart.apis.google.com/chart?' +
'chs=100x40' +
'&chd=t:' + number2string (62 * (100 / (70 * 1.5))) +
'&cht=bhs&chco=000000' +
'&chxt=x&chxl=0:|0|' + number2string ((70 * 1.5)/3) + '|' + number2string ((70 * 1.5)/2) + '|' + number2string (70 * 1.5) +
'&chm=r,ff0000,0,' + number2string(70/(70 * 1.5)-0.01) + ',' + number2string(70/(70 * 1.5)+0.01) + ',1' +
'|r,888888,0,0,' + number2string(30/(70 * 1.5)) +
'|r,AAAAAA,0,' + number2string(30/(70 * 1.5)) + ',' + number2string(60/(70 * 1.5)) +
'|r,CCCCCC,0,' + number2string(60/(70 * 1.5)) + ',1' +
'&chbh=10' +
'&chxs=0,000000,9' +
'&chf=bg,s,fafafa'

Which would ultimately resolve to:

Code Select Expand

http://chart.apis.google.com/chart?chs=100x40&chd=t:59.047619&cht=bhs&chco=000000&chxt=x&chxl=0:|0|35|52.5|105&chm=r,ff0000,0,0.656666667,0.676666667,1|r,888888,0,0,0.285714286|r,AAAAAA,0,0.285714286,0.571428571|r,CCCCCC,0,0.571428571,1&chbh=10&chxs=0,000000,9&chf=bg,s,fafafa
or

Instead of explaining each item, I'll simply link to the wonderful explanation that I pulled this from:

http://dealerdiagnostics.com/blog/2008/05/create-bullet-graphs-with-google-charts-in-7-easy-steps/

What that site doesn't explain well is that the measure has to be from 0-100, and the scale has to be from 0-1.

The maximum value in this chart will always be the target*1.5, that puts the target comfortably at 66%. This gives plenty of room to show the measure exceeding the target if necessary. The measure is being compared against the target. 62 * (100 / (70 * 1.5)) = 59.04 putting it just before the target.

The chxl axis labels are problematic. Google generates them based on their position, not based on the low-mid-high values. If there were three, Google would put them at the 0%, 50%, and 100% positions. As there are four, Google places them at 0, 33, 66, 100. Thus the second axis label being (70 * 1.5)/3, and the third being (70 * 1.5)/2

The low, mid, high, target bars are also set explicitly.

Code Select Expand

'&chm=r,ff0000,0,' + number2string(70/(70 * 1.5)-0.01) + ',' + number2string(70/(70 * 1.5)+0.01) + ',1' +
'|r,888888,0,0,' + number2string(30/(70 * 1.5)) +
'|r,AAAAAA,0,' + number2string(30/(70 * 1.5)) + ',' + number2string(60/(70 * 1.5)) +
'|r,CCCCCC,0,' + number2string(60/(70 * 1.5)) + ',1' +

Here the target is being defined first. It should be at the position 65% - 67%, the color being FF0000 (html for red). Next the low value is marked. it goes from 0 to the midvalue (30), colored 888888. The next markers are midvalue to high, colored AAAAAA, and high to 1, colored CCCCCCC.

[jbjerre]

You can't make a bullet graph directly in Report Studio. But I know that Cognos (IBM) are working on  it for the next release. I have been in touch with them regarding the same issue.

Regards J.Bjerre