Working with Discoverer Desktop - Part 02

5 Working with Discoverer Desktop

This chapter describes the basic procedures for working with Discoverer Desktop to analyze data.

The topics covered in this chapter include:

• Using Discoverer Desktop Tools to Analyze Your Data
  A description of each analysis tool and how it's used; see the following sections for details:
• Finding Exceptions to Data
• Totaling Numeric Data
• Using Percentages
• Finding Data that Meets Conditions

5.1 Using Discoverer Desktop Tools to Analyze Your Data

The Discoverer Desktop Tools menu gives you a range of tools for analyzing your data. For example, tools for creating and managing Conditions, data Sorts, Calculations etc, (see Figure 5-1 below).

Workbook behaviors that you define using the tools are known as Tool Definitions. Examples of Tool Definitions are

• Condition - "Department is Video Rental or Video Sales"
• Sort - Sort on Calendar Date Year, Department
• Calculation - Profit Increase = 'Profit SUM * 1.15'

Figure 5-1 Tools Menu

 
Description of "Figure 5-1 Tools Menu" 

Select an option from the Tools menu to see the definitions you have already created for the tool. You turn a tool definition on and off by adding or removing the checkmark in front of its name in the tool's dialog box.

Turning on a tool definition applies it to the data in the table or crosstab. The results are then displayed according to the specifications of the tool. For example, turning on (applying) a condition filters the data to show the precise information you want to see. Similarly, turning on a set of totals definitions with the Totals tool calculates and displays subtotals and Grand Totals in your data.

Turning off a tool definition returns the data to its original form. For example, turning off a condition re-displays the data previously filtered out by the condition, and turning off totals displays the data again without subtotals and Grand Totals.

Figure 5-2 The Conditions Dialog Box

 
Description of "Figure 5-2 The Conditions Dialog Box" 

Key to Figure 5-2: 
a. This panel shows the conditions that you have already created, or have been made available to you by your Discoverer manager. A selected check box indicates that a Condition is active. A cleared check box indicates that a Condition is not active. 

• To turn on another tool definition, click the box in front of that definition so a checkmark appears in front of it too.
• To turn off a tool definition, click the box in front of the definition to remove the checkmark.
  
  

  Note: Clicking the Delete button removes the Condition from the Workbook. You cannot delete Items created for you by your Discoverer manager. When you select these Items, the Delete button is grayed out.

5.1.1 Viewing Tool Definitions for Specific Items

When creating a tool definition, you select whether to assign it to all data items or to a single item. Use the View drop-down menu to see the definitions assigned to various items, (see Figure 5-3 below).

Figure 5-3 Choosing which definitions to display in the Conditions dialog box

 
Description of "Figure 5-3 Choosing which definitions to display in the Conditions dialog box" 

To see definitions for different items:

1. Click the down-pointing arrow in the View drop-down list.
2. Select one of the items on the list.

The list of definitions in the dialog box pertain to the selected item in the drop-down list. To see all of the definitions for the worksheet, choose All Items. To see only those currently turned on, choose Active Only.

Figure 5-4 Choosing definitions that apply to specific Items

 
Description of "Figure 5-4 Choosing definitions that apply to specific Items" 

Key to Figure 5-4: 
a. Here, only conditions that apply to the Year Item will be displayed. 

5.1.2 Creating New Tool Definitions

Each tool has its own features, but the initial process to create a new definition is similar.

To create a new definition:

1. Choose a tool from its menu.
   The tool's dialog box appears.
2. Click the New button.
   A dialog box appears for creating a new definition for that tool.

Each New dialog box is different. See the descriptions in the rest of this chapter for details on how to create a new definition for each tool.

5.1.3 Editing Existing Tool Definitions

To edit a tool definition, you must select it first on the list of definitions:

1. Select a tool from its menu.
   The tool's dialog box appears.
2. Select a definition from the list.
   Figure 5-5 The Conditions Dialog Box

    
   Description of "Figure 5-5 The Conditions Dialog Box " 
   
   
   Key to Figure 5-5: 
   a. In this example, the Condition 'Department is Video Rental or Video Sale' is selected. 
3. Click the Edit (or Show) button.
   A dialog box appears for editing the definition.
   Each dialog box for editing a definition is different. See the descriptions in the rest of this chapter to see how to edit definitions for each tool.

