Tag: prompts

Javascript, OLAP, Prompts

Accessing Tree Prompts with Javascript (on Cognos 10.1.1)

| cognospaul

There are many reasons why you may want to interact with a Tree Prompt with JavaScript. Maybe you want to enable the finish button if a member on the bottom level is selected, or to select the last member, or to ensure only 5 members are selected.

This post isn’t to detail every possible scenario, but to detail the some functions available and how to use them. It’s important to note that I am hardly a JavaScript expert, so there may be better ways to do anything I say here.

First you need to identify your tree prompt. Unlike most prompt controls, where the identifier changes based on the type viewer being used, the tree prompt can be called using window.treePROMPTNAME. Unfortunately we can’t apply an onmousedown event to the tree prompt, so we have to wrap it in a div.

Createa tree prompt and give it the name “Time”. Drag an HTML item to the left of the tree prompt

<div id='myTree'>

and an HTML item to the right

</div>

.

Now we can attach an event to capture the clicks:

<script>
document.getElementById('myTree').onmousedown=function(){runTree('Time')};
</script>

Any click inside that div will now trigger the runTree function passing ‘Time’ as an argument.

Because there are a number of JavaScript functions are run upon selecting an element we can’t immediately get the value of the element. So we can use the setTimeout function to wait 200 milliseconds before getting the data.

<script>
function runTree(id)
{
 t=setTimeout('checkTree("'+id+'")',200);
}
</script>

After 200 milliseconds the checkTree function will run, also passing Time as the argument.

<script>
function checkTree(id)
{
  selectedTreeNode  = window['tree'+id].getLastSelectedNode();
  if(!selectedTreeNode) {return}
  alert(selectedTreeNode.getName());
  alert(selectedTreeNode.getValue());
  alert(selectedTreeNode.getLevel());

}
</script>

The checkTree function will now alert the selected elements Name, MUN, and Tree level. Note the Tree Level is from the tree prompt, not the member’s hierarchy level. But knowing these, we can then call other functions. You could check the level number of the selected element and enable or disable the tree prompt while popping up a message.

You can programmatically set the default value of the tree prompt using JavaScript. Unfortunately it appears it is only possible to do this on the first level.

<script>
var node = window.treeTime.getRootNode().getChildren()[window.treeTime.getRootNode().getChildren().length-1];
node.setSelected(true);
node.updateNodeSelection();
node.updateParent();
window.treeTime.setLastSelectedNode(node);
</script>

This will only effect the prompt after the page has been loaded. Prompt pages should be fine, but prompts on the report page will need to have a default value set in the prompt macro.

I learned about these functions by going through the js file associated with tree prompts. Check out ..webcontentpromptingCTreeIE5NS6.js for more Tree Prompt functions.

It worth noting that these functions are written by IBM, and are liable to change on upgrade. I’d be interested in hearing if these work in any of the previous versions of Cognos.

8.4, Javascript, Prompts

Checkbox List Prompt

| cognospaul

This technique has been updated here: https://cognospaul.com/2013/05/16/checkbox-list-prompts-revisited/

The examples in this post are based on the GO Sales (query) package in Cognos 8.4.1.

Occasionally I receive a request is to create a checkbox prompt that provides additional information. The users might want a list that shows, for instance, the total revenue for each item in the prompt.

As opposed to the standard checkbox prompts, embedding the checkbox into a list gives the user additional insights. With the measures immediately visible, the user knows exactly what to select. Outliers may be highlighted, tooltips can be embedded describing each row. For example, a report that details production costs may be narrowed by this type of prompting approach that allows a user to easily identify low profit margin product lines where efforts on cost cutting may have the greatest return.

This is done with JavaScript (it may also be possible with jQuery, and I invite any jQuery experts to adapt it) so all of the normal warnings apply.

To begin create an HTML item. Put Scripts in the description and copy/paste the following into it:

<script>
function selectInCheckbox(id)
{
   var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
   for (var i=0;i<inputs.length;i++)
   {
        if (inputs[i].value == id)
		{
			inputs[i].click();
        }
   }
}

function selectInCheckboxRow(id)
{
	var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
	inputs[id].click();
}
</script>

Now create a normal checkbox prompt. This will hold the prompt, the checkboxes in the list will simply check the corresponding checkbox in this prompt. Place an HTML to the left:

<div id="checkbox1" style="display:none;">

and close the div with another HTML item to the right:

</div>

The display:none hides the prompt, but you may want to remove that bit until everything works.

Next create a list. The list should have the USE value of the prompt in the properties, and the display and any associated measures in the body. Drag in an HTML item to the list. It should create it’s own column. Unlock the report and drag in 4 more HTML items.

Change the description of each HTML item so it looks like the following:

Checkbox Start (Source Type = Text):

<input type="checkbox"

Checked (Source Type = Report Expression):

case when (ParamValue('code') +', ') contains (number2string([Product line code]) +', ') then (' checked ') else ('') end

onClick (Source Type = Text):

onClick="selectInCheckboxRow('

Row (Source Type = Report Expression):

