Does anyone know how to construct and IN clause for a query filter based on a comma separated list typed into a text box prompt? For example, I have a text box prompt that provides a parameter called IDFilter. I want the user to be able to type in:
one,two,three
into the text box prompt and I then want to do the equivalent of:
select * from names where id in ('one','two','three')
using a filter in a query like:
[ID] in ?IDFilter?
There is no need since Cognos offers a multivalue prompt from either a prompt query or a prompt storing static values. All the user has to do is to select from the presented list offered..
If a text box prompt is really necessary for some reason then you can set it to muti-select. They would enter each value one at a time and then click the insert button to move each to the choices box. Presenting them with choices in list box might be easier as blom suggests.
Thanks, all. Lynn's solution was what I needed in this case. I do still run into situations where it'd be extremely convenient to paste in a large list of CSVs and have it used in an IN clause. I'll figure it out some day.
If you absolutely must have CSVs, you could try the following:
[Namespace].[Table].[Field] in (#csv(split(',',prompt('CSV Prompt','token')))#)
So if you write in a,b,c it will be converted into:
[Namespace].[Table].[Field] in ('a','b','c')
If you need to filter on numeric values, just get rid of the csv and split functions.
You can also setup a text box to accept multiple values pasted in by setting the 'Multi Line' property. This makes it easy to copy a list of values out of excel and paste into a prompt.