DARTS : Changing a Selection for Variables in a Criteria

Click a link below to jump to the topic you would like to learn more about.

Criteria Overview

Criteria, sometimes also referred to as Selection Criteria, specify what records you wish the Query to return. This is the filter that data runs through: only those records meeting the criteria are selected. 
The simplest form of criteria compares the data in a field to a constant value:

• INSTITUTION = 'UMSYS'
• ACAD_CAREER = 'UG02'
• GRADUATION_DATE > 01/01/2005

A field in one table may be compared to a field in another table with similar data and the same format:

• A.EMPLID = B.EMPLID
• A.ACAD_CAREER = B.ACAD_CAREER

(Incidentally, this is one of the ways to join two tables together in a query, more on that later.) 
There are four components of Criteria: 
 

Back to top

How to read Criteria

When the query is created with multiple tables or prompts, the Criteria page gets complicated.

Here are the rules on query manager:

1. When the expression 2 does NOT start with colon ( : ) nor table alias, such as "A." or "B.", it is for a variable. You can change the variable as mentioned above. (see the "Variable Selection" in the screenshot below)
2. When the expression 2 starts with colon ( : ), it is for prompt. it is usually automatically added when you add a prompt on "Prompt" page. (see the "For prompts" section in the screenshot below)
3. When the expression 2 starts with table alias, such as "A." or "B.", it is for table join. it is usually automatically added when you joined tables on "Records" page. (see the "Table Join" section in the screenshot below)

Back to top

Using Criteria

There are a number of ways to add criteria to your query.

Choice #1
If the criteria is for a field that is being displayed, then you can add it from the Fields page: Click the funnel icon 

Choice #2
If you are adding criteria for a field that is NOT being displayed in the query results, add the criteria from the Query page using the funnel icon  "Use as Criteria" icon: 
 

Choice #3

You can go to the Criteria page and click the Add Criteria button:
 

After choosing the add criteria button or clicking the funnel, the "Edit Criteria Properties" window will show up. See more information in the next section.

Back to top

Expressions and Condition Types

When you are adding a criteria, you will be presented with the Edit Criteria Properties page: 
 
If you need to choose a different Record and Field for Expression 1, click the lookup icon  and a list like the following will be presented. Select the desired record and field from the list.
 
Condition Types are used to compare Expression 1 (the left side) to Expression 2 (the right side). Some condition types will change the nature of what is needed on the right side of the Criteria. The Edit Criteria Properties page will change as appropriate. Here's a list of the condition types and what they mean. 

The entry for Expression 2 depends on the Condition Type you are using. For the basic 'Equal to', 'Greater Than', 'Less Than' it looks like:
 
If you're using 'Between' condition type, it will change so that you can enter two values of types Constant, Field or Expressions. (I've always wanted to be able to do a prompt here too!) 
 
The 'In List' allows you to specify a list of constants or point to a subquery which would return a list. 
 
The 'In Tree' lets you point to one or more nodes on a tree. Click the "New Node List" and search by tree name. Expand the tree node to where you want to add and select the node and click OK.
 
The 'Like' condition only allows for constants or prompts:

After finishing adding criteria, go to Criteria page and double check the criteria and logic.

Back to top

Logical Operators AND/OR

Logical Operators (AND, AND NOT, OR, OR NOT) allow you to relate multiple criteria in specific ways. When you specify two or more selection criteria, you need to coordinate the criteria. You can use the Logical column to further define the rows of criteria. The Logical Operator always defaults to AND. The first row of criteria will have a blank operator. When you use multiple criteria, rules of logic apply. The query evaluates criteria that are linked by AND before those linked by OR.

Using these incorrectly will generate quite different results. Consider these examples: 

Back to top

Grouping Criteria with Parentheses

Group Criteria controls the order in which query executes the criteria rows. The query evaluates the criteria inside the parentheses before the criteria outside the parentheses. Similar to algebra, the group criteria (x-1)y is indicating that x-1 needs to be solved before multiplying the result by y. Use grouping to funnel data from largest to smallest to expedite search.

Here's an example. 

1. Click Group Criteria
   
   
   
2. Type an open and close parenthesis around the criteria rows to be grouped
3. Click OK
   

Example 2:

We want to see prospects in the GRAD career for term 0410, or in the MEDS career for admit term 0450. 
 
Results:
 

Back to top

Step-by-Step Example: Creating and Deleting Criteria

1. Navigate to Query Manager
2. Find the query you would like to change and click "Edit".
   
   
   
   
3. Select the "Criteria" tab and click "Edit" for the criteria you want to change. In this example, we want to change the departments that will show in the report. Click the "Edit"  button for DEPTID.
   
   
   
   
4. After clicking Edit, the Edit List dialogue box appears. Click the small magnifier glass and a new window will pop up. If you want to add new value, enter a value on the box and click "Add Value" bottom. If you want to delete an existing value, check the small box next to the value and click "Delete Checked Values." After finishing adding or deleting, click "OK" to close the window.
   
   
   
5. If you want to delete the entire criteria completely, use delete icon next to "Edit".
   
   
   
6. Save the query using "Save As" with different name.

Back to top