This article is based on 10.2, and uses the 10.2 Prompt API. It can be easily adapted to earlier versions however.
When defining a drillthrough you can set various settings as report target, output format, locales and more. Unfortunately there is no easy way to change those settings on the fly. On the other hand, I just did all the work so now there is an easy way.
Drillthrough definitions come in two basic parts. The second part, which won’t be delved into in this article, has the actual values being passed. These can be seen in the links themselves.
Looking at the code itself (modified for legibility):
<SPAN
dtTargets='
<drillTarget
drillIdx="0"
label="DrillThrough">
<drillParameter
name="Product Line"
value="[sales_and_marketing].[Products].[Products].[Product line]-&gt;:[PC].[@MEMBER].[991]"
displayValue="Camping Equipment"/>
<drillParameter
name="Year"
value="[sales_and_marketing].[Time].[Time].[Year]-&gt;:[PC].[@MEMBER].[20100101-20101231]"
displayValue="2010"/>
</drillTarget>'>
<SPAN class=hy tabIndex=-1>
81,929,179.22
</SPAN>
</SPAN>
This can be modified by looping through with JavaScript fairly easily, but I’ll leave that for another time.
The first part describes the actual settings – the metadata of the drill. This is stored in JavaScript object in the page, which makes it significantly easier to modify. Stringifying the object returns (again, modified for legibility):
[
{"m_label":"DrillThrough"
,"m_outputFormat":"HTMLFragment"
,"m_outputLocale":"en-us"
,"m_showInNewWindow":"true"
,"m_method":"execute"
,"m_path":"/content/folder[@name='JavaScript Examples']/folder[@name='Modifying Drillthrough Definitions']/report[@name='Drill 1']"
,"m_bookmark":""
,"m_parameters":"<bus:parameters xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="bus:baseParameter[2]"><item xsi:type="bus:parameter"><bus:name xsi:type="xs:string">Product Line</bus:name><bus:type xsi:type="bus:parameterDataTypeEnum">memberUniqueName</bus:type></item><item xsi:type="bus:parameter"><bus:name xsi:type="xs:string">Year</bus:name><bus:type xsi:type="bus:parameterDataTypeEnum">memberUniqueName</bus:type></item></bus:parameters>"
,"m_objectPaths":"<bus:objectPaths xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="bus:searchPathSingleObject[4]"><item xsi:type="bus:searchPathSingleObject">storeID("i326070F7595B4B5BBCD38D945E66AAEB")</item><item xsi:type="bus:searchPathSingleObject">storeID("iDB36CF1A0D654E99B3117B0B6A7F8789")/model[last()]</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[last()]</item></bus:objectPaths>"
,"m_prompt":"false"
,"m_dynamicDrillThrough":false
,"m_parameterProperties":"<PARAMETER-PROPERTIES/>"}
,
{"m_label":"DrillThrough"
,"m_outputFormat":"HTMLFragment"
,"m_outputLocale":"en-us"
,"m_showInNewWindow":"true"
,"m_method":"execute"
,"m_path":"/content/folder[@name='JavaScript Examples']/folder[@name='Modifying Drillthrough Definitions']/report[@name='Drill 1']"
,"m_bookmark":""
,"m_parameters":"<bus:parameters xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="bus:baseParameter[2]"><item xsi:type="bus:parameter"><bus:name xsi:type="xs:string">Product Line</bus:name><bus:type xsi:type="bus:parameterDataTypeEnum">memberUniqueName</bus:type></item><item xsi:type="bus:parameter"><bus:name xsi:type="xs:string">Year</bus:name><bus:type xsi:type="bus:parameterDataTypeEnum">memberUniqueName</bus:type></item></bus:parameters>"
,"m_objectPaths":"<bus:objectPaths xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="bus:searchPathSingleObject[4]"><item xsi:type="bus:searchPathSingleObject">storeID("i326070F7595B4B5BBCD38D945E66AAEB")</item><item xsi:type="bus:searchPathSingleObject">storeID("iDB36CF1A0D654E99B3117B0B6A7F8789")/model[last()]</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[last()]</item></bus:objectPaths>"
,"m_prompt":"false"
,"m_dynamicDrillThrough":false
,"m_parameterProperties":"<PARAMETER-PROPERTIES/>"}]
Each of these settings is easily changed (except for the the m_parameters and m_parameterProperties because that means changing the settings on the actual drills themselves). People with sharp eyes may notice that both drillthrough definitions are exactly the same. Why does it appear twice? The drillthrough appears twice in my report’s crosstab. The definition appears for the detail row and again for the totals row. If I was passing the individual month with the detail, then the first drillthrough in the object would include the month.
Because the drills can appear twice, any function written to rewrite the settings must include a loop to check the m_label in each element.
Consider the following:
<script>
var paulScripts = {}, oCR = cognos.Report.getReport( "_THIS_" );
paulScripts.getControl = function (promptName) {
return oCR.prompt.getControlByName(promptName);
}
/*
* function: changeDrillAttribute
* By Paul Mendelson
* This allows the user to change any attribute in the drill. Attributes may be:
* m_label, m_outputFormat, m_outputLocale, m_showInNewWindow, m_method, m_path,
* m_bookmark, m_parameters, m_objectPaths, m_prompt, m_dynamicDrillThrough,
* m_parameterProperties
* Drills may appear multiple times in the object, so the loop goes through the entire array. Each
* matching drill will have the attribute changed as desired.
* Valid m_outputFormat: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS,
* XML, XLWA.
* m_path: /content/folder[@name='folderName']/folder[@name='folderName']/report[@name='reportName']
*/
paulScripts.changeDrillAttribute = function (drillName,attribute,newValue){
var oCV = window['oCV'+'_THIS_']
, drillsObj = oCV.getRV().getCV().getDrillTargets();
for(var i=0;i<drillsObj.length;i++){
if( drillsObj[i].m_label == drillName) drillsObj[i][attribute] = newValue;
}
}
</script>
paulScripts.changeDrillAttribute function will receive the name of the drill, the attribute to change, and the new value of that attribute. It will then loop through all of the elements in the drill targets, and if the label matches, change the attribute.
The following screenshot shows the crosstab containing the drill, and two radio button groups.
The radios have a validator set on them to invoke the changeDrillAttribute function.
<script>
paulScripts.getControl('Format').setValidator(
function (values) {
paulScripts.changeDrillAttribute ('DrillThrough','m_outputFormat',values[0].use);
return true;
}
);
paulScripts.getControl('Target').setValidator(
function (values) {
paulScripts.changeDrillAttribute ('DrillThrough','m_path',values[0].use);
return true;
}
);
</script>
Whenever the radio is changed, it will modify the drill appropriately. That setValidator function is the only use of the new Prompt API, so this can be changed very easily to previous versions by using onChange events.
Ultimately the end user can decide which report he wants to drill to, and in what format, without having to right-click. Because users don’t like right-clicking.
The following XML is the report. Remember to create two targets for the drill and to fix the value prompt. To get the path of the report, simply go to the properties of the report, click on “View the search path, ID and URL” and copy the search path.
<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Quarter"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Quarter]</LUN><itemCaption>Quarter</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Product line"><dmLevel><LUN>[sales_and_marketing].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[sales_and_marketing].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[sales_and_marketing].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<HTMLItem>
<dataSource>
<staticValue><script>
var paulScripts = {}, oCR = cognos.Report.getReport( "_THIS_" );
paulScripts.getControl = function (promptName) {
return oCR.prompt.getControlByName(promptName);
}
/*
* function: changeDrillAttribute
* Paul Mendelson
* This allows the user to change any attribute in the drill. Attributes may be:
* m_label, m_outputFormat, m_outputLocale, m_showInNewWindow, m_method, m_path,
* m_bookmark, m_parameters, m_objectPaths, m_prompt, m_dynamicDrillThrough,
* m_parameterProperties
* Drills may appear multiple times in the object, so the loop goes through the entire array. Each
* matching drill will have the attribute changed as desired.
* Valid m_outputFormat: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS,
* XML, XLWA.
* m_path: /content/folder[@name='folderName']/folder[@name='folderName']/report[@name='reportName']
*/
paulScripts.changeDrillAttribute = function (drillName,attribute,newValue){
var oCV = window['oCV'+'_THIS_']
, drillsObj = oCV.getRV().getCV().getDrillTargets();
for(var i=0;i<drillsObj.length;i++){
if( drillsObj[i].m_label == drillName) drillsObj[i][attribute] = newValue;
}
}
</script>
</staticValue>
</dataSource>
</HTMLItem>
<table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></crosstabCorner>
<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quarter" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Revenue"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource><reportDrills><reportDrill name="DrillThrough"><drillLabel><dataSource><staticValue/></dataSource></drillLabel><drillTarget method="execute" showInNewWindow="true"><reportPath path="/content/folder[@name='JavaScript Examples']/folder[@name='Modifying Drillthrough Definitions']/report[@name='Drill 1']"><XMLAttributes><XMLAttribute name="ReportName" value="Drill 1" output="no"/><XMLAttribute name="class" value="report" output="no"/></XMLAttributes></reportPath><drillLinks><drillLink><drillTargetContext><parameterContext parameter="Product Line"/></drillTargetContext><drillSourceContext><dataItemContext refDataItem="Product line"/></drillSourceContext></drillLink><drillLink><drillTargetContext><parameterContext parameter="Year"/></drillTargetContext><drillSourceContext><dataItemContext refDataItem="Year"/></drillSourceContext></drillLink></drillLinks></drillTarget></reportDrill></reportDrills><style><defaultStyles><defaultStyle refStyle="hy"/></defaultStyles></style></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></contents></tableCell><tableCell><contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><selectValue parameter="Format" selectValueUI="radioGroup" name="Format"><selectOptions><selectOption useValue="PDF"/><selectOption useValue="spreadsheetML"/><selectOption useValue="HTMLFragment"/><selectOption useValue="HTML"/></selectOptions><defaultSelections><defaultSimpleSelection>HTMLFragment</defaultSimpleSelection></defaultSelections></selectValue></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><selectValue parameter="Target" name="Target" selectValueUI="radioGroup"><selectOptions><selectOption useValue="/content/folder[@name='JavaScript Examples']/folder[@name='Modifying Drillthrough Definitions']/report[@name='Drill 1']"><displayValue>Target 1</displayValue></selectOption><selectOption useValue="/content/folder[@name='JavaScript Examples']/folder[@name='Modifying Drillthrough Definitions']/report[@name='Drill 2']"><displayValue>Target 2</displayValue></selectOption></selectOptions><defaultSelections><defaultSimpleSelection>/content/folder[@name='JavaScript Examples']/folder[@name='Modifying Drillthrough Definitions']/report[@name='Drill 1']</defaultSimpleSelection></defaultSelections></selectValue></contents></tableCell></tableCells></tableRow></tableRows></table></contents><style><CSS value="vertical-align:top"/></style></tableCell></tableCells></tableRow></tableRows></table><HTMLItem>
<dataSource>
<staticValue><script>
paulScripts.getControl('Format').setValidator(
function (values) {
paulScripts.changeDrillAttribute ('DrillThrough','m_outputFormat',values[0].use);
return true;
}
);
paulScripts.getControl('Target').setValidator(
function (values) {
paulScripts.changeDrillAttribute ('DrillThrough','m_path',values[0].use);
return true;
}
);
</script></staticValue>
</dataSource>
</HTMLItem></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><reportName>Report</reportName></report>
Hi Paul
Is it possible to modify Drill through to achieve popup behavior?
I mean the target report shows up as pop up window, removing header, tool bar, etc?
Thanks
Unfortunately no, I think the window.open parameters are hardcoded in the backend JS function that controls the drill throughs. In a case like this I would skip the drillthrough and write my own function to open the window.
Hi, I tried this code for Cognos 10.1.1. The code :paulScripts.getControl(‘Format’).setValidator(
function (values) {
paulScripts.changeDrillAttribute (‘DrillThrough’,’m_outputFormat’,values[0].use);
return true;
}
);
does not work or I did something wrong. Instead of this I used classical function for get object from choice list prompt and set new value instead of values[0].use
That makes sense. The values[0].use syntax is based on the Prompt API, I hadn’t considered Cognos 10.1 when I wrote this.
Today I did yet one step for accomodate code to v10.1.1
in function paulScripts.changeDrillAttribute
var oCV;
if (typeof (oCVRS) != “undefined”){
oCV = window[‘oCV’ + ‘RS’];}
else{
oCV = window[‘oCV’ + ‘_NS_’];} …
Hi Paul,
I have a problem with the drill-through if you can help me please.
I will wish that every time the user click on a cell (at the beginning, the cell is red), the cell returns to its normal color.
I am not an expert in javascript but I’m sure it could.
thanks