Note: Editing a definition does not automatically apply it to the data. The box in front of the definition must have a checkmark in it in order to be applied to the data.

5.1.4 Deleting Tool Definitions

Although you can delete definitions, you may want to simply turn them off instead. Then, if you need a definition later, you can just turn it back on. Deleted definitions are erased permanently. If you delete a definition and want to reuse it later, you will have to re-create that definition from the beginning.

Note: You cannot remove Items created for you by your Discoverer manager.

To delete an existing tool definition:

1. Select a tool from its menu.
   The tool's dialog box appears.
2. Click the definition in the list of definitions.
3. Click the Delete button.
   The definition is deleted and removed from the list of definitions.

5.2 Finding Exceptions to Data

A typical analysis task is to find numerical data that meets or exceeds a particular amount (that is, data that is an Exception to the rest of the data). For example, you may want to find all the stores in your nationwide chain that have profits in excess of $10,000 for the year.

Finding Exceptions to the data involves two steps:

• Defining the Exception itself, such as Òis greater than 10,000Ó or Òis less than 25%Ó or Òis between 10,000 and 50,000Ó.
• Defining the format of the Exception data so you can see it easily among the rest of the data.

Use the Exception dialog box for both steps. Figure 5-6 shows an example.

Figure 5-6 Applying Exception analysis to Worksheets

 
Description of "Figure 5-6 Applying Exception analysis to Worksheets" 

Key to Figure 5-6: 
a. The selected Exception format is applied to data in the Worksheet. 
b. This Exception colors the cell green where the Item Profit SUM has a value greater or equal than $60,000. 

To turn on an Exception:

1. Choose Format | Exceptions.
   The Exceptions dialog box appears. It shows the Exceptions you have already defined.
   Figure 5-7 Exceptions Dialog Box

    
   Description of "Figure 5-7 Exceptions Dialog Box" 
   
2. Click the check box in front of the Exception definition. A checkmark appears in the check box to indicate that it is selected.
   In the example above, the Exception ÒProfit SUM >= 60000Ó is turned on.
3. Click OK.
   Discover now analyzes the data, finds the Exception data, and displays it according to the format of the Exception's definition.
   
   

   You can create many Exceptions concerning the same data items. However, the Exceptions must not conflict.:

To turn off an Exception:

1. Choose Format | Exceptions.
   The Exceptions dialog box appears.
2. Click the Exception's box to remove the checkmark.
3. Click OK. The Exception is turned off and removed from the analysis.

5.2.1 Creating a New Exception

To create a new Exception:

1. Choose Format | Exceptions.
   The Exceptions dialog box appears.
2. Click the New button.
   The Exception dialog box for a new Exception appears:

Figure 5-8 Exception Dialog Box

 
Description of "Figure 5-8 Exception Dialog Box" 

Creating a new Exception has two parts: defining the Exception and defining its format. The top portion of the dialog box is for defining the Exception; the bottom part is for the formatting.

To create the Exception definition:

1. Click the leftmost drop-down button to see a list of data items.
   Figure 5-9 Select the Exception Item

    
   Description of "Figure 5-9 Select the Exception Item" 
   
2. Choose the data item for which you want to find the Exception.
3. Click the drop-down button for the Exception expression, such as Òis betweenÓ or Òis greater than,Ó and choose the one you want.
   Figure 5-10 Select the Exception Expression

    
   Description of "Figure 5-10 Select the Exception Expression" 
   
   The following table shows some examples of the types of expressions you can define for Exceptions.

   You want to find...	Use this expression...	Comments
   Amounts over a certain value	Òis greater thanÓ <value> For example Òis greater thanÓ 10,000	The Òis greater thanÓ Exception finds data equal to or greater than the value. In this example, values equal to or greater than 10,000 will be highlighted.
   Text that follows other text alphabetically	Òis greater thanÓ <text> For example Òis greater thanÓ New York finds New York and all the names alphabetically after it	The text can be any word or text item.
   Amounts under a certain value	Òis less thanÓ <value> For example Òis less thanÓ 10,000	The Òis less thanÓ Exception finds data equal to or less than the value. In this example, a values equal to or less than 10,000 will be highlighted.
   Text that precedes other text alphabetically	Òis less thanÓ <text> For example Òis less thanÓ New York finds New York and all the names that alphabetically precede it	The name can be any word or text item.
   Amounts between two values (either text or numeric)	Òis betweenÓ <lower value> and <upper value> For example, numbers between 5000 and 10,000, or text between Jones and Smith, or Zip Codes between 95000 and 96000	The Òis betweenÓ Exception finds data equal to or greater than the lower value and equal to or less than the upper value. Data between the two values will be highlighted. Applies to both text and numeric items. For example, Zip Codes from 95000 through 96000 will be highlighted. To find all text alphabetically in a range, such as all the names starting with the letter C, enter Caas the lower value and Cz as the upper value.
   Amount or name that matches	Òis equal toÓ <value> For example Òis equal toÓ New York finds all data with New York as the name	The value can be a numeric or text item. The value and data must match exactly.

