JavaScript to Combine Multiple Prompt Values

[jeff_rob]

Hi,
I need to have a prompt that is a simple choice of Company or Non-Company in a check box or list value prompt.  The value sent to the database for Company is only one code, but there are multiple codes that comprise Non-Company.  The problem is how to pass multiple codes for one prompt selection.

I tried putting multiple codes in the Use field of Static Choices (i.e. N','A','C ), leaving off the end single quotes since Cognos supplies those, but the single quote is an escape character in the SQL Server database and the query sent to the database (viewed using the Profiler) contains multiple single quotes for each supplied single quote in the Use field.

There is JavaScript code in a Cognos Proven Practices document that shows how to put multiple values in a text box prompt from the prompt page and pass it to a hidden value prompt, but we need for the visible prompt to be a value prompt for the obvious reason that you wouldn't want users trying to type in all those codes.

http://www.ibm.com/developerworks/data/library/cognos/reporting/scripting_techniques/page516.html

I tried the example report and it works great, but my attempts at modifying the code to use a value prompt with space separated values in the Use field instead of a text box prompt bombs completely.

Does anyone know how to do this?  It seems like there should be a way.  I searched and found a number of JavaScript posts related to prompts, but nothing that would help.

Here's the original text box to value prompt code:

Code Select Expand

<script language="javascript">
var fW = (typeof getFormWarpRequest == "function" ?
getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ?
formWarpRequest_THIS_ : formWarpRequest_NS_ );}

var var1=fW._textEditBoxid2; //The visible text prompt
var var2=fW._oLstChoicesid3; //The hidden multi-select value prompt

function SplitText()
{
if ((var1.value=='')|| (var1.value==null)) //Check to see if values were entered
{
alert("No values were entered! Please enter some values.");
}
else
{
var text_array=var1.value.split(" ");
// Break out the value entered in the text prompt using the space as a separator
var numberofsplits=0;

while (numberofsplits < text_array.length)
{
for (var i=0;i < var2.length; i++)
//Go through every value in the value prompt
{
if(var2[i].value==text_array[numberofsplits])
//if the value from the text prompt is
//present in the value prompt select it
{
var2[i].selected=true;
}
}
numberofsplits++;
}
promptButtonFinish();
}
}
</script>

Any help will be greatly appreciated.  My knowledge of JavaScript can be easily put in a thimble.

[bdbits]

I don't think you really need javascript at all. Just create a static value prompt, then use a case statement in the report query to build a filter based on the user's choice.

[jeff_rob]

Yes, you are absolutely right and I'm using that method currently as a work-around.  It poses a small problem in that I have several custom SQL queries for which the prompt then becomes mandatory and sends fully loaded filters to the database for the required default.  This is a dashboard which runs six queries and the performance takes a ding with the filters loaded.

I'd love to get around that if possible.