Search Queries

The Processor can use the Create/Manage Queries menu to create new search queries, and manage existing queries. The queries are if/then conditional statements. If the defined conditions are met, then the expense report is displayed to the Expense Processor.

Understanding Conditional Expressions

When creating or editing conditional expressions, the administrator should consider the following.

  • An expression can include an unlimited number of conditions.

  • It is composed of two conditions, separated by the Boolean separator of And.

Most conditions are composed of a Field followed by an Operator and a Value. For example:

  • A Field consists of a data object (a database table) and a field (a database column), located within the data object. The field that is selected defines the data type of the condition (number, text, date, and so forth).

  • An Operator is one of several pre-defined comparison operators (equals, not equals, is greater than, and so forth). The list of operators changes depending on the type of data being compared.

  • A Value is a constant, and like the field, can be of any data type; however, the data type of the value must match the data type of the field.

When a conditional expression is composed of multiple conditions, each condition is separated by either And or Or. Parentheses are optional and are used to define order of operation for the And/Or operators. If the parentheses are omitted, And/Or operations are carried out left to right. There is no precedence of And over Or; the evaluation of the expression is simply left to right.

If a conditional expression contains parentheses, the count of left parentheses must match the count of right parentheses. There can be up to three parentheses for both left and right sides.

Simple Condition Example

A condition is a simple Boolean comparison, like:

Cash Advance Balance Is Greater Than 1000

The condition looks at the report total and sees if it is greater than 1000. If True, or yes, then the report is located and displayed to the Processor. If False, or no, the report is not displayed.

Complex Condition Example

The conditional expression can be a single condition as in the above example, or it can be a complex expression involving multiple conditions connected by And / Or operators and parentheses, such as the following example that contains four conditions:

Report Date Is Greater Than 9/30/02 and (Employee Group Equals GermanGroup or Employee Group Equals FrenchGroup) and Report Total Is Greater Than the Amount Remaining for EuropeBudget.

Reimbursement Currencies and the Exchange Rates Feature

Processor queries do not use the Exchange Rates feature to convert reimbursement currencies to the currency in the query.

Adding a Query - The Condition Editor

When adding a query, the Processor uses the condition editor. Each condition appears on two lines. The fields, from left to right, are: 

Field

Description

Left Parenthesis

Select zero to three parentheses, depending on the complexity of the condition.

Data Object

Select one of the following:

Employee: Used to create an employee-based condition.

Entry: Used to create an expense entry based condition.

Entry Allocation: Used to create an expense entry allocation based condition.

Entry Attendee: Used to create an attendee-based condition.

Report: Used to create an expense report based condition.

Field / Value

Select an item from the helper pane that appears. The information that appears within this pane is based upon the selection within the Data Object list.

Operator

Select an item from the helper pane that appears. The information that appears within this pane is based upon previous choices.

Data Object

This field will always display as Value and cannot be changed.

Field / Value

Select an item from the helper pane that appears. The information that appears within this pane is based upon the selection within the Data Object list.

Right Parenthesis

Select zero to three parentheses, as required.

And / Or

Select either option to join the current condition to the next condition.

Determining How to Create a Query

When creating or editing a query, the Expense Processor defines the conditional expression(s) – the if portion of the query. The expression can contain one or more conditions separated by And or Or.

For example:

Assume the administrator wants to locate all expense reports totaling 500.00 USD or greater:

(Report Total Greater Than or Equal 500.00 USD)

Creating a New Query

A new query can be created for use by the Expense Processor. Queries can be run against the group or groups of employees for whom you have administrative rights.

To create a new query:

  1. Select Create/Manage Queries > Create New Query. The Add A Query page appears. This page includes the following:

    Field

    Description

    Query Name

    Enter a name for the query. The name you provide will appear in the Run Query list on the Process Reports page.

    Condition

    Select the appropriate information from within the Condition Editor.

    Insert

    Click to add additional conditions to the Condition Editor. Additional rows are always added at the bottom of the current rows. There is no limit to the number of rows that can be added.

    Remove

    Select the check box next to the condition to be deleted, and then click Remove. The condition is deleted. This action is permanent. If you delete a condition in error, you will have to recreate it in its entirety.

  2. Continue adding conditional rows as required.

  3. Click Save. The Process Reports page appears, with the query available in the Run Query list.

Editing Existing Queries

Queries can be edited from the Create/Manage Queries menu.

To edit existing queries:

  1. Select Create/Manage Queries.

  2. Select the desired query. The Edit Query page appears.

  3. On the Edit Query page, edit the condition information, add another condition row, or delete a condition row.

  4. Click Save.

Deleting a Query

Queries are individually created; therefore, you will only be able to see and affect the queries that you created.

To delete a query:

  1. Select Create/Manage Queries.

  2. Select the desired query. The Edit Query page appears.

  3. Click Delete Query. The query is removed from the Run Query list.

Exporting a query

Query results can be exported to an Excel formatted file from the processor page.

To export query results:

  1. In the Run Query list, click the name of the desired query.

  2. Click Go. The search results appear.

  3. Click Spreadsheet Download.

  4. Click Save.

  5. Click the location on your computer to save the file.

  6. Click Save.