4. Type the number or text for the Exception in the box(es) at the right of the Exception dialog box. For the expression Òis betweenÓ two boxes appear for the low andhigh values.
   Figure 5-11 Enter the Exception Value(s)

    
   Description of "Figure 5-11 Enter the Exception Value(s)" 
   

If you select a data item with discrete values, such as Region, and then select the expression Òis equal toÓ you don't have to type in the value. Simply click the drop-down button on the third box to see the list of values for that item.

For example, in the sample Vidstore workbook, the three values for Region are Central, East, and West. Selecting ÒRegion is equal toÓ and then clicking the drop-down button displays Central, East, and West as the choices.

To format the Exception:

1. Select the display options for the Exception from the bottom half of the dialog box.
   Figure 5-12 The Exceptions Dialog Box

    
   Description of "Figure 5-12 The Exceptions Dialog Box" 
   
   
   Key to Figure 5-12: 
   a. The Sample box shows what the Worksheet text will look like. 
   b. Options for setting the text font, size, and style. 
   c. Options for setting the text alignment. 
   d. Options for setting text color and background color. 
2. Click OK.
   The Exceptions dialog box reappears with your new Exception listed in it. Remember to click the box in front of the Exception to turn it on and click OK to apply it to the data.
   Figure 5-13 A New Exception

    
   Description of "Figure 5-13 A New Exception" 
   

5.2.2 Editing an Exception

To edit an Exception:

1. Select the Exception on the Exceptions dialog box.
   Figure 5-14 Select an Exception to Edit

    
   Description of "Figure 5-14 Select an Exception to Edit" 
   
   In this example, the Exception ÒProfit SUM greater than 60000Ó is selected. The Description at the bottom of the box shows the current Exception and its format.
2. Click the Edit button. The Exception dialog box appears (Figure 5-8).
3. Make the changes to the Exception as required.
4. Click OK to save any changes.

If you now want to apply the edited Exception to your data items, make sure it is turned on (the check box in front of the Exception is selected) and click OK.

5.3 Totaling Numeric Data

When working with numeric items, you often want to see various types of summations of the data. Using the Totals tool you can sum rows and columns of numbers, findaverages and standard deviation, compute subtotals and Grand Totals, and so on. The Totals tool automatically places the summations at the appropriate positions on the display.

Here are some examples:

Figure 5-15 A Crosstab Total on Worksheet Rows

 
Description of "Figure 5-15 A Crosstab Total on Worksheet Rows" 

Key to Figure 5-15: 
a. An ordinary Crosstab Worksheet. 
b. The same Crosstab Worksheet with a Total on rows, which calculates a grand total for each Region (Central, East, and West) for 1998, 1999, and 2000. 

Figure 5-16 A Worksheet with Totals on Columns

 
Description of "Figure 5-16 A Worksheet with Totals on Columns" 

Key to Figure 5-16: 
a. The value to be calculated. Other choices include Average, Count, etc. 
b. The Item Label displayed on the Worksheet. 
c. The Item on which the calculation is performed. 
d. The Total is calculated as a Grand Total of all values. Alternatively, the Total could be calculated as a Sub-total. 
e. The Grand Total as it is displayed on the Worksheet. 
f. The Worksheet also has a Total Sub-grouped on each change in Region, which gives individual Totals for each Region, (see Note d.). 

Figure 5-17 Multiple Totals on a Crosstab

 
Description of "Figure 5-17 Multiple Totals on a Crosstab " 

Key to Figure 5-17: 
a. Two Totals are active. All Items SUM for Profit SUM sums the Profit SUM column. All Items SUM for Sales SUM sums the Profit Sales column. 
b. The All Items SUM for Profit SUM Item on the Crosstab Worksheet. 
c. The All Items SUM for Sales SUM Item on the Crosstab Worksheet. 

