Report Criteria Totals

Within the Millennium Reporter, a Total is a function that is performed as part of the run of a set of criteria. This function allows you to identify a set of either Gift or Dues Transaction rows that belong to the selected constituent, and then perform a mathematical or tallying calculation to produce a numeric result for that constituent. This resulting Total (the number) is passed along to the export worktable. Each report may define and use up to fifteen Totals.

Note: The Report Totals functionality does not apply to Event Reporting.

The following topics will be covered in this discussion of Totals:

• Understanding Totals - explains the purpose and application of Totals in reporting.
• Accessing Total Functions - explains the mechanics of accessing the forms needed to create, define, or edit Totals.
• Defining Gift or Dues Totals - explains the mechanics of creating Total criteria statements.
• Using a Total in a Criteria Statement - explains the mechanics of creating a report criteria statement that uses a Total.
• Editing Totals - explains the mechanics of altering an existing Total.
• Saving Totals - explains the mechanics of saving a Total definition for re-use in another set of report criteria.

Understanding Totals

Millennium stores all giving information in individual rows, based on individual Gift and Dues Transactions. At times in reporting, you need to examine a "subset" of those transactions for a particular purpose.

Defining a Total

For example, you might need to generate a list of constituents who live in a certain region, and include on the list the amount that each donated to your institution last year. In this situation, you would create report criteria statements that select constituents based solely on their Address information. In the same report, you will define a set of Total criteria statements that examine all the Gift Transactions that belong to the selected constituents (the ones with the "right" Region in their Address). The Total criteria will select all the Giving that were made last year. Then the Total will add up the Amount fields from that set of Gift Transactions. The amount of that Total does not determine whether or not the constituent is selected. The definition just determines which of their gifts will be included in the calculation. The calculated amount is delivered to the export worktable along with the other data from the selected Address row. This is the simplest application of Totals in a set of report criteria.

Using a Total

Next, take that same example but add the qualification that the list should only include constituents who live in that region and who gave more than $1000 last year. In this case, the report includes the same Address criteria and the same Total definition, but it also includes a report criteria statement that uses the Total that is defined elsewhere in the report. In this case, that report criteria would also read, "and where Total 1 is greater than '$1,000'". When a Total is used in a report criteria statement, it participates in determining whether the constituent is or is not selected.

Characteristics of Totals

When using a Total in a report criteria statement, that statement will precede the definition of the Total in the complete set of statements, because Totals are always placed at the bottom of a report, after the report criteria lines. The definition of the Total MAY NOT be the first item in a set of criteria.

Each set of report criteria may include up to fifteen different Total definitions. Each Total can be labeled with a description for easy identification, in addition to the system generated name (Total 1, Total 2, etc.). This descriptive name may be up to 142 characters in length. Therefore, when the criteria statements are displayed, the first line of a Total definition might read something like, "TOTAL 1 - Sum: Total 1999 Annual Fund." This line will be followed by all of the individual statement lines that define the Gift or Dues Transactions that should be included in the calculation of the Total.

Several "advanced" options may be applied to the definition of a Total. You may specify that the amounts from several different "linked" transactions be included or excluded from the Total. You may include any combination (including none) of the following:

• Include Match Due - for individuals selected for the report, will include match pledge balances from the companies that matched any of the selected transactions.
• Include Match Paid - for individuals selected for the report, will include match pledge payment amounts from the companies that matched any of the selected transactions.
• Include Match Credit - for individuals selected for the report that have received a credit transaction as a result of another individual's contribution, will include match credit amounts from the companies that matched any of the selected transactions. Note that this option will be "grayed out" unless one of the other Match options is selected.
• Include Spouse - for each constituent selected, will include the spouse's portions of joint transactions as well as all transactions made solely by the spouse that also meet the criteria. For this calculation, the system will look for a spousal relationship (types sp and lp) and if one is found, that constituent's giving information will be included. Spousal relationship types spnm and lpnm will not be included in the total.
• Include Joint - for each constituent selected, will include the constituent's portion of the joint transactions, as well as the joint giver's portion of those joint transactions.

Another option that you may set as part of the definition of the Total is the Calculation Amount. By default, the Totals will be set to calculate, using the Amount field from the Gift or Dues Transactions. You may change that default and have the Total examine one of the other amount fields from the transaction, instead.

You must choose one of the following options:

• Amount - the amount of the transaction, or for pledges, the balance.
• Asset Amount - for irrevocable pledges, gift, and revocable pledge payments, the Asset Amount equals the AMOUNT; for revocable pledges, the Asset Amount is 0.
• Pledge Amount - amount that stores the ORIGINAL amount of the pledge in a pledge transaction. Remember that for pledge transactions, the AMOUNT field contains the BALANCE of the pledge.
• Net Present Value - asset amount at the time the transaction was entered, based upon an accounting formula.
• Deductible Amount - the portion of the transaction amount that the IRS considers deductible.
• Custom Money 1 - the first of the institution custom-use amount fields.
• Custom Money 2 - the second of the institution custom-use amount fields.

