Checkbox List Prompt
March 1, 2011 17 Comments
This technique has been updated here: https://cognospaul.wordpress.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 "http://developer.cognos.com/schemas/report/3.0/" to "http://developer.cognos.com/schemas/report/6.0/" 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><style type="text/css">
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;
}
</style>
<script language="javascript">
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";
}
</script>
</staticValue>
</dataSource>
</HTMLItem>
<HTMLItem description="Scripts">
<dataSource>
<staticValue><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></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><div id="checkbox1" style="display:none"></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></div></staticValue>
</dataSource>
</HTMLItem></contents><style><CSS value="vertical-align:top"/></style></tableCell><tableCell><contents><HTMLItem description="div id="list"">
<dataSource>
<staticValue><div id="list"></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><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>');
">
</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><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>');
">
</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></div></staticValue>
</dataSource>
</HTMLItem><HTMLItem description="doAll">
<dataSource>
<staticValue><script>
function checkAll(){
var inputs=document.getElementById('list').getElementsByTagName('input')
for (var i=0;i<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<inputs.length;i++){
if (inputs[i].type == 'checkbox') {
if (inputs[i].checked == true)
{inputs[i].click();}
else {}
}
}
}
</script>
<input type="button" value="Select All" class="selectbutton" onmouseover = "this.style.cursor='hand'" onclick="checkAll();">
<input type="button" value="Clear All" class="selectbutton" onmouseover = "this.style.cursor='hand'" onclick="unCheckAll();"></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>
Paul's Cognos Blog 
Wat if we need to use radio button and that too autosubmit. is there a method to implement that as well.
Thanks
AvantA
Hi Avanta, a radio prompt should work just as easily. Simply change the input type to “radio” and make sure to give it a common name. Without the common name, the radio won’t move the check.
can we extract the rows to excel based on checked records selection only
This is a difficult request. It is possible, but it may be more difficult than it’s worth.
The way I would do it is to use the cognosLaunchInWindow (I think I posted about that somewhere) method to open the target report in a blank window with format Excel. Use the following string (modified for your needs) to pass only the selected values.
‘&p_Param=’ + fW._oLstChoicesPROMPTNAME.join(‘&p_Param=’)
So if you’ve selected 1, 3, 7 in the prompt it will return &p_Param=1&p_Param=3&p_Param=7
Pingback: A List Prompt From an Actual List « Bob's Business Intelligence Blog
I was going to work on a different solution to this problem, but your technique is much simpler and works perfectly Thanks!
Cheers Bob! Glad to hear that it works. Love your blog by the way.
Got to say that I like this post! Easy to understand and I got the code to work perfectly. I will say that I experience significant performance increase by looping through the checkbox and matching the item based on a unique identifier. The original way by row there was some lag, and I actually had to scrap that as I was doing some grouping in my list that didn’t carry over to the hidden listbox.
Is there any way to modify this so that I can add a checkbox to a grouping? I’ve tried to replicate what you were doing, but even though I can get the checkbox to create for the grouping, when selected it only selects the first ID in the list.
A few notes – I modified it based on your suggestion of looking up a value as opposed to row (since I had grouping). Also, the way our data is structured, the grouping level ID is contained in the same query as the grouped items ID’s. So, I am not looking to check the box on the group and have it check all the indivual items under that group – it is a unique value as well so when I check that group, it would just update one ID in the checkbox prompt. Any help is always appreciated!
A bit late, but better than never. I added grouping capabilities as per your suggestion, and also fixed a bug when trying to pass MUNs as the use value. So now if you create a list prompt using a time dimension, clicking on 2010 will select all the quarters and the months, and clicking on Q1 will then deselect Jan, Feb and Mar.
Pingback: Checkbox List Prompts revisited | Paul's Cognos Blog
Pingback: Checkbox List Prompts revisited
Pingback: Checkbox List Prompts revisited - CognosPaul
hi paul canu send me the original xml its not working
Hi paul,
How can we get select all/unselect and toogle for above list. I see you created Select all button and unselect all button differently. my requirement is to have one checkbox in the list header to select all/unselect all. when user unselects something after selected it should also toggle and remove checkmark from select chekbox
Thanks.
hi Paul,
i dont know why, but whenever i put a multi select value prompt in the page, it displays as a radio button list. i tried changing the parameter in the query thinking that the parameter is forcing the selection to be single select, but nothing seems to help and i always get a radio button list.
is this a normal behaviour?
How to capture the selected values and display a drill through list based on selected ones