Automatically looping through charts in Active Reports

June 18, 2013May 17, 2015 | cognospaul

This is the first of a series of articles on extending the usability of Active Reports.

Active Reports makes a wonderful addition to the Cognos suite. Locally processed interactivity (my Brio alter ego is shouting, “so what!”; just ignore him) means complex, though predefined, data exploration scenarios can be offered to ever more demanding users. Just by clicking on a row in a list, the entire report can be refreshed instantly. Standard web-based reports are simply incapable of that behavior.

Periodically I get requests for things Active Reports can’t do. For some reason, saying that something is not possible completely renders all of the other features moot. One such request was: “I want an animated chart that will loop through all of the months in the database”. The user wanted a chart that would show 12 months at a time, incrementing one month per second, looping back to the start once it hits the last month.

As a mockup, I built an example chart in a data deck that shows that behavior.
Chart in Data Deck

I stuck a Data Iterator under it. By pressing the next button you can make it look like it’s animated. Sadly, this wasn’t enough. The needed it to be automatic.

Now we can start playing with JavaScript. This presents a few interesting problems, some of which I’m still trying to solve.

Problem 1. Cognos rewrites IDs.
To demonstrate, I create a text item and an HTML item – a div with text inside it:
HTML Item In RS

But when I run it, and examine the HTML:
HTML Item Div ID Changed

That’s interesting! The ID of the div changed from myDiv to v9! This means that any JavaScript written will have to not use getElementById(). There is no guarantee that the div will be v9 tomorrow. Especially if there are data changes in the report. That’s fine though. I could do a getElementsByTagName(‘div’) and loop through them until I find one that has the correct name, or some other attribute I set. It’ll be slow, but better than nothing.

Let’s see what happens if I use JavaScript to count the number of divs. It should be a simple:

<script>alert(document.getElementsByTagName('div').length)</script>

I just add it to the existing HTML item:
script tag

And yet when I run it, I’m not getting the alert. The JS is sound, and if I copy it to the script console in IE Dev Toolbar I get back the expected value. So what is going on?

Unfortunately this is still one of the things that I’m still trying to figure out. My guess is that every element (list, chart, HTML item) is stored in an object and loaded into the page after the page has been loaded as needed. When running a report with a data deck, we can see that the individual cards aren’t loaded until another control references them. This allows the page to be significantly smaller on the first load, and far more responsive than if everything was rendered. Of course, this is just a guess and I could be completely wrong (but that hardly ever happens).

Effectively this means that onload events are out. And predefined functions are out. Everything will have to be inline events on elements hand written with HTML items.

Getting back to the original problems, the user said that he would be okay with a start/stop button. He presses it to start the “animation”, and clicks it again to stop it whenever he wants. This means we can define the function in the onclick event. And once again I’ve turned to my good friend Dan Freundel for help. And once again he turned 20 lines of spaghetti code into 5 lines of pure JS genius.

Since we can’t loop through divs that don’t yet exist, we’ll have to find a way to recreate the action of clicking on the next/previous buttons on the iterator. One way is to set the parameter directly. Any control that uses that param will be effected. Unfortunately, that way requires referencing minified JS functions. If we do it this way, any upgrade or fix pack will kill the report. Instead, I opted to literally click on the next/prev buttons in the loop.

First, the JS:

<button onclick="
  if(!document.runAnim)
  {

runAnim = function(buttonElement)
{
 var intervalTracker, doStuff = function() {
  var iter = buttonElement.previousSibling
  , ibuttons = iter.getElementsByTagName('button');
  if(ibuttons[2].getAttribute('disabled')) {ibuttons[0].setAttribute('disabled',false);ibuttons[0].click();} else {ibuttons[2].click()}
}
 buttonElement.onclick = function()
 {
    if(intervalTracker)
   {
     clearInterval(intervalTracker);
     intervalTracker = false;
   }
   else
   {
     intervalTracker = setInterval(doStuff,1000);
   }
 };
 intervalTracker = setInterval(doStuff,1000);

};}
runAnim(this);
this.value='stop';
" value="Start animation">Start</button>