Notice that the two Totals are displayed on the same row. When a crosstab has multiple Totals active, Discoverer Desktop puts them on the same row.

To display totals or subtotals on a table or crosstab:

1. Choose Tools | Totals.
   The Totals dialog box appears. It shows any totals you've already defined.
   Figure 5-18 Totals Dialog Box

    
   Description of "Figure 5-18 Totals Dialog Box" 
   
2. Click the check box in front of the Total definition so a checkmark appears.
3. Click OK. Discoverer Desktop now computes the totals and displays them on the table or crosstab.

To remove the totals from the data:

1. Choose Tools | Totals.
   The Totals dialog box appears.
2. Click the selected check box(es).
3. Click OK. The totals are removed from the table or crosstab.

5.3.1 Creating a New Totals Definition

Creating a new totals definition has four basic steps:

• Selecting the totals to calculate.
• Selecting the type of total and where to place it on the table or crosstab.
• Creating a label for the totals column or row.
• Defining the format for the totals column or row.

To create a new totals definition:

1. Choose Tools | Totals.
   The Totals dialog box appears (Figure 5-18).
2. Click the New button.
   The Total dialog box for a new totals appears.
   Figure 5-19 Total Dialog Box

    
   Description of "Figure 5-19 Total Dialog Box" 
   
3. Click the leftmost drop-down button to see the list of totals.
   Figure 5-20 Select the Totals to Calculate

    
   Description of "Figure 5-20 Select the Totals to Calculate" 
   
4. From the drop-down list, choose the type of total for the data.

   Option	Description
   Sum	Adds all the values.
   Average	Adds all the values and divides by the number of values.
   Count	Counts the total number of values.
   Count Distinct	Counts the number of unique values.
   Minimum	Finds the lowest value.
   Maximum	Finds the highest value.
   Standard Deviation	Calculates the standard deviation. (Standard deviation is the square root of the variance of the values.)
   Variance	Calculates the variance. (Variance is the sum of the squares of the differences between each value and the arithmetic mean divided by the number of values.)
   Percentage of Grand Total	Calculates the Grand Total of the row or column, then finds the percentage of the current column or row of the Grand Total.
5. Click the drop-down button to select the data points to be totaled.
   Figure 5-21 Select the Data Points to Total

    
   Description of "Figure 5-21 Select the Data Points to Total" 
   
   All Data Points— displays totals for each set of appropriate data points. For example, if the table contains two columns of numeric data points, both columns display totals. However, data points not appropriate for the type of total are not displayed.
   In the example above, Region is a set of data points, but summing Region data points doesn't make sense—it would be like trying to add ÒCentralÓ to ÒEastÓ. In this case, Regions are not summed even though you selected All Data Points.
   A specific numeric data point (such as Profit SUM in the example)—displays totals for the selected set of data points.
   A non-numeric data point (such as Region in the example)—when you select a non-numeric set of data points, the options for the totals in the first drop-down list are limited to only those options that apply to non-numeric data points. For example, if you select Region, sum of regions does not make sense. The only totals that make sense for non-numeric data points are Count, Count Distinct, Maximum, and Minimum.
   
   

   Note: If you choose the All Data Points option when your Worksheet contains Calculations, the Calculation is applied to the Totals. Discoverer Desktop does not total the Calculations, (see Section 5.3.2, "Totals and Calculations").

6. Click one of the options for Placement of the totals data:
   Grand total at bottom—Calculates the Grand Total for a column and places it after the last row of the table or crosstab.
   Grand total at right—Calculates the Grand Total for a row and places it to the right of the last column in the crosstab. (This option is only available from the Total dialog box for crosstabs.)
   Subtotal at each change in—Calculates the subtotals for a column and places it at each new value for the selected item. Select the item from the drop-down list. For example, the subtotals for Regions appear at the end of the data for each Region.
   Figure 5-22 Select a Subtotal Placement

    
   Description of "Figure 5-22 Select a Subtotal Placement" 
   
7. Now enter a Label for the totals data.
   You can click the drop-down menu for labels and choose additional options for the title from it.
   Figure 5-23 Create a Label for Totals

    
   Description of "Figure 5-23 Create a Label for Totals " 
   
   
   Key to Figure 5-23: 
   a. Click here and type a Label. 
   b. You can also select from this drop-down list to add various system values to the label. 

