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 Text Prompt Using Comma or Space Separated Values???

Started by DPotterSQL, 29 Jul 2009 01:03:18 PM

Previous topic - Next topic

DPotterSQL

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

uttam.mistry

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)

uttam.mistry

sorry.. ignore my previous post...

use this [EXAMPLE ID] in #  '('+ prompt('EXAMPLE_PARAMETER','token','-99') + ')'#

this should work fine

beav81psu

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.