First this defines the function. Since we can’t use inline HTML functions will have to be defined like this. On the first run, if the runAnim function doesn’t exist, it will create it. Next it will call that function in the scope of the button. That means the setInterval timer will exist only for that button, and it will not interfere with any other timers on the page. As mentioned before, because the IDs get rewritten, it is impossible to easily reference an element on the page. For this, I simply placed the button directly after the iterator. Next, when an iterator is first loaded, it has no value set and you can’t click any of the buttons. But if, somehow, the disabled=true was removed from the First button, clicking it would select the first item in the list.

So we have the script. The timer function is created the first time the button is clicked, that function and all associated variables are invoked in the scope of the button and it immediately starts working.
Loopy Charts

The list on the right acts both as a highlighter for the current row, and as a control to select which month you want to see in the chart.

Update: Some people have reported issues with the previously attached XML. I’ve updated the report with 10.2.2.
AR-JS.txt (762 downloads)

Javascript

Javascript drill up and down buttons

May 30, 2013June 21, 2013 | cognospaul

One of my more frequent requests is for a way to allow users to drill up without having to right click. Related, many developers also want a way to support both drill-throughs and drill-downs in a single click.

The way to handle this is actually fairly simple. It can be done with an inline onclick event inside an HTML item.

<img
  src="..halimagesbtn_arrow_up.gif"
  onclick="
    var oCV = window['oCV'+'_THIS_'].getRV().getCV();
    oCV.getSelectionController().selectNode(this.parentNode,this.parentNode.nodeType);
    oCV.goDrillManager.rvDrillUp();"
  onmouseover="this.style.cursor='pointer'"
/>

drill up down buttons

Stick this inside the crosstab node or list cell next to the label, make sure that drilling is enabled and test away. This behaves exactly as if you did a right-click->drill up. If drilling is disabled, this won’t do anything but select the node. This method will not override drill behaviors or security. If a user can’t right-click/drill up past a certain level, this will not let him either.

So how does this work?

The drill manager works by finding the selected node and performing the requested action on that. So whenever you click on the background of a node and it turns orange, that node has been selected. Notice that when you drill down normally, the moment before the action triggers the cell turns orange. That is the engine selecting that node before it runs the drill.

You can test this fairly easily by creating an input anywhere on the page with test drill Put that in an HTML item, run the report, highlight a node and click the button.

The next bit is getting this to work with charts. Unfortunately charts are a little more difficult. Charts themselves are composed (basically) of two parts, an image and an HTML map containing different areas. The areas are the parts of the chart that you can hover over for tooltips and click events. The map HTML of a fairly complex chart, revenue by year/product line, looks like:

<MAP class=chart_map name=RSrsvptt0 LID="rsvptt0RS">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Product line" tabIndex=-1 href="#" shape=POLY coords="352, 6, 495, 6, 495, 20, 352, 20" isChartTitle="true" ctx="11" type="legendTitle">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Product line = Camping Equipment" tabIndex=-1 href="#" shape=POLY coords="370, 20, 495, 20, 495, 36, 370, 36" ctx="5" type="legendLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Product line = Mountaineering Equipment" tabIndex=-1 href="#" shape=POLY coords="370, 36, 495, 36, 495, 52, 370, 52" ctx="6" type="legendLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Product line = Personal Accessories" tabIndex=-1 href="#" shape=POLY coords="370, 52, 495, 52, 495, 68, 370, 68" ctx="7" type="legendLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Product line = Outdoor Protection" tabIndex=-1 href="#" shape=POLY coords="370, 68, 495, 68, 495, 84, 370, 84" ctx="8" type="legendLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Product line = Golf Equipment" tabIndex=-1 href="#" shape=POLY coords="370, 84, 495, 84, 495, 100, 370, 100" ctx="9" type="legendLabel">
  <AREA class=chart_area title=Revenue tabIndex=-1 shape=POLY coords="19, 182, 19, 139, 7, 139, 7, 183, 19, 183" type="numericAxisTitle">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title=Year tabIndex=-1 href="#" shape=POLY coords="239, 344, 239, 334, 214, 334, 214, 345, 239, 345" isChartTitle="true" ctx="10" type="ordinalAxisTitle">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2010" tabIndex=-1 href="#" shape=POLY coords="148, 330, 148, 320, 122, 320, 122, 331, 148, 331" ctx="1" type="ordinalAxisLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2011" tabIndex=-1 href="#" shape=POLY coords="209, 330, 209, 320, 183, 320, 183, 331, 209, 331" ctx="2" type="ordinalAxisLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2012" tabIndex=-1 href="#" shape=POLY coords="269, 330, 269, 320, 243, 320, 243, 331, 269, 331" ctx="3" type="ordinalAxisLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2013" tabIndex=-1 href="#" shape=POLY coords="330, 330, 330, 320, 304, 320, 304, 331, 330, 331" ctx="4" type="ordinalAxisLabel">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2013 
