SAP Knowledge Base Article - Public

1940177 - How to combine the Business View Filters using the AND operator in Crystal Reports?

Symptom

  • Multiple Filters are joined by the OR operator.
  • When reporting off a Business View in Crystal Reports, the Filters are combined using: OR operator. 
  • How to combine Filters created in a Business View using: AND operator in Crystal Reports?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
        
  • Business View Manager

Reproducing the Issue

  1. In the Business View Manager, create multiple Filters in the Business Element.
      
    For example, create the following Filters:
    • Country = 'Canada'
    • City = 'Vancouver'
         
  2. Assign rights for each of the Filters.
       
  3. In Crystal Reports, create a report off the Business View.
        
  4. Notice the SQL Query generated combines the Filters using the OR operator.
      
    For example, the SQL Query generated will be like:
      
         Country = 'Canada' OR City = 'Vancouver'

    How to combine the Filters using the AND operator like:

         Country = 'Canada' AND City = 'Vancouver'

Cause

  • If all the Business View Filters have been defined in one Business Element, then the filters will always be combined using the OR operator.
      
  • If the Business View Filters have been defined in multiple Business Elements, then by default the filters will be combined using the OR operator, but there is an option to change how the Business Element Filters are combined in the Business View.

Resolution

  • To combine the Business View Filters using the AND operator, create one filter per Business Element, and change the option "Business Element Filter Combination" from: OR, to the value: AND.  
               
    1. Open the Business View in Business View Manager, to find all the Business Elements it is using.
        
    2. Open each Business Element, and if all the Business View Filters have been defined in one Business Element, then create one Business Element for each Filter.
        
    3. Add any newly created Business Element to the Business View.
             
    4. Still in the Business View, in the "Properties Browser" pane, for the option: "Business Element Filter Combination", select "AND" in the drop down list.
       
      KB01.png
        
    5. Save the Business View.
              
      Now, when creating a report off a Business View in Crystal Reports, it will combine the Filters defined in each Business Element using the AND operator.

Keywords

CR, combine, OR, AND , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016