Criteria Settings


Table of Contents   Previous Page  Next Page    

The Report Settings window will contain two or more tab pages, depending on the report or document you have selected. At a minimum, the General Settings tab and the Report Criteria tabs will be available.

The Criteria Settings tab enables you to restrict the records that will be shown on a report report by entering one or more "Criteria". You have tremendous flexibility with these queries, and can create very complex statements to precisely control which records are found and displayed on the report.

If you just want to see some examples, see Search Criteria Examples.

The Criteria Settings Tab

Criteria is what actually controls which records will appear on the report. Will the report show every member, or perhaps only members that live in "Boston?"  (This example shows members who live in Boston, aged 50 or greater.) Maybe you want to see every member with an End Date in the next 30 days, because they need to be contacted for renewal. Maybe you only want members who attended a particular activity on December 15 in a role of "sponsor" who have made a donation in the last 10 days and are NOT part of the widget analysis committee. The possibilities are nearly endless, and very powerful.

Building Criteria

Criteria is created row-by-row, with each row representing another criteria (you can think of it as a "rule") that must be true for a given record to appear on the report. For example, if you are running a membership report and use a criteria like, Last Name = Smith, then only membership records with "Smith" in the Last Name field will be on the report.

 

1Start a new criteria line by clicking the Add icon. A new blank row will appear.
2Click the dropdown arrow in the Field Name column to open the selection window. Then, choose the field whose data you want to check with this criteria. For example, if this criteria will restrict the report to members in a certain city, expand the Address section and choose "City."
3Click the dropdown arrow in the Operator column to open the selection window. Then, choose the method that will be used to compare the data to the selected field.  For example, if you want the city value to be equal to, "MyTown", you would choose "Equals".
4Click the dropdown arrow in the Value column to open an appropriate data-entry window. Then enter the appropriate value to be compared to the selected Field.

More than One Criteria

When you have several criteria rules you want checked the system needs to know if you mean "this criteria AND that criteria" or whether you mean, "this criteria OR that criteria" since there is a big difference. To control this, there is an option to specify AND or OR at the end of each row of criteria, to tell the system how to link that criteria rule to the next.

Also remember that when using multiple criteria, every piece of the criteria (at least those with "and") must be true for each record being shown on the report.  Therefore, if you use criteria like:

City = MyTown AND
City = YourTown
 

... the report won't show anything. This makes sense if you think about it, because no member has a city set to MyTown AND YourTown at the same time.  There is only one city on an address.

If the criteria was changed to:

( City = MyTown OR
City = YourTown )

...the report will work, because now it can find members with a city of MyTown OR a city of YourTown -- it doesn't have to match both at once.

Using Parenthesis

The advanced criteria list includes options for left and right parenthesis. Parenthesis are very important if you use an OR to join your criteria rows. Consider this criteria:

State = Texas AND 
(City = Mytown OR
City = YourTown)

 

This will require a state of Texas, and either a city of Mytown or a city of YourTown, because the AND refers to all of the criteria grouped by the parenthesis.  However, if the parenthesis were omitted, the criteria would look like this:

State = Texas AND 
City = Mytown OR
City = YourTown

 

This can produce unpredictable behavior, because now it essentially says, "state of Texas and city of MyTown, or the city of YourTown without regard to the state".  Thus, records could be returned for YourTown, Washington, and YourTown, Oregon, as well as YourTown, Texas.

 

Special Expressions using LIKE and NOT LIKE Operators

% The percent sign is used in conjunction with the word "LIKE" to search for data that begins, ends, or contains specific text or numbers. For example, if you selected Last Name as the field, and LIKE as the operator, you might enter any of the following in the Value field:

"Smith%" to get "Smith", "Smithe", or "Smithington.
"%burg" to get "Sederburg", "Frankenburg", "Hidelburg"
"%der" to get "Sederburg", "Fender", and "Derston"

 

You can also use the opposite of LIKE, which is NOT LIKE, to reverse the search requirement.

Complex Expressions Using LIKE and NOT LIKE Operators

If you are comfortable with the concepts of LIKE and NOT LIKE, the following table provides several advanced examples of ways to use wildcards and expressions for even more precise queries. Note that each expression below would be preceded with LIKE or NOT LIKE.

 

Expression

Searches for...

Sample Return Data

Mc%

Names that begin with the letters "Mc"

McEvoy

%er

Names that end with "er"

Brier, Miller, Weaver, Rayner

%en%

Names containing the letters "en"

Pettengill, Lencki, Cohen

_ish

Four-letter names ending in "ish"

Fish

Br[iy][ae]r

Brier, Bryer, Briar, or Bryar

Brier

[M-Z]owell

Names ending with "owell" that begin

with a single letter in the range M to Z

Powell

M[^c]%

Names beginning with "M" that do not

have "c" as the second letter

Moore, Mulley, Miller

 

As illustrated above, the % (percent) symbol will be replaced by any number of characters, whereas the _ (underscore) symbol can only be replaced by one character.

Text within brackets ( [ ] ) will be required unless preceded by the carat (^) symbol, in which case the text is disallowed.

If you want to see some examples, see Search Criteria Examples.

 

Address Criteria

When using MemberTies Professional, if you use address fields as part of your criteria, i.e., "City = MyTown", and your membership records often include multiple addresses, you may want to use a special criteria field called "Address to Use".  With this criteria, you can tell the report which address you want it to look at when matching the City value.

If you don't use this criteria, the system will automatically assume you want to use the Current Address whenever it needs to look for address data.  That means if a member has a Home Address in "MyTown" and a Summer Address in "YourTown", the record won't be found unless the Home Address is marked as Current on the membership record.

Include in Mailings, Include on Reports

These two options are available on membership records. A report preference controls whether these "include" options are automatically enforced on  all reports.  By default they are enforced, so no report will show any member with the Include on Reports check box cleared. However, if you manually use the "Include on Reports" field as part of your criteria, you will override the default behavior.  The "Include on Reports" field applies to non-label reports. The "Include in Mailings" field applies to mailing label reports.