This is a more in depth description of the technique described here :
http://www.dotnet-tips.com/2010/03/multiple-select-values-in-cognos-report.html
I took what was said there and tried it myself and created the following notes:
In some cases you may want a control on the input page that allows the end user to select one or more of a list of values. This involves dropping a Value Prompt insertable object onto the input page and then changing Multi-Select to Yes. You can then select between “Check box group” or “List box” for the Select UI property, either one works, but for this example we will be using “Check box group”.
http://www.dotnet-tips.com/2010/03/multiple-select-values-in-cognos-report.html
I took what was said there and tried it myself and created the following notes:
In some cases you may want a control on the input page that allows the end user to select one or more of a list of values. This involves dropping a Value Prompt insertable object onto the input page and then changing Multi-Select to Yes. You can then select between “Check box group” or “List box” for the Select UI property, either one works, but for this example we will be using “Check box group”.
When first attempting to do this you would think by associating a parameter with the multi-select control then the selected values would be returned in that parameter. Then the question is, returned as what type? Does it return a String array? Or does it return a string of values that are delimited, maybe by commas? The answers are a String and no and no. It is not an array and it only returns the first selected value. Cognos is ”smart” (yes that was sarcasm) in that it will not display a multi-select control when the report is run unless the parameter being selected against is actually used in a multi-select manner, meaning in an ‘IN’ statement. It does not give you any feedback on this behavior, so despite having Multi-Select set to Yes it will continue to render a single select control when you run your report (if a parameter is specified) until you explicitly use the parameter in an ‘IN’ statement.
Here is my note on this issue:
- If you need to create an input page that requires a multi select there is a quirk that can be quite annoying. If, despite having chosen Multi-Select for a Value Prompt control, it renders as single select, then you need to look at the input parameter against which you are selecting and have one of your output parameters limit themselves against that input parameter using the IN clause. That will clue Cognos into the fact that it is not a single value parameter. Of course in our case, meaning the one of using an Info*Engine task as the data source we will most likely do the filtering against the input parameter ourselves in the IE query as opposed to having Cognos do it for us, so this becomes simply a kludge to get the control to render properly. So for example, let’s assume we want to filter our query based on the workflow state of the change tasks we are looking up. We output the change tasks to our report through the “taskState” parameter that is in our query. And the input parameter that the user selects values against is called “chgTaskState”. So in order for the multi select to work we must add the “IN” clause to the query parameter like so:
[com.ptc.windchill.enterprise.report.ReportTask].[IEQueryTaskNameDataSource].[taskState] IN ?chgTaskState?
So despite having done that when reading the parameter in your Info*Engine task you will only get the first selected value. This is a big problem and one that took a while to figure out how to solve. As I said, I would have expected it to return a String array (aka String[]) but there is no way to set the type of the parameters in Cognos, they all seem to default to String, at least no way that was obvious to me. So as a hack/work around what we do is create a hidden Text Box Prompt that we then fill with the values when the user clicks on the Finish button. We do this by creating our own Finish button and calling JavaScript code that parses each of the check boxes looking to see if they are checked, then constructing a string that we then populate the hidden text box with. Then by mapping our parameter to the hidden text box we get back a CSV string in our I*E task. Of course we need to remove our parameter from the multi-select control and actually have the parameter set to nothing. Yes that means the whole tirade I made about how to make the Multi-Select show up as a Multi-Select is moot because with this solution we never specify a parameter on the Multi-Select control hence we would never run into the “smartness” of Cognos not rendering it as multi-select.
So to recap the solution is to create a hidden text box and populate it with the selection made against the multi-select control by executing JavaScript code when the user clicks on the Finished button. Since the hidden text box is mapped to the parameter we care about we will get a CSV string as output from running the report, which we can then parse as we see fit in our code. Below I have included an annotated screen shot of the Input Page for a report that does multi-select. The technique involves using the HTML Item insertable object in the proper places and putting in our own HTML and JavaScript code. The screen shot shows where we use the HTML div tag to provide IDs that we can use in our JavaScript to find the multi-select and text box controls. It also shows the custom Finish button. Note that the original Finish button was deleted.
The HTML Item circled in red is the JavaScript that implements the assignParamValue function that is called by the finish button. The source is listed here as it was too large to fit in the picture:
<script>
function assignParamValue()
{
// get the reference to the Cognos form
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if(fW == null)
alert('Failed to get a reference to the Cognos form');
var i; // for loop indexing
// get the handle for the checkbox prompt - added div around them to distinguish
var multiSelectCtrl = document.getElementById('multiSelectCtrl');
var csvStr = "";
// find all the children of the div of type checkboxes
for (i=0; i < multiSelectCtrl.childNodes.length; i++)
{
var node_list = multiSelectCtrl.getElementsByTagName('input');
for (var i = 0; i < node_list.length; i++)
{
var node = node_list[i];
if (node.getAttribute('type') == 'checkbox')
{
if (node.checked)
{
if( csvStr == "" )
{
csvStr = node.value;
}
else
{
csvStr = csvStr + "," + node.value;
}
//alert("csvStr ="+csvStr);
}
}
}
}
var hiddenTextBoxCtrl=document.getElementById('hiddenTextBoxCtrl');
if(hiddenTextBoxCtrl== null)
alert('hiddenTextBoxCtrl is null');
else
{
for (i=0; i < hiddenTextBoxCtrl.childNodes.length; i++)
{
var node_list = hiddenTextBoxCtrl.getElementsByTagName('input');
for (var i = 0; i < node_list.length; i++)
{
var node = node_list[i];
if (node.getAttribute('type') == 'text')
{
node.value = csvStr;
}
}
}
}
//canSubmitPrompt();
promptButtonFinish();
}
</script>
Note that canSubmitPrompt() was causing errors but I am seeing issues with Required fields that I think are related to this.
