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 "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>
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
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.
Good technique. How does it work if I have 5 prompts on a page which I want to tag to the list/crosstab? Do I end up in creating 5 diff columns with the scripts and HTML items?
The updated version has better support for multiple lists on a single page. You still need to use multiple HTML items though.
if we want to use the selected row values in the filter than what we have to use..
Vipul,
The technique works by clicking on the member in the prompt that you’ve dragged in. So you drag in the value prompt, set it up as a checkbox, then drag in the list and HTML items. In your queries you’d use the parameter from your checkbox prompt.
Thanks Paul,
Such a great and simple technique….
Paul,
Is it possible to deselect the checkbox which by default is selected in a date & time prompt when the filter is set to optional.
Sorry for the delay, your comment got caught by the spam catcher. I only now started going through it.
It’s essentially a matter of trying to identify the checkbox dom element. Which version of Cognos are you using?
Hi Paul,
Is it possible adding check boxes for Repeater table .My scenario is the reports page dispalys the demographical information on reports page based on selection of prompt value(ex by zip code )
Search results would be like 20 hospitals.
Next we have to implement the comparison function for up to 3 hospitals.
Need to add check box to select the 3 hospitals and do the comparison on there services.
How to add the check box on report result page to select 3 hospitals ?
And after the selection when we click on compare results button, the next result page will display 3 hospital data results in side by side to compare.
Can you help me to implement this.
Sailu, it sounds like you just want to change the data container for the checkbox list to a repeating table? In theory it should work the same way. What happens when you try it?
Paul – you are extremely powerful.. You never say no and is always optimistic.. Great to know that you share your expertise and skills to others. Very few people do.. I truly appreciate.
i need to select top 6 countries (top 6 determined by total count of suverys) by default when the prompt page is loaded. can this be possible using the above approach. Please advice.
This method isn’t so great for a request like that. If you’re on 10.2, your best bet would be to create a hidden value prompt that only returns those 6 countries. Then you could use the Prompt API to do something like paulScripts.getControl(‘Countries’).setValues(paulScripts.getControl(‘DefaultCountries’)).getValues(true))
So in the query feeding the DefaultCountries control, you would have the query filtering on the rank of countries by survey count, while the Countries control contains all of the Countries.
Hi ,
I am using exact Code for displaying additional values in check box, However i have 2 new requirements here.
1)Need a button to check all the values
2)Exporting the selected values in list to excel
Can you help me here!!!
Did you look at the updated version? https://cognospaul.com/2013/05/16/checkbox-list-prompts-revisited I’m pretty sure I added select all links. Simply selecting the values and exporting to Excel won’t work as the parameter values have not been submitted. Use the technique here: https://cognospaul.com/2014/03/20/passing-parameters-without-refreshing-page/ It will collect the values from the hidden prompt and use them to open your target report. Just set the output format to spreadsheetML.