The system values from the drop-down list produce labels that can change as the data changes by adding text codes (such as Ò&ItemÓ and Ò&ValueÓ) to the label when you insert them in the label text. In the actual labels on the table or crosstab, the ampersand (&) will not appear, and appropriate names from the table or crosstab will be inserted in place of the words ÒItemÓ or ÒValueÓ.

This table shows some examples.

Option	Example	Sample label(s)
Insert Item Name	Latest Data from &Item	Latest Data from Region
Insert Data Point Name	Top Performers of &Data	Top Performers of Profit Sum
Insert Value	Total Income for &Value	Total Income for Central
Generate Label Automatically	Sum	Sum; automatic label is the name of the type of calculation (e.g., Sum, Count, Standard Deviation)

If the total calculates for all data points (as selected at the top of the dialog box), the labels can appear for each appropriate name. For example, when totaling two items, and you select Insert Item Name (&Name), labels for both item names appear in the data or crosstab.

To remove options from the labels, click in the label text on the dialog box and edit it as you would regular text.

1. Click Wrap Text if you think the label will be too long for the column where it will appear.
2. Click the Format button to format the Totals data. The Format dialog box appears.
3. Click OK.
   You'll now see the Totals dialog box again with your new total definition listed in it.

5.3.2 Totals and Calculations

When creating your Totals, if you total a Calculation, the Calculation is applied to the Total. Discoverer Desktop does not total the Calculations.

For example, in Figure 5-24, the Profit Item is a Calculation: Sales SUM / Cost SUM. The Sum value for Profits is $3.23, ($1,150,603 / 356,087). The SUM value is NOT $9.70, ($3.20 + $3.23 + $3.27).

Figure 5-24 A Worksheet Total on a Calculation (Profits)

 
Description of "Figure 5-24 A Worksheet Total on a Calculation (Profits)" 

Note: If you want Discoverer Desktop to total Calculations rather than apply the Calculations to Totals, contact your Discoverer manager.

5.3.3 Editing a Totals Definition

To edit a totals definition:

1. Select the definition you want to edit on the Totals dialog box.
2. Click the Edit button. The Total dialog box appears (Figure 5-25).
   Figure 5-25 Total Dialog Box

    
   Description of "Figure 5-25 Total Dialog Box" 
   
3. Make the changes you want.
4. Click OK. The totals definition is now edited.

5.4 Using Percentages

A typical data analysis task is to calculate Item percentages. To calculate percentages in Discoverer Desktop, use the Percentages option on the Tools menu.

Note: Due to rounding of data, percentages may not add up exactly to 100. The amount of rounding depends on the number of decimal places that you specify for data. Use the Format Data dialog box to set the number of decimal places for your data.

Figure 5-26 Using Percentages on a Crosstab Worksheet

 
Description of "Figure 5-26 Using Percentages on a Crosstab Worksheet" 

Key to Figure 5-26: 
a. On the Worksheet you can see the Percentage Item Percentage of Annual, which shows the Profit SUM as a percentage of annual profits. 
b. The Percentage dialog box used to define and edit Percentages. 
c. The Item on which the Percentage is calculated. 
d. 'Grand Total for each column' calculates the value as a percentage of the annual total.. 
e. The name of the Item as it appears on the Worksheet. 

5.4.1 Using the Percentages Tool

To find a percentage with the Percentages tool on the tool bar:

1. Select the column containing the data that you want to use to find percentages.
2. Click the Percentages tool on the tool bar.
   The percentage column shows the percentage of each row to the total of all the rows displayed on the table or crosstab.
   Here are examples of percentages on a Crosstab Worksheet:
   Figure 5-27 A Percentages example on a Crosstab Worksheet

    
   Description of "Figure 5-27 A Percentages example on a Crosstab Worksheet" 
   
   
   Key to Figure 5-27: 
   a. Percentage SUM Profit, Region calculates profit for each row (in this case City) as a percentage of the Region total, (see note c). 
   b. Percentage SUM Profit calculates profit for each row (in this case City) as a percentage of the annual total for the year 2000.. 
   c. The Percentage dialog box for the item Percentage SUM Profit, Region, showing how the value is calculated as a percentage of the Region total. 
   d. The Percentage dialog box for the item Percentage SUM Profit, showing how the value is calculated as a percentage of the annual total. 

