Checkbox List Prompt

This technique has been updated here: http://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>

Remove prompt title with javascript

It seems that most people find this blog looking for a way to remove the title from a value prompt. So, in an attempt not to disappoint, here it is.

For 8.3+
Name your value prompt, Month for example. Immediately following the prompt add the following HTML item:
var form = getFormWarpRequest();
var MonthList = form._oLstChoicesMonth;
MonthList.remove(0);
MonthList.remove(0);
MonthList.removeAttribute(“hasLabel”);

As this is using underlying Cognos javascript functions it may not be supported in an upgrade. The removeAttribute(“hasLabel”) is important to remember. Normally the value prompt has two rows at the beginning that have no data. The hasLabel attribute tells Cognos not to return a value when either of those two rows are selected.

Macros (3 of n++): Prompts

Macros allow you to alter SQL/MDX before it’s send to the datasource. Using the macro functions timestampmask, you can easily select specific members, or query certain tables.

Prompt macros give you an extremely fine grain of control. You can, for instance, allow a user to which measure he wants to see in his graph, or create dynamic default values.

To begin with, the structure of the prompt macro:

#prompt/promptmany(‘parameterName’,’dataType’,’defaultValue’,’leadingText’,’source’,’trailingText’)#

parameterName is self explanatory. Note that it is case sensitive. If you want to pass a value to that parameter via the URL or POST you need to preface it with a p_ so Par_Year becomes p_Par_Year.

The dataType will reject anything not covered by the datatype. The string ‘Hello World!’ would be rejected if the datatype was set to integer. The datatype also determines the type of prompt generated. ‘Integer’, for example, will always create a text box prompt even if the source field (see below) is filled.

The defaultValue will be returned if no parameter is selected. This does not need to obey the dataType. For example, you could have a filter set up: [Year] = #prompt(‘parYear’,’integer’,’year(getDate())’)# If 2010 was selected the filter would be [Year] = 2010, if no value was returned to the prompt the filter would be [Year] = year(getDate()).

The leadingText is text that is automatically entered before the selected parameter. For example a filter: #prompt(‘parYear’,’integer’,’1=1′,'[Year] = ‘)# In that example if 2010 was selected [Year] = 2010 would be returned to the filter. If nothing was selected, the filter would be 1=1.

The source will a list or tree prompt based on a referenced field. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy]’)# will create a tree prompt that will allow the user to select any member from the TimeHierarchy. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy].[MonthLevel]’)# will create a list prompt that will allow the user to select a single member from the MonthLevel. You can also include functions in the source: #prompt(‘timeMember’,’memberuniquename’,”,”,’filter([NS].[TimeDimension].[TimeHierarchy].[MonthLevel],[Measures].[Sales]>1000)’)# will create a list prompt of all months that had more than 1000 sales.

The trailingText, much like the leadingText, placing text after any values selected. #promptmany(‘Years’,’memberuniquename’,’children([NS].[TimeDimension].[TimeHierarchy]->[All Member])’,’set(‘,'[NS].[TimeDimension].[TimeHierarchy].[YearLevel]’,’)’)# will either return the children of the All member (if no values are selected), or will return a set of members that the user selected.