Many many people have complained about the standard Cognos charts. And while there have been numerous improvements, they still lack some features many people need for their reports. Fortunately you have several third party chart packs.
Fusion Charts has a number of incredibly useful charts and graphs. Over the course of the next few articles I’ll go through various fusion charts and how to embed them into your reports. Because I’m only a poor report developer, I don’t have access to the fancy schmancy for-pay version (at least not at the client I’m currently working for), so the following examples will all be on the free version. The examples will work with the full version however.
To start, you will need a few things:
Cognos
Fusion Charts Free (which you can download here)
There are a number of ways you can populate a Fusion Chart. My preferred way is to use the dataXML method. Other methods involve pulling the data from an external XML sheet via Ajax, or populating it with JavaScript.
To start, we’ll make a simple query based on the Great Outdoors Company cube. 
Now we’ll start with the chart. The chart must be entirely in HTML. The price for having charts exactly the way you want them is buy working a bit harder on them.
Drag an HTML item onto the page. Type First into the description.
Leave the source type as text, and paste the following in it:
<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="600" height="400" id="Column3D" >
<param name="movie" value="../samples/images/CPM/FusionChartsFree/FCF_Column3D.swf" />
<param name="FlashVars" value="&chartWidth=600&chartHeight=400&dataXML=<graph caption='Quarterly Revenue (K$)' xAxisName='Quarters' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'>
Note, I placed the Fusion Chart files under samplesimagesCPMFusionChartsFree. You’ll probably want to change the url to match wherever you’ve placed them.
Next drag in a repeater. Make sure it is a repeater, and not a repeater table. A repeater table will create an HTML table and royally mess up the graph. A repeater on the other hand does not add any extra HTML tags. Attach it to the query made previously, and put both data items (Quarter and Revenue) into the properties of the repeater. Drag 5 HTML items into the repeater:
- Source Text: <set name=’
- Source Data Item Value: Quarter
- Source Text: ‘ value=’
- Source Report Expression: number2string([Query1].[Revenue]/1000)
- Source Text: ‘ />
Finally, drag another HTML item after the repeater with the text:
</graph>">
<param name="quality" value="high" />
</object>
In the fourth HTML item we’re using a report expression to convert the value from numeric to string, after dividing it by 1000. This is because the free version of Fusion Charts does not accept values with thousand separated values. The value is divided by 1000 because Cognos doesn’t simply convert the value to text, it puts it in exponential format. Any value over 999,999 will appear like: 1e+006
If you’ve followed the instructions your page should look something like:
And finally, when you run the report you should get:
Beautiful.
Now that it works, try playing around with it. Add a prompt to the page that slices query. Try changing the chart from a 3D column to a line. See what happens when you too many fields for the repeater to show on one page.
In the next article I’ll describe how to use the link property to mimic drillthrough functionality.
Report XML below. The schema is Cognos 8.2, but the method works well on all Cognos versions. Simply modify the query as needed.
<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="he" interactivePageBreakByFrame="true"><!--RS:8.2-->
<modelPath>/content/package[@name='Great Outdoors Company']/model[@name='model']</modelPath>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<selectValue parameter="Product" refQuery="Products" multiSelect="false" range="false" required="false" autoSubmit="true"><useItem refDataItem="Product line"/></selectValue>
<table class="tb"><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Quarters By Revenue</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><HTMLItem description="First">
<dataSource>
<staticValue> <OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="600" height="400" id="Column3D" >
<param name="movie" value="../samples/images/CPM/FusionChartsFree/FCF_Column3D.swf" />
<param name="FlashVars" value="&chartWidth=600&chartHeight=400&dataXML=<graph caption='Quarterly Revenue (K$)' xAxisName='Quarters' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'></staticValue>
</dataSource>
</HTMLItem>
<repeater refQuery="Query1">
<contents><HTMLItem description="set name=">
<dataSource>
<staticValue><set name='</staticValue>
</dataSource>
</HTMLItem>
<HTMLItem description="Quarter">
<dataSource>
<dataItemValue refDataItem="Quarter"/></dataSource>
</HTMLItem>
<HTMLItem description=" value=">
<dataSource>
<staticValue>' value='</staticValue>
</dataSource>
</HTMLItem>
<HTMLItem description="Revenue">
<dataSource>
<reportExpression>number2string([Query1].[Revenue]/1000)</reportExpression></dataSource>
</HTMLItem>
<HTMLItem description="/set">
<dataSource>
<staticValue>' /></staticValue>
</dataSource>
</HTMLItem>
</contents>
<propertyList><propertyItem refDataItem="Quarter"/><propertyItem refDataItem="Revenue"/></propertyList></repeater>
<HTMLItem description="Last">
<dataSource>
<staticValue></graph>">
<param name="quality" value="high" />
</object>
</staticValue>
</dataSource>
</HTMLItem></contents></tableCell><tableCell><contents><textItem><dataSource><staticValue> <OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="600" height="400" id="Column3D" >
<param name="movie" value="../samples/images/CPM/FusionChartsFree/FCF_Column3D.swf" />
<param name="FlashVars" value="&chartWidth=600&chartHeight=400&dataXML=<graph caption='Quarterly Revenue (K$)' xAxisName='Quarters' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'></staticValue></dataSource></textItem><repeater refQuery="Query1">
<contents><textItem><dataSource><staticValue><set name='</staticValue></dataSource></textItem>
<textItem><dataSource><dataItemValue refDataItem="Quarter"/></dataSource></textItem>
<textItem><dataSource><staticValue>' value='</staticValue></dataSource></textItem>
<textItem><dataSource><reportExpression>number2string([Query1].[Revenue]/1000)</reportExpression></dataSource></textItem>
<textItem><dataSource><staticValue></set></staticValue></dataSource></textItem>
</contents>
<propertyList><propertyItem refDataItem="Quarter"/><propertyItem refDataItem="Revenue"/></propertyList></repeater><textItem><dataSource><staticValue></graph>"> <param name="quality" value="high" /> </object></staticValue></dataSource></textItem></contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse"/></style></table></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<queries><query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Quarter" aggregate="none"><expression>[great_outdoors_company].[Years].[Years].[Quarter]</expression></dataItem><dataItem name="Revenue"><expression>[great_outdoors_company].[Measures].[Revenue]</expression></dataItem></selection>
<slicer><slicerMemberSet>#prompt('Product','mun','[great_outdoors_company].[Products].[Products].[Products]->:[PC].[@MEMBER].[Products]')#</slicerMemberSet></slicer></query>
<query name="Products"><source><model/></source><selection><dataItem name="Product line" aggregate="none"><expression>[great_outdoors_company].[Products].[Products].[Product line]</expression></dataItem></selection></query></queries></report>