More information

About Criteria - Columns, Operators, Objects

Within the Reporting World in Millennium, Criteria statements are sentences that you construct to describe a particular kind of data that you want to locate within the database. You can combine multiple statements to form a complete set of criteria that the software will use as a benchmark for operator against the data in the database. These sentences are not constructed as free text, but through a series of forms within the Millennium Reporter. When you identify the components, the system will generate the text of the criteria statement. A single criteria statement might read:

"Select all Addresses where the Locator is equal to "good".

Criteria statements consist of three main elements: the column, the operator, and the object. If you want, you could think of these elements as the grammatical parts of the select statement, where the column is the subject, the operator is the verb and the object is the direct object of the sentence. In the sample sentence above, the subject (column) is Locator, the verb (operator) is is equal to, and the direct object (object) is good. The system will automatically construct the portion of the sentence that includes the Select command and the name of the table that contains the specified field, which in the example is, Addresses.

Columns

The column that is named in the criteria statement identifies both the data table and the column within that table that the Millennium Reporter will focus on when it runs a selection.

For example, if the criteria statement reads:

"Select all Addresses where the Place from the Address screen is equal to Virginia"

then the Millennium Reporter will first go to the Address data table, and examine each row, looking at the Places column. When it finds a row that has the data, Virginia in the Places column, that row will be selected. This means that the criteria statement is true for that row, or we sometimes say that the row "passes" the criteria. Any row that does not have Virginia in that field, will not be selected. If this were the only statement in the set of criteria, it would make no difference to the system what data were present in any other field in this row, or in any other row in the database, because this statement specifies the Places column in the Address data table.

Any field in any data table can be used to construct a criteria statement, including those non-displaying fields that are used "behind the scenes" for system use, or those that your institution has defined, that are not part of the standard list of columns. Every field in the database has a data type and a length. Those characteristics will determine the kinds of operators and objects that can be used in the same criteria statement with that field.

There are two techniques for choosing a Column for a criteria statement. If the field is one that is entered through data maintenance (not one that is system-generated), then you may use the replica of the data maintenance form that is displayed. Any field may be chosen using the pull down list box that is also presented.

Operators

The operator portion of a criteria statement defines the way in which the data in the row should relate to the object portion of the criteria statement, in order for that row to be selected.

In the example, " Select all Addresses where the Place from the Address screen is equal to Virginia" the phrase, is equal to is the operator.

In many cases, you will not simply be looking in the data base for rows whose data exactly matches a single piece of data that you use as an object. You might be looking for all rows that do NOT match that data, you might be looking for data that falls into a certain range, you might be looking for data that matches any one of a number of pieces of data, or any number of other situations. The following is a complete set of the operators that might be used by the Millennium Reporter.