Product line = Golf Equipment 
Revenue = 174,740,819.29" tabIndex=-1 href="#" shape=POLY coords="341, 310, 341, 224, 332, 224, 332, 311, 341, 311" ctx="30::9::4" type="chartElement" display="174,740,819.29">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2013 
Product line = Outdoor Protection 
Revenue = 4,471,025.26" tabIndex=-1 href="#" shape=POLY coords="332, 310, 322, 308, 322, 311, 332, 311" ctx="26::8::4" type="chartElement" display="4,471,025.26">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2013 
Product line = Personal Accessories 
Revenue = 443,693,449.85" tabIndex=-1 href="#" shape=POLY coords="322, 310, 322, 90, 312, 90, 312, 311, 322, 311" ctx="22::7::4" type="chartElement" display="443,693,449.85">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2013 
Product line = Mountaineering Equipment 
Revenue = 141,520,649.70" tabIndex=-1 href="#" shape=POLY coords="312, 310, 312, 240, 302, 240, 302, 311, 312, 311" ctx="18::6::4" type="chartElement" display="141,520,649.70">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2013 
Product line = Camping Equipment 
Revenue = 352,910,329.97" tabIndex=-1 href="#" shape=POLY coords="302, 310, 302, 135, 293, 135, 293, 311, 302, 311" ctx="15::5::4" type="chartElement" display="352,910,329.97">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2012 
Product line = Golf Equipment 
Revenue = 230,110,270.55" tabIndex=-1 href="#" shape=POLY coords="281, 310, 281, 196, 271, 196, 271, 311, 281, 311" ctx="29::9::3" type="chartElement" display="230,110,270.55">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2012 
Product line = Outdoor Protection 
Revenue = 10,349,175.84" tabIndex=-1 href="#" shape=POLY coords="271, 310, 271, 306, 261, 306, 261, 311, 271, 311" ctx="25::8::3" type="chartElement" display="10,349,175.84">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2012 
Product line = Personal Accessories 
Revenue = 594,009,408.42" tabIndex=-1 href="#" shape=POLY coords="261, 310, 261, 15, 252, 15, 252, 311, 261, 311" ctx="21::7::3" type="chartElement" display="594,009,408.42">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2012 
Product line = Mountaineering Equipment 
Revenue = 161,039,823.26" tabIndex=-1 href="#" shape=POLY coords="252, 310, 252, 230, 242, 230, 242, 311, 252, 311" ctx="17::6::3" type="chartElement" display="161,039,823.26">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2012 
Product line = Camping Equipment 
Revenue = 500,382,422.83" tabIndex=-1 href="#" shape=POLY coords="242, 310, 242, 61, 232, 61, 232, 311, 242, 311" ctx="14::5::3" type="chartElement" display="500,382,422.83">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2011 
Product line = Golf Equipment 
Revenue = 168,006,427.07" tabIndex=-1 href="#" shape=POLY coords="220, 310, 220, 227, 210, 227, 210, 311, 220, 311" ctx="28::9::2" type="chartElement" display="168,006,427.07">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2011 
Product line = Outdoor Protection 
Revenue = 25,008,574.08" tabIndex=-1 href="#" shape=POLY coords="210, 310, 210, 298, 201, 298, 201, 311, 210, 311" ctx="24::8::2" type="chartElement" display="25,008,574.08">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2011 
Product line = Personal Accessories 
Revenue = 456,323,355.90" tabIndex=-1 href="#" shape=POLY coords="201, 310, 201, 83, 191, 83, 191, 311, 201, 311" ctx="20::7::2" type="chartElement" display="456,323,355.90">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2011 
Product line = Mountaineering Equipment 
Revenue = 107,099,659.94" tabIndex=-1 href="#" shape=POLY coords="191, 310, 191, 257, 181, 257, 181, 311, 191, 311" ctx="16::6::2" type="chartElement" display="107,099,659.94">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2011 
Product line = Camping Equipment 
Revenue = 402,757,573.17" tabIndex=-1 href="#" shape=POLY coords="181, 310, 181, 110, 171, 110, 171, 311, 181, 311" ctx="13::5::2" type="chartElement" display="402,757,573.17">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2010 
Product line = Golf Equipment 
Revenue = 153,553,850.98" tabIndex=-1 href="#" shape=POLY coords="159, 310, 159, 234, 150, 234, 150, 311, 159, 311" ctx="27::9::1" type="chartElement" display="153,553,850.98">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2010 
Product line = Outdoor Protection 
Revenue = 36,165,521.07" tabIndex=-1 href="#" shape=POLY coords="150, 310, 150, 293, 140, 293, 140, 311, 150, 311" ctx="23::8::1" type="chartElement" display="36,165,521.07">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2010 
Product line = Personal Accessories 
Revenue = 391,647,093.61" tabIndex=-1 href="#" shape=POLY coords="140, 310, 140, 116, 130, 116, 130, 311, 140, 311" ctx="19::7::1" type="chartElement" display="391,647,093.61">
  <AREA onmouseup=window.oCVRS.pcc(event) class="dl chart_area" title="Year = 2010 