You may specify the Calculation Type that you want to have the Total perform. By default, the system will sum the amount fields from each of the transactions.

You must choose one of the following options:

• Sum Transactions - The numeric value of the Total is the numeric value of the sum of the (designated) amount fields from all the Transactions that are defined by the Total criteria lines. Note that this sum may include the amounts from other, linked transactions belonging to other constituents, according to the options you choose in regard to match due, match paid, spouse, joint, or match credit.
• Count Transactions - The numeric value of the Total is a count of the number of Transactions that conform to the Total criteria definition (for that constituent). Note that this calculation counts only the transactions that belong to the constituent, not those that are linked but that belong to others (spouse, joint, match, etc.)
• Largest Transaction - The numeric value of the Total is the numeric value of the single, largest Transaction from the set that conforms to the Total criteria (belonging to the selected constituent), after any linked Transactions belonging to other constituents have been included, according to the options you choose in regard to match due, match paid, spouse, joint, or match credit.

If the criteria statements for the Total specifically select transactions with a Type of Credit, you may specify the kinds of Credits to be included. You may choose one of the following options:

• Include all credits - The system includes all Credit transactions that meet the Total criteria statements in the calculation of the Total.
• Include all unlinked credits - The system includes all Credit transactions that meet the Total criteria statements AND which are not linked to another transaction in the database (independently created credit transactions) in the calculation of the Total.
• Include all credits not linked to spouse records - The system includes all Credit transactions that meet the Total criteria statements AND which are not linked to a spouse's transaction in the calculation of the Total. These Credits may be independent or linked to another transaction, as long as the transaction to which it is linked does not belong to the spouse.

Access the Total Functions

To access the total functions, you should first have an open report that contains at least one criteria statement. (A set of report criteria cannot begin with the definition of a Total.) can be a new report that has not yet been saved or an existing one that you are editing.

The Total functions are available as buttons at the top of the Criteria section of the report form, including Gift Total, Dues Total and Use Total.

The Gift Total item activates a Total criteria form showing the Table Display Form for Gift Transactions. This form is nearly identical in appearance to the one that is used in the second step of creating report criteria. (The first step in that process is to identify a data table. It is not necessary to do that when creating a Total criteria statement because that is determined by choosing the Gift Total or Dues Total action item.) The use of this form is described in the next topic, Defining Totals.

The Dues Total item also activates a Total criteria form showing the Table Display Form for Dues Transactions. This form is identical in behavior to the one that is used for Giving. The use of this form is described in the next topic, Defining Totals.

The Use Total item activates a Use Total form. This form allows you to construct a report criteria statement that uses a Total as the field (or subject) of the criteria statement. Since you cannot "use" a Total that has not already been defined in the same report, the Use Total action item will be disabled until or unless at least one Total has been created. This criteria statement is part of the general report criteria that determines whether a constituent is or is not selected. A criteria statement using a Total might read: "...and where Total 1 is greater than '$1000".

If two or more Totals have been defined in the same report, the Use Total form allows you to specify one Total as the field portion and another Total as the object portion of a criteria statement. For example such a Report Criteria statement would read, "...and where Total 1 is greater than Total 2." The techniques for constructing such a statement are described in the topic, Using Totals in Criteria Statements.

Define Gift or Dues Totals

Totals are defined by a set of Total criteria statements that together will identify a set of Transaction rows that belong to the selected constituent. Those Transactions and the other options that are set for the Total allow the system to calculate a numeric value for the Total. That value will be recorded in the corresponding field in the export worktable along with the appropriate data from the selected row.

Once you have accessed the Total form needed to create or define a Total, you will see that the Gift Total form (or the Dues Total form) is nearly identical to that which you would use to create a line of Report Criteria, using the Giving (or Dues) table. Step One in the process of creating a line for a Total definition begins with the process of identifying a particular field to use as the subject of the statement. This may be done by clicking the text box for the desired field or by selecting the field name from the pull-down list at the top of the form. In either case, the field is identified and the Next button moves the process to the next step.

Step 2 provides the form required to identify a comparison and an object for the criteria statement. The procedures for doing so are the same as those involved in Writing Criteria Statements.

When both a comparison and object have been identified and you have clicked the Finished button, you will be given a form asking whether or not you want to create additional criteria statements as part of the Total definition. If you respond, Yes, you will be given a new, blank form to begin at Step 1 with a new line. If you respond, No, you will be given the Total Options form. This form is used to set or define a number of options for the system to use in calculating the Total.

