How to add 2 data items to a single value prompt

[Annon]

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

[BigChris]

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.

[Lynn]

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 Expand


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