Operator	Will Select Rows:	Example:
equal to	that are exactly the same as the Object. For text columns, the case must match exactly (uppercase/lowercase) Note: For zip code (postal code) fields, the equal to operator will ignore the + 4 part of the zip code when doing the comparison.	Select all Giving where the Gift Amount from the Giving screen is equal to "50". Will select: $50 Will not select: $50.01, $25, $100 Select all Addresses where the Postal Code from the address page is equal to "23690" Will select: 23690, 23690-0210, 23690-0345 and so forth. Will not select 23696
greater than	that have a higher value than the Object.	Select all Giving where the Gift Amount from the Giving screen is greater than "50". Will select: $50.01, $100, $4,000,000 Will not select: $50, $25, $1
greater than or equal to	that have an equal or higher value than the Object	Select all Giving where the Gift Amount from the Giving screen is greater than or equal to"50". Will select: $50, $100, $4,000,000 Will not select: $49.99, $25, $1
less than	that have a lower value than the Object.	Select all Giving where the Gift Amount from the Giving screen is less than "50". Will select: $49.99, $25, $1 Will not select: $50, $100, $4,000,000
less than or equal to	that have an equal or lower value than the Object.	Select all Giving where the Gift Amount from the Giving screen is less than or equal to "50". Will select: $50, $25, $1 Will not select: $50.01, $100, $4,000,000
between	that are within the range indicated, inclusively. Note: Two Objects are required to identify the range.	Select all Giving where the Gift Amount from the Giving screen is between "50" and "100". Will select: $50, , $75, $100 Will not select: $49.99, $100.01, $4,000,000
in the list of	that are equal to one of the Objects listed.	Select all Constituents where the category from the Basic Data screen is in the list of: "Alumni" "Corporation" "Individual" "Parent "Trustee" Will select: Alumni, Corporation, Individual, Parent, Trustee Will not select: Foundation, Major Donor, Religious Denomination
checked	that include a check in the field. Note: No Object required	Select all Jobs where the Match Flag from the Jobs screen is checked. Will select: All Jobs rows with a check in the Match Flag check box. Will not select: Jobs rows without a check in the Match Flag check box.
valid this month	that have the current month checked.	Select all Jobs where the Match Flag from the Jobs screen is checked. Will select: All Jobs rows with a check in the Match Flag check box. Will not select: Jobs rows without a check in the Match Flag check box.
contains	that include the Object within a free text field. Note: "Contains" is not case sensitive but is EXACT. Wild cards should be used as part of the Object.	Select all Jobs where the Title Bar from the Basic Data screen contains "%VIP%". Will select: All Basic Data rows with VIP or vip in the Title Bar field. Will not select: Basic Data rows with V.I.P.
not equal to (negative operator)	that do not exactly match the Object. Note: For text columns, the case must match exactly (uppercase/lowercase).	Select all Jobs where the category from the Basic Data screen is equal to Individual and where the Research Level from the Tracking screen is not equal to "Completed". Will select: All Basic Data rows with a category of Individual for those constituents that do not have a Tracking row with "Completed" in the Research Level field. Will not select: Basic Data rows for constituents that have "Completed" in the Research Level of the Tracking row.
never equal to (negative operator)	that never exactly match the Object (used for multiple rows within a table). Note: For text columns, the case must match exactly (uppercase/lowercase).	Select all constituents where the category from the Basic Data screen is equal to Individual and where the Type from the Attribute screen is never equal to "Do Not Solicit". Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Attribute row with "Do Not Solicit" in the Type field. Will not select: Basic Data rows for constituents that have any Attribute row that has "Do Not Solicit" in the Type field.
not between (negative operator)	that are outside the range indicated. Note: The two "endpoints" of the range are included within the range.	Select all Constituents where the category from the Basic Data screen is equal to Individual and where the Capacity from the Tracking screen is not between "5000" and "10000". Will select: All Basic Data rows with a category of Individual for those constituents that do not have a Tracking row with between 5000 and 10000 (inclusively) in the Capacity field. Will not select:Basic Data rows for constituents that have less than 5000 or greater than 10000 in the capacity field of the Tracking row.
not in the list of (negative operator)	that are not equal to one of the Objects listed.	Select all Constituents where the category from the Basic Data screen is equal to Individual and where the Research Level from the Tracking screen is not in the list of: "Stage 1" "Stage 2" Will select: All Basic Data rows with a category of Individual for those constituents that do not have a Tracking row with Stage 1 or Stage 2 in the Research Level field. Will not select: Basic Data rows for constituents that have Stage 1 or Stage 2 in the Research Level field of the Tracking row.
never in the list of (negative operator)	that are never equal to one of the Objects listed (used for multiple rows within a table).	Select all Constituents where the category from the Basic Data screen is equal to Individual and where the Type from the Attribute screen is never in the list of: "Do Not Solicit" "No Mail" "Trustee" Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Attribute row with Do Not Solicit, OR No Mail, OR Trustee in the Type field. Will not select: Basic Data rows for constituents that have at least one Attribute row that contains one of the listed Attribute Types.
does not contain (negative operator)	that do not include the Object within a free text field. Note: "Does not contain" is not case sensitive but is EXACT. Wildcards may be used.	Select all constituents where the category from the Basic Data screen is equal to Individual and where the Title Bar from the Basic Data screen does not contain "VIP". Will select: All Basic Data rows without VIP OR vip in the Title Bar field. Will not select: Basic Data rows with VIP OR vip in the Title Bar field.
not checked (negative operator)	that do not include a check in the specified field. Note: No Object required.	Select all Jobs where the Status from the Jobs screen is equal to Active and where the Match Flag from the Jobs screen is not checked. Will select: Active Job rows without a check in the Match Flag check box. Will not select: Job rows with a check in the Match Flag check box.
never checked (negative operator)	that never have a data row with a check in the specified field. Note: No Object required.	Select all constituents where the category from the Basic Data screen is equal to Individual and where the Match Flag from the Jobs screen is never checked. Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Employment row with the Match Flag checked. Will not select: Basic Data rows for constituents that have any Employment row with the Match Flag checked.
never contains (negative operator)	that never include the Object within a free text field. Note: "Never contains" is not case sensitive but is EXACT. Wild cards should be used as part of the Object.	Select all constituents where the category from the Basic Data screen is equal to Individual and where the Title from the Employment screen never contains "President". Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Employment row with "President" in the Title field. Will not select: Basic Data rows for constituents that have any Employment row that has "President" in the Title field.