RowNumber ()

CheckboxEnd (Source Type = Text):

');">

This will use the row number of the list and check the corresponding row in the checkbox prompt. Obviously the checkbox prompt and the list will needed to be sorted exactly the same way.

Alternately you could use the code of the line and loop through the checkbox prompt and check the box that matches the code. To do that change the onClick HTML Item to selectInCheckbox and change the Source Type of the Row HTML Item to use Data Item Value and the data item of the code.

Below is the XML of an example report using this method.

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="he" ignoreFilterContext="false"><!--RSU-SPC-0093 The report specification was upgraded from &quot;http://developer.cognos.com/schemas/report/3.0/&quot; to &quot;http://developer.cognos.com/schemas/report/6.0/&quot; at 2010-12-23. 15:33:53--><!--RS:8.2-->
	<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
	<layouts>
		<layout>
			<reportPages>
				<page name="Page1">
					<pageBody>
						<contents><HTMLItem description="Styles">
								<dataSource>
									<staticValue>&lt;style type="text/css"&gt;

input.updatebutton
{
   font-size:11px;
   font-weight:bold;
   width:140px;
   height:27px;
   color:#000000;
   background-color:#cdc9c9;
   border-style:solid;
   border-color:#003377;
   border-width:3px;
}

input.matchbutton
{
   font-size:11px;
   font-weight:bold;
   width:140px;
   height:27px;
   color:#EEFFFF;
   background-color:#0088DD;
   border-style:solid;
   border-color:#003377;
   border-width:3px;
}

input.selectbutton
{
   font-size:9px;
   font-weight:bold;
   width:60px;
   height:20px;
   color:#000000;
   background-color:#FFFFFF;
   border-style:solid;
   border-color:#FFFFFF;
   border-width:3px;
}

&lt;/style&gt;

&lt;script language="javascript"&gt;

function goLite(FRM,BTN)
{
   window.document.forms[FRM].elements[BTN].style.color = "#FFFF99";
   window.document.forms[FRM].elements[BTN].style.backgroundColor = "#11AAEE";
}

function goDim(FRM,BTN)
{
   window.document.forms[FRM].elements[BTN].style.color = "#EEFFFF";
   window.document.forms[FRM].elements[BTN].style.backgroundColor = "#0088DD";
}

&lt;/script&gt;
</staticValue>
								</dataSource>
							</HTMLItem>
							<HTMLItem description="Scripts">
								<dataSource>
									<staticValue>&lt;script&gt;
function selectInCheckbox(id)
{
   var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
   for (var i=0;i&lt;inputs.length;i++)
   {
        if (inputs[i].value == id)
		{
			inputs[i].click();
        }
   }
}

function selectInCheckboxRow(id)
{
	var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
	inputs[id].click();
}
&lt;/script&gt;</staticValue>
								</dataSource>
							</HTMLItem>

							<promptButton type="reprompt">
			<contents/>
			<style>
				<defaultStyles>
					<defaultStyle refStyle="bp"/>
				</defaultStyles>
			</style>
		</promptButton><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><HTMLItem description="div">
			<dataSource>
				<staticValue>&lt;div id="checkbox1" style="display:none"&gt;</staticValue>
			</dataSource>
		</HTMLItem><selectValue parameter="code" multiSelect="true" range="false" required="false" selectValueUI="checkboxGroup" refQuery="Prompt"><useItem refDataItem="Product line code"/><sortList><sortItem refDataItem="Product line code"/></sortList></selectValue><HTMLItem description="/div">
			<dataSource>
				<staticValue>&lt;/div&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents><style><CSS value="vertical-align:top"/></style></tableCell><tableCell><contents><HTMLItem description="div id=&quot;list&quot;">
																<dataSource>
																	<staticValue>&lt;div id="list"&gt;</staticValue>
																</dataSource>
															</HTMLItem><list horizontalPagination="true" name="List2" refQuery="Prompt">

			<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><staticValue>HTML Item</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><HTMLItem description="Checkbox Start">
																					<dataSource>
																						<staticValue>&lt;input title="Faster but if the sorting is off then this won't work" type="checkbox"
</staticValue></dataSource>
																				</HTMLItem><HTMLItem description="checked">
			<dataSource>

			<reportExpression>case when (ParamValue('code') +', ') contains (number2string([Product line code]) +', ') then (' checked ') else ('') end</reportExpression></dataSource>
		</HTMLItem><HTMLItem description="onClick">
			<dataSource>
				<staticValue>onClick="selectInCheckboxRow('</staticValue>
			</dataSource>
		</HTMLItem><HTMLItem description="Row">
			<dataSource>

			<reportExpression>RowNumber ()</reportExpression></dataSource>
		</HTMLItem><HTMLItem description="CheckboxEnd">
																					<dataSource>
																						<staticValue>');
"&gt;
</staticValue>
																					</dataSource>
																				</HTMLItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>By Code</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><HTMLItem description="Checkbox Start">
																					<dataSource>
																						<staticValue>&lt;input title="Slower but less prone to error" type="checkbox"
