Print Page - How to add 2 data items to a single value prompt

Title: How to add 2 data items to a single value prompt
Post by: Annon on 15 Apr 2015 09:38:56 AM

Hi, I have a question. I have 2 data items [Country] and [City] and my requirement is i should be able to choose either Country OR City from the value prompt. The value prompt should show the options Argentina, Brazil, Beijing, China, Brasilia, New York, Seoul, USA and i should be able to choose one single 'Country' or singe 'City' or 'All' for all the countries and Cities. IT IS A RELATIONAL DATA BASE. I have tried ([Country] or [City]) = ?Parameter? and i have tried giving 2 separate filters [Country]= ?Parameter? and [City]=?Parameter?. But nothing is working.

It would be a great help if someone can tell me the solution.

Thanks

Title: Re: How to add 2 data items to a single value prompt
Post by: BigChris on 15 Apr 2015 10:11:10 AM

Hi,

I'm not sure if this is the solution you're looking for, but hopefully it'll work for you at least.

To get the values for your prompt I think you need to create a UNION query. That should give you a consolidated list which you can then use for the query for your prompt.

If you make the prompt optional then when a user runs the report they can just ignore that prompt to get ALL the data.

Title: Re: How to add 2 data items to a single value prompt
Post by: Lynn on 16 Apr 2015 02:54:50 AM

Quote from: Annon on 15 Apr 2015 09:38:56 AM
Hi, I have a question. I have 2 data items [Country] and [City] and my requirement is i should be able to choose either Country OR City from the value prompt. The value prompt should show the options Argentina, Brazil, Beijing, China, Brasilia, New York, Seoul, USA and i should be able to choose one single 'Country' or singe 'City' or 'All' for all the countries and Cities. IT IS A RELATIONAL DATA BASE. I have tried ([Country] or [City]) = ?Parameter? and i have tried giving 2 separate filters [Country]= ?Parameter? and [City]=?Parameter?. But nothing is working.

It would be a great help if someone can tell me the solution.

Thanks

Assuming you have only one single-select prompt control associated with Parameter, and that control presents the combined list of cities and countries plus the option for 'All' (either from your prompt query or via a static choice), then I think this filter expression would work.

Code Select


( [Country] = ?Parameter? )
or 
( [City] = ?Parameter? )
or
( ?Parameter = 'All' )

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: Annon on 15 Apr 2015 09:38:56 AM