2
Getting the Data You Want

Getting specific data--the data that you want to see--from your company's database involves four basic steps:

1. Open the workbook that contains the data you want. If several workbooks exist, you open the one that contains the specific data you want. For example, from a set of workbooks created for a fictitious video store retail chain, you can open the sales workbook to check monthly profits, and then open another workbook to see how your store renovation program is progressing. Click to learn more about Opening an Existing Workbook and Viewing Scheduled Workbooks.

2. As part of the process to open a workbook, select from choices, called Parameters, that define the precise data you want to see in the workbook. One Parameter might be the name of cities where your company has stores. To see profits from New York and Los Angeles, there's no reason to display data from every other city. So, when opening a workbook you choose those two cities, and the workbook opens with exactly the data you want. While working with that data you can always get the sales figures from the rest of the cities as well. Click to learn more about Finding Specific Data When a Workbook Opens.

3. Reduce the amount of data by using Conditions. Conditions filter data and display only the data that meets the Conditions. For example, a Condition might be "find all the stores that did not meet their sales goals in the first quarter and that had fixed expenses greater than 40% of revenues." Discoverer compares the worksheet's data to the Condition, and then displays only the data that meets the Condition. Click to learn more about Finding Specific Data When a Workbook Opens.

4. Create a new workbook, if necessary. If none of the workbooks meet your requirements you can create a new one, customized for displaying exactly the right combination of data. To create a new workbook, you must have the appropriate database privileges. Please see your Discoverer Administrator. Click to learn more about Creating a New Workbook.

Opening an Existing Workbook

If you have questions about the Discoverer workbook names, whether you have access to them, the location of workbooks on company servers, and so on, see your Discoverer Administrator. The Discoverer Administrator also supplies the passwords and server access instructions that you need to connect to your company's database and open a Discoverer workbook.

To open a workbook you first connect to your company's database. Choose the workbook to open from the Open Workbook from Database dialog. One workbook can be open at a time. To open another workbook you close the workbook already opened. You can open a workbook in your database account, or in another person's account which is shared to you.

Click to learn how
To connect to your company's database:
To open a workbook:
To open a scheduled workbook:

See also:
Viewing Scheduled Workbooks
Opening Workbooks in a Non-Oracle Database
Creating a New Workbook


To connect to your company's database:

1. Launch your Web browser.

2. Go to the Discoverer Web site address that your Discoverer Administrator gives you.

3. Depending on the Web browser you are using and your company's Web server configuration, you may need to follow a one-time-only set up process. See your Discoverer Administrator for details.

   You may also see a dialog about security. This security dialog appears because Discoverer requests extra permissions so it can access the Discoverer server or local devices, such as a printer. If you don't want to see this dialog every time you connect, click the option "Always trust content from Oracle Corporation." Click Yes (or OK or Grant depending on the type of dialog) to continue launching Discoverer.

   A Welcome page appears.

   
   