Product line = Camping Equipment 
Revenue = 332,986,338.06" tabIndex=-1 href="#" shape=POLY coords="121, 310, 121, 145, 111, 145, 111, 311, 121, 311" ctx="12::5::1" type="chartElement" display="332,986,338.06">
</MAP>

Areas can’t be selected the same way as crosstab nodes. For one, the button can’t be placed directly into the chart. Secondly, the selectNode function won’t work on charts. No, for that we’ll need something radically different: setSelectedChartArea()

First, let’s decide what we want to drill up on. For the sake of this example, I’ll say that the users are drilling down on the time dimension in the ordinal axis. So in order to drill up, we need to isolate one of those areas. It normally doesn’t matter which one as they will all drill up to the same member (this may not always be the case, especially if you change the default drill behavior). We need to loop through the areas of the chart, and find the first area that has the type “ordinalAxisLAbel”. Once we have it, we can use the drastically different function to select it.

Since the button doesn’t exist inside the chart, we also need a way to find the chart element on the page. Fortunately there is a Cognos function for that. getLayoutElementFromLid works just like getElementById. Except just for Cognos objects. And when using it, you have to remember to specify the namespace you’re working in. So not like getElementById at all. Using getLayoutElementFromLid will return the actual image of the chart, so to get the areas we have to go two nodes up.

var oCV = window['oCV'+'_THIS_'].getRV().getCV()
  , areas = oCV.getLayoutElementFromLid('ChartName'+'_THIS_').parentNode.parentNode.getElementsByTagName('AREA')  ;

for(var i = 0;i<areas.length;i++){
  if ( areas[i].getAttribute('type')=='ordinalAxisLabel')
  {
    oCV.getSelectionController().setSelectedChartArea(areas[i]);
    oCV.goDrillManager.rvDrillUp();break;
  }

}

Stick that into the onclick event of an element, and it will drill up on the first ordinal area it finds in your chart.

This should, in theory, work in previous versions of Cognos. It works perfectly in 10.1.1 (switch _THIS_ with the correct namespace (RS for running from Report Studio or _NS_ for running from connection or in a portlet)), but unfortunately I don’t have access to any prior versions to test.

