4
Using workbooks and worksheets

This chapter explains how to use Discoverer workbooks and worksheets, and contains the following topics:

• "What are workbooks?"

• "What are worksheets?"

• "How to open workbooks"

• "How to open a scheduled workbook"

• "How to edit workbooks"

• "About saving workbooks to the database"

• "How to save workbooks"

• "How to rename workbooks and worksheets"

• "How to re-order worksheets within a workbook"

• "How to refresh worksheets"

• "How to delete workbooks from the database"

• "About creating new workbooks"

• "About icons in the Workbook Wizard"

• "How to create new workbooks"

• "How to add new worksheets to a workbook"

• "How to view a workbook's properties"

What are workbooks?

Workbooks are Discoverer files that contain worksheets displaying data retrieved from the database.

If you are familiar with spreadsheet applications (e.g. Microsoft Excel), think of a workbook as a spreadsheet file.

Workbooks typically contain data that is related in some way but organized to show different perspectives. For example, you might want to analyze different aspects of sales performance and create different worksheets for each aspect. For example (see figure below):

• a worksheet called Rank of Sales to calculate a league table of cities based on sales performance

• a worksheet called Top N/Bottom N to calculate the best sales performers and worst sales performers

• a worksheet called Moving Average to calculate a rolling three-month average for sales figures

  Figure 4-1 A Discoverer workbook containing multiple worksheets

  
  
  Text description of the illustration wbfull2.gif

You use workbooks in the following ways:

• You can create your own workbooks that you can subsequently share with other Discoverer users. Or, you can use workbooks created by other Discoverer users or by the Discoverer manager. For more information about sharing workbooks, see "Sharing workbooks".

• You can include parameters to filter the workbook each time it is opened or refreshed. Including parameters enables workbook users to filter out data that they are not interested in and go directly to the data that they want to analyze. For more information about using parameters, see "Using parameters".

• You can save workbooks to the database using Discoverer Plus, and open workbooks from the database. You can also open scheduled workbooks (for more information about scheduled workbooks, see "Using scheduled workbooks").

What are worksheets?

Worksheets contain the data that you want to analyze, together with a number of Discoverer components to help you analyze the data. For example, a worksheet can contain parameters, totals, percentages, exceptions, and calculations.

You create a worksheet in a workbook (for more information, see "What are workbooks?").

If you are familiar with spreadsheet applications (e.g. Microsoft Excel), think of a workbook as a spreadsheet file and worksheets as different sheets in that spreadsheet file.

In the figure below, a Discoverer worksheet called Tabular Layout contains information about profits made by two departments across three regions in the year 2000.

Figure 4-2 A Discoverer worksheet

Text description of the illustration ws.gif

You can develop worksheets in different ways:

• you can create your own worksheets, which you can subsequently share with other Discoverer users.

• you can use worksheets created by other Discoverer users or by the Discoverer manager

• you can include parameters in a worksheet to filter the worksheet each time it is opened or refreshed

How to open workbooks

You can open a workbook that you have saved in the database. You can also open workbooks that other users have saved in the database and have shared with you.

To open a workbook:

1. Start Discoverer (for more information, see "About starting Discoverer").

   When you start Discoverer, the "Workbook Wizard: Create/Open Workbook dialog (Open)" is displayed.

   Hint: If you have already started Discoverer, do the following:

   1. Choose File | Open to display the Open Workbook dialog.

   2. Select the Database radio button.

   3. Click Open to display the "Open Workbook from Database dialog".

   4. Move to Step 4.

2. Click the Open an existing workbook button.

   The extra options Database and Scheduled are displayed.

3. Click the Database button to display the "Open Workbook from Database dialog".

   
   
   Text description of the illustration openfrom.gif

4. Select the workbook that you want to analyze from the list of workbooks.

5. Click Open to display the workbook.

   Discoverer first evaluates the workbook to determine how long it will take to open the first worksheet. Depending on how Discoverer is configured, a progress dialog shows you the time estimate for loading the first sheet.

   
   
   Text description of the illustration progress.gif

   For more information about configuring Discoverer, see "Options dialog: Query Governor tab".

