Writing Report Criteria

To create a new criteria statement, you must first access a Report form by either

• displaying an existing report
• beginning a new report from a standard report library template.
• beginning a new Custom Profile or Custom Event report that uses your personal report default settings.

Once a Report Form is accessed, select the Criteria menu to add or edit report criteria.

To review the components of a criteria statement, see Understanding Criteria. For a discussion of the significance of the order in which criteria lines appear in a report, see Criteria Organization.

Constructing Criteria Statements

Use the Report form to construct Criteria statements. The process for creating a criteria statement includes the following steps.

Step One - Identifying the Table

Each Criteria statement you write focuses on a particular field from a particular data table in the database. The Table Display Form instructs you to 'Select a table and field and click Next.

1. Click a table name in the Available Tables list.
2. Select a field from the selected table:
   • The Field Name drop-down list contains all fields in the selected data table. This list includes the names of the non-displaying fields, as well as the names of any fields that your site may have created and added to that data table, as long as the names of these custom fields have been added to Field List (fieldlst) table as well. Scroll through the list and click on a field name to highlight it.
     OR
   • Locate the field in the list displayed below the Field Name drop-down list. Click the box. When you do so, you will notice that the field list above the double line will automatically scroll to the show the name of that field you have just selected.
   • Note that some fields might not be available. For example, event Activity Name fields that are longer than 50 characters cannot be selected for reporting.
     

3. Proceed to Step Two, or click Clear to remove the selected field from the New Criteria section and start again.

Step Two - Choosing an Operator and an Object

Once you have identified the desired data field on the Table Display Form (Step 1), the system will display the corresponding Data Selection form as Step Two of writing a criteria statement. This form has three areas, each separated by a double line. The area at the top of the form is used to select a comparison for the criteria statement. The middle area (below the first double line) displays the tools needed to specify an object for the criteria statement. The choices that are made available for each of these elements are controlled by the nature of the field that was chosen in Step One. The bottom area (below the second double line) is headed 'Special', and displays the Prompt button and the Relation to Constituent list box. The Prompt button is used to choose a Prompted Reply object for the criteria statement. Once the Prompt button is selected, the Prompt Text text box will then appear. The Relation to Constituent list box is not currently functional and is retained on the Data Selection form for future use.

The system will prompt you to add the next component of the criteria statement by showing the text of the criteria statement, thus far. It might read, "The Constituent Type from the Basic Data table must be...;" followed by a pull-down list box that contains all of the possible comparisons that might be used with the chosen field. The complete list is discussed in the topic, Understanding Criteria, Comparisons.

To choose a comparison, click on the desired entry in the pull-down list box.

When you select a comparison, the bottom portion of the form may change. This is to allow you to specify an object that is appropriate to use with the comparison you chose. For example, if you choose the comparison, in the list of, the object text box will expand to allow multiple entries. If you choose the comparison, between, you will be given two text boxes to accept the end points for the object. The complete list of object types is discussed in the topic, Understanding Criteria, Objects.

Once you have chosen both the Comparison and an Object, click Next Line to create a new criteria for the same table. Or, click Finished to return to the Report Form.

About Objects and Data Types

The techniques used to choose an Object depend on the data type of the field that was chosen and the comparison that is used.

Standard Lookup Table Driven Field

When a field is lookup table driven, the Object will be an entry from a lookup table. The active entries from the table are listed in a list box to the left of the area below the double line. The will be listed by Code or by Value, depending on the Lookup User Option that you have selected. See User Options, Lookup Option.

1. If the comparison that you have chosen is something other than in the list of, not in the list of, or never in the list of:

   1. Highlight the desired entry in the list box on the left

   2. Click Add to place it in the text box on the right.

2. If the comparison that you have chosen is in the list of, not in the list of, or never in the list of, and the lookup table field is not driven by one of the value-based lookup tables, the lookup table entries will be listed in the box on the left. One or more entries can be moved from the list box on the left to the text box on the right:

   1. Highlight the desired entries.

   2. Click Add.

   3. If you change your mind, one or more of the entries can be moved back to the list box on the left by highlighting them and clicking Remove.

   Or

   Click Remove All to move all of the entries back at one time.

3. If the comparison you have chosen is in the list of, not in the list of, or never in the list of, and the lookup table driven field is not driven by one of the value-based lookup tables, you can place multiple entries in the text box on the right by using Paste functionality.

   1. Develop the list of objects in a text editor such as Notepad. Each item in the list, including the last, must end with a carriage-return, line feed character sequence. (this sequence is typically inserted by hitting the Enter or Return key on your keyboard)

   2. Copy the entire list from the text editor.

   3. Click Paste.

Value-Based Lookup Table Driven Field

If the lookup table field is driven by one of the value-based lookup tables, a text box that has special Search functionality will display.

1. Enter information to identify the entry or entries.

2. Click Next Line or Finished. The system will use a matching process to locate a unique match for the information that you entered.

3. If a unique match (either exact or partial) to a lookup table entry is located, the text box will be populated with the value that is associated with the matching value-based lookup table entry.

4. If no lookup table entry matches, or if more than one entry matches the information that you entered, then a Lookup Search form will display.

5. Use the form controls to search for the desired entry or entries.

6. If the comparison that you have chosen is in the list of, not in the list of, or never in the list of, then you can highlight multiple entries in the Search form and place them in the text box.

7. If the comparison is not in the list of, not in the list of, or never in the list of, then only one entry can be highlighted, and placed in the text box.

8. Click OK.

Note: Paste functionality is not available for value-based lookup table fields.

The system's Value-based Lookups Matching Process and the Lookup Search form are discussed in the Value Based Lookup Tables in Reporting topic.

Free Text Field

When a field is free text, TAB to or click on the Object text box to position the cursor there. If the field is an ID number, then you can enter an ID number without its leading zeroes. The system will add the leading zeroes for you. For other fields, type in the EXACT and complete text you want to identify in the data for that field. (If desired, wildcard objects may be used in combination with free text objects.) Click the Finished button in the upper corner of the form.

If the comparison you have chosen is in the list of, not in the list of, or never in the list of, you can place multiple Free Text Objects in the text box on the right by using Paste functionality.

1. Develop the list of objects in a text editor such as Notepad. Each item in the list, including the last, must end with a carriage-return, line feed character sequence. (this sequence is typically inserted by hitting the Enter or Return key on your keyboard)

2. Copy the entire list from the text editor.

3. Click Paste.

Every item on your list will be added to the object text box on the right. The system will not check for duplicates.

Wildcards

When a field is free text and the comparisons, contains or does not contain are used, you may use wildcard (wild card) substitutes for particular kinds of data that you want to identify. Wildcards may be used in combination with portions of exact text. See the topic, Understand Report Criteria, Wildcard Characters.

1. TAB to or click on the Object text box to position the cursor there.

2. Type in the text and/or wildcard characters.

Date Fields

When a datetime field is chosen, the object portion of the form will display several items, in addition to the object text box.

Note: If you want to identify only the month, only the day, or only the year portion of the date, you must click on the appropriate radio button, before you specify the date. 

Type in the desired date, in proper Date Format, in the Object text box.

If the system does not recognize the form of the date that you entered, the row will not be accepted until you edit the format of the date and click Next Line or Finished again.

OR

1. Use the arrow buttons at the top of the calendar to scroll until the desired date is shown. Single arrows scroll one month at a time, double arrows scroll one year at a time.

2. Click the date to highlight it. This is a single click; there is no need to double-click the date.

3. Click Add.

OR

Click the Today's date check box. The system will automatically use the date on which the report is run as the object of this criteria statement.

If the comparison you have chosen is in the list of, not in the list of, or never in the list of, you may place multiple dates in the text box on the right by using Paste functionality.

1. Develop the list of objects in a text editor such as Notepad. Each item in the list, including the last, must end with a carriage-return, line feed character sequence. (this sequence is typically inserted by hitting the Enter or Return key on your keyboard)

2. Copy the entire list from the text editor.

3. Click Paste.

Every item on your list will be added to the object text box on the right. The system will not check for duplicates nor will it check for proper date formatting.

Total Fields

When a report contains two or more Total definitions, and a criteria statement uses one Total as the field (subject) of the criteria statement, another one of the Totals from that report may be used as the object. These criteria statements may only be created using the Use Total function.

This function does not apply to Event Reporting and therefore this is not shown on the Event Report Form.

Prompt (Prompted Replies)

Regardless of the data type of the field that is used, you may use a Prompted Reply as a "temporary" object. When a report is run that contains criteria statements using this object, the user is prompted to identify the data that will be used as the actual object. See the topic, Understanding Criteria, Objects.

1. Click the Prompt button in the lower portion of the form. The object text box will now display <a prompted reply>.

2. In the Prompt Text text box, enter instructions to the user on how to reply to the prompt.

The prompting text for each Prompted Reply in the set of report criteria must be unique.

The prompting text should consist of numbers and/or upper and lower case letters only, with no punctuation. Other characters may work, but it is recommended that they not be used; for example, a colon (:) will not work.

Note: If the user chooses, criteria statements that use a prompted reply can be ignored when Millennium Reporter executes the report. See the Run Report Criteria, Prompted Reply topic for more details.

Blanks

When you want to examine the data in a field to identify those rows which have no data in that field, the object of the criteria statement is left blank.

Blank and Null are not the same 'value'. If you want to examine the data in a field to identify those rows that are Null, then the Comparison must be Null. When you select a Null Comparison, you will not have the opportunity to select an Object.