To display percentages on a table or crosstab:

1. Choose Tools | Percentages.
   The Percentages dialog box appears. It shows the percentages you've already defined.
   Figure 5-28 Percentages Dialog Box

    
   Description of "Figure 5-28 Percentages Dialog Box" 
   
2. Select the check box in front of a percentage definition.
3. Click OK.

Discover now computes the percentages and displays them on the table or crosstab.

To remove the percentages from the data:

1. Choose Tools | Percentages.
   The Percentages dialog box appears.
2. Clear the check box(es).
3. Click OK to remove the percentages from the data.

5.4.2 Creating a New Percentages Definition

Creating a new percentage definition has four basic steps:

• Selecting the data item for calculating the percentage.
• Selecting to calculate the percentage of a total or change in values.
• Creating a label for the percentage column.
• Defining the format for the column.

To create a new percentage definition:

1. Choose Tools | Percentages.
   The Percentages dialog box appears (Figure 5-28).
2. Click the New button.
   The Percentage dialog box for a new definition appears:
   Figure 5-29 Percentage Dialog Box

    
   Description of "Figure 5-29 Percentage Dialog Box" 
   
3. On the Percentage dialog box, click the drop-down button at the top of the box to see the list of data items for which to calculate percentages.
4. Select the data item from the list to use to calculate percentages.
5. Select one of the options to calculate a percentage of. If you select the option Each Change in, select the data item where you want the percentage to be displayed when the value changes.
   The following table lists your choices:

   Option	Description
   Grand total	Calculates the percentage of the Grand Total for each column. this option is only available from the Percentages dialog box for tables.
   Grand total of all values	Calculates the percentage of the Grand Total for all the columns.
   Grand total for each column	Calculates the percentage of the Grand Total for each column.
   Grand total for each row	Calculates the percentage of the Grand Total for each row.
   Each change in	Calculates the percentage places it at each new value for the selected item. Select the item from the drop-down menu.

   Figure 5-30 Select Data for Percentages

    
   Description of "Figure 5-30 Select Data for Percentages" 
   
6. Enter a column heading for the percentages column.
7. Select the check boxes at the bottom of the dialog box to display subtotals and their percentages, or subtotals as a percentage of the Grand Total.
8. Enter labels for the subtotal and Grand Total percentages, or click the drop-down menus for labels and choose additional options.

Figure 5-31 Create Labels for Percentage Columns

 
Description of "Figure 5-31 Create Labels for Percentage Columns" 

The options from the drop-down menu produce labels that can change as the data changes by adding text codes such as Ò&ItemÓ and Ò&ValueÓ where you insert them in the label text. In the actual labels on the table or crosstab, the ampersand (&) will not appear, and appropriate names from the table or crosstab will be inserted in place of the words ÒItemÓ orÓ ValueÓ.

The table below shows some examples.

Option	Example	Sample label(s)
Insert Item Name	Percent of Profit from &Item	Percent of Profit from Region
Insert Data Point Name	Top Performers of &Data	Top Performers of Profit Sum
Insert Value	Yearly Percent for &Value	Yearly Percent for Central

To remove options from the labels, click in the label text on the dialog box and edit it as you would regular text.

1. Click the Format button next to the different label definitions to format the percentages. The Format dialog box appears.
2. Click OK to return to the Percentages dialog box with the new definition.

5.4.3 Editing a Percentage Definition

To edit a percentage definition:

1. Select the definition you want to edit on the Percentages dialog box.
2. Click the Edit button. The Percentage dialog box appears.
3. Make the changes you want.
4. Click OK. The percentage definition is now edited.

5.5 Finding Data that Meets Conditions

A typical data analysis task is to filter the data to find only that data that meets certain conditions. For example, you might want to limit the display of data to the last two years of sales. Or, you want to see the data for only two types of sales items. Each of these tasks involves filtering the data to find the specific data that meets the conditions.

Some sample conditions are:

• Year = 1998 or 1999—The displayed data applies to 1998 and 1999 only. The workbook may contain data from other years, but it will not be displayed.
• Profit SUM > 3000—The worksheet displays Profit SUMs greater than 3000.
• City <> 'Boston'—The worksheet displays data for all cities, except Boston. Note that the value Boston is enclosed in single quotes. Text values in conditional expressions must be in single quotes.
  
  

  Note: Several features for creating conditions involve advanced analysis techniques. For example, instead of creating a condition for a defined data element, you can specify a condition based on a calculated value that computes which data can meet the condition. The advanced features are covered in Chapter 8, "Advanced Discoverer Desktop Features".