Below is the report XML. You can downgrade this to 10.1 fairly easily by changing /report/9.0/ to /report/8.0/.

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

				<layouts>
					<layout>
						<reportPages>
							<page name="Page1">
								<style>
									<defaultStyles>
										<defaultStyle refStyle="pg"/>
									</defaultStyles>
								</style>
								<pageBody>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="pb"/>
										</defaultStyles>
									</style>
									<contents><v2_combinationChart maxHotspots="10000" refQuery="Query1" name="MyChart">
								<v2_combinationTypeTooltips/>
								<v2_legend>
									<v2_legendPosition>
										<v2_legendPreset/>
									</v2_legendPosition>
									<v2_legendTitle refQuery="Query1">
										<v2_chartTextContents>
											<v2_automaticText/>
										</v2_chartTextContents>
										<style>
											<defaultStyles>
												<defaultStyle refStyle="lx"/>
											</defaultStyles>
										</style>
									</v2_legendTitle>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="lg"/>
										</defaultStyles>
									</style>
								</v2_legend>
								<v2_commonAxis>
									<v2_ordinalAxis>
										<v2_axisTitle refQuery="Query1">
											<v2_chartTextContents>
												<v2_automaticText/>
											</v2_chartTextContents>
											<style>
												<defaultStyles>
													<defaultStyle refStyle="at"/>
												</defaultStyles>
											</style>
										</v2_axisTitle>
										<v2_axisLine lineWeight="0"/>
										<v2_axisLabels>
											<style>
												<defaultStyles>
													<defaultStyle refStyle="al"/>
												</defaultStyles>
											</style>
										</v2_axisLabels>
									</v2_ordinalAxis>
									<chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Year"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></v2_commonAxis>
								<v2_topLeftAxis>
									<v2_combinationChartTypes>
										<v2_bar borders="show">
											<v2_solidPalette>
												<v2_solidPaletteEntries>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#8599D3">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#8599D3"/>
																<v2_gradientColor colorPosition="100" gradientColor="#5876AE"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#E3AE6C">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#E3AE6C"/>
																<v2_gradientColor colorPosition="100" gradientColor="#CD854E"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#839862">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#839862"/>
																<v2_gradientColor colorPosition="100" gradientColor="#6C7F56"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#B7C873">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#B7C873"/>
																<v2_gradientColor colorPosition="100" gradientColor="#AFB885"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#8484A8">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#8484A8"/>
																<v2_gradientColor colorPosition="100" gradientColor="#525E7E"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#C0CCED">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#C0CCED"/>
																<v2_gradientColor colorPosition="100" gradientColor="#B0C2E5"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#8C5580">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#8C5580"/>
																<v2_gradientColor colorPosition="100" gradientColor="#794067"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#C789BC">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#C789BC"/>
																<v2_gradientColor colorPosition="100" gradientColor="#BB72BC"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#D5BAEF">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#D5BAEF"/>
																<v2_gradientColor colorPosition="100" gradientColor="#C29FD1"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#83683F">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#83683F"/>
																<v2_gradientColor colorPosition="100" gradientColor="#604926"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#DCB05A">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#DCB05A"/>
																<v2_gradientColor colorPosition="100" gradientColor="#C09C52"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#F4DF9E">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#F4DF9E"/>
																<v2_gradientColor colorPosition="100" gradientColor="#E4CF87"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#5F8A8C">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#5F8A8C"/>
																<v2_gradientColor colorPosition="100" gradientColor="#537579"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#96C4B2">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#96C4B2"/>
																<v2_gradientColor colorPosition="100" gradientColor="#89B0A0"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#CBE8E7">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#CBE8E7"/>
																<v2_gradientColor colorPosition="100" gradientColor="#BDD6D5"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#AE6564">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#AE6564"/>
																<v2_gradientColor colorPosition="100" gradientColor="#875352"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#D88C6F">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#D88C6F"/>
																<v2_gradientColor colorPosition="100" gradientColor="#C47D61"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#E3C9B0">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#E3C9B0"/>
																<v2_gradientColor colorPosition="100" gradientColor="#D2B2A5"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#848484">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#848484"/>
																<v2_gradientColor colorPosition="100" gradientColor="#555555"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#a4a4a4">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#a4a4a4"/>
																<v2_gradientColor colorPosition="100" gradientColor="#909090"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
													<v2_solidPaletteEntry>
														<v2_fillEffect defaultColor="#C7C7C7">
															<v2_linearGradient gradientAngle="0">
																<v2_gradientColor colorPosition="0" gradientColor="#C7C7C7"/>
																<v2_gradientColor colorPosition="100" gradientColor="#c1c1c1"/>
															</v2_linearGradient>
														</v2_fillEffect>
													</v2_solidPaletteEntry>
												</v2_solidPaletteEntries>
											</v2_solidPalette>
											<!-- v2_solidPaletteRef ref="gDefaultSolid"/ -->
										<chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Product line"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></v2_bar>
									</v2_combinationChartTypes>
									<v2_axis>
										<v2_axisTitle refQuery="Query1">
											<v2_chartTextContents>
												<v2_automaticText/>
											</v2_chartTextContents>
											<style>
												<defaultStyles>
													<defaultStyle refStyle="at"/>
												</defaultStyles>
											</style>
										</v2_axisTitle>
										<v2_axisLine lineWeight="0"/>
										<v2_axisRange>
											<v2_automaticRange/>
										</v2_axisRange>
										<v2_axisLabels>
											<style>
												<defaultStyles>
													<defaultStyle refStyle="al"/>
												</defaultStyles>
											</style>
										</v2_axisLabels>
										<v2_majorGridlines lineWeight="0" lineColor="#CCCCCC"/>
										<v2_majorBackgroundColors>
											<v2_firstBackgroundColor color="#D2D2D2" transparency="50"/>
											<v2_secondBackgroundColor color="#E2E2E2" transparency="50"/>
										</v2_majorBackgroundColors>
									</v2_axis>
								</v2_topLeftAxis>
								<style>
									<defaultStyles>
										<defaultStyle refStyle="ch"/>
									</defaultStyles>
								</style>
								<noDataHandler>
									<contents>
										<block>
											<contents>
												<textItem>
													<dataSource>
														<staticValue>No Data Available</staticValue>
													</dataSource>
													<style>
														<CSS value="padding:10px 18px;"/>
													</style>
												</textItem>
											</contents>
										</block>
									</contents>
								</noDataHandler>
								<v2_defaultChartMeasure refDataItem="Revenue"/></v2_combinationChart>
							<HTMLItem>
			<dataSource>
				<staticValue>&lt;input type="button" onclick="
