COGNOiSe.com - The IBM Cognos Community

I am not even sure if the title is right but appreciate all your help!

I have a campus column which has values: Home, US, Spain,London,Italy etc (non US cities). Would like my prompt to have these options. I tried doing it through static choices but since the non-home and foreign are multiple values, not sure.

Home
Non-Home (US+other countries/cities)
Foreign (only foreign countries - home-US)

Thanks in advance!

Ideally your data source should have a field indicating the campus type, with the values Home, Non-Home, and Foreign. Failing that there's no problem to use a static value here. In addition to the standard string, dates, integer data types, prompts can also take expression fragments.

You could try the following:

Description |    Use
------------+-----------
Home        | in ('New York')
Non-Home    | in ('Florida','Kansas','Pennsylvania')
Foreign     | not in ('New York','Florida','Kansas','Pennsylvania')


You can then simply set a filter to do something like:

[Campus] #prompt('campusType','token')#

When the report runs, it will pass whatever is in the use value to the prompt. So if you selected Foreign the filter will look like:
[Campus] not in ('New York','Florida','Kansas','Pennsylvania')

Thanks for the solution. However I am still not clear how to define the filter? This is how my data source looks like

Camp_code     Camp_desc
c                       campus
us                    off campus domestic
a                      spain
b                      london
d                      denmark
e                      france
etc <more foreign locations>

I want my prompt to have the following options to select from

campus (should be c)
domestic (should be us)
off campus foreign (not in 'c','us')

Thanks again!

Quote from: sinrag2005 on 14 Aug 2014 04:32:58 PM
Thanks for the solution. However I am still not clear how to define the filter? This is how my data source looks like

Camp_code     Camp_desc
c                       campus
us                    off campus domestic
a                      spain
b                      london
d                      denmark
e                      france
etc <more foreign locations>

I want my prompt to have the following options to select from

campus (should be c)
domestic (should be us)
off campus foreign (not in 'c','us')

Thanks again!

Hi,

Just follow Paul's guidance above - code your filter as [Campus] #prompt('campusType','token')#

Then when you create your prompt, define three static values in the prompt with "Use value" and "Display Value" exactly as Paul detailed.

Cheers!

MF.