In my previous post I discussed a basic column graph, quarters and revenue. In this post I’ll expand on that basic graph by adding the link parameter.

The link parameter will allow us to run JavaScript functions to call Parameterized URLS.

Open the XML I attached to the previous post.

We’ll start with imitating a drilldown. To do this we need to have each column contain a link to the same report, passing the MUN. The x-axis should be a higher level than the Quarters. Since it’s supposed to receive MUN we’ll use a prompt macro with a default value of the year level. Rename the existing Data Item to Time, and replace the expression with:

#prompt('Time','mun','[great_outdoors_company].[Years].[Years].[Year]','children(','',')')#

The macro will wrap whatever member is sent to it with children().

We will also need to explicitly set the MUN being set. Create a new data item in the query called TimeMUN.

roleValue('_memberUniqueName',[Time])

This will return the MUN as a string value. Useful for inserting it into an HTML item.

Now that the query is set, we need a way of calling the report. Instead of putting the full URL for the report directly into each column, we can instead call a JavaScript function. At the top of the page drag in a new HTML item, set it to Report Expression, and paste in the following code:

[sourecode]’
var report=”../cgi-bin/cognosisapi.dll?b_action=xts.run&m=portal/report-viewer.xts&ui.action=run&ui.object=’+URLEncode(ReportPath())+”’

function drillDown(mun)
{
window.open(report+”&run.prompt=false&p_Time=”+mun,”_self”)

}
‘[/sourcecode]

As you can see the report path is being generated from the Cognos report function ReportPath. This will only work from Cognos Connection, so you may want to manually put the full path of the report so you can run it from Report Studio.

Now for the link itself.

Right now you should have a total of 7 HTML items generating the HTML for the chart. 5 of those should be inside a repeater defining the categories and associated values. Insert a new HTML item after the Revenue HTML item with the following expression:

' link='JavaScript:drillDown("

The first quotes is to close the value from the revenue. The &quot is important as regular quotes won’t work as that would close the link string, and doublequotes won’t work as that would close the value string from the object tag.

Insert another HTML item to the right of Link, give it the description MUN, and set the source type to Data Item Value, with the Data Item Value set to TimeMUN.

Now modify the /set HTML Item to

");' />

Save and run it from the Cognos Connection and the first page should look like:

And clicking on 2006 should return:

The actual HTML generated will look something like:

<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='Revenue (K$)' xAxisName='Period' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'><set name='2004' value='34750.6' link='JavaScript:drillDown(&quot;[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20040101-20041231]&quot;);' /><set name='2005' value='62540.9' link='JavaScript:drillDown(&quot;[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20050101-20051231]&quot;);' /><set name='2006' value='74285' link='JavaScript:drillDown(&quot;[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20060101-20061231]&quot;);' /></graph>">    <param name="quality" value="high" />  </object>

The same method can be used as drillthroughs.

A little more difficult is to do a drill-up. I’ll leave that as an exercise that I will leave to my readers.

As usual, the XML is below.

<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>

							<HTMLItem description="drillDown">
								<dataSource>

								<reportExpression>'&lt;script&gt;
var report=''../cgi-bin/cognosisapi.dll?b_action=xts.run&amp;m=portal/report-viewer.xts&amp;ui.action=run&amp;ui.object='+URLEncode(ReportPath())+'''

function drillDown(mun)
{
   window.open(report+''&amp;run.prompt=false&amp;p_Time=''+mun,''_self'')

}

&lt;/script&gt;'</reportExpression></dataSource>
							</HTMLItem>
							<selectValue parameter="Product" refQuery="Products" multiSelect="false" range="false" required="false" autoSubmit="true"><useItem refDataItem="Product line"/></selectValue>
							<textBox numbersOnly="false" multiSelect="false" range="false" required="false" parameter="Time"><style><CSS value="display:none"/></style></textBox>
							<table class="tb"><tableRows><tableRow><tableCells><tableCell><contents/></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents>

													</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='Revenue (K$)' xAxisName='Period' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'&gt;</staticValue>
														</dataSource>
													</HTMLItem>
													<repeater refQuery="Query1" rowsPerPage="132123">
														<contents><HTMLItem description="set name=">
																<dataSource>
																	<staticValue>&lt;set name='</staticValue>
																</dataSource>
															</HTMLItem>
															<HTMLItem description="Time">
																<dataSource>
																	<dataItemValue refDataItem="Time"/></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="link">
																<dataSource>
																	<staticValue>' link='JavaScript:drillDown(&amp;quot;</staticValue>
																</dataSource>
															</HTMLItem>
															<HTMLItem description="MUN">
																<dataSource>
																	<dataItemValue refDataItem="TimeMUN"/></dataSource>
															</HTMLItem>
															<HTMLItem description="/set">
																<dataSource>
																	<staticValue>&amp;quot;);' /&gt;</staticValue>
																</dataSource>
															</HTMLItem>
														</contents>
														<propertyList><propertyItem refDataItem="Time"/><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></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse"/></style></table><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='Revenue (K$)' xAxisName='Period' yAxisName='Revenue' rotateNames='1' showNames='1' numberPrefix='$' numberSuffix='K' decimalPrecision='0' formatNumberScale='0'&gt;</staticValue></dataSource></textItem><repeater refQuery="Query1" rowsPerPage="132123">
														<contents><textItem><dataSource><staticValue>&lt;set name='</staticValue></dataSource></textItem>
															<HTMLItem description="Time">
																<dataSource>
																	<dataItemValue refDataItem="Time"/></dataSource>
															</HTMLItem>
															<HTMLItem description=" value=">
																<dataSource>
																	<staticValue>' value='</staticValue>
																</dataSource>
															</HTMLItem>
															<HTMLItem description="Revenue">
																<dataSource>
																	<reportExpression>number2string([Query1].[Revenue]/1000)</reportExpression></dataSource>
															</HTMLItem>

															<textItem><dataSource><staticValue>' link='JavaScript:drillDown(&amp;quot;</staticValue></dataSource></textItem>
															<HTMLItem description="MUN">
																<dataSource>
																	<dataItemValue refDataItem="TimeMUN"/></dataSource>
															</HTMLItem>
															<textItem><dataSource><staticValue>&amp;quot;);' /&gt;</staticValue></dataSource></textItem>
														</contents>
														<propertyList><propertyItem refDataItem="Time"/><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>
					</pageBody>
				</page>
			</reportPages>
		</layout>
	</layouts>
<queries><query name="Query1">
			<source>
				<model/>
			</source>
			<selection><dataItem name="Time" aggregate="none"><expression>#prompt('Time','mun','[great_outdoors_company].[Years].[Years].[Year]','children(','',')')#</expression></dataItem><dataItem name="TimeMUN" aggregate="none"><expression>roleValue('_memberUniqueName',[Time])</expression></dataItem><dataItem name="Revenue" aggregate="none" rollupAggregate="none"><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>

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 samplesimagesCPMFusionChartsFree. 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>