Conditions can be similar to Exceptions. The differences between conditions and Exceptions, however, are significant.

• An Exception finds data that meets the Exception definition and highlights it in the tables and crosstabs. All other data remains visible.
• A condition finds data that meets the condition and removes the rest of the data from the display so you see only the data that meets the conditions.

Conditions are essentially powerful data filters that find the specific data you're looking for and displays only that data. You can define numerous conditions and turn them on and off to filter the data in the tables and crosstabs.

Note: Applying a condition to a table or crosstab 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, turn off the condition.

To find data that meets certain conditions:

1. Choose Tools | Conditions.
   The Conditions dialog box appears.
   Figure 5-32 Conditions Dialog Box

    
   Description of "Figure 5-32 Conditions Dialog Box" 
   
   
   Key to Figure 5-32: 
   a. These are the conditions that you have already defined, or have been made available to you by your Discoverer manager. 
   b. Selected check boxes in front of a condition definition indicate that the condition is active and is being applied to the data already. 
2. To find data that meets a condition, select the check box in front of the condition definition. That turns on the condition.
   In the example above, the condition ÒDepartment is Video Rental or Video SaleÓ is selected.
3. Click OK. Discover now filters the data, finds the data that meets the condition, and displays it.
   
   

   Note: If you select two (or more) conditions that conflict, a warning appears. For example, the two conditions ÒYear = 2000Ó and ÒYear = 1998 or 1999Ó applied to the same layout conflict, because the first condition removes all years except 2000 and the second condition tries to display 1998 and 1999 at the same time.

To see conditions for specific data items:

1. At the Conditions dialog box, choose an item from the drop-down list, View Conditions For. You can display all the conditions defined in a workbook, or only those that apply to particular data items.
   • To see conditions that apply to a specific data item, click the drop-down button and select a data item to see its conditions.
   • To see all of the conditions defined for the workbook, choose All Items.
   • To see only those conditions currently turned on, choose Active Only.
   Figure 5-33 View Conditions for a Specific Data Item

    
   Description of "Figure 5-33 View Conditions for a Specific Data Item" 
   
   
   Key to Figure 5-33: 
   a. Choose a data item to see its condition. 

To remove the condition from the data:

1. Choose Tools | Conditions.
   The Conditions dialog box appears (Figure 5-32).
2. Clear in the check box next to the condition.
   Do not click the Delete button. That deletes the condition definition from the list of conditions.
3. Click OK. The condition is removed from the data.

Now, the data is restored to the table or crosstab, because the data is not being filtered. That is, the condition is turned off.

5.5.1 Creating Conditions

To create a new condition:

1. Choose Tools | Conditions.
   The Conditions dialog box appears.
2. Click the New button.
   The New Condition dialog box appears:
   Figure 5-34 New Condition Dialog Box

    
   Description of "Figure 5-34 New Condition Dialog Box" 
   
   Name—automatically generates a name for the condition based on the item, the condition, and the values that you select for it. To create a different name, clear the check box Generate name automatically. You can then enter a name for the condition in the Name box.
   Description—For simple, straightforward conditions, the name and description are usually sufficient to explain how the condition will filter the data, and you don't need to type an extra description. However, advanced conditions may need descriptions for clarity. Enter a description here and it appears on the Conditions dialog box when the condition is selected.
   Location—The workbook where the condition will be applied.
3. Click the drop-down button for Item.
   The drop-down list shows the data items in the workbook that you can use for the condition. It also lists Create Calculation and Select Condition, which are some other ways to create the first part of the condition.
   Figure 5-35 Select the Condition Item

    
   Description of "Figure 5-35 Select the Condition Item" 
   
   Create Calculation—You can use a calculation to filter the data (see Chapter 8, "Advanced Discoverer Desktop Features").
   Select Condition—Creates conditions that use other conditions to filter the data first (see Chapter 8, "Advanced Discoverer Desktop Features").
   Notice that the list of items contains all the data items in the workbook, not just those being displayed on the current table or crosstab. You can use any data item to create a condition.