var oCV = window['oCV'+'_THIS_'].getRV().getCV()
  , areas = oCV.getLayoutElementFromLid('MyChart'+'_THIS_').parentNode.parentNode.getElementsByTagName('AREA')  ;

for(var i = 0;i&lt;areas.length;i++){
  if ( areas[i].getAttribute('type')=='ordinalAxisLabel')
  {
    oCV.getSelectionController().setSelectedChartArea(areas[i]);
    oCV.goDrillManager.rvDrillUp();break;
  }

}
" value="Drill up chart"/&gt;</staticValue>
			</dataSource>
		</HTMLItem><list horizontalPagination="true" name="List1" refQuery="Query1">

			<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>
				<CSS value="border-collapse:collapse"/>
				<defaultStyles>
					<defaultStyle refStyle="ls"/>
				</defaultStyles>
			</style>
		<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><HTMLItem description="drillUp">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_up.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillUp();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem><HTMLItem description="drillDown">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_down.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillDown();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents></listColumnBody></listColumn></listColumns></list><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
			<crosstabCorner>
				<contents/>
				<style>
					<defaultStyles>
						<defaultStyle refStyle="xm"/>
					</defaultStyles>
				</style>
			</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>
				<CSS value="border-collapse:collapse"/>
				<defaultStyles>
					<defaultStyle refStyle="xt"/>
				</defaultStyles>
			</style>
		<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><HTMLItem description="drillUp">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_up.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillUp();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem><textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem description="drillDown">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_down.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillDown();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><HTMLItem description="drillUp">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_up.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillUp();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem><textItem><dataSource><memberCaption/></dataSource><reportDrills><reportDrill name="Drill-Through Definition1"><drillLabel><dataSource><staticValue/></dataSource></drillLabel><drillTarget method="execute" showInNewWindow="true"><reportPath path="CAMID(&quot;OpenDJ:u:cn=administrator&quot;)/folder[@name='My Folders']/report[@name='test drill down']"><XMLAttributes><XMLAttribute name="ReportName" value="test drill down" output="no"/><XMLAttribute name="class" value="report" output="no"/></XMLAttributes></reportPath></drillTarget></reportDrill></reportDrills><style><defaultStyles><defaultStyle refStyle="hy"/></defaultStyles></style></textItem><HTMLItem description="drillDown">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_down.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillDown();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><HTMLItem description="drillUp">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_up.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillUp();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem><textItem><dataSource><cellValue/></dataSource></textItem><HTMLItem description="drillDown">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_down.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillDown();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><defaultMeasure refDataItem="Revenue"/><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Region" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><HTMLItem description="drillUp">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_up.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillUp();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem><textItem><dataSource><memberCaption/></dataSource></textItem><HTMLItem description="drillDown">
			<dataSource>
				<staticValue>&lt;img src="..halimagesbtn_arrow_down.gif" onclick="var oCV = window['oCV'+'_THIS_'].getRV().getCV();oCV.getSelectionController().selectNode(this.parentNode);oCV.goDrillManager.rvDrillDown();" onmouseover="this.style.cursor='pointer'"/&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></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><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="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><dataItemLevelSet name="Region"><dmLevel><LUN>[sales_and_marketing].[Retailers].[Retailers].[Region]</LUN><itemCaption>Region</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Retailers]</DUN><itemCaption>Retailers</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Retailers].[Retailers]</HUN><itemCaption>Retailers</itemCaption></dmHierarchy></dataItemLevelSet></selection></query></queries><reportName>test drill down</reportName><drillBehavior drillUpDown="true" modelBasedDrillThru="true"/></report>

