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?"

• "What are multiple conditions?"

• "What are nested conditions?"

• "About using conditions"

• "About applying more than one condition"

• "How to activate and deactivate existing conditions"

• "How to create single conditions"

• "How to create multiple conditions"

• "How to create nested conditions"

• "How to edit conditions"

• "How to delete conditions"

• "Notes on how Discoverer applies conditions to roll-ups"

• "Example of how Discoverer applies conditions to roll-ups"

• "Examples of conditions"

13.1 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 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 (that is, Calendar Year = 2000).

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:

• 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?")

13.2 What are multiple conditions?

Multiple conditions consist of multiple condition statements 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".

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

13.4 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 (for more information, 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.

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

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

   
   

   The Conditions tab lists existing conditions available in the worksheet. Active conditions are marked with a checkmark 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

• You can also activate existing conditions in the following way:

  • If the Available Items pane is displayed, drag and drop a condition from the Conditions tab to the worksheet.

• You can also deactivate conditions in the following way:

  • If the Selected Items pane is displayed, right-click on a condition in the Selected Items list and select Remove from Worksheet.

• If you turn on more than one condition at the same time, this can have the same effect as applying a single multiple condition (for more information, 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.

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

   
   

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

   
   

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. To match upper and lowercase 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

• You can also create a condition in the following ways:

  • Select the worksheet item you want to filter, then select the New Condition option on the Standard toolbar and choose one of the condition operators available.

  • If the Available Items pane is displayed, the New Condition option on the Available Items toolbar (for more information, see "Available Items pane").

  • If the Available Items pane is displayed, right-click in the Conditions tab and select New Condition.

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

  • = (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)".

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

   
   

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

   
   

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.

   
   

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 (for example, 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

• You can also create a condition in the following ways:

  • If the Available Items pane is displayed, select the New Condition option on the Available Items toolbar (for more information, see "Available Items pane").

  • If the Available Items pane is displayed, right-click in the Conditions tab and select New Condition.

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

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

   
   

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

• You can also create a condition in the following ways:

  • If the Available Items pane is displayed, select the New Condition option on the Available Items toolbar (for more information, see "Available Items pane").

  • If the Available Items pane is displayed, right-click in the Conditions tab and select New Condition.

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

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

   
   

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

• You can also edit conditions in the following way:

  • If the Available Items pane is displayed, display the Conditions tab, right-click on a condition, and select Edit.

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

  • the Edit button is grayed out for conditions created by the Discoverer manager.

  • the Show button is active for read-only conditions created by the Discoverer manager.

13.11 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: 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".

   
   

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

• You can also delete conditions in the following way:

  • If the Available Items pane is displayed, display the Conditions tab, right-click on a condition, and select Delete.

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

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

13.13 Example of how Discoverer applies conditions to roll-ups

In this example, a worksheet contains sales totals for regions (see Figure 13-2).

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

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.

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 then returns the East region row (see Figure 13-4).

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

13.14 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

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

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

In the figure above, a nested condition is created (Calendar Month = January 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.