They are described as follows:

• Total Name The system will assign each Total a number (from one to fifteen, in order) but you may also assign a descriptive name to the Total. This name may be up to 142 characters in length. We encourage you to enter enough text that others can easily distinguish the purpose and design of this Total from any others. This description is entered as free text at the top of the Total form. When the Total is displayed in the Report form, the header line for the Total will display the system-assigned number, the kind of calculation, and the descriptive name you entered.
• Total Options Gift or Dues Transactions may have links to several other transactions, belonging to other constituents. The MR allows you include the amount field from those linked transactions along with those that are defined by the Total criteria. There are several such transactions and you may click to place a check in the check box by each (and therefore include it in the calculation) in any combination with any of the others.
• Calculation Amount When signaling the system as to the specific amount field that you want to use for the purpose of calculating this Total, you must activate the radio button by the option you prefer. When you do so, the other options will automatically be toggled off. You may choose the Amount field, the Asset field, the Pledge Amount field, the Net Present Value, the Deductible Amount, the Custom Money 1, or the Custom Money 2 field as the focus of the Total. Each Total may focus on only one kind of amount field, but within a report, each Total may use a different amount field, if desired.
• Calculation Type The Total function can perform different types of calculations on the set of transactions that are identified by the criteria statements. You must click on the appropriate radio button to instruct the utility which to perform. It can produce a sum of the amount fields from the set of transactions (using the Calculation Amount that you chose in the previous option). It can count the number of transactions that are included in the selected set. It can identify the largest of the transactions from the selected set and use the specified amount as the data for this Total.
• Credit Options If the criteria statements for the Total include statements such as, 'Transaction Type is equal to 'Credit', or 'Transaction Type is in the list of 'Credit', (and any other Type), then you may specify that the system further restrict the kinds of Credit transactions to include in the Total calculation. You may include all selected Credits, only those that are not linked to any other transaction, or only those that are not linked to a spouse's transaction.

Use Totals in Criteria Statements

When a Total has been defined within a report, that Total may be used as the field (subject) of another Report criteria statement, or if multiple Totals are defined in the report, they may be used as the field and the object of a Report criteria.

To construct such a Report criteria statement, you must first define the Totals. Next, you must activate the Use Total action item that is available as one of the Add functions. This item will not be available until or unless a Total has already been defined in the current report. When you do so, you will be given an abbreviated form for use in constructing the criteria statement.

There are three elements to be identified for the statement:

• The first pull down list box is used to select the name of the Total that you want to use as the field in the statement. The pull down list will display the system assigned names of all Totals that have been defined in the report.
• The second pull-down list box is used to select the form of comparison that the criteria statement should use. The available comparisons are limited to those that are appropriate for numeric columns. This includes: equal, not equal to, greater than, greater than or equal to, less than, less than or equal to, between, and not between.
• The final list box is used to select the object for the criteria statement. There are two options for this text box, as specified by the radio buttons next to it. You may use a numeric value that you type into the text box, or you may use another Total.
• To use a numeric value, you may type the number into the text box without punctuation (dollar signs or commas).
  To use another Total, you must click on the Total radio button next to the text box. When you do so, it will become a pull down list box, showing the names of all Totals that have been defined in this report. You may click on one to select it.

When the three elements have been identified for the Report criteria statement, click on OK to add the criteria statement to the set of statements shown in the Report form. Note that when you create such a statement using a Total, the system will automatically position the statement above the definition of any Totals.

Edit Totals

While creating a definition for a Total, the system will ask after each line is constructed, whether you want to create additional lines within the Total. When you respond, No, the system will return you to the Report form, with the Total definition shown at the bottom of the set of report criteria statements. If you want to make changes to the Total or to any of the individual lines, you must do so via the MR's editing functions as described below.

Existing Criteria Lines

To edit the comparison or object of an individual line of criteria in a Total definition, you must first click to highlight that specific line, and then the Edit function, and then the Change item. This will present the appropriate Total form, used to select the comparison and object elements of the statement. The existing elements will appear in the text boxes, and are available for editing. Note that the field portion of the Total criteria statement is shown as text at the top of this form and it is not available for editing. To change that portion of the statement, you will need to remove that entire line and construct a new one, using the appropriate field.

When any appropriate edits have been made, using the standard techniques for Writing Criteria Statements, click on OK to replace the original version of the statement with the edited one.

Individual lines from a Total definition may be Cut, Copied, and Pasted, using the standard techniques as discussed in Edit Functions. Note that these individual lines may be pasted into another Total or even into the main body of the Report Criteria. They may not be pasted into another report, however.

Edit Total Options

