Categories: Tutorials

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.

guest

Next Finally we get social - How we created our first Twitter dashboard »

Previous « Behind the scenes of our shiny new product tour

View Comments

Nick Waters says:

July 25, 2011 at 4:00 pm

Hi there... Does this work in Cognos 10 ?.
I am struggling to get the example working under Cognos 10 and IE8.
Paul Mendelson says:

July 26, 2011 at 12:26 pm

Hi Nick,

This should works perfectly under Cognos 10. This method simply generates the HTML that the client's browser interprets into the graphs, so it will work in any version of Cogons that has HTML items and repeaters.

The things to check:
1. Are the files in a place the users can access? Just like images, the user needs to be able to reach the files with his browser. A good place to put them might be on a dedicated images server, or in the webcontents directory of the Cognos dispatcher.
2. Double-check that the path in your HTML items match the path in the file system. If it's in the webcontents of the dispatcher you might use "../FusionCharts/Charts/FusionCharts.js" or if it's on another server: "\ImagesServerFusionChartsChartsFusionCharts.js"
3. Make sure the data is coming from a repeater and not a list or repeater table. Both lists and repeater tables function by actually generating a table and populating each cell accordingly. Repeaters simply dump the contents onto the page without adding any extra HTML tags.
4. The data should be in HTML, not in text items. Like with the lists and repeater tables, text items add extra HTML, in this case span tags. HTML items will simply return the data directly to the page without modifying it.
5. Do the browsers support javascript and flash?
Nick Waters says:

July 26, 2011 at 1:25 pm

Hi Paul
Thanks for the feedback.
I'm getting a syntax error at line 111 (which when looking at the source code) it is at the line
110 <script type="text/javascript">111 <!—

I've tried all the things you recommended:
1. My fusioncharts folder is held in the root of my webserver :C:inetpubwwwrootFusionCharts
2. My PATH to https://localhost/FusionCharts/JSClass/FusionCharts.js is fully qualified.
3. Repeaters are definitely being used (not repeater tables). I can see the source being generated:

myChart.setXMLData

("<chart caption='Total Revenue by Year' xAxisName='Years' yAxisName='Revenue' inThousandSeparator=','><set label='2004' value='914,352,803.72'/><set label='2005' value='1,159,195,590.16'/><set label='2006' value='1,495,891,100.9'/><set label='2007' value='1,117,336,274.07'/></chart>");

4. Ensured that the data is HTML and not text items.
5. Browser (IE8 and Chrome tested) and they both support javascript and flash. The example pages in my FusionCharts implementation folder work perfectly.

Do you have working reportspec that you could post or send on ???

Nick
Paul Mendelson says:

July 27, 2011 at 1:04 am

I'll try to post a copy of the XML sometime tomorrow morning, but in the meantime a few other things to try.
What happens when you copy the data XML to a static html page? The dataXML looks perfect to
It may also be possible that the characters for " (doublequote: ") and ' (singlequote: ') got replaced with whatever characters ” and ’ are. Those could be the cause of the javascript errors.
Alka says:

December 23, 2011 at 2:38 pm

Hi,

Packt Publishing is looking for Technical Reviewers on "FusionCharts: Beginner’s Guide".

If interested in reviewing it, contact alkan@packtpub.com
Peter Birksmith says:

February 10, 2012 at 10:30 am

Hi Paul,
I've been looking at using FusionCharts in Cognos for a couple of months and with assistance from Gerry a mate in IAG, we've been able to determine how to generate a multi line chart with a series name. I'm using the free samples so there were some false starts with labels but we've arrive.
We did notice that you need to define values where there may be null values as the lines are basically pushed backwards to the Y Axis. So where you may have the following scenario, logic needs to be applied to generate the correctly formatted xml structure
reportnameA Month Count
Rpt1              NUL    NUL
Rpt2              NUL    NUL
Rpt1              NUL    NUL
Rpt2              JUN     1
Rpt1              JUL     1
Rpt2              JUL     1
Otherwise it's pretty good. Tip read the update notes in the readme! It's kinda important.
Great article! I hope there are some more examples about as I'd like to know how to pad the line to start a few pixels in from the Y axis.
Cheers
Peter B
Charlotte says:

July 23, 2013 at 5:51 pm

I am looking for a way to only show a data label for only a selected item on a chart in Cognos. Can these charts help with that? I can use Conditional formatting to show the item selected in the chart, but it need additional technology (possibly JavaScript) to only show the data label for that selected item.
Here is an example: https://i.imgur.com/aM4gEYu.jpg In this example, I only need to show the datafor Florida. Showing all the other data labels in this chart makes it way too jumbled.
- Swarnam says:
  
  July 30, 2013 at 1:16 pm
  
  Hey,
  
  Yes, it is possible to show only specific data labels by using and then setting showLabel='1' for the set you want to show the label(For Florida).
karthik says:

August 1, 2013 at 12:13 pm

Hi,
I have integrated fusion charts into Cognos 10.2. But, now all the data in Y axis is having the same value. And in Y-axis, I am getting the values like 'NaN.00M'. I have removed that by usin Yminval and Ymaxval property.
Revenue for each year is different, but I am getting the same bar level in the chart (and the values are 'NaN.00M'). Please help me out to resolve this problem.

Thanks in advance.
- Swarnam says:
  
  August 1, 2013 at 1:05 pm
  
  Hi Karthik,
  
  If FusionCharts Free has been used, please ensure the values retrieved from the database and passed to "value" attribute in the set element does not contain any separators like ','
  
  For example:
  -- Y axis will be displayed as NaN.00M and all bars will be displayed at the same level.
  
  Hope this helps.
Bharath says:

August 7, 2013 at 12:21 am

Could you please post the detailed step-by-step instructions on integrating Fusion Charts in Cognos 10.2.1. Also, please mention where exactly to put the FusionCharts folder and the comparability of the browsers.

Thanks in Advance.
- Swarnam says:
  
  August 7, 2013 at 10:48 am
  
  Hi Bharath,
  
  The blogpost provides information required for integrating FusionCharts with Cognos Report. The similar step by step instruction can be followed for integrating with Cognos 10.2.1. If you are looking in for any specific segment for more detailed information, please do let us know. It will help us to add the relevant info to the blogpost.
  
  The JavaScript files can be placed in the webcontents directory of the Cognos dispatcher or as per your requirements. However, please ensure the path has been specified accordingly for the JavaScript files.
  
  Please refer for more info on "Browser compatibility" at: https://www.fusioncharts.com/products/suite/tech-specs/
Bharath says:

August 15, 2013 at 7:09 pm

Swarnam,
I appreciate your guidance in helping me out. I could able to implement it now and would like to know if we can create drill through links to get detailed data out of the charts?
Any help regarding this is highly appreciated.