Hi, I am currently working with Cognos 8.3 in Report Studio.
I am trying to find a way to input a list of values separated by either spaces or commas into an unvalidated text box prompt (one not attached to a query to search and select values). I need to then use each of the values in a filter clause with the "IN" operator.
For example, the user would input a list of values such as below into a text box prompt.
12345678, 87654321, 98765432, 23456789
These values would then need to be handled in the filter expression like this:
[EXAMPLE ID] IN ('12345678', '87654321', '98765432', '23456789')
But the only way I am currently familiar with would produce this:
[DATA ITEM EXAMPLE ID] IN ?EXAMPLE PARAMETER?
or literally this:
[EXAMPLE ID] IN ('12345678, 87654321, 98765432, 23456789')
Does 8.3 have the capability to do this? If not, has anyone found a way to work around this? I know that I can use a multi-select text prompt but then the user would have to enter each ID individually and the idea is that the user would just copy in a long list that they already have saved elsewhere.
Thanks for your help,
David
not the perfectly working solution...
use this as a filter
cast([EXAMPLE ID],varchar(9)) in # '('+csv(split(',',prompt('EXAMPLE_PARAMETER','text','0')))+')'#
i will not use this because i need to cast data item as a varchar.
and it is working correctly only if your prompt value starts with , (in short it is ignoring first and last value in prompt)
so if you want to select 12345678, 87654321, 98765432, 23456789
enter ,12345678,87654321,98765432,23456789, in prompt
may be someone can suggest better solution or may be you can modify above solution to make it perfect (if so plz let me know)
sorry.. ignore my previous post...
use this [EXAMPLE ID] in # '('+ prompt('EXAMPLE_PARAMETER','token','-99') + ')'#
this should work fine
Hi I have the same issue, but when I put this in as a filter....
[Student Global Online ID] in # '('+ prompt('EXAMPLE_PARAMETER','token','-99') + ')'#
It queries the database and gives me a list of email address from the db where i want it to be a free form promt to allow the user to enter in values.