Operator

Will Select Rows:

Example:

equal to

that are exactly the same as the Object.

For text columns, the case must match exactly (uppercase/lowercase)

Note: For zip code (postal code) fields, the equal to operator will ignore the + 4 part of the zip code when doing the comparison.

Select all Giving where the Gift Amount from the Giving screen is equal to "50".

Will select: $50
Will not select: $50.01, $25, $100

Select all Addresses where the Postal Code from the address page is equal to "23690"

Will select: 23690, 23690-0210, 23690-0345 and so forth.
Will not select 23696

greater than

that have a higher value than the Object.

Select all Giving where the Gift Amount from the Giving screen is greater than "50".

Will select: $50.01, $100, $4,000,000
Will not select: $50, $25, $1

greater than or equal to

that have an equal or higher value than the Object

Select all Giving where the Gift Amount from the Giving screen is greater than or equal to"50".

Will select: $50, $100, $4,000,000
Will not select: $49.99, $25, $1

less than

that have a lower value than the Object.

Select all Giving where the Gift Amount from the Giving screen is less than "50".

Will select: $49.99, $25, $1
Will not select: $50, $100, $4,000,000

less than or equal to

that have an equal or lower value than the Object.

Select all Giving where the Gift Amount from the Giving screen is less than or equal to "50".

Will select: $50, $25, $1
Will not select: $50.01, $100, $4,000,000

between

that are within the range indicated, inclusively.

Note: Two Objects are required to identify the range.

Select all Giving where the Gift Amount from the Giving screen is between "50" and "100".

Will select: $50, , $75, $100
Will not select: $49.99, $100.01, $4,000,000

in the list of

that are equal to one of the Objects listed.

Select all Constituents where the category from the Basic Data screen is in the list of:
                      "Alumni"
                      "Corporation"
                      "Individual"
                      "Parent
                      "Trustee"

Will select: Alumni, Corporation, Individual, Parent, Trustee
Will not select: Foundation, Major Donor, Religious Denomination

checked

that include a check in the field.

Note: No Object required

Select all Jobs where the Match Flag from the Jobs screen is checked.

Will select: All Jobs rows with a check in the Match Flag check box.
Will not select: Jobs rows without a check in the Match Flag check box.

valid this month

that have the current month checked.

Select all Jobs where the Match Flag from the Jobs screen is checked.

Will select: All Jobs rows with a check in the Match Flag check box.
Will not select: Jobs rows without a check in the Match Flag check box.

contains

that include the Object within a free text field.

Note: "Contains" is not case sensitive but is EXACT. Wild cards should be used as part of the Object.

Select all Jobs where the Title Bar from the Basic Data screen contains "%VIP%".

Will select: All Basic Data rows with VIP or vip in the Title Bar field.
Will not select: Basic Data rows with V.I.P.

not equal to

(negative operator)

that do not exactly match the Object.

Note: For text columns, the case must match exactly (uppercase/lowercase).

Select all Jobs where the category from the Basic Data screen is equal to Individual and where the Research Level from the Tracking screen is not equal to "Completed".

Will select: All Basic Data rows with a category of Individual for those constituents that do not have a Tracking row with "Completed" in the Research Level field.
Will not select: Basic Data rows for constituents that have "Completed" in the Research Level of the Tracking row.

never equal to

(negative operator)

that never exactly match the Object (used for multiple rows within a table).

Note: For text columns, the case must match exactly (uppercase/lowercase).

Select all constituents where the category from the Basic Data screen is equal to Individual and where the Type from the Attribute screen is never equal to "Do Not Solicit".

Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Attribute row with "Do Not Solicit" in the Type field.
Will not select: Basic Data rows for constituents that have any Attribute row that has "Do Not Solicit" in the Type field.

not between

(negative operator)

that are outside the range indicated.

