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:

data that matches your condition statements is displayed
data that does not match your condition statements is not displayed

Conditions are categorized as follows:

single conditions - contain a single condition statement
multiple conditions - contain two or more condition statements in a single condition item (for more information, see "What are multiple conditions?")
nested conditions - contain condition statements that are defined within other condition statements (for more information, see "What are nested conditions?")

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:

You can group multiple condition statements. Conditions consisting of multiple statements are connected using the logical AND and OR operators.
You can also nest statements, so that one statement is contained within the definition of another statement.

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:

If you have the privileges to edit a worksheet, you select which conditions to apply to the worksheet.
When you create a condition, the condition is available to all worksheets in the workbook. You apply the condition to individual worksheets.
If none of the existing conditions filter the data exactly as you want, you can create your own conditions and apply them to the worksheet.
To apply conditions more flexibly, you can use parameters to give workbook users a choice of what data to display on a worksheet (see "Using parameters").
Conditions created when a parameter is added to a worksheet are automatically selected when the parameter is turned on, and automatically deselected when the parameter is turned off.

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:

Year = 2001
Sales SUM > $100,000

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

Year = 2001 AND Sales SUM > $100,000

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:

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

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.
Select the check box next to conditions that you want to turn on.

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

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

Notes

If you turn on more than one condition at the same time, this can have the same effect as applying a single multiple condition (see "About applying more than one condition").
If you select two (or more) conditions that conflict, a warning appears. For example, the two Conditions "Year = 2000" and "Year = 2001 or 2002" conflict. This is because the first condition filters out data that does not apply to 2000, and the second condition tries to display 2001 and 2002 data at the same time.

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:

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

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.
Clear the check box next to conditions that you want to turn off.
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:

Choose Tools | Conditions to display the "Edit Worksheet dialog: Conditions tab".
Click New to display the "New Condition dialog".

Text description of the illustration cond7.gif

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.
(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.
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)".
If you want to match upper and lower case text data exactly, select the Case sensitive check box.
Click OK to save the details and close the dialog.

The new condition appears in the Conditions dialog and is turned on.
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 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 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 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 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 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 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 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 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.