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 " 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("[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20040101-20041231]");' /><set name='2005' value='62540.9' link='JavaScript:drillDown("[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20050101-20051231]");' /><set name='2006' value='74285' link='JavaScript:drillDown("[great_outdoors_company].[Years].[Years].[Year]->:[PC].[@MEMBER].[20060101-20061231]");' /></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>'<script>
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'')
}
</script>'</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> <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'></staticValue>
</dataSource>
</HTMLItem>
<repeater refQuery="Query1" rowsPerPage="132123">
<contents><HTMLItem description="set name=">
<dataSource>
<staticValue><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(&quot;</staticValue>
</dataSource>
</HTMLItem>
<HTMLItem description="MUN">
<dataSource>
<dataItemValue refDataItem="TimeMUN"/></dataSource>
</HTMLItem>
<HTMLItem description="/set">
<dataSource>
<staticValue>&quot;);' /></staticValue>
</dataSource>
</HTMLItem>
</contents>
<propertyList><propertyItem refDataItem="Time"/><propertyItem refDataItem="Revenue"/></propertyList></repeater><HTMLItem description="Last">
<dataSource>
<staticValue></graph>">
<param name="quality" value="high" />
</object>
</staticValue>
</dataSource>
</HTMLItem></contents></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse"/></style></table><textItem><dataSource><staticValue> <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'></staticValue></dataSource></textItem><repeater refQuery="Query1" rowsPerPage="132123">
<contents><textItem><dataSource><staticValue><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(&quot;</staticValue></dataSource></textItem>
<HTMLItem description="MUN">
<dataSource>
<dataItemValue refDataItem="TimeMUN"/></dataSource>
</HTMLItem>
<textItem><dataSource><staticValue>&quot;);' /></staticValue></dataSource></textItem>
</contents>
<propertyList><propertyItem refDataItem="Time"/><propertyItem refDataItem="Revenue"/></propertyList></repeater><textItem><dataSource><staticValue></graph>"> <param name="quality" value="high" /> </object></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]->:[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>
Hi Paul, great article you got there. Can you tell me if these charts will be displayed in a PDF output since, it seems like HTML fragments don’t appear in PDF reports.
Thank you.
Hi Phillipe,
Unfortunately these charts don’t export to PDF. Generally when I run into a situation where I have to support both HTML and PDF or Excel I’d create two charts, one normal and the other Fusion and use a conditional block to determine which to render. The variable is usually something like reportOutput() = ‘HTML’.