Skip Headers

Oracle9iAS Discoverer Plus User's Guide
Version 9.0.2

Part Number A90879-02
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

10
Using conditions

This chapter explains how to use Discoverer conditions to answer typical business questions. For example, which product items sell more than 10,000 each week? This section contains the following topics:

What are conditions?

Conditions are worksheet items that enable you to choose what data to display on worksheets. Conditions filter out data that you are not interested in, enabling you to concentrate on data that you want to analyze. For example, in the figure below, a condition is being used that only displays data for the year 2001.

Figure 10-1 Worksheet conditions in Discoverer


Text description of consimp.gif follows.
Text description of the illustration consimp.gif

You create conditions by specifying condition statements against which to match worksheet data. Discoverer uses conditions as follows:

Conditions are categorized as follows:

What are multiple conditions?

Multiple conditions comprise more than one condition statement in a single condition item. For example, you might want to only display data for the year 2000 where the profits are greater than $900,000.

Note: You might also create two single conditions here to achieve the same result. For more information, see "About applying more than one condition".

What are nested conditions?

Nested conditions comprise condition statements contained within the definition of other condition statements. Nested conditions work as follows:

For example, you might want to find data for the year 2000, where either the Region equals Eastern and Profits are greater than $900,000, or where the Region equals Northern and Profits are greater than $500,000.

About using conditions

Worksheets can contain conditions defined by you, by the Discoverer manager, or by other Discoverer users. Conditions work as follows:

About applying more than one condition

Applying more than one single condition at the same time can have the same effect as creating a multiple condition. This can keep your condition statements short and make them easier to understand by other Discoverer users. Single condition statements also enable you to selectively apply individual condition statements.

For example, you apply the following two single conditions:

This has the same effect as one multiple condition containing two condition statements:

Note: When filtering certain types of data, using a multiple condition produces different results from using more than one single condition. For example, when using analytic functions (see "About analytic functions and sequencing").

How to turn conditions on

You turn conditions on when you want to filter worksheet data according to the condition statement. For example, to turn on the condition Year = 2001 to display only data for the year 2001.

To turn a condition on:

  1. Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".


    Text description of cond10.gif follows.
    Text description of the illustration cond10.gif

    The Conditions tab lists conditions available to the worksheet. The check box beside each item indicates whether the condition is turned on.

  2. Select the check box next to conditions that you want to turn on.

    You can turn on more than one condition at a time.

  3. Click OK.

Discoverer displays the data that meets the condition(s) that you have turned on.

Notes

How to turn conditions off

You turn conditions off when you no longer want to filter the worksheet with a condition. If you need to filter the data later using the condition, you can always turn the condition back on. For example, you might turn off the condition Year = 2001 to display data for all years available.

Note: Do not click the Delete button to turn off a condition. The Delete button permanently removes the condition from your workbook (see "How to delete conditions").

To turn a condition off:

  1. Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".


    Text description of cond10.gif follows.
    Text description of the illustration cond10.gif

    The Conditions tab lists conditions available to the worksheet. The check box beside each item indicates whether it is turned on.

  2. Clear the check box next to conditions that you want to turn off.

  3. Click OK.

Discoverer displays data that meets any conditions that are still turned on.

How to create single conditions

You create single conditions when you want to filter worksheet data in a new way using a single condition statement. For example, to display data for the year 2001, you might create the condition 'Calendar year = 2001'.