4. Choose the data item to use for filtering the data.
5. Click the drop-down button for the condition expression and choose the one you want. See the table below for descriptions and examples of the expressions.
   Figure 5-36 Select the Condition Expression

    
   Description of "Figure 5-36 Select the Condition Expression" 
   
6. To complete the definition of the condition enter a value in the Value(s) box.
   
   

   Note: When you create a condition with text for the value, the text must be enclosed in single quotes. For example, in the condition Region = 'Central', the text value, 'Central', must be enclosed in single quotes. Numbers do not have to be in quotes.

   The following table describes the condition expressions:

   Expression	Meaning	Example
   =	Equals	Region = 'Central'; only the Central Region data is displayed.
   <>	Not equal	Region <> 'Central'; all other region data except Central is displayed.
   >	Greater than	Profit SUM > 10000; all Profit SUM items greater than 10000 are displayed.
   <	Less than	Profit SUM < 10000; all Profit SUM items less than 10000 are displayed.
   <=	Less than or equal to	Profit SUM <= 10000; all Profit SUM items less than or equal to 10000 are displayed.
   >=	Greater than or equal to	Profit SUM >= 10000; all Profit SUM items greater than or equal to 10000 are displayed.
   LIKE	Similar to (using wildcard matching)	Name LIKE 'A%'; finds all names beginning with the letter A. The percent (%) sign matches any number of characters. An underscore symbol (_) matches a single character.
   IN	Contains one or more values	City IN ('Boston', 'Los Angles', 'New York'); finds data that contains at least one of the values.
   IS NULL	Contains no data (not even zero)	Commission IS NULL; displays data only when commission has no value.
   IS NOT NULL	Contains some data (even zero)	Commission IS NOT NULL; displays data when commission has any value.
   NOT IN	Is not contained in one or more values	City NOT IN ('Boston', 'Chicago'); does not display data that contains Boston or Chicago.
   BETWEEN	A value lies between two values	Profit BETWEEN 1000 AND 2000; displays profits greater than or equal to 1000 or less than or equal to 2000.
   NOT BETWEEN	A value lies outside of two values	Profits NOT BETWEEN 1000 AND 2000; displays profits less than 1000 or greater than 2000.
   NOT LIKE	Not similar to	Name NOT LIKE 'A%'; finds all names not beginning with A. The percent (%) sign matches any number of characters. An underscore symbol (_) matches a single character.
   != and ^ =	Not equals	Region ! = 'Central'; finds all regions except Central. Note: These two expressions have the same meaning because both are supported by SQL programming. Therefore, if you use an SQL programming statement to create a complex conditional value, Discoverer Desktop can recognize it regardless of which expression you use in the program.

   To create an advanced condition, click the drop-down button to see other options. You can also click the Advanced button for more options. See Chapter 8, "Advanced Discoverer Desktop Features" for a description of the value options and other advanced features.
   Figure 5-37 Enter the Condition Value(s)

    
   Description of "Figure 5-37 Enter the Condition Value(s)" 
   
   If you select a data item with discrete values, such as Calendar Year, and then select Òis equal toÓ you do not have to type in the value. Click the Value(s) drop-down button to see the list of values for that item, then select the one that you want to use.
   For example, in the sample Video Stores workbook, the three values for Calendar Year are 1998, 1999, and 2000.
7. If you are dealing with text and want the condition to match the uppercase and lowercase characters in the text, select the Match Case check box.
   For example, if you want the condition to filter the data to find all ÒWidgetsÓ but not ÒwidgetsÓ, select the Match Case check box.
8. Click OK. The new condition appears on the Conditions dialog box.

5.5.2 Editing and Removing Condition Expressions

To edit an existing condition:

1. Select the condition on the Conditions dialog box.
   Figure 5-38 Conditions Dialog Box

    
   Description of "Figure 5-38 Conditions Dialog Box" 
   
   
   Key to Figure 5-38: 
   a. The selected check box indicates that this condition is active. 
   b. This condition is selected. 
2. Click the Edit button. The Edit Condition dialog box appears.
3. Make the changes you want to the condition.
4. Click OK. The condition is now edited.

If you now want to apply that condition to the data, make sure it is turned on (the check box in front of the condition is selected) and click OK.

To delete a condition from the list of conditions that you can apply to the data:

1. Choose Tools | Conditions.
   The Conditions dialog box appears (Figure 5-38).
2. Select the condition you want to delete.
3. Click Delete to remove the condition from the list.