Report Criteria Groups
Within the Millennium Reporter, there are two kinds of Groups that may be used in the construction of a set of criteria.
An Optional Group is a set of criteria statements that define a set or group of rows from the database that should be selected and identified in the export worktable. Each report may have multiple groups, each with a distinct definition for a set of rows from the database. To be included in the export worktable, a data row must conform to at least one of the groups of criteria statements (but need not conform to more than one group).
The second kind of Group is a Mandatory Group. This is a set of criteria statements that define characteristics to which all selected rows must conform, followed by two or more optional Groups. To be selected, a row must conform to the Mandatory Group criteria AND at least one of the optional Groups.
If your criteria statements use fields from the Chart of Accounts table, the criteria statements in all Report Groups will only be able to use fields from the Chart of Accounts table.
Each report may have up to twenty-five Groups, total.
The following information is included in this topic:
- Understanding Groups - explains the purpose and application of Optional and Mandatory Groups in reporting.
- Creating Groups - explains the mechanics of creating a Group of criteria statements.
- Mandatory Groups - explains the mechanics of creating a Mandatory Group.
- Editing Groups - explains the mechanics of altering an existing Group.
- Saving Groups - explains the process of saving the criteria statements that make up a Group, for use in another report.
Understanding Optional and Mandatory Groups
Optional Groups
When a set of report criteria is run, the Millennium Reporter compares the information in the criteria statements to the information in the data rows within the database and if the data rows conform, they are "selected". That means that identifying information from the row is written in an export worktable, which is then delivered to the formatting software. At times, you may want to select data rows that belong to distinctly separate groups and include the data from both groups in a single formatted result. The use of Groups in the Millennium Reporter allows you to perform this task with the run of a single report.
For example, let's say that you want to compile a list of constituents who have birthdays in May, who also have a Giving Transaction with a Type of Expectancy and an amount of at least $10,000 OR constituents who have birthdays in May and who have a lifetime Giving total of at least $10,000 (we'll assume for our example that information is stored in Summary 12). To select those two distinct groups of rows, you may use two distinct sets of criteria statements. To do so for the this example, those groups might read:
GROUP A
- Select all Basic Data where the Birth Month from the Basic Data page is equal to 'May'
- and where the Gift Type from the Giving page is equal to 'Expectancy Transaction',
- and where the Gift Amount from the Giving page is greater than or equal to '10000.
GROUP B
- Select all Basic Data where the Birth Month from the Basic Data page is equal to 'May'
- and where the Summary 12 from the Summaries page is greater than or equal to '10000'.
To be included in the formatted report, a data row must conform to either Group A or Group B. At times a constituent may own rows that belong to more than one group and in this case, the Duplicate Removal options may be used to eliminate redundancy, if that is appropriate for the purposes of your report.
See the topic, Creating Optional Groups for more information.
Mandatory Groups
There is one specialized kind of group, the Mandatory Group. As you can see from the example above, the criteria statement that specifies May birth dates, is present in both groups. Adding that line to both groups is one technique that assures that all constituents who are selected will have a May birthday. Another technique that could be used would be the creation of a Mandatory Group.
Mandatory Groups contain a set of criteria statements that define characteristics that are common to all groups that are defined in the report. Notice that in our example above, both Groups contain the statement that selects Basic Data rows showing a birth month of 'May'. Criteria statements that define characteristics that are common to all groups, may be removed from the individual Groups and placed in a Mandatory Group, as shown below.
MANDATORY GROUP
- Select all Basic Data where the Birth Month from the Basic Data page is equal to 'May'.
GROUP A
- Select all Giving where the Gift Type from the Giving page is equal to 'Expectancy Transaction',
- and where the Gift Amount from the Giving page is greater than or equal to '10000.
GROUP B
- Select all Summaries where the Summary 12 from the Summaries page is greater than or equal to '10000'.
In this case, only those with a birth month of May will be selected, AND then, only if they have either a Gift that conforms to Group A OR a Summary that conforms to Group B.
Mandatory Groups are only used when the report contains at least two optional Groups.
Note that this process is not necessarily as simple as moving all lines that are common to all Optional Groups to a Mandatory Group, due to the system behavior toward the key table and criteria line order. When the Millennium Reporter performs the selection, it does so by 'pasting' the mandatory criteria statements at the beginning of each of the optional group of criteria statements and then processing the selection. This means that the key table in the mandatory group becomes the key table for all groups. Also, the last criteria statement in the mandatory group will be adjacent to the first statement in each of the optional groups. If they refer to the same data table, this means that the same data row must satisfy that set of statements. Therefore, consideration should be given to the placement of criteria statement to ensure that the report will select data rows as you intend.
See the topic, Creating Mandatory Groups for additional information.
Creating Optional Groups
To create optional Groups within a set of report criteria, you must have either a new or an existing report criteria displayed in a Report form. When this is the case, the Add Functions must be activated and the Group function button should be in view (near the middle of the set of function buttons). The behavior at this point will vary, depending on the condition of the report that is displayed in the Report form.
Creating Groups in New Report Criteria
If you have not yet created any criteria statements in the Report form that you are using, you can click on the Group button to begin the criteria with the header line for a Group. The Millennium Reporter will automatically label this Group A as the first group. Following that line, you may create as many criteria statements as you want, using the standard procedures for Writing Criteria Statements. All statements will be considered part of Group A until you again click on the Group function button to add a new header line, which will automatically be labeled, Group B. From that point on, all new criteria statements will become part of the definition of Group B.
You may use the standard Cut, Copy and Paste functions to move or copy individual criteria statements from one Group to another or to move or remove a Group header line.
Creating Groups in Existing Sets of Criteria
When criteria statements already exist, and you want to place them into Groups, the behavior of the system varies depending on whether or not an individual criteria line is highlighted at the time that you click on the Group function button. the situation is as follows:
- If criteria lines have been constructed but none are highlighted, clicking the Group button will cause the line "Group A" to be placed at the very beginning of the report and "Group B" to be placed after the last line of criteria. All existing criteria statements will be assumed to be part of Group A, with new lines assigned to Group B.
- If criteria lines have been constructed and one is highlighted, that line will become the dividing point between the two groups. All lines above the highlighted line will become Group A and the highlighted line and all following lines will become Group B.
- If Groups have already been established and one of the lines within a Group has been highlighted, then that Group will be divided into two Groups and the next Group letter assigned to the second Group. All following Groups will be assigned the next Group letter in sequence.
There some additional rules of behavior in regard to optional Groups as described in the topic, Editing Groups.
Group Tags
When a report that uses optional Groups is run, each data row that is identified to the export work table does so because it passed all the selection criteria statements from at least one of the optional Groups. The letter of the Group that was responsible for the selection of that row is also placed in the export row, in the grouptag field.
Furthermore, a data row will be represented in the export work table for EACH group that it 'passes'. Therefore, if a particular data row conforms to both Group A and Group B, it will generate two rows in the export work table, one with a grouptag of A and one with a grouptag of B.
The Millennium Reporter processes the criteria statements in the order that the Groups appear in the report. Therefore, you can conclude that if a particular row in the export work table has a grouptag of B and has no corresponding row with a grouptag of A, that it did NOT conform to the criteria in Group A.
Creating Mandatory Groups
To create a Mandatory Group within a set of report criteria, you must have either a new or an existing report criteria displayed in a Report form. When this is the case, the Add Functions must be activated and the Mand(atory) Group function button should be in view (toward the left of the set of function buttons). The behavior at this point will vary, depending on the condition of the report that is displayed in the Report form at the time that you click on the Mand Group button.
- If the report has no criteria statements, the header line and label, Mandatory Group will appear as the first line. All criteria statements that are created are part of the Mandatory Group until you insert the header line for the first optional Group.
- If the report contains criteria statements, the Mandatory Group header line will become the first line of the report and all criteria statements become part of the Mandatory Group until you insert a header line for the first optional Group.
- If the report contains optional Groups when you click on the Mand Group button, Group A will be labeled as the Mandatory Group, Group B will become Group A, and so on.
Editing Groups
When Groups of criteria statements are in place in a report, the Group headers or the individual criteria statements within them may be edited, using the standard techniques for Cutting, Copying and Pasting criteria lines (see the Edit Functions topic), and the following behaviors:
- A new Group may not be created until or unless the previous Group contains at least one criteria statement.
- The header for Group A may not be Cut from the report if there are other Group headers present in the report.
- If Group headers (other than A) are Cut from the report, the criteria lines that followed that header become part of the previous Group.
- If a Group header (other than A) is Cut from the report, the remaining Group labels are automatically adjusted to remain in sequence. (A report could not have only a Group A and a Group C.)