...

...

...

Applying Conditions and Parameters

Ownership

The Director of the Business Support Center is responsible for ensuring that this document is necessary, reflects actual practice, and supports University policy.

Introduction

Creating a query requires planning and an understanding of your business requirements, as well as understanding the policies and procedures that are applicable to the UVA environment.  After you have decided upon the questions you want to ask, you will then begin to create your query.  This also requires decisions along the way.  What business areas to use, what folder or folders contain your information, and what restrictions do you want to apply prior to running the query?  The Noetix help file is a great tool for finding the information for your query.  The wizard guides you through the process, and you can always make changes later.  After you have created your query, you will want to review the results and format the data.  Remember that you can always add items and/or make changes later.  Using the skills that you have learned in the introduction to ODS, we will focus on the advanced tools associated with defining and refining the results.

Section Objectives

At the end of this section, you should be able to:

• Select and apply conditions
• Create simple conditions
• Create complex conditions
• Create nested conditions
• Create parameterized conditions

Applying Conditions

Conditions Overview

A condition is a filter that you place on an item to restrict the number of rows returned from the query.  For example, you may want to see financial information for your Organization that is identified as Org 12345.  To do this, you place a condition on the output to filter all Organizations that do not begin with 12345.

Applying a condition to a table or crosstab after you have created your workbook does not remove the data from the workbook.  It merely filters the data to show the precise data you want to see.  To return the rest of the data to the table or crosstab, you must turn off the condition.

Selecting Conditions

Pre-defined conditions are available at UVA only when accessing pre-defined reports. You can select conditions from the list of available items in the wizard. While stepping through the wizard to build your query, you can choose to include the same conditions or new conditions.

The administrator can predefine two types of conditions: mandatory and optional.

• A mandatory condition is automatically applied when you select an item for inclusion in your query.
• An optional condition appears in the item selector as a filter icon. You have the option of selecting it or not.

Pre-defined conditions are displayed in the Conditions window and in the Available list in the Workbook Wizard. To see a condition, follow these steps:

1. Select the condition you want to see.

2. Select [Edit] in the Conditions window.  An Edit Condition window appears displaying the condition.

Creating Conditions

You can also create conditions. The conditions that you create are available only in the workbook in which they were created. Thus, you might want to gather worksheets together in the same workbook so that you can use the conditions from all the worksheets in the new workbook.

You can create conditions when you begin to make the selections for your query from the database.  In most instances, if you do not create at least one pre-defined condition while you are creating your query, you may receive vast amounts of information that is not applicable to your question.

Expanding an item and making one or more selections from the list of values creates a condition.  Another way to do this is to create the condition using the Condition Wizard.

Applying a Condition in the Wizard

The slide above shows an example of the Conditions dialog box. A check mark in front of a condition definition indicates that the condition is already applied to the data. You can enable numerous conditions to filter the data in tables and crosstabs.

Conditions dialog box: You can create conditions in the Conditions dialog box, which you open from the Tools menu.

Viewing Conditions for Specific Items: To display either all the conditions defined in a workbook or only those that apply to particular data items, select All Items or the particular data item from the “View Conditions for” drop-down list.

Conflicting Conditions:  If you select two or more conditions that conflict a warning message is displayed.  For example, the two conditions Pa_Period Name=:Pay Period and Pa_Period_Name IN (Mar-02) will conflict if you choose any month in the parameter besides March 2002.  In this example, if you have selected the Month of April, you will receive the conflicting conditions message.  You will need to deselect the conditions that conflict prior to running a successful query.

Creating Simple Conditions

New Condition Dialog Box

Select Conditions will be present in the Item drop-down list if any conditions already exist. Select Multiple Values in the Values list will be present if the item has a list of values.

New Condition Dialog Box

Conditional Operators

You can use conditions to reduce the information contained in a particular set of results.  For example, you can display all project numbers, but for a particular result set, you may want to see only those projects for a certain award. 

A conditional operator specifies how an item is to be compared.  The following table gives you examples of the most commonly used operators that are available to you. 

Creating a Single Condition

You can create conditions on any item in the results window or in the wizard. To create a condition, follow these steps:

1. In the Conditions dialog box, select [New].
2. Select an item from the Item drop-down list.
3. Select a conditional operator from the Condition drop-down list.
4. Select a value or option from the Values drop-down list. Options include:

