If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Multi-select prompt not functioning properly

Started by wbarry, 29 Oct 2007 01:13:12 PM

Previous topic - Next topic

wbarry

I have built the following filter as a wildcard serach.  On the prompt page I have used a text box prompt and have changed the multi-select property to "Yes".  My problem is that this prompt has to be optional and I have to be able to add multiple values.  When I run the report, the prompt is optional but it does not allow for multiple values.  Anybody have any ideas?  I am on Cognos 8 using Report Studio.

upper([TA ].[Data].[Registration Line 1] +[TA].[Data].[Registration Line 2]+ [TA ].[Data].[Registration Line 3]+ [TA].[Data].[Registration Line 4]+ [TA].[Data].[Registration Line 5]+ [TA].[Data].[Registration Line 6]) contains '%' + upper(?OFAC?) + '%'
 


COGNOiSe administrator

If it is a multi-select, you are on supposed to use contains, but instead leverage IN (?OFAC?). I fyou need it to be uppercased, do it in the prompt query's values column, not in the filter.

wbarry

I have tried using IN and I get the same result.
Thanks,
Wayne

rockytopmark

I don't think what you are trying to do can be done in a multiselect, with a textbox and contains, as the previous poster alluded to already.

I was able to get a reasonable facsimile, however, of what you want using the Search and Select Prompt.

I used the Wizard, within which I specified the IN clause and I let it build a query to list the distinct values that I am searching on.

For your case, you would have to alter this query's query item that you want to search, to include the Upper()s and the concatenation of all the query items you are searching in.

I feel this is your best option.

nuknsltnt

Wbarry,

I hope I understood your question properly. I had a similar issue couple of years ago.

In my case I had a maximum number of 2 selections, i.e, A, B. The prompt had to be optional.

1) I used a string prompt, wherein I created 3 strings. A, B and A&B.

2) I set the default value to A&B, which meant that if the user did not choose any value, it would default to A&B - meaning everything - therefore optional.

I was able to solve this as my values were limited in number - in this case 2. Also this was done in ReportNet, which had certain limitations for what I was trying to do.


In your case, why don't you try using a CASE statement.

CASE
WHEN upper([TA ].[Data].[Registration Line 1] THEN xxx
WHEN upper([TA ].[Data].[Registration Line 1] +[TA].[Data].[Registration Line 2] THEN yyyy
WHEN upper([TA ].[Data].[Registration Line 1] +[TA].[Data].[Registration Line 2]+ [TA ].[Data].[Registration Line 3]
ELSE
END

I have not tested the syntax, as I am working on Cognos Connection right now. But I have used CASE statement for something similar. This is not a neat solution, but it should work. You will have to maintain the CASE statement if you have to add another Registration Line n

Hope this helps.

Suraj

One thing to check is, even if you have a multi-select prompt, if you use that prompt value to create a data item in a report, it changes to required single-select prompt.