6. If the workbook or worksheet includes a parameter, you are prompted to enter a value with which to filter the data in the worksheet (see "Using parameters").

   The workbook and the first worksheet are displayed. If the workbook contains more than one worksheet, the worksheets are displayed as tabs along the bottom of the Discoverer window.

   Now you are ready to begin analyzing data using Discoverer!

Notes:

• If the time estimate for opening a workbook exceeds the value you set in the Query Governor options (see "Options dialog: Query Governor tab"), a dialog opens to alert you and gives you two options:
  • Click Yes to open the workbook, regardless of the time estimate. If the query estimate is more than a few minutes, you can do other work while Discoverer gets the data for the worksheet.

  • Click No to open the workbook, but to show no data in the first worksheet. You can run the query at another time

  For example, you might click No if the data that you want is not in the first worksheet, but is in another worksheet. Click that other worksheet's tab at the bottom of the window.

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

How to open a scheduled workbook

You can open a workbook that you have scheduled in the database. You can also open workbooks that other users have scheduled in the database and have shared with you (for more information, see "What are scheduled workbooks?").

To open a scheduled workbook:

1. Start Discoverer (for more information, see "About starting Discoverer").

   When you start Discoverer, the "Workbook Wizard: Create/Open Workbook dialog (Open)" is displayed.

   Hint: If you have already started Discoverer, do the following:

   1. Choose File | Open to display the Open Workbook dialog.

   2. Select the Scheduled radio button.

   3. Click Open to display the "Open Scheduled Workbook dialog".

   4. Move to Step 4.

2. Click the Open an existing workbook button.

   The extra options Database and Scheduled are displayed.

3. Click the Scheduled button to display the "Open Scheduled Workbook dialog".

   
   
   Text description of the illustration opensche.gif

4. Select the workbook that you want to analyze from the list of workbooks.

5. Click Open to display the workbook.

   Now you are ready to begin analyzing data using Discoverer!

Notes:

• If you are already connected to Discoverer, you can also use the "Scheduling Manager dialog" to open scheduled workbooks, as follows:
  1. Choose File | Manage Workbooks | Scheduling Manager to display the "Scheduling Manager dialog".

  2. In the Scheduled Workbooks list, click + next to a scheduled workbook to expand the list and display sets of results for that scheduled workbook.

  3. Select a set of results from the expanded list of scheduled workbook results.

  4. Click Open to open the selected worksheet.

• 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 might contain different results from the original scheduled workbook that was created.

  For more information about using Discoverer as an Oracle Applications user, see "How to start Discoverer in Oracle Applications mode using an existing connection".

How to edit workbooks

You edit a workbook when you want to update the workbook's worksheets, or change the workbook's properties. For example, you might want to add or remove worksheets, or share the workbook with other Discoverer users.

To edit a workbook:

1. Open the workbook that you want to edit from the database (for more information, "How to open workbooks").

2. Make changes to the workbook properties or the worksheets contained within the workbook.

3. Choose File | Save to save the workbook to the database (for more information, "About saving workbooks to the database").

   Discoverer updates the workbook as you specified.

About saving workbooks to the database

Before you can save a workbook to the database, the Discoverer manager must have given you permission to do so. The Discoverer manager gives you permission to save workbooks to the database by granting you a Discoverer privilege. Provided that you have this privilege, you can:

• save changes to the workbooks that you have created

• open a shared workbook and save the workbook as your own private copy of that workbook

Contact the Discoverer manager to find out what type of privileges you have to save workbooks (for more information about giving access privileges to other Discoverer users, see "About sharing workbooks").

How to save workbooks

When you save a workbook, you save it in the database. Before you can save it to the database, the Discoverer manager must have given you permission to do so. For more information about saving workbooks to the database, see "About saving workbooks to the database".

To save changes to a workbook and keep the workbook open:

1. Choose File | Save.

   The changes are saved and the workbook remains open.

To save changes to a workbook and close the workbook:

1. File | Close.

   If you have not made changes to any worksheet in the workbook, the workbook closes. If the workbook contains unsaved changes on any worksheet, a prompt reminds you to save the changes.