If you want to edit any of the Total Option settings (name, calculation amount, calculation type, etc.) you must first highlight the title line for the Total. This is the first line of the Total definition that might read, "Total 1 - Sum: Total 1997 Annual Fund". When that line is highlighted, click on the Edit, Change items. You will be given the Option form with the current settings shown. You may change any of these settings, using standard techniques as described in the topic, Defining Gift and Dues Totals. When all options are set as you want, click on OK to save the edits you made or Cancel to end the process without making any changes.

Cut, Copy and Paste Totals

If you want to remove an entire Total definition from the report, you may do so by first highlighting the header (title) line for the Total, and then clicking the Edit, Cut function items. The entire definition and title line of the Total will be removed. Note that when you cut an entire Total definition, the system will automatically re-number the remaining Totals appropriately.

For example, if you had defined Totals 1 through 5 in the report and then you Cut Total 3 from the report, the system will re-number Totals 4 and 5 to become Totals 3 and 4. This could have an impact on the Report criteria lines if you had used Totals 3, 4 or 5 in any of those statements. In that case, you must verify that the Report criteria statements using Totals, are properly aligned (numerically) with the Total definitions that remain.

Once a Total definition has been Cut from its place, it is held on a clipboard and can be Pasted into a different place in the series of Totals in that report. If you do so, the system will re-number the Total definitions and you must be cautious in verifying that the new numbering scheme conforms to any Report criteria statements that already exist.

It is more likely that you would want to Copy a Total and then Paste it into the Report, and then edit selected criteria lines. This technique is useful if you are creating multiple Totals that are similar, and therefore use several criteria lines that are the same. For example, if you want to define a Total that calculates the constituent's giving in 1996 to each of three Campaigns, you might construct all the criteria statements for the first one and then copy it into the report again. It may be simpler to edit the Campaign object in the second Total than to create a second Total "from scratch". You would repeat the process for the third Campaign.

Add Criteria Lines to a Total

If you want to add more criteria lines to the definition of a Total, you must highlight the first line (the title line) of the Total and then click on the Add, New Line function items. When you do so, you will be given either a Gift Total form or a Dues Total form which you may use to create the additional Total criteria statement. When the statement is complete, you will be given the opportunity to create more statements or not, as you prefer.

Saving Totals

When a Total has been defined within a set of report criteria, you may save the set of criteria statements defining the Total, independently of saving the entire report. When you do so, you will be prompted to assign a name for the saved Total and it will automatically be stored in the report_criteria system table in the MILLREPORTS database.

When a Total has been saved in this way, the definition may be retrieved and copied into another report by any user. The saved Total includes the criteria statements, the Total Name, and all of the options that were set in the original Total. When a saved Total is copied into another report, the number that is assigned to the Total is in accordance with that report, not necessarily the same number that the Total carried in the report in which it was created and saved.

As an aid to reporting consistency within your institution, you may want to create a standard set of Total definitions, with the desired criteria statements, and the desired settings for the options on the handling of joints, credits, matches, and pledges.

To Save Totals

To Save a Total, you must first have one defined as desired in the current set of report criteria. This may be one that you created using the Add, Gift Total or Dues Total action or one that already existed in a set of criteria, whether that set of report criteria has yet been saved or not. You must identify a Total by clicking the header statement line (TOTAL 1 - Sum: Most Recent Year, for example) to highlight it. When you do so, File > Save Total is enabled and you may click on it to proceed.

A form will be presented, prompting you to Enter a name and click OK to save the total criteria. Below that instruction, you will see a list box to the left, containing the names of all existing saved Totals, a text box to the right, and an Add button between the two. You may either type in a name that you want to use for the Total, or highlight an existing name in the list and click Add (or double click on the name) to move it to the text box at the right. When you click OK, if the name you chose duplicates an existing one, you will be asked to confirm your desire to over-write the existing one.

Re-Use a Saved Total

To retrieve a saved Total and copy its definition into a report, you must activate the Add function items and then choose Saved Criteria from the action items shown in the second row of buttons on the main report form. You will be presented with a form prompting that you Select the saved criteria to be added. At the top of the form, you will see two radio-style buttons, labeled Add saved criteria and Add saved Total. This form is used to identify either saved Totals, or saved sets of criteria statements. If necessary, you should click to activate the Add saved Total button and when you do, the list box below and to the left will show the names of all saved Totals (for all users). You may scroll to locate the desired Total, highlight it and click the Add button to move the name to the text box at the right, or you may double click on the name of the Total in the list. Alternately, you may type the exact name of the desired Total into the text box.

When the name has been specified, and you click on OK, the entire set of criteria statements and supporting settings for the Total are added to the current report. The Total will be added to the end of the set of any existing Totals in the report, and assigned the next number in sequence. Once a saved Total has been 'pasted' into a report, you may edit it in any way that you want. Those edits will not affect the definition of the saved Total.

Top of Page