Skip Headers
Oracle® Business Intelligence Discoverer Plus User's Guide
10g Release 2 (10.1.2.1)
B13915-04
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

13 Using conditions

This chapter explains how to use Discoverer Plus Relational's 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, the "Edit Worksheet dialog: Select Items tab: Conditions tab" shows that a condition is active that only displays 2000 data (i.e. Calendar Year = 2000).

Figure 13-1 Worksheet conditions in Discoverer

Description of Figure 13-1  follows
Description of "Figure 13-1 Worksheet conditions in Discoverer"

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 (for more information, see "About analytic functions and sequencing").

How to activate and deactivate existing conditions

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

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

To activate or deactivate a condition:

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

    Description of cond10.gif follows
    Description of the illustration cond10.gif

    The Conditions tab lists existing conditions available in the worksheet. Active conditions are marked with a tick symbol (or check mark) in the Available list and are also displayed in the Selected list.

  2. To activate a condition, move the condition from the Available list to the Selected list.

  3. To deactivate a condition, move the condition from the Selected list to the Available list.

  4. Click OK.

Discoverer refreshes the worksheet.

Notes

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: Select Items tab: Conditions tab".

    Description of cond10.gif follows
    Description of the illustration cond10.gif

  2. Click New and select New Condition from the drop down list to display the "New Condition dialog".

    Description of cond7.gif follows
    Description of the illustration cond7.gif

  3. In the What would you like to name your 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.

  4. (Optional) Use the What description would you like 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.

  5. 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 in 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 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)".

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

  7. Click OK to save the details 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 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

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. Choose Tools | Conditions to display the "Edit Worksheet dialog: Select Items tab: Conditions tab".

    Description of cond10.gif follows
    Description of the illustration cond10.gif

  2. Click New and select New Condition from the drop down list to display the "New Condition dialog".

    Description of cond7.gif follows
    Description of the illustration cond7.gif

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

  4. Click Advanced.

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

    Description of condex2.gif follows
    Description of the illustration condex2.gif

  5. To build a multiple condition, do one or more of the following:

    • 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.

    • Click And in the Insert box to insert a new condition statement line to the condition. Using AND narrows a search to display only items that match all criteria.

    • Click Or in the Insert box to insert a new condition statement line to the condition. Using OR widens a search to display items that match any of the criteria.

      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).

  6. 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.

  7. 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

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.

    Description of condex3.gif follows
    Description of the illustration condex3.gif

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

  5. Enter the condition statement details.

  6. 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.

  7. 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

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: Select Items tab: Conditions tab".

    Description of cond10.gif follows
    Description of the illustration cond10.gif

  2. Select a condition in the Available 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 activate and deactivate existing conditions").

Notes

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 (for more information, see "How to activate and deactivate existing conditions").

To delete a condition:

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

    Description of cond10.gif follows
    Description of the illustration cond10.gif

  2. Select a condition in the Available 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 filtered out.

Notes

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 13-2 The example worksheet containing aggregated totals for regions

Description of Figure 13-2  follows
Description of "Figure 13-2 The example worksheet containing aggregated totals for regions"

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 13-3 The example worksheet showing underlying Brand sub-totals

Description of Figure 13-3  follows
Description of "Figure 13-3 The example worksheet showing underlying Brand sub-totals"

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 13-4 The example worksheet with the Brand item removed and the condition Sales SUM > 400,000 applied

Description of Figure 13-4  follows
Description of "Figure 13-4 The example worksheet with the Brand item removed and the condition Sales SUM > 400,000 applied"

Examples of conditions

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

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

Description of Figure 13-5  follows
Description of "Figure 13-5 A single condition to return data for the month of January"

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 13-6 A multiple condition to return data for the month of January for the East region

Description of Figure 13-6  follows
Description of "Figure 13-6 A multiple condition to return data for the month of January for the East region"

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 13-7 A nested condition to return data for the month of January and the East region or the North region

Description of Figure 13-7  follows
Description of "Figure 13-7 A nested condition to return data for the month of January and the East region or the North region"

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

Notes

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