Note: The two "endpoints" of the range are included within the range.

Select all Constituents where the category from the Basic Data screen is equal to Individual and where the Capacity from the Tracking screen is not between "5000" and "10000".

Will select: All Basic Data rows with a category of Individual for those constituents that do not have a Tracking row with between 5000 and 10000 (inclusively) in the Capacity field.
Will not select:Basic Data rows for constituents that have less than 5000 or greater than 10000 in the capacity field of the Tracking row.

not in the list of

(negative operator)

that are not equal to one of the Objects listed.

Select all Constituents where the category from the Basic Data screen is equal to Individual and where the Research Level from the Tracking screen is not in the list of:
"Stage 1"
"Stage 2"

Will select: All Basic Data rows with a category of Individual for those constituents that do not have a Tracking row with Stage 1 or Stage 2 in the Research Level field.
Will not select: Basic Data rows for constituents that have Stage 1 or Stage 2 in the Research Level field of the Tracking row.

never in the list of

(negative operator)

that are never equal to one of the Objects listed (used for multiple rows within a table).

Select all Constituents where the category from the Basic Data screen is equal to Individual and where the Type from the Attribute screen is never in the list of:
                  "Do Not Solicit"
                  "No Mail"
                  "Trustee"

Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Attribute row with Do Not Solicit, OR No Mail, OR Trustee in the Type field.
Will not select: Basic Data rows for constituents that have at least one Attribute row that contains one of the listed Attribute Types.

does not contain

(negative operator)

that do not include the Object within a free text field.

Note: "Does not contain" is not case sensitive but is EXACT. Wildcards may be used.

Select all constituents where the category from the Basic Data screen is equal to Individual and where the Title Bar from the Basic Data screen does not contain "VIP".

Will select: All Basic Data rows without VIP OR vip in the Title Bar field.
Will not select: Basic Data rows with VIP OR vip in the Title Bar field.

not checked

(negative operator)

that do not include a check in the specified field.

Note: No Object required.

Select all Jobs where the Status from the Jobs screen is equal to Active and where the Match Flag from the Jobs screen is not checked.

Will select: Active Job rows without a check in the Match Flag check box.
Will not select: Job rows with a check in the Match Flag check box.

never checked

(negative operator)

that never have a data row with a check in the specified field.

Note: No Object required.

Select all constituents where the category from the Basic Data screen is equal to Individual and where the Match Flag from the Jobs screen is never checked.

Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Employment row with the Match Flag checked.
Will not select: Basic Data rows for constituents that have any Employment row with the Match Flag checked.

never contains

(negative operator)

that never include the Object within a free text field.

Note: "Never contains" is not case sensitive but is EXACT. Wild cards should be used as part of the Object.

Select all constituents where the category from the Basic Data screen is equal to Individual and where the Title from the Employment screen never contains "President".

Will select: All Basic Data rows with a category of Individual for those constituents that do not have any Employment row with "President" in the Title field.
Will not select: Basic Data rows for constituents that have any Employment row that has "President" in the Title field.

Negative Operators

Negative operators cannot be used in the first criteria statement in a report. The negative operators are:

not equal to
never equal to
not between
not in the list of
never in the list of
does not contain
not checked
never contains
never checked

Objects

After selecting a column and an operator, you must specify an object for the criteria statement. This object is the standard to which you want the system to compare the data in the Millennium field, to determine whether that row does or does not conform to the statement, and therefore whether it passes or fails the selection. There are several kinds of object which you may specify, depending on the nature of the column you are working with. Objects may be lookup table driven, free text, a wildcard, a date, a total, a prompted reply, or a blank. The system will recognize these and present the appropriate text boxes, lists, calendars, or buttons during the construction of the statement.

