Reporting Worktables
When a set of criteria is run in the Millennium Reporter, the system automatically creates a standard worktable. The data in the worktable is determined by the selection criteria and the standard layout of the worktable. There are several different layouts for a standard generic worktable.
Profiles (GENERIC) Worktable Layout
The GENERIC worktable for the Profiles data tables is composed of a standard set of fields taken from the rows that are selected by the criteria, or taken from rows that are related to the selected rows. A chart of those fields follows.
|
Field Name |
Data Type |
Length |
Allow Nulls |
Description |
|---|---|---|---|---|
|
id_number |
varchar/varchar2 |
10 |
Y |
The ID number for the constituent to whom the key row belongs. If the keyfileno = 80 (Chart of Accounts), this holds the chart_code for the account of the row.
Note: Even though the database allows nulls, Millennium always populates this field with values. |
|
keyfileno |
varchar/varchar2 |
2 |
|
The number of the Millennium Table in which this row resides. This may be any table. See Table Numbers. |
|
timekey |
Numeric |
9 |
Y |
The number that uniquely identifies the key row. If the keyfileno = 80 (Chart of Accounts), this will be 0.
Note: Even though the database allows nulls, Millennium always populates this field with values. |
|
total1 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total2 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total3 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total4 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total5 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total6 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total7 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total8 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total9 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total10 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total11 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total12 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total13 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total14 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total15 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
grouptag |
varchar/varchar2 |
1 |
|
The letter of the criteria group that was responsible for the selection of this row. If groups were not used in the criteria, this field will be set to A. |
|
spouse_id |
varchar/varchar2 |
10 |
Y |
The ID number for the constituent's spouse. |
|
combine |
varchar/varchar2 |
1 |
|
A flag that indicates whether the name field in this worktable row is or is not a combined name. |
|
name |
varchar/varchar2 |
60 |
Y |
The formatted form of the chosen Name row. |
|
sortname |
varchar/varchar2 |
30 |
Y |
A variation of the formatted name
field, used for sorting. Sometimes called "Smashed name". |
|
salutation |
varchar/varchar2 |
60 |
Y |
A system-created, salutation form of a Name row. This field is generated using a Name row of the Name Type that you specify. See Salutation Note. |
|
addrkey |
int |
9 |
Y |
The Time field from the chosen Address row. |
|
varchar/varchar2 |
20 |
Y |
If the 'Decrypt Columns'
report option is ON: If keyfileno is 00 (Basic Data), holds the decrypted
value of the coressnum field. |
Chosen Address
A worktable may include fields with a prefix of addr and such a field is referred to as a Chosen Address field. This terminology is used because you may choose to have either the Selected or the Preferred Address row identified in the worktable. You may identify which of these is the "chosen" Address row in advanced report settings (see Options, Report, Address) in the Millennium Reporter.
- If the selection criteria includes a reference to the Address table (either as the key table or as an additional criteria line), the Address row that causes the key row to pass the criteria is called the Selected Address.
- If you choose Preferred in the report options, the worktable will contain the address key (addrkey) from the Valid this Month, Preferred (addrmc1) Good or Returned address locator (addrlocatr) address row. If none is found, the addrkey field in the worktable will be null.
- If you choose None in the report options, then the addrkey field in the worktable will contain 0.
Chosen Name
An advanced report setting in the Millennium Reporter (See Options, Report, Worktable Options) form allows you to specify that a Name row of a particular Type be identified in the export worktable. If the selected constituent has no Name row of the Type that you specify, the system will choose the first Name row found (which will usually be the Preferred Name row. Most report templates identify the Preferred Name as the chosen Type but you may alter this in your report if you have a need to do so. It is not necessary for the selection criteria to include a reference to the Names table in order for this behavior to occur.
Salutation
The salutation field will hold the Salutation name that you have chosen, either by default or by editing the default choice for this report. The default Salutation is shown in the advanced report settings form, accessed via the Options function's, report button (See Options, Report, Include Salutation). You may alter this choice using that form. It is also possible to suppress the creation of the Salutation name field in the export worktable by leaving the Include Salutation check box unchecked in advanced report settings form.
Sortname
The sortname field is a variation of the formatted name. In general, it formats a name to all upper case, puts the last name first, sets it off by a back slash, and eliminates certain characters. This allows you to arrange rows in ASCII order and have them appear in alphabetic order at the same time. It may also be called the "Smashed Name."
If you have chosen to use the combined spouse names, the name field will hold the formatted name of the spouse pair, regardless of which of the pair owns the row that was selected. The Sortname field will hold the compressed or rearranged form of the name of the one constituent who owns the selected row. Therefore, while the NAME field might hold the data, Mr. and Mrs. Gary Little, the SORTNAME field might hold the data, LITTLE/PATRICIA ANNE. If you choose to sort based on the SORTNAME field, and print the NAME field, you will have Mr. and Mrs. Gary Little in the place in the list where Patricia Anne Little's name fits in sort order.
Events Worktable Layout
The Generic worktable for the Event data tables is named GENERIC_EVENT and includes a standard set of fields taken from the rows that are selected by the criteria, or taken from rows that are related to the selected rows. A chart of those fields follows.
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
id_number |
char |
10 |
The Event Code for the Event row to which the key row is linked.
Note: Even though the database allows nulls, Millennium always populates this field with values. |
|
timekey |
numeric/number |
13,0 |
The unique number, based on the time and date that this row was created that uniquely identifies the key row.
Note: Even though the database allows nulls, Millennium always populates this field with values. |
|
keyfileno |
char |
2 |
The file number of the data table for the key row. |
|
grouptag |
varchar/varchar2 |
1 |
The letter of the criteria group that was responsible for the selection of this row. If groups were not used in the criteria, this field will be blank. |
|
name |
char |
110 |
The name of the Event to which the key row is linked. |
Ecommerce Users Worktable Layout
The Ecommerce Users Worktable is used by the standard Online Directory Report and Online Directory Export reports, and is named GENERIC_ECOMM. Its purpose is to provide the decrypted value for your constituents' Online Directory passwords for use with these reports. Worktables that use this layout cannot be saved.
The table's structure is as follows:
|
Field Name |
Data Type |
Length |
Allow Nulls |
Description |
|---|---|---|---|---|
|
id_number |
char/char |
10 |
Y |
The ID number for the constituent to whom the key row belongs. If the keyfileno = 80 (Chart of Accounts), this holds the chart_code for the account of the row.
Note: Even though the database allows nulls, Millennium always populates this field with values. |
|
keyfileno |
char/char |
2 |
|
The number of the Millennium Table in which this row resides. This may be any table. See Table Numbers. |
|
timekey |
Numeric |
9 |
Y |
The number that uniquely identifies the key row. If the keyfileno = 80 (Chart of Accounts), this will be 0.
Note: Even though the database allows nulls, Millennium always populates this field with values. |
|
total1 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total2 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total3 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total4 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total5 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total6 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total7 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total8 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total9 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total10 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total11 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total12 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total13 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total14 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
total15 |
money/number |
9 |
|
The field that was calculated based on the definition of the Total within the criteria. If not defined in the report, the field will be blank. |
|
grouptag |
varchar/varchar2 |
1 |
|
The letter of the criteria group that was responsible for the selection of this row. If groups were not used in the criteria, this field will be set to A. |
|
spouse_id |
char/char |
10 |
Y |
The ID number for the constituent's spouse. |
|
combine |
varchar/varchar2 |
1 |
|
A flag that indicates whether the name field in this worktable row is or is not a combined name. |
|
name |
varchar/varchar2 |
60 |
Y |
The formatted form of the chosen Name row. See Chosen Name Note. |
|
sortname |
varchar/varchar2 |
30 |
Y |
A variation of the formatted name field, used for sorting. Sometimes called "Smashed name". See Sortname Note. |
|
salutation |
varchar/varchar2 |
60 |
Y |
A system-created, salutation form of a Name row. This field is generated using a Name row of the Name Type that you specify. See Salutation Note. |
|
addrkey |
int |
9 |
Y |
The Time field from the chosen Address row. |
|
core1passw |
char/char |
1 |
Y |
The value of the core1passw field - a flag to indicate that the user must change his or her password the next time her or she logs into the Online Directory. |
|
corepwdec |
char/char |
30 |
Y |
The decrypted value of the corepwdec (password) field. |
Audit Worktable Layout
The Audit worktable layout for the data tables for which you can enable auditing will be composed of a standard set of fields taken from the rows that are selected by the criteria, or taken from rows that are related to the selected rows. A chart of those fields follows.
|
Field Name |
Data Type |
Length |
Description |
|---|---|---|---|
|
primarykey |
numeric/number |
13 |
Specifies the primary key that uniquely identifies the row in the table. |
|
changetype
|
char |
6 |
Identifies the type of change to the row in the table, such as insert, update, or delete. |
|
changerowtype |
char |
6 |
Identifies the row before the changed row. Used only for updates. |
|
changedate |
datetime/date |
|
Identifies the date and time that the row was last updated. |
|
changeuser |
char |
30 |
Specifies the user name of the user making the change to the row. |
|
tablename |
char |
30 |
Specifies the name of the data table. |
|
mill_key |
numeric/number |
13 |
Specifies the Millennium primary key. |
|
mill_id |
char |
10 |
Specifies the Millennium ID number. |
|
column_changed |
char |
10 |
Specifies the name of the column where data was changed. |
|
Before_value |
char |
3000 |
Specifies the data entered into the row before the change. |
|
After_value |
char |
3000 |
Specifies the data entered into the row after the change. |
Serial Receipt Worktable Layout
When printing and reprinting serial receipts, the system creates a temporary worktable for Crystal Reports to use to format the printed receipts. Donor, receipt and gift/dues transaction information are included for each record in the worktable. The temporary worktable has a system-generated name, and the name will be different every time serial receipts are printed or reprinted. Just prior to 'passing' the temporary worktable to a Crystal Reports format, the system will name it GENERIC_SERIALRECEIPT.
Important! This temporary worktable cannot be saved through the Millennium interface.
The Serial Receipt worktable structure is as follows.
|
Field Name |
Data Type SQL/Oracle |
Length |
Description |
|---|---|---|---|
| o_key | numeric/number |
13 |
The primary key that uniquely identifies the receipt row in the table. |
| o_id | char | 10 | Donor information: id number |
| o_formatnm | money/number | 110 | Donor information: formatted preferred name |
| o_addrkey | numeric/number | 13 | Donor information: address key from preferred address (or from the address with the utilization run time option as selected by the user). |
| o_address | varchar/varchar2 | 356 | Donor information: formatted address of o_addrkey: formatted as line1, line2, line3, city, state (value), country (value) and zip. |
| o_line1 | char | 60 | Donor information: address line 1 of o_addrkey |
| o_line2 | char | 60 | Donor information: address line 2 of o_addrkey |
| o_line3 | char | 60 | Donor information: address line 3 of o_addrkey |
| o_city | char | 30 | Donor information: address city of o_addrkey |
| o_state | char | 65 | Donor information: address state (value) of o_addrkey |
| o_country | char | 65 | Donor information: address country (value) of o_addrkey |
| o_zipcode | char | 15 | Donor information: address zipcode of o_addrkey |
| o_prefix | char | 4 | Receipt information: receipt prefix. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_rcptnum | char | 10 | Receipt information: receipt number. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_rcptkey | numeric/number | 13 | Receipt information: primary key from the Serial Receipts (serialreceipt) data table. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_rcptamt | money/number | 19, 4 | Receipt information: total receiptable amount for all transactions that are included on the receipt (eligible amount ). If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_paytot | money/number | 19, 4 | Receipt information: total amount for all transactions that are included on the receipt receipted (eligible amount + value of advantage). If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_npaytot | money/number | 19, 4 | Receipt information: total non-receiptable amount for all transactions that are included on the receipt (value of advantage) . If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_paycount | int | 10 | Receipt information: the number of transactions on the receipt. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_paymindt | datetime/timestamp | Receipt information: earliest transaction's process date on the receipt (giftprocdt/duesprocdt). If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. | |
| o_paymaxdt | datetime/timestamp | Receipt information: latest transaction process date on the receipt (giftprocdt/duesprocdt). If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. | |
| o_donyear | char | 4 | Receipt information: the year part of the transaction's process date (giftprocdt/duesprocdt). If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_gendate | datetime/timestamp | Receipt information: date that the receipt was created. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. | |
| o_printcnt | int | 4 | Receipt information: the number of times that the receipt has been printed. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. |
| o_ltprntdt | datetime/timestamp | Receipt information: the last date on which the receipt was printed. If the user selected the Combine multiple transactions on one receipt run time option, then the value of this field is repeated for every gift or dues transaction that is included on the receipt. | |
| o_pylinkky | numeric/number | 13 | Gift/Dues transaction information: for pledge payments, this field is the transaction's pledge key (giftplgkey/duesplgkey). For gifts, this field is the transaction's primary key (giftkey/dueskey). |
| o_payfile | char | 5 | Gift/Dues transaction information: '05' for gift transactions or '08' for dues transactions. |
| o_paykey | numeric/number | 13 | Gift/Dues transaction information: the transaction's primary key (giftkey/dueskey). |
| o_paydate | datetime/timestamp | Gift/Dues transaction information: the transaction's process date (giftprocdt/duesprocdt). | |
| o_payyear | char | 4 | Gift/Dues transaction information: the year part of the transaction's process date. |
| o_paytend | char | 65 | Gift/Dues transaction information: tender (value) |
| o_payamt | money/number | 19, 4 | Gift/Dues transaction information: the total amount for this transaction - receiptable amount + non-receiptable amount. |
| o_rpayamt | money/number | 19, 4 | Gift/Dues transaction information: receiptable amount for this transaction (eligible amount). |
| o_nrpayamt | money/number | 19, 4 | Gift/Dues transaction information: the non-receiptable amount for this transaction (value of advantage). |
| o_paysourc | char | 10 | Gift/Dues transaction information: '05' for gift transactions or '08' for dues transactions. |
| o_seq | int | 10 | Gift/Dues transaction information: the index number of the transaction on the receipt. Applicable if the user selected the Combine multiple transactions on one receipt run time option. For example, if 5 transactions are included on one receipt, then the first transaction in the worktable has o_seq = 1, the second transaction in the worktable has o_seq = 2, and so on. |
| o_camp | char | 6 | Gift/Dues transaction information: the transaction's campaign (giftcamp/duescamp code). |
| o_campdesc | char | 65 | Gift/Dues transaction information: the transaction's campaign (giftcamp/duescamp value). |
| o_campyr | char | 4 | Gift/Dues transaction information: the transaction's campaign year (giftcampyr/duescampyr). This is a four-digit year between 1600 and 2200. |
| o_acctnm | varchar/varchar2 | 80 | Gift/Dues transaction information: the transaction's purpose (giftacctnm/duesacctnm). |
| o_gikdesc | varchar/varchar2 | 4000 | Gift-in-kind information: if the transaction's tender is Gift in Kind (gik), the item description, formatted as 'Description: ' + gikdesc |
| o_gikappr | varchar/varchar2 | 4000 | Gift-in-kind information: if the transaction's tender is Gift in Kind (gik), information about the appraisal, formatted as 'Name: ' + gikappname + ' Phone: ' + gikphone + ' Company: ' + gikappcpny + ' Disposition: ' + gikdispos |
| o_giktype | varchar/varchar2 | 4000 | Gift-in-kind information: if the transaction's tender is Gift in Kind (gik), the item type formatted as 'Type: ' + (giktype value). |
| o_quiddesc | varchar/varchar2 | 4000 | Quid pro quo information: if the transaction has a linked quid record, the type of quid (quidtype value). |
| o_quidval | money/number | 19, 4 | Quid pro quo information: if the transaction has a linked quid record, the quid value (quidvalue). |
| o_message | char | 70 | Receipt information: the optional reprint message that users can specify when reprinting receipts (only). |