Years Utility

Using the Years Utility is a two-step process:

1. Access Step 1 by navigating to X:\Inetpub\WWWRoot\Mill\Tools and double-clicking SetYearsParameters.exe. It executes in its own window (not from within the Millennium interface).
2. Access Step 2 by using Millennium Explorer, Reporting>New Criteria>Custom Profile or Reporting>New Criteria and then any one of the reporting libraries to select a report format template.

Summary data rows hold information that is based on information in the Gift data rows, rather than unique data that is inserted and updated using standard data entry procedures. Data f or the Summaries data rows is maintained through the use of the Summaries utility and this Years utility. While the Summaries utility is primarily designed to assign information calculated in Totals in the Millennium Reporter, the Years utility is designed to identify the year in which the first, latest, or largest transaction occurred, or to perform counts of the number of years in which transactions were made.

Using the Years utility is a two step process. You must first launch the utility interface (independent of the standard Millennium interface) and create and save a definition for a single Summary row using one of the system-defined Years functions. To complete the process, you must then run a set of criteria through Millennium Reporter that selects the constituents whose Summary data rows you want to create or update, and which specifies the Years utility for the final step of the run. Since there can only be a single set of parameters (a single definition) that may be saved for use with the Years utility, the system does not need to prompt you for the name of that definition (as is the case when using the Summaries utility.)

In general, the Years utility counts the number of years or identifies the particular year in which various circumstances occurred. It may count or identify any one of the following:

• Year of Largest Transaction. The utility locates the transaction in the Gift table with the largest amount (amount will depend on the type of transaction and utility options that are set) and then identifies either the calendar, fiscal, or campaign year (at your option) associated with that transaction.
• Year of Last Transaction. The utility locates the transaction in the Gift table with the most recent calendar, fiscal, or campaign year (at your option) associated with that transaction.
• Year of First Transaction. The utility locates the transaction in the Gift table with the earliest calendar, fiscal, or campaign year (at your option) associated with that transaction.
• Total Years of Giving. The utility identifies the calendar, fiscal, or campaign years (at your option) of all giving transactions and then counts the number of different years in which giving occurred.
• Total Years of Consecutive Giving. The utility counts the number of years (in a row) immediately prior to the current year (calendar, fiscal, or campaign year at your option) in which giving occurred. If the Include Current Year in Consecutive Years Giving option is checked, then the current fiscal, calendar, or campaign year will be included in the count, but the count will not be reset to 0 if a constituent has not yet given a gift in the current fiscal, calendar, or campaign year.

Years Utility Principles

The Years utility uses the following principles.

• It examines a constituent's data in the gift table only.
• It will consider the spouse’s transactions in addition to the constituent’s transactions when counting the number of years or identifying a particular year for a constituent if the Include Spouse option is checked. The amounts of the spouse’s transactions will not be added into the amounts of the constituent’s transactions unless the Include Joint option is checked and the transaction is jointly owned with the spouse.
• It considers transactions that have gift types of gift (g), pledge payment (y), bequest (b), and cumulative (z), and any non-standard gift types that your site maintains.
• It will consider transactions that have gift types of pledge (p), match pledge (mp) and expectancy (e) if the Include Pledges option is checked.
• It will consider transactions that have gift types of credit (c), match credit (a) and pledge credit (r) if the Include Credits option is checked.
• It examines the giftamount field when considering transactions that are gifts of types other than pledge (p), match pledge (mp) or expectancy (e). If the Include Joint option is checked, it will include the full amount of transactions that are jointly owned. If the Include Match Due option is checked, it will include the unpaid balance from all match pledges that are linked to the transaction. If the Include Match Paid option is checked, it will include the paid amounts from all match pledges that are linked to the transaction.
• It examines the giftamtplg field, less any write-offs, when considering transactions that have gift types of pledge (p), match pledge (mp), and expectancy (e).(Include Pledge option must be checked). If the Include Joint option is checked, it will include the full giftamtplg, less any write-offs, of transactions that are jointly owned. If the Include Match Due option is checked, it will include the unpaid balance of all match pledges that are linked to the transaction. If the Include Match Paid option is checked, it will include the paid amounts from all match pledges that are linked to the transaction.
• It sums the amounts of all allocations of a transaction when determining the largest transaction.
• It uses the effective date, process date, or campaign year (at your option) from the primary allocation of a transaction when determining the fiscal, calendar or campaign year (at your option) of the transaction.

Years Instructions

Years Preliminary Procedures

Before using the utility, you must have created a corresponding entry in the Memory Names (memory_names) lookup table that will act as a label for the Years information that you will define. Remember that the Group field in the lookup entry determines the kind of formatting that will be applied to the data that is displayed. If the entry will be used to display a year, the Group should be No Format. If the entry will be used to display a count, the Group should be blank.

Defining the Years Utility Parameters

