More information

Defining Filter Criteria

Writing Criteria Statements

Groups in Criteria

Reporting World

Site Specific Notes

Criteria Organization

When criteria statements are displayed in a report form, they can be read as a sentence that describes the action that will take place when that set of criteria is run. The first line will begin, "Select all..." followed by the name of the table. All subsequent criteria lines will begin, "and where". This sentence emphasizes the fact that in order to be selected, the row must conform to all of the statements (at least all in that Group.) But usually the criteria statements refer to many different tables in a single set of criteria statements. To understand the importance of the first line of criteria, see Key Table below.

Often, you want to examine data from several fields within the same table. At times, you will intend to examine that data in those fields in the same row. At other times, it may not be significant if the data comes from the same row, or from two different rows (from the same table). This is a factor that can be controlled through the Criteria Line Order.

Key Table

When the Millennium Reporter runs a report, it is selecting rows from the database and not just the constituents who own the rows. To illustrate the significance of this distinction, and how it impacts the process of writing criteria statements, we'll use the following example:

You want to generate a list of all gifts made by foundations. You might create the following set of criteria statements.

If your intention is to create a list of gifts, then this set of criteria is appropriate for the task. When this report is run, it will "Select all Giving..." In fact, an expanded version of the criteria statements could accurately read, "Select all Gift rows where the Gift Type field holds the lookup table entry 'gift' and where the constituent who owns that Gift row also owns a Basic Data row that contains a Constituent Type field that holds the lookup table entry 'Foundation'.

Note that in the example, the Basic Data rows are only examined as part of the determination as to whether or not the Giving row qualifies for selection. The Basic Data row is not the one that is selected. Each Foundation could own many such Gift rows and therefore be represented in the selection many times. If you are compiling a list of gifts, then this is appropriate.

If the conditions of the example are altered so that you want to compile a list of all foundations that have made a gift, the most efficient approach would be as follows:

In this configuration, the Key table is Basic Data and the selected rows will come from the Basic Data table; not from the Giving table.

Once a set of rows have been selected, the export work table is created, using the ID number of the constituent who owns the selected row, the key field from the selected row (also known as the Time Stamp for that row), and other related data. The ID number and the key field provide a link to other data that may be needed by either the Millennium Reporter or the report formatting software (for example, Crystal).

Initially, each row in the export work table will represent one selected row from the Key table. Therefore, a constituent may be represented many times in the export. There are report options in the Millennium Reporter that you may use (if desired) to remove multiples (multiple rows belonging to the same constituent), remove one spouse's row (when combined names are used), and remove rows belonging to deceased constituents from the export. Therefore, in its final form, the export work table may have many fewer rows than the number of rows that were originally selected.

When a report contains a Mandatory Group, the first criteria statement identifies the Key table for the entire report. However, when a report contains optional Groups without a Mandatory Group, the first criteria statement in each Group identifies the Key table for that Group. It is possible that another Group in the same report could use a different Key table.

Chart of Accounts

If your intention is to select rows from the Chart of Accounts table, then only Chart of Accounts table fields can be included in your criteria. You cannot add Chart of Accounts criteria to a report whose key table is a table other than Chart of Accounts, nor can you include criteria lines from other data tables in a report whose key table is the Chart of Accounts.

Criteria Line Order

There are certain principles you should understand concerning the organization of criteria statements within a report. One of those is the identification of the Key Table (by the first line of criteria in a report). Another important principle is that of consecutive vs. non-consecutive criteria statements, which is discussed in this topic.

Consecutive vs. Non-Consecutive Criteria Statements

Often, you will want to construct several lines of criteria that deal with data from the same row within a table. To ensure that criteria statements that examine data from the same table also examine the same row, the criteria statements must be adjacent to each other in the set of statements that make up the report. Contiguous statements that examine different fields in the same table, will only allow the selection to pass if the two (or more) statements are true within the same row.

For example, the following two criteria statements, if positioned one after the other, will find Employment rows belonging to constituents who are actively employed as programmers.

If you are actually looking for constituents who are actively employed (in any occupation) and who have ever been employed as a programmer, then the two criteria statements should be separated by another criteria statement that references another table.

Note that this 'split' form of criteria statements allows the constituent to meet the criteria with two different Employment rows - but it does not require that the statements apply to different rows. Presumably, you would want the constituent to be included if he or she owns one Employment row that conforms to both statements.

In a large majority of cases, you will be looking for data in the same row, rather than as shown in the second example. You must take care that you use the form (contiguous or split criteria statements) that is appropriate to your intentions.

Groups and Criteria Line Order

An understanding of the principle of adjacent line order is important in connection with the use of Groups. Within an optional Group, all principles of criteria organization apply just as if the Group of criteria were a stand-alone report. The first statement determines the Key Table, adjacent statements referencing the same table will require that the data be appropriate in the same row, a Group may not begin with a Total definition, and so on. The principle of adjacent lines explains the reason that criteria statements cannot simply be moved from an optional group to a Mandatory Group. Statements that appear in a Mandatory Group will not (necessarily) be applied to the same row as those that appear in a following optional Group.

For example, if you want to compile a list of constituents who are actively employed by SoftTech (in any position) OR who are actively employed (anywhere) as a programmer, you might use the following set of criteria.

At first glance, you might be tempted to remove the Status = Active qualification to a Mandatory Group however, that would not produce the desired results. If the Status statement were separated as shown below, you would select constituents who own any active Employment row (regardless of position or firm) AND who also own either an Employment row with a position equal to programmer, OR a firm equal to SoftTech.

Top of Page