• Selecting single or multiple item values
• Creating a calculation
• Selecting an item
• Selecting a new parameter
• Creating a subquery

1. Select [OK] to create the condition.  Select [OK] in the Conditions dialog box to apply the condition to the output.

Advanced Conditions

Creating Complex Conditions

Advanced Conditions

To create multiple conditions for the same condition name, click Advanced in the New Conditions dialog box. The Edit Condition dialog box is displayed. Then follow these steps:

1. Click the Add button.
2. Select an item from the Item drop-down list.
3. Select a conditional operator from the Condition drop-down list.
4. Select a value or option from the Values drop-down list.
5. Repeat these steps for each condition.

Grouping Conditions

Condition groups are displayed in the Formula region in the Edit Condition dialog box.  If you want to edit the conditions, you can do so from this dialog box.

The results window displays the results of the grouping conditions.

For instance, you want to find the expenditures for all Educational and Food and Drink expenditure category types. 

In the example above, the condition is:

• Return all expenditure types named “Educational Supplies” or all expenditure types named “Food & Drink” AND the Burdened Cost Pay Period is greater than $500.

AND has precedence over OR, while nesting conditions alter the default precedence.  Review the conditions above.  In this example, AND has precedence over OR because it is a nested condition.

Note:            To group, hold down the shift key and select items to group, chen click [And] or [Or] as appropriate.

Changing Logical Operators

By default, the AND operator is used to combine conditions. To toggle between AND and OR, double-click the Group field. To create a condition with multiple logical operators, follow these steps:

1. Create the conditions as described earlier.
2. Select the two conditions you want to group with either the [Shift] or [Ctrl] key.
3. Click the desired logical operator button.

Nested Conditions

A nested condition is a condition that contains other conditions as its components.

Nested conditions are displayed in the Formula region in the Edit Condition dialog box. If you want to edit the conditions, you can do so from this dialog box.

The results window displays the results of the nested condition.

For instance, you want to find out which expenditures for  Educational Supplies and Food & Drink supplies are greater than $500 for your organization. 

In the example above the condition is:

Expenditure Type = Supplies, Educational OR Expenditure Type = Supplies, Food & Drink AND ((expenses are greater than $500) AND (Project Carrying Out Org))

Creating Nested Conditions

1. Create each condition as described previously.

2. Select [Advanced] in the New Condition dialog box.

3. Select a logical operator to group the conditions.

4. Click at the left side of the Item field.  The following list appears:

• Create Calculation
• Select Condition (allows copy but not edit)
• Copy Condition (allows copy and edit)

5. Select the conditions or items you want to nest.  The Conditions box appears.

6. Select the particular condition you want to nest and select [OK].

Parameterized Conditions

Parameters

A parameter is a value within a condition that you specify when you run a query. You use parameters to change conditions without having to change the query. If a query includes a parameter, you are prompted to specify the information you want to see when you run the query. For example, you can create a parameterized query that prompts you for a Pay Period Name and for an Organization Number.

Executing a Parameterized Condition

1. Run the query

2. Enter values or accept the default values.

3. Select [Finish] to execute the query.

4. Select [Sheet / Edit Parameter Value] to enter new values.  You can also select the [Refresh Sheet icon  ] to edit the parameter values.

5. Select [Finish] to execute the query again.

Creating a Parameterized Condition

Select Tools—>Parameters to open the Parameters dialog box. When you create or modify a parameterized condition, you must provide the following information:

• Name: The name of the parameter that appears in the Available Parameters dialog box.
• Operator: If an operator is to be used in the condition, select it from the Create Condition drop-down list.
• Prompt: Enter the user prompt.
• Description: Enter a description of the parameter that is displayed in the Description area of the Available Parameters dialog box.
• Default Value: Select a value from the drop-down list to be used as a default if no value is selected in the Parameter Wizard.

Note: To change the conditional operator for a parameter, you must modify it in the Edit Condition dialog box. To disable a parameter, you must disable the condition.

Summary

In this lesson, you should have learned how to:

• Select and apply pre-defined conditions
• Create simple conditions
• Create complex conditions
• Create nested conditions
• Create parameterized conditions

  Effective: 02/03/06

  Revision: 4

  ---

  Copyright 2004 University of Virginia and Oracle Corporation. All rights reserved.

  Oracle® Tutor   Copyright © 1997, 2004, Oracle. All rights reserved.