Create a Query Using Criteria Components

You are interested in retrieving data on AP vouchers, so the VOUCHER - AP option will be selected.

You are transferred to the Query tab.

Select a field for inclusion in the query by clicking the checkbox to the left of the Field Name.

NOTE: The query must be named when saved for the first time. The query must be saved as a Private query. If a Public query is modified, you must save the modified query as a Private query.

Method 1 - Add criteria from the Fields tab

In this example, you will enter a specific Business Unit value as criteria to filter the query results.

Enter the desired information into the Constant field by typing in the Business Unit. Enter "LSUSH".

NOTE: System Business Unit security defines which units a user may view. Users at LSUNO may not view LSUSH or HCSD data and vice versa. The example uses LSUSH, but you may enter your Business Unit instead.

Method 1 Summary:

1. The Business Unit Add Criteria was selected, and

2. A Constant Business Unit value was specified on the Edit Criteria Properties panel.

3. The system will retrieve AP voucher data for LSUSH only.

This completes the discussion of Method 1 - Add criteria from the Fields tab.

Method 2 - Add criteria from the Criteria tab

In this example, you will specify a date range for the INVOICE_DT.

When selecting Add Criteria on the Fields tab, the Field Name defaulted into Expression 1. When adding criteria from the Criteria tab, you must select the Field by using the Select Record or Field (magnifying glass) button.

In this example, you do not want the INVOICE_DT to equal a specific date. You want the system to retrieve data using a specified range for the INVOICE_DT. Therefore, the Condition Type must be changed.

For this example, you want to specify a range of dates, so the between option will be selected.

Once the "between" option is selected, the Expression 2 changes from a single Constant field to multiple Constant fields so that you may enter a beginning date and an ending date.

Method 2 Summary:

1. Clicked the Add Criteria button on the Criteria tab;

2. Selected INVOICE_DT as the Field Name for Expression 1;

3. Changed the Condition Type from "equal to" to "between";

4. Entered the Invoice Date range 01/01/2015 through 01/31/2015;

5. The system will retrieve vouchers with an Invoice Date between 01/01/2015 and 01/31/2015.

This completes the discussion of Method 2 - Add criteria from the Criteria tab.

Method 3 - Add criteria from the Query tab

In this example, you will specify the POST_STATUS_AP equal "P" for "Posted".

You clicked the Add Criteria (funnel) on the Query tab for a specific field, therefore, the field name defaults into the Expression 1 box. You want the POST_STATUS_AP to equal a specific value, so the Condition Type will remain "equal to". You will identify the POST_STATUS_AP value in Expression 2. POST_STATUS_AP has several predefined value options.

In this example, you want the POST_STATUS_AP value to be equal to "Posted", so the "P" value will be selected.

Method 3 Summary:

1. Clicked the Query tab;

2. Clicked the Add Criteria button on the Query tab for POST_STATUS_AP;

3. Selected "P" for Posted for Expression 2;

4. The system will retrieve vouchers with a Post Status of Posted.

NOTE: If desired, you may navigate to the Run tab to run your query.

This completes the discussion of Method 3 - Add criteria from the Query tab.

In this exercise, you will create a query listing Average Gross Vouchers for the time period 01/01/2015 through 01/31/2015. The following Criteria will be applied:

1. Business Unit constant;

2. Invoice Date between 01/01/2015 and 01/31/2015; and

3. Post Status AP of Posted.

You can return to the Query tab to add or delete field.

Fields may be deleted on the Fields tab by clicking the Delete button (-). However, fields cannot be added on the Fields tab.

You add criteria to a query to filter or limit the results based on specified parameters. There are three (3) methods for adding criteria to a query. Each method will be demonstrated in this topic. The methods are:

Method 1 - Add criteria from the Fields tab;

Method 2 - Add criteria from the Criteria tab; and/or

Method 3 - Add criteria from the Query tab.

Expression 2 defaults to the Constant option. Other options are available for Expression 2 and will be demonstrated later in this topic. For this example a single Business Unit will be specified as the Constant, so the system will retrieve data for the specified Business Unit only. If the Business Unit designation is unknown, click the Select Constant From List (magnifying glass) button.

As criteria are added to the query, they are listed on the Criteria tab.

The system filters data based on the "Logical" order of added criteria. The Logical represents how criteria compare to each other.

If only one criterion is added, the Logical field will either be left blank, or the option "NOT" may be selected. In this example the field is left blank, so data will be retrieved for LSUSH only. If the "NOT" logical was used, the system would retrieve all Business except LSUSH.

Defining Criteria allows you to:

1. Reduce the number of rows of data returned in your results, and

2. Retrieve only the data you need at the time the query runs.

Based on the criteria selected in Expression 1, Condition Type and Expression 2, the system will retrieve data for the Business Unit equal to the Constant LSUSH. In other words, only AP voucher data for the Business Unit LSUSH will be retrieved.

Changes can be made to any of the components on the Edit Criteria Properties panel. For this example, the Choose Expression 1 Type, Choose Expression 2 Type and Expression 1, Condition Type will remain as defaulted.

Based on the criteria selected in Expression 1, Condition Type and Expression 2, the system will retrieve data for vouchers with an INVOICE_DT between 01/01/2015 and 01/31/2015.

An additional row displays on the Criteria tab for INVOICE_DT.

The Logical field for INVOICE_DT defaults as AND, requiring the system to only retrieve data meeting both criteria. If the Logical had been set to "OR", the system would have retrieved only data that met either criteria (i.e. the Business Unit does not equal LSUSH, or the Invoice Date does not fall within the Invoice Date range).

Based on the selected criteria, the system will only retrieve vouchers for the LSUSH Business Unit with an Invoice Date between 01/01/2015 and 01/31/2015.

An additional row now displays on the Criteria tab for POST_STATUS_AP.

The Logical field for POST_STATUS_AP defaults as AND, requiring the system to only retrieve data meeting all three criteria. If the Logical had defaulted to OR, the system would have retrieved only data meeting one of the criteria (i.e. the Business Unit does not equal LSUSH, or the Invoice Date does not fall within the specified date range, or the Post Status does not equal Posted).

Based on the selected criteria, the system will only retrieve vouchers for the LSUSH Business Unit, with an Invoice Date between 01/01/2015 and 01/31/2015, and having a Post Status of Posted.

The Condition Type determines how a query compares values specified in Expression 1 and Expression 2. In this example you want to specify a Business Unit, so the Condition Type will remain "equal to". Other Condition Type options are available and will be demonstrated later in the topic.

Enter the desired information into the Constant field by typing in the Business Unit. Enter a valid value e.g. "LSUSH".

Once the "between" option is selected, the Expression 2 changes from a single Constant field to multiple Constant fields so that you may enter a beginning date and an ending date.