</staticValue></dataSource>
																				</HTMLItem><HTMLItem description="checked">
			<dataSource>

			<reportExpression>case when (ParamValue('code') +', ') contains (number2string([Product line code]) +', ') then (' checked ') else ('') end</reportExpression></dataSource>
		</HTMLItem><HTMLItem description="onClick">
			<dataSource>
				<staticValue>onClick="selectInCheckbox('</staticValue>
			</dataSource>
		</HTMLItem><HTMLItem description="Code">
			<dataSource>

			<dataItemValue refDataItem="Product line code"/></dataSource>
		</HTMLItem><HTMLItem description="CheckboxEnd">
																					<dataSource>
																						<staticValue>');
"&gt;
</staticValue>
																					</dataSource>
																				</HTMLItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Gross profit"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Gross profit"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><propertyList><propertyItem refDataItem="Product line code"/></propertyList><sortList><sortItem refDataItem="Product line code"/></sortList></list><HTMLItem description="/div">
																<dataSource>
																	<staticValue>&lt;/div&gt;</staticValue>
																</dataSource>
															</HTMLItem><HTMLItem description="doAll">
														<dataSource>
															<staticValue>&lt;script&gt;
function checkAll(){
var inputs=document.getElementById('list').getElementsByTagName('input')
for (var i=0;i&lt;inputs.length;i++){
        if (inputs[i].type == 'checkbox') {
            if (inputs[i].checked == true)
		{}
		else {inputs[i].click();}
        }
}
}

function unCheckAll(){
var inputs=document.getElementById('list').getElementsByTagName('input')
for (var i=0;i&lt;inputs.length;i++){
        if (inputs[i].type == 'checkbox') {
            if (inputs[i].checked == true)
		{inputs[i].click();}
		else {}
        }
}
}
&lt;/script&gt;

&lt;input type="button" value="Select All" class="selectbutton" onmouseover = "this.style.cursor='hand'" onclick="checkAll();"&gt;
&lt;input type="button" value="Clear All" class="selectbutton" onmouseover = "this.style.cursor='hand'" onclick="unCheckAll();"&gt;</staticValue>
														</dataSource>
													</HTMLItem></contents><style><CSS value="vertical-align:top"/></style></tableCell></tableCells></tableRow></tableRows></table><block>
								<contents/>
							</block>
						<combinationChart showTooltips="true" maxHotspots="10000" name="Combination Chart1" refQuery="Report">
								<legend>
									<legendPosition>
										<relativePosition/>
									</legendPosition>
									<legendTitle refQuery="Report">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="lx"/>
											</defaultStyles>
										</style>
									</legendTitle>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="lg"/>
										</defaultStyles>
									</style>
								</legend>
								<ordinalAxis>
									<axisTitle refQuery="Report">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</ordinalAxis>
								<numericalAxisY1>
									<axisTitle refQuery="Report">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<gridlines color="#cccccc"/>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</numericalAxisY1>
								<combinationChartTypes>
									<bar><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Revenue"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></bar>
								</combinationChartTypes>
								<style>
									<defaultStyles>
										<defaultStyle refStyle="ch"/>
									</defaultStyles>
								</style>
								<commonClusters><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Product type"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></commonClusters><conditionalRender refVariable="renderGraph"><renderFor refVariableValue="1"/></conditionalRender></combinationChart>
						</contents>
					<style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style></pageBody>
				<style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style></page>
			</reportPages>
		</layout>
	</layouts>
<queries>

	<query name="Prompt">
			<source>
				<model/>
			</source>
			<selection><dataItem name="Product line code" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line code]</expression></dataItem><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression></dataItem><dataItem name="Gross profit" aggregate="total"><expression>[Sales (query)].[Sales].[Gross profit]</expression></dataItem></selection>
		</query><query name="Report"><source><model/></source><selection><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression></dataItem><dataItem name="Product type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product type]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Sales (query)].[Product].[Product line code] in (?code?)</filterExpression></detailFilter></detailFilters></query></queries><XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><reportVariables><reportVariable type="boolean" name="renderGraph">
			<reportExpression>ParamValue('code') is not null</reportExpression>
			<variableValues>
				<variableValue value="1"/>
			</variableValues>
		</reportVariable></reportVariables></report>
8.4, Macros

Token prompts

| cognospaul

Token prompts are an extremely powerful macro. They allow authors to create extremely efficient code that would otherwise be difficult or impossible to make.

The following examples will all be based on the Great Outdoor Sales (cube) package.

A simple example would be a static prompt that would allow the user to see a list the top or bottom products by revenue. Without a token prompt you might be tempted to use the following expression:

case #prompt('TopOrBottom','string',sq('top'))#
when 'top' then topCount([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])
when 'bottom' then bottomCount([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])
end

The problem with that is that it simply doesn’t work. You could also try to make a conditional block, but that would make the report needlessly complex.

Instead you could use a token prompt:
#prompt('TopOrBottom','token','top')#Count([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])

When the macro resolves the function will be either topCount or bottomCount.