Fusion Charts in Cognos. Volume 1, Part 1 (3D Column Charts)

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 \samples\images\CPM\FusionChartsFree\. 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:

  1. Source Text: <set name=’
  2. Source Data Item Value: Quarter
  3. Source Text: ‘ value=’
  4. Source Report Expression: number2string([Query1].[Revenue]/1000)
  5. 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>  &lt;OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="600" height="400" id="Column3D" &gt;
    &lt;param name="movie" value="../samples/images/CPM/FusionChartsFree/FCF_Column3D.swf" /&gt;
    &lt;param name="FlashVars" value="&amp;chartWidth=600&amp;chartHeight=400&amp;dataXML=&lt;graph caption='Quarterly Revenue (K$)' xAxisName='Quarters' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'&gt;</staticValue>
														</dataSource>
													</HTMLItem>
													<repeater refQuery="Query1">
														<contents><HTMLItem description="set name=">
																<dataSource>
																	<staticValue>&lt;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>' /&gt;</staticValue>
																</dataSource>
															</HTMLItem>
														</contents>
														<propertyList><propertyItem refDataItem="Quarter"/><propertyItem refDataItem="Revenue"/></propertyList></repeater>
													<HTMLItem description="Last">
										<dataSource>
											<staticValue>&lt;/graph&gt;"&gt;
    &lt;param name="quality" value="high" /&gt;
  &lt;/object&gt;
</staticValue>
										</dataSource>
									</HTMLItem></contents></tableCell><tableCell><contents><textItem><dataSource><staticValue>  &lt;OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="600" height="400" id="Column3D" &gt;
    &lt;param name="movie" value="../samples/images/CPM/FusionChartsFree/FCF_Column3D.swf" /&gt;
    &lt;param name="FlashVars" value="&amp;chartWidth=600&amp;chartHeight=400&amp;dataXML=&lt;graph caption='Quarterly Revenue (K$)' xAxisName='Quarters' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'&gt;</staticValue></dataSource></textItem><repeater refQuery="Query1">
														<contents><textItem><dataSource><staticValue>&lt;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>&lt;/set&gt;</staticValue></dataSource></textItem>
														</contents>
														<propertyList><propertyItem refDataItem="Quarter"/><propertyItem refDataItem="Revenue"/></propertyList></repeater><textItem><dataSource><staticValue>&lt;/graph&gt;"&gt;    &lt;param name="quality" value="high" /&gt;  &lt;/object&gt;</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]-&gt;:[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>
Advertisements

13 Responses to Fusion Charts in Cognos. Volume 1, Part 1 (3D Column Charts)

  1. moshe says:

    very nice Mr. Paul !

  2. MFGF says:

    Cool technique!

  3. John Petler says:

    This technique is amazing, currently im working on multi measure fusion charts with cognos. I’m facing the problem category and dataset. If category is repeating then dateset is not and vice versa. Im just using repeater only. let me know if you can pass some information.

  4. Gitesh says:

    please tell me ho to reduce 3d bar width using fusion chart

  5. Sandeep says:

    This thing is amazing. You have an awesome blog. This has inspired me to write creative stuff on my blog rather than writing on the issues I come across.

  6. Yanir says:

    Hi Paul, do you have an example of thermometer gauge (FusionWidgets XT Trial )?

  7. Biju says:

    Hi Paul,

    Gr8 article! I was trying your your technique and got a message as “Invalid XML Data”. It first shows loading chart then at last gives me this message. I am using GO sales (Query) package and gross profit and Quarter data items from the sales tree. Please guide me!

    Thanks,
    Bob

    • CognosPaul says:

      Hi Biju,

      There are a few things to check. First, do you have any new lines in the generated XML? That seems to mess things up. Second, try replacing all of your HTML items with text items, and copy/paste that into the FusionChart samples. Sometimes seeing the actual generated XML in front of you can help figure out where you’re going wrong.

  8. Adal says:

    Hi Paul,

    I followed the steps you mentioned here, but the graph is not displayed, I am using report studio 10.2.1, I would appreciate your help

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: