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?) + '%'
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.
I have tried using IN and I get the same result.
Thanks,
Wayne
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.
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.
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.