To save the workbook under a new workbook name and keep the workbook open:

1. Choose File | Save As to display the "Save Workbook to Database dialog".

   
   
   Text description of the illustration savewb.gif

2. Enter a new name in the text box.

   You can enter a name that:

   • has a maximum length of approximately 45 characters (depending on the language in which you are running Discoverer)

   • includes upper or lower case characters, and spaces (for example Jchan Sales Workbook March 2002)

3. Click Save to save the workbook.

   Changes to the workbook made since the workbook was last saved are saved to the database under the new workbook name. The worksheet remains open. The original workbook remains in the database.

How to rename workbooks and worksheets

You rename a worksheet when you want to change the name displayed in the worksheet tab. For example, you might want to change the default worksheet name created by Discoverer to something more meaningful.

To rename a workbook:

1. Save the workbook with a new name (for more information, see "How to save workbooks").

2. (optional) Delete the original workbook (for more information, see "How to delete workbooks from the database").

To rename a worksheet:

1. If it is not already open, open the workbook that contains the worksheet you want to rename.

2. Choose Sheet | Rename Sheet to display the "Rename Worksheet dialog".

   
   
   Text description of the illustration namewkst.gif

3. Enter the new name for the worksheet in the New name field.

4. Click OK to rename the worksheet.

The worksheet's new name appears on the worksheet's tab at the bottom of the worksheet.

How to re-order worksheets within a workbook

You re-order worksheets within a workbook when you want to change the order that worksheets appear in the workbook. For example, you might want to put the most commonly used worksheet at the front of a workbook to access information more easily.

How to re-order worksheets in a workbook:

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

2. Choose Sheet | Move Sheets to display the "Move Worksheets dialog".

   
   
   Text description of the illustration movewkst.gif

3. To move a worksheet:
   1. Click on the name of the worksheet that you want to move.

   2. Click the up arrow or down arrow as required to move the worksheet to a different position in the workbook.

4. Repeat the previous step for each worksheet that you want to move.

5. When you have finished, click OK to save the details.

The worksheets are now arranged in the order that you specified.

How to refresh worksheets

Data in a workbook appears as the result of querying the database at a particular time. If you have had a workbook open for a while, the data in the workbook might be out-of-date. You refresh a worksheet when you want to make sure that you are accessing the most recent information. When you refresh a worksheet, Discoverer re-queries the database.

1. Open the worksheet that you want to refresh.

2. Choose Sheet | Refresh Sheet.

Discoverer displays up-to-date data in the worksheet.

How to delete workbooks from the database

You delete a workbook when you no longer want to use the workbook, and want to remove it permanently from the database. Before deleting old workbooks, make sure that no other Discoverer users want to use the workbook in the future.

To delete a workbook from the database:

1. Choose File | Manage Workbooks | Delete to display the "Delete Workbooks from Database dialog".

   
   
   Text description of the illustration wb_del.gif

2. Select the workbook that you want to delete from the list of workbooks.

3. Click Delete to delete the workbook and close the dialog.

4. Click Yes at the confirmation dialog.

The selected workbook is permanently removed from the database.

Notes:

• If you want to delete a workbook that you currently have open, chose File | Manage Workbooks | Delete and select the current workbook from the workbook list. Then, close the currently opened workbook without saving it.

About creating new workbooks

In some organizations, the Discoverer manager or an experienced Discoverer user creates the workbooks required by Discoverer users. In other organizations, individual users create their own workbooks.

Typically, you create a new workbook when you want to analyze data in a new way. For example, you might want to create a performance analysis workbook that you will make available to all sales analysts in a marketing department.

Before you can create a new workbook, the Discoverer manager must have given you permission to do so. The Discoverer manager gives you permission to create a new workbook by granting you a Discoverer privilege.

About icons in the Workbook Wizard

You use the Workbook Wizard to guide you when you create worksheets in a workbook. Discoverer uses the following icons in the Workbook Wizard and Worksheet Wizard:

Table 4-1 Workbook Wizard and Worksheet Wizard icons