10, Javascript

Manipulate drill through definitions with JavaScript

May 27, 2013March 25, 2014 | cognospaul

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.
drillthrough HTML
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]-&amp;gt;:[PC].[@MEMBER].[991]"
        displayValue="Camping Equipment"/>
      <drillParameter
        name="Year"
        value="[sales_and_marketing].[Time].[Time].[Year]-&amp;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(&quot;i326070F7595B4B5BBCD38D945E66AAEB&quot;)</item><item xsi:type="bus:searchPathSingleObject">storeID(&quot;iDB36CF1A0D654E99B3117B0B6A7F8789&quot;)/model[last()]</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Cubes&apos;]/package[@name=&apos;Sales and Marketing (cube)&apos;]/model[@name=&apos;2008-07-25T15:28:38.072Z&apos;]</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Cubes&apos;]/package[@name=&apos;Sales and Marketing (cube)&apos;]/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(&quot;i326070F7595B4B5BBCD38D945E66AAEB&quot;)</item><item xsi:type="bus:searchPathSingleObject">storeID(&quot;iDB36CF1A0D654E99B3117B0B6A7F8789&quot;)/model[last()]</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Cubes&apos;]/package[@name=&apos;Sales and Marketing (cube)&apos;]/model[@name=&apos;2008-07-25T15:28:38.072Z&apos;]</item><item xsi:type="bus:searchPathSingleObject">/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Cubes&apos;]/package[@name=&apos;Sales and Marketing (cube)&apos;]/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.
radios can change the drill

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>&lt;script&gt;
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&lt;drillsObj.length;i++){
   if( drillsObj[i].m_label == drillName) drillsObj[i][attribute] = newValue;
  }
}

  &lt;/script&gt;
	</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>&lt;script&gt;
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;
  }
);

&lt;/script&gt;</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>

CognosPaul

Category: Javascript

Automatically looping through charts in Active Reports

Javascript drill up and down buttons

Manipulate drill through definitions with JavaScript