4. Click the Click to Start icon.

   The Connect to Oracle Discoverer dialog appears.

   
   
   5. Type the username, password, and database name given to you by your Discoverer Administrator.
      
      NOTE: For information about connecting to an Oracle Applications EUL, see Appendix , "Oracle Applications Support".

   6. Click Connect.
      
      The Workbook Wizard appears, which means you are connected to the database and ready to use Discoverer.

      
      

   
   
   
   To open a workbook:
   1. Click Open an existing workbook.

      The dialog then shows options for opening a workbook from the database or a scheduled workbook.

      
      
   2. Select one of the following:

      Database--displays a list of workbooks in the database to which you are connected.

      Scheduled--displays a list of scheduled workbooks that have run and are now available. Scheduled workbooks run automatically at the time you specify, usually overnight, on the weekend, or at some periodic interval. You can view the most current results when you open the scheduled workbooks.

      NOTE: If the button for opening a workbook from the database is not available, you can only open scheduled workbooks. Your Discoverer Administrator decides the types of workbooks you can open.

      
      
      3. Depending on your selection, either a list of stored workbooks or a list of scheduled workbooks appears. Select a workbook that contains the data you want to see, and click Open.

         The workbook opens. Discoverer evaluates the query to determine how much time it will take to open the first worksheet. Depending on the default options you've selected for opening worksheets, a progress dialog shows you the time estimate for loading the first sheet. See Setting General Options for the choices to load a worksheet.

         
         

         The worksheet appears. If the workbook has more than one worksheet, a series of tabs across the bottom of the window name the various worksheets.

         NOTE: The first worksheet in the workbook is not necessarily the first active worksheet.

      4. If the time estimate exceeds the value you set in the General Options, a dialog opens to alert you. If the query time lasts more than a few minutes you can do other work while Discoverer gets the data for the worksheet, or you can run the query at another time.
         
         Do one of the following:

         Click Yes. The query finds the most recent data and fills in the worksheet.

         Click No. The worksheet opens but does not contain any data. Click No if you want to see another worksheet with data first. Click that other worksheet's tab at the bottom of the window.

         NOTE: If you click No, you can always run the query for the worksheet at a later time by choosing Sheet | Refresh Sheet.

         If you click Yes, and if the workbook has Parameters for the data, you'll see a dialog for selecting those Parameters.

         
         To open another workbook:
         1. From the menu, choose File | Close to close the current workbook.

         2. Choose File | Open. The Open Workbook dialog appears.

            
            

            Note: Only one workbook at a time can be open. If you choose File | Open while a workbook is already open, the current workbook closes automatically.

         3. Select one of the options and click Open.

         You can connect to only one database account at a time. For example, if you are connected to Account1 but want to open a workbook saved in Account2, you must disconnect from Account1 then connect to Account2. However, if you have access to a shared workbook, you can open that workbook in your current account.

         Opening Workbooks in a Non-Oracle Database

         If your business uses non-Oracle databases, your Discoverer Administrator can set up Discoverer to open workbooks in those databases. You can then use Discoverer to get the data you want. However, depending on the type of database, you might not be able to use all the data access and analysis features available with Oracle databases.

         The process to open workbooks in non-Oracle databases is essentially the same as opening a workbook in an Oracle database. Please see your Discoverer Administrator for the correct steps to open the workbooks in non-Oracle databases.

         Viewing Scheduled Workbooks

         Scheduled workbooks run at a specified time. For example, you might want to run a workbook at the end of each month that calculates monthly profits from monthly sales figures, or you might want to run a workbook every two weeks based on a biweekly cash-flow analysis.

         Often, workbooks that you want to schedule are designed specifically for that purpose, rather than for your day-to-day analysis. For example, the workbook might include special calculations or Conditions that produce the results you want on a periodic basis only.

         See your Discoverer Administrator to schedule a workbook. You must have appropriate privileges to schedule a workbook.

         Typically you schedule workbooks if:

         • The workbook will take a long time to run; scheduling a workbook to run at night or on the weekend avoids overburdening the server during business hours.

         • You want to run a workbook at regular intervals, such as a weekly report of sales figures or a monthly cash-flow analysis.

         Because a scheduled report runs on the server, you do not need to leave your computer on overnight (or whenever you schedule the report to run). The results of the scheduled report are saved on the server and are available when you connect to the database and start Discoverer.

         A scheduled workbook produces a worksheet or set of worksheets with the results derived from running the workbook. You can open a scheduled workbook when you start Discoverer, or while working in a workbook. If you run a scheduled workbook overnight (or over the weekend) and want to see the results first thing the next morning, open the workbook as you connect to Discoverer.

         Viewing Scheduled Workbooks when connected as an Oracle Applications User

         When connected as an Oracle Applications User, the following rules apply:

         • If you connect as a particular User and Responsibility and schedule a Workbook, the Scheduled Workbook is specific to that User and Responsibility.

         • If you connect as a particular User but a different Responsibility from the one used to schedule a Workbook, the Scheduled Workbook may contain different results from the original Scheduled Workbook that was created.

         
         To open a scheduled workbook:
         1. Do one of the following:
            • Connect to the database and launch Discoverer as described in Section , "Opening an Existing Workbook".

            • If you are already connected to the database, from the menus choose
              File | Open. The Open Workbook dialog appears.

         2. Click Scheduled and click Open. The dialog lists the scheduled workbooks.

            
            

         3. Select the scheduled workbook you want to see and click Open.

         NOTE: The worksheets produced by running the scheduled workbook contain data derived specifically for that report and you can work with the worksheet in the normal manner. But if you change any of the data, a message reminds you that the new data on the worksheet is not the same as that derived from the scheduled workbook.

         Finding Specific Data When a Workbook Opens

         Databases often contain enormous amounts of information, and one key task necessary to work with a database efficiently is to find the specific information you want to see or analyze. Discoverer has a number of ways to filter out the data that you don't need to see and to find the specific data you want. Discoverer has filtering techniques both when you open a workbook initially and as you are working with the data.

         The two basic ways to filter out unnecessary data and find the specific information you want when opening a workbook are to select Parameters and to apply Conditions.

         • Parameters offer pre-defined choices of data when you open a workbook. For example, suppose you are opening a workbook containing data about retail outlets in stores across the nation, but you only want to see the data from stores in New York and Los Angeles. If one of the Parameters is "City," you can choose New York and Los Angeles as the data values for that Parameter. When the workbook opens it shows data from only those two cities--exactly what you want to see. Without the Parameter, the workbook opens with data from all the cities. Click to learn more about Parameters.

         • Conditions also filter the data to display only the exact information you want. Suppose, for example, a workbook contains five years of data, but you only want to see data from 1998. By applying the Condition statement of Year=1998, the workbook opens and displays data only from the year 1998. Condition statements tell Discoverer to find and display only the data that meets the Condition. Click to learn more about Conditions.

         Parameters actually use Condition statements to find specific data. However, unlike regular Conditions that find the same data each time they're applied, Parameters offer choices at the time the worksheet opens. For example, if the two Parameters for a worksheet are Department and City, the underlying Condition statement is "Find all the data about <Department> for a <City>". The two Parameters are essentially placeholders in the Condition statement until the person opening the worksheet picks a data value for each one. Then, Discoverer finds all the data based on the selected values. Click to learn more about Using Conditions.

         Although similar, Parameters and Conditions are designed for different purposes. Parameters offer you a choice and help you open a workbook quickly to see just the data that you want to see. Conditions are specific, fixed statements. Conditions are designed more for analysis so you can apply Condition statements while you are involved with data analysis to find very specific sets of data. However, Conditions and Parameters can also be used with each other for more sophisticated filtering procedures.

         Parameters

         The main benefits of using Parameters are:

         • you can choose the specific data to see on the worksheet

         • worksheets open more quickly because the amount of data on a worksheet is limited by the choices offered by the Parameter

         • if several people are using a worksheet, each person can open the worksheet and get just the data of interest to themselves

         A Discoverer user often creates Parameters when creating the initial workbook. However, anyone with the proper access rights (granted by the Discoverer Administrator) can create Parameters too. When creating or choosing Parameters, the term data values refers to the choices offered by the Parameter. For example, if the Parameter is for choosing the cities for which you want to see data, the city names are the data values, that is, New York, Los Angeles, Denver are the data values for the Parameter to choose cities.

         When opening a workbook you'll choose the data values for the Parameters. But if the Parameters do not offer the choices you want, you can create your own data values.

         Click to learn how:
         To create a new Parameter:
         To edit an existing Parameter:
         To select different Parameter values:

         See also:
         Choosing Data Values for Parameters When a Workbook Opens
         

         Choosing Data Values for Parameters When a Workbook Opens

         When opening a worksheet with pre-defined Parameters, a dialog lists the Parameters so you can select the ones you want on the worksheet.

         Note: Although choosing a data value for a Parameter limits the data initially displayed on the worksheet--for example, you limit the data to New York only--Parameters do not limit the data available for the worksheet as you are working with it. You can always add any additional data as you are working on the worksheet. Also, you can change Parameter values every time the query is refreshed, and you can edit Parameter values from the menu.

         Depending on the design of the Parameters, you can choose:

         • a data value for a single Parameter

         • multiple data values for a single Parameter

         • data values for multiple Parameters

           
           To choose a data value for a Parameter:

         1. Open a worksheet. If Parameters are defined for the worksheet, a dialog lists the Parameters available for it. The default value is in the text box next to each Parameter.

         2. Choose a data value for the Parameter by doing one of the following:
            • To choose the default value, click OK

            • To choose a value other than the default value, click the down arrow next to the text field. From the drop-down list, choose the data value(s) you want. Then click OK.

            The worksheet appears and contains data only for the values you chose. In the example below, the Parameter value Central is selected. The resulting worksheet contains data only for the Central region.

            
            
         3. If the Parameter is set up to allow for multiple data values, you can select several data values when opening the worksheet. This type of Parameter selection offers complete flexibility to choose the exact combination of data to see. From the Edit Parameter Values dialog, click the drop down arrow and choose Select Multiple Values. The Values dialog appears.

            
            

         4. In the Select Values list, select the check box(es) next to the Items that you want to see in your Worksheet. To see all Items, click Select All.

         5. Click OK on the Values dialog and then click OK on the Parameters dialog. The worksheet is refreshed to display only data from the Items selected.

            
            

         6. If the workbook has multiple Parameters defined for it, click the drop down arrow next to each Parameter and select a data value for each one. The following example shows two Parameters--one for cities and the other for year--so you can select a combination of data to see.

            
            

         Creating Parameters

         Because Parameters use Condition statements to find specific data, creating a Parameter is similar to creating a simple Condition statement. You specify the data item to use for the Parameter, for example, the list of city names in your database, and then specify the choices available for that Parameter.

         In addition to creating Parameters, you can edit them to change their default values, descriptions or headings. For example, if your company adds a new store to a sales region, you may want to edit the Parameter so that the new store is the default value for the Store.Name Parameter.

         You can create Parameters at two levels:

         1. Workbook level - Here, the Parameter applies to all worksheets in your workbook. Changes to the Parameter in any worksheet cascade to all worksheets in the workbook.

         2. Worksheet level - Here, the Parameter applies to the current worksheet only.

         
         To create a new Parameter:
         1. Display the worksheet to which you want to apply the Parameter.

         2. From the menu choose Tools | Parameters. The Edit Worksheet dialog opens with the Parameters tab highlighted.

         

         3. Click New. The New Parameter dialog appears.

            
            

      4. Type in or select the features of the Parameter.

         What do you want to name this Parameter--type the name that you want to appear in the Parameters dialog. If you don't type a name, Discoverer inserts a default Parameter name.

         Which item would you like to base you Parameter on--select the data item for the Parameter from the drop down list. For example, to create a Parameter for selecting a city, select the data item that contains the city names. The list shows the data items currently used in the worksheet. It also shows all items related to the items selected in the worksheet and all calculations.

         What prompt do you want to show to other users--this text appears in the dialog that appears prior to opening the worksheet; type text that prompts the user to make a selection.

         What description do you want to show to other users--this text also appears in the dialog; it explains the Parameter.

         What default value do you want to give this Parameter--this is the pre-selected data value for the Parameter. Click the drop down arrow and select a data value from the list, or type the default value directly into the box.

         Let other users select multiple values--select this option if you want the person using the worksheet to be able to select multiple data values for the Parameter when opening the worksheet. If this option is not selected, the person can choose only one value for the Parameter.

         What is the value of this parameter if it is used in more than one sheet?--allows you to create the Parameter either at Workbook level or Worksheet level.
         Click 'Allow only one value for all Sheets' to make the parameter value cascade across all worksheets in the workbook. Click 'Allow a different value in each Sheet' to make the parameter value apply to the current worksheet only.

         Parameterized Conditions refer to Condition statements that use a Parameter in their formulas. For example, if the Condition statement uses City in its formula, and you select New York as the data value for the Parameter, the Condition statement use New York as the City in the formula.

         Create Condition/use operator--creates a Condition with an operator. You can select the operator from the drop list. For example, select equals (=) to create a Condition with the formula "For Item" = "Parameter's Name." A typical use of this feature is to find data values greater then (>) or less than (<) a data value. For example, to find all the data after the year 1997 the Condition formula is "Year" > 1997. The worksheet then appears with data from 1998 on.

         Note: If you are creating a Parameter as part of a Condition, the portion of the dialog for creating Parameterized Conditions is not available because you are already defining a Condition.

      5. Click OK. The new Parameter now appears in the Parameters dialog.

         Moving the Parameters up and down in the Parameters dialog changes their position in the dialog that appears when opening a worksheet.

      6. Click OK in the Edit Worksheet Parameters tab.

         The Edit Parameter Values dialog appears, and you can specify the data value. The worksheet now displays the specific result for the data specified in the Edit Parameters dialog.

      Parameters that are part of an active Condition are automatically activated as well. If you select the option Create Condition/use operator in the New Parameter dialog, a new Condition is created and activated, therefore the Parameter is also activated.

      To deactivate a Parameter, deactivate the Condition. Deleting the Condition deletes the Parameter and vice versa.

      
      To edit an existing Parameter:
      1. Display the worksheet to which you want to apply the Parameter.

      2. From the menu choose Tools | Parameters. The Edit Worksheet dialog opens with the Parameters tab highlighted.

         
         

      3. Click the name of the Parameter you want to edit and then click Edit. The Edit Parameter dialog appears.

      Figure 2-1 Edit Parameter dialog
      

      4. Type in or select the features of the Parameter that you want to change.

         What do you want to name this Parameter--type the name that you want to appear in the Parameters dialog. If you don't type a name, Discoverer inserts a default Parameter name.

         What prompt do you want to show to other users--this text appears in the dialog that appears prior to opening the worksheet; type text that prompts the user to make a selection.

         What description do you want to show to other users--this text also appears in the dialog; it explains the Parameter.

         What default value do you want to give this Parameter--this is the pre-selected data value for the Parameter. Click the drop down arrow and select a data value from the list, or type the default value directly into the box.

         Let other users select multiple values--select this option if you want the person using the worksheet to be able to select multiple data values for the Parameter when opening the worksheet. If this option is not selected, the person can choose only one value for the Parameter.

      5. Click OK. You return to the Parameters tab.

      6. Click OK in the Parameters tab to apply your changes.

         
         To select different Parameter values:

      1. From the menu, choose Sheet | Edit Parameter Values. The Edit Parameters dialog appears.

      Tip: You can click the Refresh icon to display the Edit Parameters dialog, (or choose Sheet | Refresh Sheet).

      
      
      2. Select a new data value, and click OK.

         You'll see the results corresponding to the data value you have chosen.

      Conditions

      A Condition is a statement for finding specific data. Turning on a Condition filters out the data that does not meet the Condition, and displays only the data that you want to see.

      The following examples illustrate the concept of Conditions.

      The year 1998 was your best ever and you want to analyze sales data from 1998 in detail to pinpoint why the business was so successful. To run the analysis, you need to see data only from 1998.

      The Condition statement is: Year=1998. Data from the other years is not displayed.

      The stated business goal of your company is for each store to generate daily profits in excess of $3000. You want to find out which stores already exceed their profit goal of $3000.

      The Condition statement is: Profit SUM>3000. Data from all stores with profits greater than $3000 is displayed and you can quickly see which stores already meet their goal.

      On the other hand, to see the stores that haven't generated $3000 in daily profits yet, the Condition statement would be Profit SUM <3000.

      Worksheets can have many Conditions previously defined by the Discoverer Administrator, or by you. If you have the privilege to edit a worksheet, you can pick and choose which Conditions to apply to see the data you want. In addition, if none of the existing Conditions filters the data exactly as you want, you can create your own Condition statements and apply them.

      Click to learn about:
      Using Conditions
      Creating New Conditions
      Applying Conditions to Worksheets
      Grouping Multiple Conditions
      Editing and Deleting Conditions

      Using Conditions

      Conditions previously defined for a worksheet are listed on the Conditions dialog. You can turn these Conditions on and off to find the data you want to see. Turning a Condition on displays only the data that meets the Condition. Turning a Condition off restores the other data to the display.

      Turning on a Condition filters out the data you don't want to see. If you want to see all the data again, turn the Condition off.

      
      To view available Conditions:
      1. Choose Tools | Conditions or click the Condition icon on the Toolbar to see the Conditions dialog. The Conditions dialog appears.

         
         

         2. Click the drop down arrow next to the text box labeled View Conditions for to determine which Conditions you want to see.

         Figure 2-2 Choosing Conditions relating to Items

         
         
         3. Select one of the following:

            <data item>--lists Conditions that apply only to the selected data item.

            All Items--lists Conditions defined for all items in the workbook.

            Active Only--lists only the Conditions turned on for the current worksheet.

         
         To turn Conditions on and off:
         1. Choose Tools | Conditions or click the Condition tool on the Toolbar to see the Conditions dialog. The Conditions dialog lists the Conditions already defined for your workbook, and shows which are turned on or off.

         2. To turn on a Condition, click the box next to it so a checkmark appears. You can turn on more than one Condition at a time. To turn off a Condition, click a checkmarked box to remove the checkmark.

         3. Click OK. Discoverer finds the data that meets the Condition(s), and displays it.

         CAUTION: Do not click the Delete button to turn off a Condition. The Delete button permanently removes the Condition from your workbook.

         If you select two (or more) Conditions that conflict, a warning appears. For example, the two Conditions "Year = 1994" and "Year = 1995 or 1996" conflict because the first Condition removes data for all years except 1994 and the second Condition tries to display 1995 and 1996 at the same time.

         Complex conflicts may not be detected. In that case the worksheet appears with no rows of data.

         Creating New Conditions

         If none of the existing Conditions filter the data to find the specific information that you want to see, you can create your own Condition statements. This section explains how to create relatively simple Condition statements. The section, Grouping Multiple Conditions, explains how to create more complex Condition statements.

         
         To create a new Condition:
         1. Choose Tools | Conditions, or click the Conditions tool on the Toolbar to see the Conditions dialog.

         2. Click the New button to see the New Condition dialog.

            
            

         3. Type text for the following:

            What would you like to name your Condition--Type a name in the text box. To automatically generate a name for the Condition based on the data item, the Condition, and the values that you select for it, check the box Generate name automatically.

            What description would you like to give your Condition--For simple, straightforward Conditions, the name and description are usually sufficient to explain how the Condition will filter the data. However, advanced Conditions might need more detailed descriptions for clarity. Descriptions typed here appear in the Conditions dialog when the Condition is selected. If you don't enter a description, the Condition formula automatically appears as the description.

         4. Create the formula for the Condition statement using the Formula section of the dialog. You build or edit a formula by first choosing an Item and Condition and then choosing or entering the appropriate values in the Value(s) text box.

         5. Click the drop down button for Item and choose the data item for the first part of the Condition formula.

            The drop down list shows the data items in the workbook that you can use for the Condition. The list shows all the data items in the workbook, not just those data items currently being displayed on the worksheet. You can use any data item defined for the workbook to create a Condition.

            
            

            Other options on the drop-down list include Create Calculation and Select Condition, which use calculations or other Conditions to create the first part of the Condition. If you are editing an existing Condition, the option, Copy Condition, appears on the drop-down list. It is for quickly replacing an existing Condition on the Edit Condition dialog. A list of defined Condition appears and you can select the one you want. It replaces the currently selected Condition in the Edit Condition dialog.

            If you select an existing Condition as the item, the boxes for the Condition operator and value are removed because the Condition you select is already complete.You can then use the Condition to create a more advanced Condition.

   6. Click the drop down button for Operator and choose the Condition operator you want.
      
      
      7. To complete the definition of the Condition click in the Value(s) box and enter a data value.

         The drop-down list of values is a shortcut so you don't have to manually type the data value for an item. However, data values for various items might or might not appear in the list, depending on whether your Discoverer Administrator set up the workbook to show lists of values for different items. If clicking the Value(s) drop list button shows a list of data values you can then select the value you want for the item instead of manually typing it.

         For example, if the three values for the data item named Region are Central, East, and West, selecting "Region is equal to," and then clicking the drop list button displays Central, East, and West as the choices.

         The following example shows a Values drop-down list with choices for cities.

         
         

         To use the results of a calculation as the value, select Create Calculation. A dialog for creating a new calculation appears.

         To use another item as the Condition's value, choose Select Item. A list of the items in the worksheet appears and you can select an item from the list.

         If the selected item for the Condition has Parameters defined for it, you can choose the option Select Parameter from the drop-down list to select an existing Parameter for the item.

         If the Condition's Parameter definition include the option for selecting multiple Parameters, you can choose Select Multiple Values from the drop-down list. A list of data values appears and you can select the ones you want.

         You can also create new Parameters for the item by selecting New Parameter from the drop-down list. The dialog for creating new Parameters appears.

         NOTE: If you use Parameters in a Condition, the Parameter appears in the formula with a colon in front of it, such as ":myParameter." If you are using calculations, the calculation appears with an equals sign in front of it so Discoverer knows it is a calculation The Condition will then substitute the results of the calculation for the item or value where you specified a calculation. Using the equals sign you can also type a calculation directly into the Item box or Value box, such as "=Profit.SUM = Sales.SUM".

      8. If you are dealing with text and want the Condition to match the uppercase and lowercase characters in the text, click the box for Match Case. For example, if you want the Condition to filter the data to find all "Widgets" but not "widgets," click the Match Case box.

      9. Click OK. The new Condition appears in the Conditions dialog and is turned on ready to be applied to the data.

      10. Click OK in the Conditions dialog to see the data that meets the Condition.

          
          Condition Operators

          To create a Condition, you use a Condition operator to produce the statement. The following table lists the operators and some examples.

          NOTE: Put Text in Single Quotes: 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.

          Condition Operator	Meaning	Examples
          =	Equals	Region = `Central' Finds data in only the Central Region.
          <>	Not equal	Region <> `Central' Finds data in all regions except Central.
          >	Greater than	ProfitSUM > 10000 Find all data if Profit Sum is greater than 10000 Sales_Date > 01_JAN_99 Finds all sales dates after January 1, 1999.
          <	Less than	ProfitSUM < 10000 Find all data if Profit Sum is less than 10000. Sales_Date < 01_JAN_99 Finds all sales dates before January 1, 1999.
          <=	Less than or equal to	ProfitSUM <= 10000 Finds all data if Profit SUM is less than or equal to 10000. Product_Name <= `M' Finds all products with names from A to M.
          >=	Greater than or equal to	ProfitSUM >= 10000 Finds all data if Profit SUM is greater than or equal to 10000. Product_Name >= `M' Finds all products with names from M to Z.
          LIKE	Similar to (uses wildcard matching)	Name LIKE `A_' Finds all two-letter names beginning with the letter A. The underscore (_) sign matches a single character. Name LIKE `%ING' Finds all names ending with the letters ING. The percent symbol (%) matches multiple characters.
          IN	Contains one or more values	City IN (`Boston', `Los Angeles', `New York') Finds data from Boston, Los Angeles, New York.
          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') Finds all data except data that contains Boston or Chicago.
          BETWEEN	A value lies between two values	Profit BETWEEN 1000 AND 2000 Finds data that contains 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 Finds data that contains profits less than 1000 or greater than 2000.
          NOT LIKE	Not similar to	Name NOT LIKE `A_' Finds all names not beginning with A. Name NOT LIKE `%ING' Finds all names not ending in ING.
          != and ^ = and <>	Not equals	Region! = `Central' Finds all regions except Central. Note: These three operators have the same meaning because all three are used by different programming languages to mean "not equals." Pick the one you are most comfortable using.

          Grouping Multiple Conditions

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

          NOTE: There isn't a NOT operator, but you can create negated Conditions by using complementary operators.

          Examples:

          Find data from stores with profits greater than $3000 in 1995.

          The Condition statement is: Profit SUM>3000 AND Year=1995. Data from other years and from stores with less profits is not displayed.

          Find the data from all the stores in California, plus all the stores that have profits of $3000.

          The Condition statement is: State='CA' OR Profit SUM>3000. The data display shows data from all the stores in California regardless of profits, and all those from any state with profits in excess of $3000.

          
          To group multiple Conditions:
          1. In the New Condition dialog, click the Advanced button. The Advanced Conditions dialog appears.

             
             

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

          2. Create the first line of the Condition. In the example above it is Calendar Year IN 2000.

          3. Click the New Item button to add another line to the Condition statement.

             
             

          Notice the new Group column added at the left side of the dialog. The Group column indicates how the statements are grouped by the operator. By default, when you first write multiple statements they are grouped with the logical AND operator. To change the group operator to OR, NOT AND, or NOT OR, click the drop-down menu next to it.

          • Create the second line of the Condition.

            Discoverer displays the formula at the bottom of the dialog so you can verify that the statement's logical construction is correct.

          • Click OK to save the multi-statement Condition.

      NOTE: You can drag Conditions and items on the dialog. Dragging Condition A onto Condition B replaces Condition B with Condition A. You can also select Copy Condition from the Item drop-down list. In that case the values of the copied Condition replace the values of the selected Condition.

      The operators can also be "nested" to several levels to group multiple Conditions. For example, the Condition to find data from stores with costs greater than $1000 in 2000 or from stores with costs greater than $1000 in the East sales region for any year is: Cost>1000 AND (Region='East' OR Year=2000).

      
      To nest multiple Conditions:
      1. Click the column handle next to the group operator.

      2. Click the And or Or button to add another group to the Condition.

         
         

         The formula at the bottom of the dialog shows the new statement construction.

      3. Select the Item, Condition, and Value as you did for the other lines of the Condition.

         NOTE: Using the AND and OR operators can be tricky especially when grouping statements. Check the data carefully to see if the Condition produces the desired result.

         NOTE: If you delete a Condition, the Undo button becomes active, so you can restore it if you need to. Undo only works after a deletion.

      4. Click OK to save the multi-statement Condition.

      Applying Conditions to Worksheets

      When you finish creating a Condition, Discoverer checkmarks it to indicate it is ready to be applied to the data.

      
      

      Click OK to apply the Condition to the data and see the results.

      Editing and Deleting Conditions

      If a Condition statement does not find the exact data that you want, you can edit the statement. For example, if the statement finds data for 1998, but your database now includes data from 1999, you can edit the Condition to update it to find the newer data.

      Deleting a Condition removes it permanently from the workbook. However, because you can turn Conditions on and off, you may not want to delete a Condition in case you'll need it in the future.

      
      To edit a Condition:
      1. Choose Tools | Conditions, or click the Conditions tool on the Toolbar. The Conditions dialog appears.

      2. Select the Condition in the Conditions dialog.

      3. Click the Edit button. The Edit Condition dialog appears.

         NOTE: You cannot edit Conditions created by the Discoverer Administrator. If you select one of these Conditions, the Edit button changes to Show. You can click the Show button to review the Condition and see its formula, but you cannot make changes. In addition, advanced Conditions containing subqueries created in Discoverer 3.1 cannot be reviewed or edited. A message tells you that the Condition cannot be reviewed or edited. You can still turn these Conditions on and off, however, to analyze your data in the way you want.

      4. Make the changes you want to the Condition.

      5. Click OK. The Condition is now edited.

      6. To apply that edited Condition to the data, make sure it is checked on and click OK.

         
         To delete a Condition:

      1. Choose Tools | Conditions, or click the Conditions tool on the Toolbar. The Conditions dialog appears.

      2. Select the Condition you want to delete.

      3. Click the Delete button. Discoverer removes the Condition from the list.

      Creating a New Workbook

      While working with Discoverer, you may want to create additional workbooks and worksheets of your own. For example, you may want to consolidate project-specific information in a separate workbook that you share with other team members.

      If you have the appropriate access rights (granted by the Discoverer Administrator), you can create workbooks and worksheets.

      NOTE: You use the same process to create workbooks and worksheets. In fact, to create a new workbook, you create the initial worksheet for the new workbook. Thus, the steps described in this section are for both processes--building a new workbook and building a new worksheet.

      Click to learn how
      To create a new workbook or worksheet:
      To add an item to the current worksheet:
      To delete an item from the current worksheet:

      See also:
      Opening an Existing Workbook
      Editing a Worksheet
      Saving a Workbook
      Refreshing Data in a Workbook
      Deleting a Workbook

      
      To create a new workbook or worksheet:
      1. Choose either of the following:
         • Choose Sheet | New Sheet to build a new worksheet.

         • Choose File | New to create a new workbook.

      The Worksheet Wizard dialog appears. This dialog is where you select the layout--table or crosstab--that you want to use to display the data on the new worksheet.

      NOTE: The sample dialogs in the rest of this section are for creating a new worksheet. The dialogs for creating a new workbook are the same, except the dialog titles are "Create Workbook" instead of "Create Worksheet."

      2. Click the icon for the type of display for the new worksheet. As you select each type, the corresponding description is displayed as well.

         
         
         

      3. Click Next. This dialog is for selecting the data that you want on the new worksheet.

         
         

      This dialog lists the data in the business area that you can use to build the new worksheet. The following table describes the icons that you may see on the dialog.

      Table 2-1 New Worksheet Icons

      Sample Icon	Description
      	Business area--displays a business area created by the Discoverer Administrator; to select another business area for the new worksheet, click the drop-down arrow and choose from the list of business areas. A business area contains one or more folders.
      	Folders--organizes the items that you can select for your worksheets.Clicking the plus (+) and minus (-) symbol next to the folder opens and closes it.
      	Axis Item--corresponds to a column on a table or a level on a crosstab axis; axis items remain constant and have relatively few unique values, such as the names of Departments in your company, or the names of your Sales Regions. The values of an axis item are shown as a list of values.
      	Axis Item Value--one of the values of an axis item.
      	Numeric Item--represents numeric data; The values of numeric items can change as you analyze the data, for example, summing profits will produce different results for cities than for regions. Numeric items behave as Axis items on table and correspond to the data in the body of a crosstab.
      	Aggregations--the mathematical functions to aggregate the data; for text items such as Region, the typical aggregations are Count, Max, and Min. That is, you can count the number of text items, or find the highest or lowest (where A might be the highest and Z the lowest). For numeric data, the typical aggregations are Sum, Count, Max, Min, Average, and Detail. For example, you can find the Sum or Average of the numeric data with the aggregation. The aggregation in boldface type is the default. The Discoverer Administrator defines the default aggregation.
      	Condition--a filter for finding specific data. Conditions/Calculations defined by the Discoverer Administrator appear in folders, but user-defined Conditions/calculations do not.
      	Calculation--a mathematical expression to produce new data from other items. Conditions/Calculations defined by the Discoverer Administrator appear in folders, but user-defined Conditions/calculations do not.

      4. Select the business area from the drop-down menu at the top of the Available list.

      5. Click the plus (+) sign next to a folder to see all of the items in it.

         Folders containing items available for the current worksheet are active. Others are grayed out.

         Items may have plus signs next to them as well, indicating you can select values for those items as well. For example, a City item contains the names of the cities in the database. You can select a specific city to add to the worksheet. By doing this, you are implicitly creating and activating the Condition `City' = <name>.

      6. From the list of available data items, select the specific data items to add to your worksheet. Shift-click on items to select multiple items. Ctrl-click to select items not adjacent to one another. The Right Arrow button in the middle of the dialog becomes active.

      7. Click the Right Arrow button to move the available items to the Selected list. Those items are then the data items for the new worksheet. You can also drag the selected items from the Available list to the Selected list. The following example shows several items moved to the Selected list

         
         

      You can select data at various levels in the Available list. For example, selecting a folder and moving it to the Selected list, moves all the data within the folder to the list. Similarly, moving an item to the Selected list moves all values in it to the list and, ultimately, to the worksheet. For example, moving the City item to the Selected list results in the names of all the cities being on the worksheet.

      Moving a numeric item to the Selected list automatically includes its default aggregation functions. All values are automatically included as well. Selecting and moving an axis item, however, does not automatically include aggregate functions.

      To remove an item from the Selected list, click it and drag it back to the Available list, or click the Left Arrow button.

   8. Depending on the items you select from multiple folders, you may see a dialog that asks you to identify the manner in which the folders are joined. This means there are multiple ways of combining the items they contain and you may select which way will be used.

      NOTE: Contact your Discoverer Administrator to describe the specific choices you are given. Often, your Discoverer Administrator only intended one of these options to be available, and they can modify a business area to remove the extra option. For more information about join paths, see "About Multiple Join Paths".

      
      
      9. At this point, you can click Finish to create a new worksheet. Clicking Next shows the next (optional) page for adding other features to the new worksheet.

      NOTE: To change selections on the previous pages, click the Back button.

      Editing a Worksheet

      The dialogs for editing a worksheet offer the same selections and features that you use to create a worksheet or workbook.

      Click to learn how
      To edit a worksheet:
      To add an item to the current worksheet:
      To delete an item from the current worksheet:

      See also
      Changing a Worksheet's Layout
      Creating a New Workbook
      Saving a Workbook
      Refreshing Data in a Workbook
      Deleting a Workbook

      
      To edit a worksheet:
      1. Open the worksheet that you want to edit.

      2. Click the Edit Sheet icon on the toolbar, or choose Sheet | Edit Sheet.

         The Edit Sheet dialog appears.

      The tabs across the top of the dialog are for editing the various features of the worksheet. Clicking on a tab displays the options for the feature. For example, when the Select Items tab is selected, the items shown in the Selected list are the items currently in use on the worksheet. Items in the Available list can be added to the worksheet unless they are grayed out.

      
      

      The figure above is for editing a tabular worksheet. A similar dialog appears for crosstab worksheets, except Table Layout tab becomes Crosstab Layout and the dialog does not include the Sort tab. To sort crosstab data, choose Tools | Sort.

      Adding and Deleting Items on a Worksheet

      The first tab on the Edit Sheet dialog is for adding or deleting items on a worksheet. For example, if the original item on the worksheet is Region, but does not include City names, you can add an item for the cities within the regions.

      Adding a new item to a worksheet adds a column to the table or a row or column to a crosstab.

      See also:
      Editing a Worksheet
      Changing a Worksheet's Layout
      Saving a Workbook
      Refreshing Data in a Workbook
      Deleting a Workbook

      
      To add an item to the current worksheet:
      1. Click the plus (+) sign next to folders and items to see their contents.

      2. Select the item in the Available list.

      3. Click the Right Arrow button or drag the item to the Selected list.

         
         To delete an item from the current worksheet:

      1. Select the item in the Selected list.

      2. Click the Left Arrow button.

      You can also delete items from a worksheet using the Table Layout tab or the Crosstab Layout tab. Click on the item then press the Delete key.

      Changing a Worksheet's Layout

      You can rearrange and pivot the page items, axis items, and columns on a worksheet by editing the layout.

      1. Open the worksheet that you want to edit.

      2. Click the Edit Sheet icon on the toolbar, or choose Sheet | Edit Sheet.

      3. Click the Table Layout tab or Crosstab Layout tab. The layout shows the current arrangement of the items on the worksheet.

         The following examples show the Table Layout and the Crosstab Layout.

      

      4. Select one of the items on the layout.

      5. Drag the item to its new position on the layout. A black line on the top/bottom/side of an adjacent item shows where the items will be located when you release the mouse button.

      6. Release the mouse button when the item is in its new position.

      7. To delete an item from the layout, select it and click the Delete key on the keyboard.

      The following examples on the Table layout show:

      • moving the Region column to the right to become the second column on the worksheet

      • pivoting the Department item from a column to become a Page Item.

        
        

      If the worksheet contains rows with duplicate data you can hide those rows by clicking the option, Hide Duplicate Rows.

      To remove the Page Items box from the top of the worksheet, drag all items from that box to the report body, then uncheck Show Page Items.

      Formatting Text, Numbers, and Dates

      The Workbook Wizard provides the Format Panel to help you customize the way text, numbers, and dates appear in your worksheets. You can change font size, color, and alignment one column at a time or one row at a time. You can even select multiple items to format simultaneously.

      You can format the data in your worksheets, format row and column headings, and change the way an item's name displays in a worksheet. For example, you can increase the font size for a grand total row to make it more prominent. You can change the alignment of row and column headings so that they are centered or right-justified. And you can change an item's heading to something more meaningful to you; for example, you can change the heading "Profit SUM" to "Total Annual Profit".

      The formats you create using the Format Panel apply to one worksheet at a time. To set default formats for all worksheets, see "Setting Sheet Format Options" and "Setting Default Format Options".

      Click to learn how

      To change the format of worksheet data:

      To change the format of row and column headings:

      To change a heading's heading:

      See also:

      Changing a Worksheet's Layout

      
      To change the format of worksheet data:
      1. With a workbook open, click on the tab for the worksheet that you want to format.

      2. From the Sheet menu, choose Format... The Format Panel of the Workbook Wizard appears.

      

      3. In the list box on the left, click the items that you want to format. You can format one item at a time or format multiple items. The Example box shows you the item's current heading format.

      4. Click the Format Data button to change the way worksheet data appears in cells, for example, to change the font size, color, and alignment of numbers. The Format Data dialog appears.

      

      5. In the Format Data dialog, do any of the following:
         • Click the Size drop-down menu to increase or decrease the font size for data.

         • Click one or more of the Style buttons to make your data bold, italic, underlined, or strike-through.

         • Click the icons next to Text and Background to choose their colors from a color palette.

         • Click one horizontal alignment button and one vertical alignment button to change the way data is aligned within worksheet cells.

         • Click the Wrap words in cell checkbox if you want long words to be visible inside a single cell.

         • Click the Show Actual font size checkbox if you want to preview your changes in the Example box using the font size as well as the other changes that you chose above.

      6. Do one of the following:
         • If the item you are formatting contains numbers (for example, currency or percentages), you will also see a tab labelled Number on the Format Data dialog. Click the Number tab to add or remove decimal places, to show or hide a currency symbol for your country, or to create a custom number format.

           NOTE: The currency symbol displayed is determined by the Country setting. To change the currency symbol, close Discoverer, then click the Choose a Language option at the Discoverer Start Page. Then follow the screen instructions for starting Discoverer, and choose a different Country setting.

         • If the item you are formatting contains dates (for example, Year or Quarter), you will also see a tab labelled Date on the Format Data dialog. Click the Date tab to change how dates appear in your worksheet.

         • If the item you are formatting contains text (for example, Region), you will also see a tab labelled Text on the Format Data dialog. Click the Text tab to change the text's capitalization to UPPERCASE, lowercase, or Capitalized.

         

         7. Preview your changes in the Example box, and then click OK. You return to the Format Panel, where you can also format row and column headings or change the way an item's name is displayed in a worksheet.

         
         To change the format of row and column headings:
         1. With a workbook open, click on the tab for the worksheet that you want to format.

         2. From the Sheet menu, choose Format... The Format Panel of the Workbook Wizard appears.

         

         3. In the list box on the left, click the item that you want to format. You can format the heading for one item at a time or format multiple headings. The text inside the Example box shows you the item's current heading formatting.

         4. Click the Format Heading button to change the way row and column headings appear on the worksheet. For example, the change the font size, color, and alignment of headings. The Format Heading dialog appears.

         

         5. In the Format Heading dialog, do any of the following:
            • Click the Size drop-down menu to increase or decrease the font size for headings.

            • Click one or more of the Style buttons to make your headings bold, italic, underline, or strike-through.

            • Click the icons next to Text and Background to choose their colors from a color palette.

            • Click one horizontal alignment button and one vertical alignment button to change the way headings are aligned within columns or rows.

            • Click the Wrap words in cell checkbox if you want long headings to be visible inside a single cell.

            • Click the Show Actual font size checkbox if you want to preview your changes in the Example box.

         6. On the Format Heading dialog, you will also see a tab labelled Text. Click the Text tab to change the heading's capitalization to UPPERCASE, lowercase, or Capitalized.

         

         7. Preview your changes in the Example box, and then click OK. You return to the Format Panel, where you can also format worksheet data or change a item's heading.

         
         To change a heading's heading:
         1. With a workbook open, click on the tab for the worksheet that you want to format.

         2. From the Sheet menu, choose Format... The Format Panel of the Workbook Wizard appears.

         

         3. In the list box on the left, click the item that you want to edit.

         4. Click the Edit Heading button to change the way an item's name appears on the worksheet; for example, to change the heading Calendar Year to Year. The Edit Heading dialog appears.

         

         5. In the Heading text box, type a new name for this item.

         6. Click OK. You return to the Format Panel, where you can also format worksheet data and format row and column headings.

         Saving a Workbook

         Your options for saving a workbook depend on whether you have the privileges to save it to the database.

         • If you are the owner of the workbook, and you have privileges to save it to the database, you can save the workbook and any changes to it.

         • If you open a shared workbook you can use the Save As command to save the workbook under a different workbook name, if you have privileges to save workbooks to the database. However, if you don't have privileges to save workbooks to the database, you can view and edit the workbook but cannot save it. This precludes people without the proper privileges from overwriting someone else's workbook.

         See your Discoverer Administrator to find out what type of privileges you have to save workbooks.

         Click to learn how to:
         To save a workbook:
         To rename a worksheet:
         To re-order worksheets in a workbook:

         See also:
         Editing a Worksheet
         Creating a New Workbook
         Refreshing Data in a Workbook
         Deleting a Workbook

         
         To save a workbook:
         1. Choose one of the following:
            • Choose File | Save. The changes are saved and the workbook remains open.

            • To close and save a workbook at the same time, choose File | Close. If you haven't made changes to any worksheet in the workbook, it closes. If the workbook contains any unsaved changes on any worksheet, a dialog reminds you to save the changes.

            • To save the workbook under a new workbook name, choose File | Save As. The dialog appears for saving a workbook under a new name.

              
              

         2. Enter a new name in the text box.

         3. Click Save to save the changes

            Click Cancel to keep the workbook open without saving it.

            
            To rename a worksheet:
            1. Open the workbook that contains the sheet you want to rename.

            2. Do one of the following:

            • Double-click the tab at the bottom of the worksheet you want to rename.

            • From the menu, choose Sheet | Rename Sheet.
              
              The Rename Worksheet dialog appears.

            

            3. In the New name text field, type the new name for the worksheet.

            4. Click OK. The worksheet's new name appears on its tab, which is located on the bottom of the worksheet.

               
               To re-order worksheets in a workbook:

            1. Open the workbook that contains the worksheets you want to re-order.

            2. From the menu, choose Sheet | Move Sheet.
               
               The Move Worksheets dialog appears.

            

            3. Click on the name of a worksheet and then click the up arrow or down arrow. The worksheet moves up or down to a different position.

            4. Repeat step 3 for every worksheet you want to re-order.

            5. When you are finished, click OK.

            Refreshing Data in a Workbook

            Data in a workbook appears as the result of querying the database at a particular time. To refresh the data, you re-query the database. Refreshing often applies to databases receiving data from online transactions or other dynamic sources. Refreshing the data ensures that the information you are viewing is up-to-date.

            See also:
            Creating a New Workbook
            Saving a Workbook
            Deleting a Workbook

            
            To refresh the data in a worksheet:
            1. Choose Sheet | Refresh Sheet. Discoverer displays the worksheet results based on the updated data.

            Looking at a Workbook's Properties

            A workbook's properties provide basic information about the workbook.

            To see a workbook's properties:

            1. Open the workbook.

            2. Choose File | Manage Workbooks | Properties.

               The Workbook Properties dialog box appears showing information about the Workbook. You can record additional information about the workbook in the Description box.

               
               

            NOTE: Identifiers are unique names that Discoverer uses to identify EUL and Workbook elements, (e.g. Business Areas, Folders, Items etc.). The default Identifier value is generated automatically by Discoverer. Do not change Identifiers except under the following circumstances: - The Identifier must be changed to comply with strict naming conventions. - An element has been deleted and needs to be recreated with the same Identifier. If you do change an Identifier, you must update matching identifiers in other EULs to reflect the change.

            3. Click OK to close the Workbook Properties dialog box.

            Deleting a Workbook

            Deleting a workbook from the database permanently removes it. You should not delete a workbook from the database unless you are absolutely certain that you won't need it in the future.

            Note: You cannot delete a workbook unless you have the appropriate database privileges. Also, you cannot delete a shared workbook unless you created it.

            See also:
            Creating a New Workbook
            Saving a Workbook
            Refreshing Data in a Workbook

            
            To delete a workbook from the database:
            1. Choose File | Manage Workbooks | Delete. The Delete Workbook from Database dialog appears and lists the workbooks currently in the database that you own.

               
               

            2. Click the name of the workbook you want to delete, then click Delete.