Embedding FusionCharts into Cognos Reports

Cognos is a Business Intelligence platform that allows authors to connect to almost any database, design a metadata model, and build reports and dashboards. These reports can be viewed in a number of formats, HTML, PDF, Excel, CSV and XML. While Cognos has its own portal system, these reports can also be attached to Sharepoint (MOSS). This post will detail how to embed FusionCharts v3 into Cognos reports using standard Report Studio objects. It assumes a basic level of understanding of Cognos Report Studio and HTML. While Cognos has a suite of charts, they are often impossible to format exactly to the specifications desired. FusionCharts, while needing more effort to create than Cognos reports, allows for significantly more adjustments in the appearance to allow the user to generate the javascript graph and chart exactly as he wants it.

Data String Method

FusionCharts allow authors to embed the source XML directly into the page. This will allow Cognos authors to use HTML Objects to programmatically generate the expected XML. To start, drag an HTML object to the Page Header. Paste in the reference to the FusionCharts.js file:

<script type="text/javascript" src=".. /FusionCharts/Charts/FusionCharts.js"></script>

Set the description of the HTML Object to FusionCharts.js so you know what it is in the future. Next, drag another HTML object to wherever you want the chart to appear:

<div id="chartContainer">FusionCharts will load here</div>

Set the description of the HTML Object to [cciel lang=”html”]chartContainer[/cciel]. Now the parameters for the chart need to be set. Ultimately the full HTML on the page needs to appear similar to the following:

This can be achieved by combining HTML objects with repeaters.


Drag in an HTML item, and paste everything from [cciel lang="html"]<script type[/cciel] to [cciel lang="html"]inThousandSeparator=','>[/cciel]



The inThousandSeparatorflag is important as Cognos will automatically add the thousands separator to numeric fields.

Set the description of this HTML item to ChartStart

Drag another HTML item to the right, description is Chart End, and paste from  "</chart>"  to </script>


Now we have to HTML items, ChartStart and ChartEnd next to each other. When the report is run, it will have all of the settings for the chart, except for the data itself.



Now for the values of the chart. Create a query for your chart. In this example, the chart should show revenue by years.



The results of this query are:



Revenue is formatted in the cube, so we'll need to get rid of the dollar sign in the report.

Drag a repeater between the ChartStart and ChartEnd HTML items. Set the query to the one you made previously, and put both items into the properties of the repeater.



Drag 5 HTML items into the repeater. The following list explains each item:

	
Text: [cciel lang="html"]<set label='[/cciel]
	
Data Item Value: [cciel lang="html"]Year[/cciel]
	
Text: [cciel lang="html"]' value='[/cciel]
	
Report Expression: [cciel lang="javascript"][Query1].[Revenue]*1[/cciel] This is to remove the $ from the string. If it weren't for the dollar sign you could have left it as a data item value.
	
Text: [cciel lang="html"]'/>[/cciel]

Name the HTML items appropriately, so you can reference them later if needed.

Your page should now look like this:



Now try running it. If every step was completed correctly, the report should look like this:



By simply changing the Column2d.swf to Pie2d.swf your chart would look like:



And as Line:



That is how you can embed FusionCharts v3 into your Cognos reports. Do you like what you see?



Note: Cognos saves the metadata of the reports as XML inside the Cognos database. This makes it very easy to share reports via email.

Authors can simply download the XML and copy it into the clipboard and use the authoring tool to load it.

This is a guest post written by Paul Mendelson. Paul has worked in the Business Intelligence field for 8 years. He currently works for Opisoft Ltd as a Cognos expert. He works at a number of client sites including the Road Safety Authority, Menora Insurance, Teva Pharmaceuticals, and a number of other companies. Paul also maintains a Cognos blog.