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:
[cciel lang=”html”]<script type=”text/javascript” src=”.. /FusionCharts/Charts/FusionCharts.js”></script>[/cciel]
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:

[cciel lang=”html”]

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

[/cciel]

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:
[ccWel lang=”html”]<script type=”text/javascript”>
<!—
var myChart = new FusionCharts
(“../samples/images/test/FusionCharts_Website/Charts/Column2D.swf”
, “myChartId”
, “400”
, “300”
, “0”
, “1”);
myChart.setXMLData
(“<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′ />
</chart>”);
myChart.render(“chartContainer”);
// –></script>[/ccWel]

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]

Pasting the intial chart parameters to an HTML item

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]

Pasting the final chart parameters in an HTML item

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.

HTML items, ChartStart and ChartEnd next to each other

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

Query to show revenue by years

The results of this query are:

Results of the query

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.

Year and Revenue in the properties of the repeater

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

  1. Text: [cciel lang=”html”]<set label='[/cciel]
  2. Data Item Value: [cciel lang=”html”]Year[/cciel]
  3. Text: [cciel lang=”html”]’ value='[/cciel]
  4. 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.
  5. 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:

Page with the repeater put in

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

Total Revenue by Year Column Chart

By simply changing the [cciel lang=”html”]Column2d.swf[/cciel] to [cciel lang=”html”]Pie2d.swf[/cciel] your chart would look like:

Total Revenue by Year Pie Chart

And as Line:

Total Revenue by Year Line Chart

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.

  • Nick Waters July 25, 2011, 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 July 26, 2011, 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 July 26, 2011, 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 http://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 July 27, 2011, 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 December 23, 2011, 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 February 10, 2012, 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 July 23, 2013, 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: http://i.imgur.com/aM4gEYu.jpg  In this example, I only need to show the data label for Florida.  Showing all the other data labels in this chart makes it way too jumbled.

    • Swarnam July 30, 2013, 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 August 1, 2013, 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 August 1, 2013, 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 August 7, 2013, 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 August 7, 2013, 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: http://www.fusioncharts.com/products/suite/tech-specs/

  • Bharath August 15, 2013, 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.

  • Jayesh Patil August 22, 2014, 9:39 pm

    The above blog shows solution when the charts were flash based. Now the charts are completely js. There is no swf files. How does this work?

  • Swarnam August 26, 2014, 12:58 pm

    Hi,

    The swf files have been removed since we have deprecated the support for Flash. However, you can still follow the same steps as mentioned in the blog post to render FusionCharts with Cognos report.

    While rendering a chart, to specify a chart type, use JavaScript alias name instead of specifying the chart folder path.

    Ref. Code:
    var myChart = new FusionCharts(“column2d” , “myChartId” , “400″ , “300″ , “0″ , “1″);

  • Adal December 19, 2016, 5:09 am

    Hi.

    I try to follow the steps mentioned here, but no graph is displayed (report studio 10.2.1), I would appreciate your help

    • swarnam February 24, 2017, 3:58 pm

      Hi,

      Do you have any error message in the web browser or in the console?

Leave a Comment