Having Criteria

The Having button allows you to add the having criteria to the search. Having criteria is criteria based on aggrigate values within the search. Examples are:

• find donors HAVING a total of all donations over xxx
• find ticket buyers having purchased to multiple distinct events for the current season
• find subscribers that have a subscriber package set up for all of the last 10 years
• find gift certificate purchasers with an average gift certificate purchase over $25
• find auction item buyers with a maximum purchase over $1000 for all items bought

Having is an SQL action that re-examines records for an aggregate (average, minimum, maximum, sum, count, distinct count) based on the record found and then keeps or discards the data.

For example: if you used criteria to find all subscriber records in the past 5 years, you would get 1, 2 ,3 4 or 5 records - depending on how many years the person has been a subscriber. Since we know each subscription package has a year associated with it, if you make a having criteria that is having a unique count of subscription year >= 4, you would find people who were 4 or 5 year subscribers.

To create the query, you need two things:

• A normal query that finds all the records you want to aggregate in some way. eg: tickets with date sold since 2004 and price paid > 0
• An additional having clause that uses one field for the aggregation. eg
  • sum price paid >500 -or-
  • count ticket #> 50 -or-
  • distinct count event # > 4

1. Open the Mail List you want to add the criteria to.

   

   The Mail List Criteria window opens.

2. Click the Criteria Groups tab.

   

3. Click the create Group button.

   

4. Make your selection to Add or Remove Patrons from the listing.

   The Mail List dialog opens.

   

5. Select the file for the primary source of information for your list. Then click the Next button.

   The Parameters window opens.

   

6. Click the Having button.

   The Edit Report Parameters window opens.

   

   Note the criteria for selection on the left side of the window gives yous different options to choose from.

7. Make your selections for the Criteria, and when complete, click the Save button.

   You are returned to the Parameters window and your criteria selection appears in the search.

   

8. Click the Done button.

   You are returned to the Mail List window.

   

9. Click the Execute to execute your search.