I have a sales database with a large number of cities.  I have created a multiple value parameter so a regional manager can see results for one or more of the cities in his/her region.  I have manaually entered a parameter value called "ALL" that uses a wildcard in the code (like '*') to show data for all cities (assuming they are not filtered out based on other parameters).

 

What I would like to do is create sets/groups that contain multiple cities.  For example, I want the manager to be able to select "WEST" from the parameter list, which automatically selects the 13 cities in that region.  I would like to avoid using a long 'OR' statement and burying the list of 13 "west" cities in the code.  Is there a way that I can have a central point to define the cities of each region, check if a given field value is contained in the selected region, then do a distinct count of InvoiceID (i.e. only count each unique InvoiceID once, even though one InvoiceID will have multiple ItemID).

 

Any help would be greatly appreciated.  I have had trouble finding exactly what I was looking for on the help text and online.

 

 

Craig

 

if you don't use a stored proc, and you don't have a table that lists the cities and regions, then you are going to have to bury the translation somewhere.  It doesn't need to be a bunch of ORs, it could be just 1 string like "|los angeles|las vegas|...|" and you can concatenate different regions together and perhaps append individual cities to that you have 1 big string with some delimiter between each one.  Then in the record selection filter you can check something like:

 

instr("|"+{table.field}+"|", delimitedString)>0  

 

this by itself, would filter, you can add it to an IF statement if there is more or it is easier for you.

 

HTH