Using 'OR' in filter Condition

[sandy_vitty]

Hi Gurus

         Desperately trying to fix an prompt issue in report studio and my mind has shut down on me. Here is the senario
         
         I have a multi select value prompt with static values
         
         
         use value             display value
           ALL               ALL
           01               REGION 01
           02                       REGION 02
           99               Non Regions
           
When clicked on Non regions an addtional prompt gets displayed which lists all the cities not under any region.
           
          When "ALL" is selected the data for all regions and cities are displayed and when any single region is selected the report is filtered to that region.  But when Region(like region 01 or 02) and "Non Regions" are selected then data for only the cities, i donot see any data even though when selected individually they show data.

          i am trying to do the following in filter condition
         
          ([DataMart_Reports].[XXXX].[Region] in ?Region? or [DataMart_Reports].[XXXX].[cities] in ?cities?) or ?Region?='ALL' or ?Region?='99'
         
          Hope i am able to explain my problem

[whastings]

When working with complex formulas, if statements, or conditions, I find that formatting the line helps me debug it.

In your current statement, you have 3 conditions. You have brackets around two conditions effectively making it one:

    ([DataMart_Reports].[XXXX].[Region] in ?Region? or [DataMart_Reports].[XXXX].[cities] in ?cities?)
or ?Region?='ALL'
or ?Region?='99'

So basically, if ?Region? = '99', everything is selected. If ?Region? = 'ALL', everything is selected. Otherwise, either the region matches or the city matches.

You really want the city selection associated with ?Region? = '99'.

Try this:

    [DataMart_Reports].[XXXX].[Region] in ?Region?
or (?Region? = '99' and [DataMart_Reports].[XXXX].[cities] in ?cities?)
or ?Region? = 'ALL'

I am not sure, but in the clause selecting the non-region cities, you may also need 'and [DataMart_Reports].[XXXX].[Region] is missing' to make sure that you are not picking up anything that has a valid region even though the city is in your selection.

[cschnu]

Also, I you might want to think about how your database evaluates an OR statements in a WHERE clause. Here is what i mean http://it.toolbox.com/blogs/database-solutions/finding-data-sql-where-and-or-11191.  Based on what whastings and you posted i think you will get the results you want in this case.