Icon	Description
Text description of the illustration icon_ba.gif	Business area - a business area created by the Discoverer manager. To select another business area for the new worksheet, click the drop-down arrow and select from the list of business areas. A business area contains one or more folders.
Text description of the illustration icon_fol.gif	Folder - a related set of items that you can select for your worksheets. Click the plus (+) and minus (-) symbol next to the folder to open and close the folder.
Text description of the illustration icon_ax.gif	Axis Item - a column on a table or a level on a crosstab axis. Axis items remain constant and have relatively few unique values (e.g. the names of departments in your organization, the names of your Sales Regions). The values of an axis item are shown as a list of values (LOV).
Text description of the illustration icon_axi.gif	Item Value - an item in a list of values (LOV).
Text description of the illustration icon_num.gif	Numeric Item - a numeric item. The values of numeric items can change as you analyze the data (e.g. summing profits will produce different results for cities than for regions). Numeric items behave as axis items on table worksheets and correspond to the data in the body of a crosstab.
Text description of the illustration icon_agg.gif	Aggregation - a mathematical function for aggregating data. For text items (e.g. Region), the typical aggregations are Count, Max, and Min. For example, you can count the number of Regions items, or find the highest or lowest (where A might be the highest and Z the lowest). For numeric items, typical aggregations are Sum, Count, Max, Min, Average, and Detail. For example, you can find the Sum or Average of the numeric data. The default aggregation (specified by the Discoverer manager) is displayed in bold.
Text description of the illustration icon_con.gif	Condition - a filter for finding specific data. Note: Conditions defined by the Discoverer manager appear in folders. User-defined conditions do not appear in folders.
Text description of the illustration icon_cal.gif	Calculation - a mathematical expression to produce new data from other items. Note: Calculations defined by the Discoverer manager appear in folders. User-defined calculations do not appear in folders.
Text description of the illustration torcha.gif	Find - use this icon to display the "Find dialog (in Item Navigator)", which enables you to quickly locate items in business areas.

How to create new workbooks

While working with Discoverer, you might want to create a new workbook and one or more worksheets. You can then use the new workbook yourself and distribute it to other Discoverer users.

When you create a new workbook, Discoverer immediately prompts you to create a new worksheet to go into the workbook.

To create a new workbook:

1. Start Discoverer (for more information, see "About starting Discoverer").

   When you start Discoverer, the "Workbook Wizard: Create/Open Workbook dialog (Open)" is displayed.

   Hint: If you have already started Discoverer, choose File | New to display the "Workbook Wizard: Create/Open Workbook dialog (New)" and skip the next step.

2. Click Create a new workbook.

   The extra options How do you want to display information? are displayed, which enable you to specify a worksheet style for the first worksheet in the new workbook (e.g. table, crosstab).

   
   
   Text description of the illustration ww_1.gif

3. Select a worksheet style from the How do you want to display the information radio button.

   For more information about worksheet layouts available, see "About the four types of Discoverer worksheet".

4. Click Next to display the Select Items page of the Workbook Wizard.

   
   
   Text description of the illustration ww_2.gif

   You use the Select Items page to select what data to display on the worksheet.

5. Select the business area that you want to use from the drop down list at the top of the Available box.

   The folders and items in the business area you selected appear beneath the business area name.

   Hint: Click the torch icon ( ) above the Available box to display the "Find dialog (in Item Navigator)", where you search the EUL for items that you want to add to the worksheet.

   Hint: Folders containing items available to the current worksheet are active. Items not available to the current worksheet are grayed out.

