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.
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:
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:
var myChart = new FusionCharts
(“<chart caption=’Total Revenue by Year’ xAxisName=’Years’ yAxisName=’Revenue’ inThousandSeparator=’,’>
<set label=’2004′ value=’34,750,563.5′ />
<set label=’2005′ value=’62,540,865.06′ />
<set label=’2006′ value=’74,284,959.32′ />
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 [cciel lang=”html”]inThousandSeparator[/cciel] flag is important as Cognos will automatically add the thousands separator to numeric fields.
Set the description of this HTML item to [cciel lang=”html”]ChartStart[/cciel].
Drag another HTML item to the right, description is Chart End, and paste from [cciel lang=”xml”] “</chart>” [/cciel] to [cciel lang=”html”]</script>[/cciel]
Now we have to HTML items, [cciel lang=”html”]ChartStart[/cciel] and [cciel lang=”html”]ChartEnd[/cciel] 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 [cciel lang=”html”]ChartStart[/cciel] and [cciel lang=”html”]ChartEnd[/cciel] 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]
- 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 [cciel lang=”html”]Column2d.swf[/cciel] to [cciel lang=”html”]Pie2d.swf[/cciel] 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.