Object Type	Description
Lookup Table Driven	When the field that is chosen for the criteria statement is driven by a lookup table, you must also choose the object(s) for the criteria statement from the existing set of entries from that lookup table. If the field is driven by a standard lookup table, then the Millennium Reporter will provide you with the appropriate list, when you identify a field. If the field is driven by a value-based lookup table instead, then the Reporter will provide you with a text box that has Search functionality to help you choose the object(s) that you want.
Free Text	When the field that is chosen for the criteria statement is a free text, you may enter any data that you want for the object of the criteria statement. If the field is an ID number, then you can enter an ID number without its leading zeroes. The system will add the leading zeroes for you. For all other fields, the system will only recognize the same data in the constituent's data row when the match is EXACTLY the same.
Wildcards	When the field that is chosen for the criteria statement is a free text field, and the chosen operator is contains or does not contain, wild cards should be used as a substitute or variable for the kind of data that the criteria statement will "pass". See the topic, Wildcard Characters.
Dates	When the field that is chosen for the criteria statement is a datetime field, you may type in the date, using any of the forms acceptable to the Millennium Reporter, or you may use the calendar to select a date. In the Millennium Reporter, you may also use the check box to automatically insert Today's Date as the object. This instructs the system to use the current day's date at the time the report is actually run. You may also specify that the object should be the complete date, the month only, the day only, or the year only. See the topic Writing Criteria, Dates.
Totals	When the field that is chosen for the criteria statement is a Total, then another Total from the same set of criteria may be used as the object of the statement. See the topic, Totals. (This function does not apply to Event Reporting and therefore this is not shown on the Event Report Form.)
Prompted Replies	Any criteria statement, using any type of field, may use a Prompted Reply as an object. This object is simply a "placeholder" that must be replaced with the desired data at the time that the report is run. This object allows you to construct generalized reports, and then the system will prompt the operator to fill in the specific object as the report is run. For example, you might construct a report that calculates a sum of all activity for the year for a particular campaign. Rather than specify the campaign at the time that the report is constructed, you might use the Prompted Reply and then substitute the name of any of the campaigns at the time that the report is run. Note: At the time that the report is run, you can choose to have the Millennium Reporter ignore that one line of criteria by checking the box, not applicable to this report.
Blank	When a criteria statement is looking for rows that have no data in the chosen field, then you may leave the object portion of the statement blank.

The system will automatically give you the provisions to chose any of the objects that are appropriate to the field and operator that you have already specified. The individual techniques are discussed in the topic, Writing Criteria Statements.

Wildcards

Wild cards are particular characters or symbols that the system will recognize as "substitutes" for another set of characters. They may only be used in connection with free text fields within any data table, to define an object.

When entering an object in those circumstances, you should enter wildcard symbols in connection with specific text to select rows with varying data.

For instance, to select all Gift Transaction rows with any reference to "Exxon" in the Comment field, you must use a wild card. Otherwise, you would have to enter the complete and exact text of the desired Comment column(s).

The use of wild cards in the Millennium Reporter follows SQL LIKE Clause conventions as listed and described below.

Character	Will Match in Data
_ (underscore)	Any one and only one character
%	Any number of characters (including none)
[characterlist]	Any one character from the list of characters within the database

Wildcard Rules and Examples

When the system encounters one of the above characters used within an object, it will interpret it as a wildcard and follow the "rules" for that character. Therefore, to specify a _, % or ^ without having it be interpreted as a wildcard, it must be placed in brackets as part of a character list.
Character lists within brackets should not be separated by spaces or commas [akntz].
A range of characters may be designated within the character list in brackets by starting with the lowest character, hyphen, highest character [a-z].
Multiple ranges can be designated within one set of brackets. They are not separated by commas or spaces [a-dg-lr-z].
If a hyphen appears at the beginning or end of a character list, it will be interpreted as another character to be matched [-g-k].

The following chart gives several examples of the way the system will handle and interpret wild cards.

Substituting For	Object	Passing Data	Failing Data
Single Character	a_a	aaa, a3a, aBa	abba, aa
Multiple Characters	a%a	aa, aBa, aBBBa	abc, Aaa
Multiple Characters	ab%	abcdefg, Abc	aab, cab, a
Specified Character From List	a[kqwyz]a	aqa, aza, aka, aya	aaa, aba, aa
Specified Special Character From List	a[*#?]a	a*a, a#a, a?a	aba, aaa, a7a
Range of Characters	[a-z]	c, d, t, v	5, %., *

Remember that Contains and Does not contain are not upper/lower case sensitive. Therefore, %Alumni% will match, Is an alumni of State University.

Be aware, too, that the system will apply the rules mechanically, not intuitively! This means that if you use the object, %child%*, the system will also find "grandchild", "children", "Childress", "Children's Hospital", etc. You can get around this by making the object, %(space)child(space)% but that will not find comments that either begin or end with the word, "child." To include those, you would need to use %[^a-zA-Z]child[^a-zA-Z]

Top of Page