To complete the first step, access the Years utility interface by using the Windows Explorer and navigating to Mill\Tools\SetYearsParameters.exe. Double-click to execute. You will be prompted to log on, using an ODBC connection to the database.You must be the database administrator (sa or MILLORA user ID) or a Millennium system administrator in order to execute this utility. (When completing the step that uses Millennium Reporter, the user must have Insert and Update permissions on the memories table, and the user's security Views on any data tables referenced in the criteria will affect the selection results).

This interface is a standalone program that allows you to create and save a single set of parameters for the creation or updating of a Summary data row based on the Years functions. You may edit this set of parameters to create other Summary data rows based on a different Years function, as often as you want. When a set of selection criteria is run in the Millennium Reporter, specifying the Years utility, the currently saved Years parameters are those that will be applied.

The utility interface presents a set of options; several of the options will instruct the utility what transactions to include in its calculations and counts and determination of first and last, and other options will instruct the utility what amounts to include when calculating the largest transaction. Check Include Pledges if you want to include pledge (p), match pledge (mp) and expectancy (e) transactions for a constituent. Check Include Credits if you want the utility to include credit (c), match credit (a) and pledge credit (r) transactions. Check Include Spouse if you want the utility to include the spouse’s transactions. When calculating the largest transaction, if you want the utility to include the full amount of transactions that are jointly owned when calculating the largest transaction, then check Include Joint. If you want the utility to include unpaid balances of match pledges that are linked to a transaction then check Include Match Due. If you want the utility to include paid amounts from match pledges that are linked to a transaction then check Include Match Paid. Check Include Current Fiscal Year in Consecutive Years Giving if you want the utility to include the current fiscal, calendar or campaign year when counting the number of consecutive years of giving. Note that if the Include Spouse option is checked, all options selected for a constituent’s transactions will apply to the spouse’s transactions as well.

The utility interface presents the set of functions that may be activated, each with a check box. You may select any one of the functions and when you do so, a pull-down list box will appear to its right, showing the entries from the Memory Names lookup table.

Below the Memory Assignment area is an area labeled, Date Settings, and containing one list box that allows you to define a "year" for the purposes of this utility.

The Year to Process box contains the items Effective Year, Process Year, and Campaign Year. The item you choose determines which of the fields from the Giving data row will be examined by the utility. If you choose the Campaign Year, the utility will disregard any giving dates and simply examine the Campaign Year field.

When you have set up the parameters for the utility to use, you must save the definition by clicking File, Save or the Save button on the Function Toolbar. The parameters will be saved in a row in the system table, memory_definitions. These settings are persist until you change them and save the new settings. Click File > Exit to close the utility window.

Running the Years Utility

The next step of the process is to run a set of criteria in the Millennium Reporter that selects all constituents for whom you want to create or update the Summary data row that corresponds to the parameters currently saved via the Years utility. When the criteria is run, the system will automatically create or update the selected constituents' Summary data rows, using the Years definition that is currently saved in the memory_definitions system table. If a selected constituent has no gifts or if the gift campaign year is blank, null, non-numeric, or less than 1000, then no data row will be created. If a selected constituent already has a Summary data row using the specified Memory Name, then that data row is updated. If no data row is found using the specified Memory Name, then one is created.

The following instructions assume that you are familiar with the use of the Millennium Reporter. For more information about any procedure described in the following steps, use the hyperlinks to read more on that subject.

1. Use the Millennium Explorer to navigate to the Millennium Reporter and access a Report Form via the item New Criteria, and then the item Custom, or access a Report Form via one of the report library templates.

2. In the Report Form, click on Options, Report. On the Report Options form, the following items are significant for the run of this utility.

   Set the Output List to Utilities.

   Uncheck the Include Salutation check box.

   Set the Address option to None.

   Set Remove Spouses to No.

   Set the Worktable Format to Generic.

3. Click OK to return to the Report Form.
4. The Formats/Utilities list should now show the utility names. Select Years Utility.
5. Set the Combine Names Options to Do not combine spouse names. Set the Duplicates Options to Remove Duplicates. Deceased Options may be set as you want.
6. Create criteria statements that will identify the constituents for whom the Summary rows for Years should be created or updated. You may use any data table as the key table in the set of criteria.

7. When all elements of the report criteria have been set and created, select File>Run. You will first be prompted to Save your criteria. A Run Options form will then display.

   Select the Run For User user name. Remember that this user must have Insert and Update permissions on the memories table, and that this user's security Views on any data tables referenced in the criteria will affect the selection results.

   Select the Queue you want to use for the run of the report.

   Set Format Names to No.

   The Save Export option may be set as you want, although we suggest that you save the file and print out a list of the Constituent ID's that were affected.

   The Print Criteria option may be set as you want, although we suggest that you print and save the criteria in a notebook as a log.

   You may set the run Priority as you want.

8. When all options are set as desired, click OK.

The report will immediately be sent to the Queue, where it will be launched in accordance with the Run Options you specified. You may check on its progress via the Queue Management item in the Millennium Explorer.

Years Utility Log File

The results of each Years Utility run are written to a log file named CreateYearsOut.log. The log will note the utility start date and time, the number of records selected by your criteria, the number of distinct id numbers within the records selected, the number of records read, the number of new Summary rows created, and the number of Summary rows that are updated. SQL or programming errors generated by the utility run will also be noted. If the utility successfully runs to completion, the finish date and time will print. The user monitors this log file by using Windows Explorer to navigate to its location in the Queue directory on the Queue machine. This log will continue to show information about all Years Utility runs until it is cleared or deleted. Clear the log by using Windows Explorer to navigate to its location on the Queue machine, open the log, and then delete its contents. Or, use Windows Explorer to navigate to its location on the Queue machine and then delete the file; when the Years Utility is next run, a new CreateYearsOut.log will be created.