6. Move the folders and items that you want to include in the new worksheet from the Available list to the Selected list.

   Hints:

   • Click the plus (+) sign next to a folder to items within the folder.

   • You can select more that one folder or item at a time by pressing the Ctrl key and clicking another folder or item.

   • If you select a folder, you select all items in that folder.

   • Click the plus (+) sign next to items to expand items. For example, you might expand a Region to display the values North, East, and West. If you select North to display data for that region in the worksheet, you create a condition `Region = North' (for more information about conditions, see "Using conditions").

   • Selecting a numeric item automatically includes its default aggregates (e.g. Sum, Count, Max). You can also expand a numeric item list and select individual aggregates.

   Having specified the folders and items to include in the worksheet, you can add other features to the worksheet or close the Workbook Wizard to start analyzing the worksheet data.

7. Click Next to display the "Edit Worksheet dialog: Table Layout tab", where you can change the default position of worksheet items.

8. Click Next to display the "Edit Worksheet dialog: Format tab", where you can change the default format of worksheet items (e.g. text color, text style, number format).

9. (optional) Use the Next button to navigate through the remaining pages of the Workbook Wizard to add other features to the worksheet, including:
   • parameters (for more information, see "Using parameters")

   • sorts (for more information, see "Sorting data")

   • totals (for more information, see "Using totals")

   • percentages (for more information, see "Using percentages")

   • conditions (for more information, see "Using conditions")

   • calculations (for more information, see "Using calculations")

   Hint: You can change selections you made on previous pages by clicking the Back button.

10. Click Finish to create the new worksheet using default values for any options that you did not specify.

    Now you are ready to begin analyzing data using Discoverer!

Notes:

• After creating a worksheet, you can always change default options later by choosing Sheet | Edit Sheet to display the "Edit Worksheet dialog". Here, you can change the worksheet layout and display formats, and add analysis items (e.g. parameters, conditions, calculations).

• If you select items from different folders (or multiple folders), you might be prompted by the Join Folders dialog to specify how to join folders. This means that information in folders can be associated in more than one way. For example, a product key item in a Products folder might be associated with a product key item in a Sales folder. The Products folder and Sales folder might also both contain an item called Location, which is an alternative item on which to associate the two folders.

  Contact the Discoverer manager for more information about multiple join paths, or see "About multiple join paths".

  In the figure below, the Join Folders dialog is displayed when Discoverer detects a multiple join path. In this instance, the Store item can be associated with the Sales Fact folder or the Sales Facts folder. The Please select the join or joins that you want to use list contains an entry for each of these options.

  Figure 4-3 Join Folders dialog

  
  
  Text description of the illustration ww_2b.gif

How to add new worksheets to a workbook

You add a worksheet to a workbook when you want to analyze data in a new way. For example, you might have a web traffic analysis workbook to which you want to add a new worksheet on click-stream analysis.

You can add a worksheet in two ways:

• by taking a copy of an existing worksheet in the current workbook and modifying the copy to meet your needs (see "How to duplicate a worksheet")

• by creating a new worksheet from the beginning using the Discoverer Worksheet Wizard to guide you through the process (as described below)

To create a new worksheet from the beginning:

1. Start Discoverer and open the workbook to which you want to add a worksheet (for more information, see "How to open workbooks").

2. Choose Sheet | New Sheet to display the Worksheet Wizard.

3. Follow the instructions on the Worksheet Wizard.

The new worksheet is added to the workbook.

How to view a workbook's properties

A workbook's properties provide basic information about the workbook. For example, you might want to find out the name of the workbook owner. Or, you might want to find out the date that the workbook was revised so that you can compare it with another workbook.

To view a workbook's properties:

1. Open the workbook.

2. Choose File | Manage Workbooks | Properties to display the "Workbook Properties dialog".

   The "Workbook Properties dialog" displays information about the workbook. You can enter additional information about the workbook in the Description box.

   
   
   Text description of the illustration props.gif
3. Click OK to save any changes and close the Workbook Properties dialog box.

Notes:

• Identifiers are unique names that Discoverer uses to identify EUL and workbook elements (e.g. Business Areas, Folders, Items). Discoverer generates a default identifier automatically. Typically, you will never have to change the default identifier. However, you (or the Discoverer manager) might want to modify identifiers in the following circumstances:
  • if your organization has strict naming conventions with which identifiers must comply

  • if an element has been deleted and has 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.

About opening workbooks in a non-Oracle database

If your organization uses non-Oracle databases, the Discoverer manager 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 of the data access and analysis features available with Oracle databases. For more information, contact the Discoverer manager.