To create a single condition:

  1. Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".

  2. Click New to display the "New Condition dialog".


    Text description of cond7.gif follows.
    Text description of the illustration cond7.gif

  1. In the What do you want to name this condition field, specify a name for the condition.

    Hint: If you want Discoverer to create a condition name for you from the conditions statements that you enter, select the Generate name automatically check box.

  2. (Optional) Use the What description do you want to give your condition field to enter additional information about the condition. For example, hints and tips about when to use the condition. This information is displayed to workbook users to help them select which conditions to use.

  3. Use the Formula area to define the condition statements:

    1. Use the Item drop down list to choose what item you want to filter the data on.

      For example, you might choose Year to display data for a particular year.

      Hint: The Item drop down list shows the items available to the worksheet that you can use in the condition. You can use items that are not currently displayed on the worksheet to filter the worksheet data.

    2. Use the Condition drop down list to choose how to match data against the item.

      For example, you might select '>' here to filter data where the item value is greater than a certain number.

    3. Use the Values field to define what data you want to match against.

      For example, you might enter 2001 here to look only at data for the year 2001.

      If a list of values is defined for the item, you can also select from items and values in the drop down list, which might contain items and values made available to you by the Discoverer manager. For more information, see "Using lists of values (LOVs)".

  4. If you want to match upper and lower case text data exactly, select the Case sensitive check box.

  5. Click OK to save the details and close the dialog.

    The new condition appears in the Conditions dialog and is turned on.

  6. Click OK to close the Conditions dialog and return the worksheet.

    Discoverer filters the worksheet to display only data that matches the condition. Data that does not match the condition is not displayed.

    Notes

    • When entering values into the Values field, you can enter multiple values when the condition operator is one of the following types:

      • = (equals)

      • <> (not equals)

      • IN

      • NOT IN

      For more information about what values you can enter in the Values field, see "Using lists of values (LOVs)".

    How to create multiple conditions

    You use a multiple condition to display only data that matches multiple condition statements that you cannot display using a single condition. For example, to display data for the year 2000 that also relates to the Eastern region.

    To create a multiple condition:

    1. Open the worksheet that you want to analyze.

    2. Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".

    3. Click New to display the "New Condition dialog".

    4. Create a single condition (for more information, see "How to create single conditions").

    5. Click Advanced.

      Discoverer adds Insert buttons for New Item, And and Or. You use these buttons to create the advanced condition.


      Text description of condex2a.gif follows.
      Text description of the illustration condex2a.gif

    6. Use the Insert buttons to build the multiple condition:

      1. Click New Item in the Insert box to insert a new condition statement line to the condition.

        By default, the new item is grouped with a logical AND, which means that data must match all condition statements contained within the AND group.

      2. Click And in the Insert box to insert a new condition statement line to the condition grouped with a logical AND.

      3. Click Or in the Insert box to insert a new condition statement line to the condition grouped with a logical OR.

        Hint: To change the way that condition statements are grouped, click the buttons in the Group column to display a drop down list of options (e.g. AND, OR, NOT AND, or NOT OR).

    7. When you have finished, click OK to save the multiple condition and close the dialog.

      The new condition appears in the Conditions dialog and is turned on.

    8. Click OK to close the Conditions dialog and return the worksheet.

    Discoverer filters the worksheet to display only data that matches the condition. Data that does not match the condition is not displayed.

    Notes:

    • To create a multiple condition, you might also add condition statements to an existing single condition.

    How to create nested conditions

    You use nested conditions to display only data that matches a specific set of condition statements that you cannot apply in a single or multiple condition.

    To create a nested condition:

    1. Open the worksheet that you want to analyze.

    2. Create a multiple condition (see "How to create multiple conditions").

    3. In the New Condition dialog, click Advanced.

      Discoverer adds Insert buttons for New Item, And and Or. You use these buttons to create the nested conditions.


      Text description of condex3a.gif follows.
      Text description of the illustration condex3a.gif

    1. Use the grouping button (AND, OR, NOT AND, or NOT OR) to add a new condition statement line under the currently selected Group.

    2. Enter the condition statement details.

    3. When you have finished, click OK to save the nested condition and close the dialog.

      The new condition appears in the Conditions dialog and is turned on.

    4. Click OK to close the Conditions dialog and return to the worksheet.

    Discoverer filters the worksheet to display only data that matches the condition. Data that does not match the condition is not displayed.

    Notes:

    • To create a nested condition, you might also edit an existing single or multiple condition.

    How to edit conditions

    You edit conditions when you want to change the way that they filter data. For example, you might have a condition that displays sales people who generate more than $100,000 worth of sales. You might want to change this to more than $150,000 worth of sales.

    To edit a condition:

    1. Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".

    2. Select the condition that you want to change from the Conditions list.

    3. Click Edit to display the "Edit Condition dialog".

    4. Edit the condition details as required.

    5. Click OK to save the details and close the "Edit Condition dialog".

    6. Click OK to close the Conditions dialog and return to the worksheet.

    If the condition is active, Discoverer filters the worksheet to display only data that matches the condition (for more information, see "How to turn conditions on").

    Notes

    • You cannot edit conditions created by the Discoverer manager. Only Discoverer managers can edit conditions that they have created. The Edit button is replaced by a Show button for conditions created by the Discoverer manager.

    How to delete conditions

    You delete a condition when you no longer want to use it, and you want to remove it permanently from a workbook. For example, you might have created a temporary condition to produce an ad hoc report and now want to remove the condition from the workbook.

    Note: If you want to disable the condition without deleting the condition permanently, you can turn the condition off (see "How to turn conditions off").

    To delete a condition:

    1. Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".

    2. Select the condition that you want to delete from the Conditions list.

    3. Click Delete.

    4. Click OK to close the Conditions dialog and return to the worksheet.

    If the deleted condition was previously active, Discoverer removes the condition and displays data that was previously not displayed.

    Notes

    • You cannot delete conditions created by the Discoverer manager. Only Discoverer managers can delete conditions that they have created.

    Notes on how Discoverer applies conditions to roll-ups

    When a worksheet has page items, Discoverer applies conditions to underlying sub-totals. Discoverer does not apply conditions to roll-ups.

    The following example illustrates how this affects Discoverer worksheets.

    Example of how Discoverer applies conditions to roll-ups

    In this example, a worksheet contains sales totals for regions (see figure below).

    Figure 10-2 The example worksheet containing aggregated totals for regions


    Text description of eda1.gif follows.
    Text description of the illustration eda1.gif

    Notice that the Brand item is displayed in the Page Items area. The Sales SUM values are roll-ups of underlying Brand sub-totals for each region (see figure below).

    Figure 10-3 The example worksheet showing underlying Brand sub-totals


    Text description of eda2.gif follows.
    Text description of the illustration eda2.gif

    For example, Astro is $4,553, and Big Studios is $71,661. The largest sub-total is Wolf ($133,154).

    Now imagine that you apply the condition Sales SUM > 400,000 to the worksheet.

    The result is that Discoverer returns no rows, because none of the underlying Brand sub-totals are greater than $400,000.

    If you want to apply the condition Sales SUM > 400,000 to the roll-ups displayed on the worksheet, you must remove the Brand item from the worksheet. Discoverer will then return the East region row (see figure below).

    Figure 10-4 The example worksheet with the Brand item removed and the condition Sales SUM > 400,000 applied


    Text description of eda4.gif follows.
    Text description of the illustration eda4.gif

    Examples of conditions

    Example 1: In this example, you want to display only data for the month of January.

    Figure 10-5 A single condition to return data for the month of January


    Text description of condex1.gif follows.
    Text description of the illustration condex1.gif

    In the figure above, a single condition statement is defined (Calendar Month = January).

    Example 2: In this example, you want to display only data for the month of January and the East region.

    Figure 10-6 A multiple condition to return data for the month of January for the East region


    Text description of condex2.gif follows.
    Text description of the illustration condex2.gif

    In the figure above, a multiple condition statement is defined (Calendar Month = January AND Region = East).

    Example 3: In this example, you want to display only data for the month of January, and data for the East region or data for the North region.

    Figure 10-7 A nested condition to return data for the month of January and the East region or the North region


    Text description of condex3.gif follows.
    Text description of the illustration condex3.gif

    In the figure above, a nested condition is created (Calendar Year = 2001 AND Region = East OR Region = North).

    Notes:

    • The Case sensitive check box is selected in these examples, which means that text data must match exactly. For example, when Case sensitive is selected for a condition statement 'Region = East', data would not be displayed where the Region equalled 'EAST' or 'east'.

    When the Case sensitive check box is not selected, the worksheet query might take longer to run.


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index