Skip Headers
Oracle® Business Intelligence Discoverer Plus User's Guide
10g Release 2 (10.1.2.1)
B13915-04
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

6 Using workbooks and worksheets

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

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. Discoverer workbooks are stored in the database.

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):

Note: For more information about maximizing Discoverer performance, see "About designing workbooks for maximum performance".

Figure 6-1 A Discoverer workbook containing multiple worksheets

Description of Figure 6-1  follows
Description of "Figure 6-1 A Discoverer workbook containing multiple worksheets"

You use workbooks in the following ways:

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 6-2 A Discoverer worksheet

Description of Figure 6-2  follows
Description of "Figure 6-2 A Discoverer worksheet"

You can develop worksheets in different ways:

About Discoverer worksheet types

In Discoverer you can display data in two different ways:

About table worksheets

A table worksheet lists data in rows and columns. The figure below shows an example table worksheet analyzing profit values for cities within a region.

Figure 6-3 A table worksheet

Description of Figure 6-3  follows
Description of "Figure 6-3 A table worksheet"

About crosstab worksheets

A crosstab worksheet (short for cross-tabulated worksheet) relates two different sets of data and summarizes their interrelationship in terms of a third set of data. The figure below shows an example crosstab analyzing profit values for regions by department.

Figure 6-4 A crosstab worksheet

Description of Figure 6-4  follows
Description of "Figure 6-4 A crosstab worksheet "


Key to figure:
a. Top axis, containing the Region item.
b. Left axis, containing the Department item.
c. Data points, containing profit sum figures for each region in each department.

The region and department items are displayed as rows and columns on the crosstab. Each row and column intersection shows a data point, which in this case is the profit total for a particular region and a particular department.

About designing workbooks for maximum performance

Whether you are using Discoverer Plus Relational to perform ad hoc queries, or to create reports for other end users, you want to minimize the time it takes to run queries and reports. By following a few simple design guidelines, you can maximize Discoverer performance.

Where possible:

Notes

How to open workbooks

You open a workbook when you want to access Discoverer worksheets that you have saved previously or have been shared with you. For example, you might open a sales report that you saved previously. Or, you might open a large report that you scheduled to be processed overnight.

You can also open workbooks or scheduled workbook results that other users 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" is displayed.

    Hint: If you have already started Discoverer, choose File | Open to display the "Open Workbook from Database dialog" and skip the next step.

  2. Click the Open an existing workbook button to display the "Open Workbook from Database dialog".

    Description of openfrom.gif follows
    Description of the illustration openfrom.gif

    Workbooks are displayed with a book icon. Scheduled workbooks are displayed with a clock icon.

  3. Select the workbook that you want to analyze from the Workbooks list.

    Hint: If you want to open a scheduled workbook, click the + symbol next to the scheduled workbook to expand the list of scheduled workbook results and select a set of results.

    Description of osched2.gif follows
    Description of the illustration osched2.gif

  4. Click Open to display the selected workbook or set of results.

    Discoverer analyzes 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 estimated time for loading the first worksheet.

    Description of progress.gif follows
    Description of the illustration progress.gif

    For more information about configuration settings, see "Changing default settings".

  5. (optional) If the workbook or worksheet includes a parameter, Discoverer prompts you to enter a value with which to filter the data in the worksheet (for more information, see "How to set parameters").

    Discoverer opens the workbook and displays the first worksheet. 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

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.

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 or the worksheets contained within the workbook.

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

    Discoverer updates the workbook as you specified.

About saving a workbook to the database

You will often want to save a workbook that you have created so that you can use the workbook again. When you save a workbook using Discoverer Plus Relational, you save the workbook in the database. Having saved the workbook in the database, you can open the workbook later using Discoverer Plus Relational, Discoverer Viewer, and OracleBI Discoverer Desktop.

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:

Contact the Discoverer manager to find out whether you can save workbooks to the database.

How to save workbooks

You save a workbook when you want to store Discoverer worksheets in the database. When a workbook is saved to the database, it is available in Discoverer Plus Relational and Discoverer Viewer to all users with which it is shared (for more information, see "About sharing workbooks").

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

To save a workbook for the first time:

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

    Description of savewb.gif follows
    Description of the illustration savewb.gif

  2. Enter a new unique workbook name in the New name field.

    Hint: You can enter a name that includes upper or lower case characters, and spaces (for example Jchan Sales Analysis Workbook for March).

    Note: To avoid export issues on some platforms, do not use the colon (:) in workbook names.

  3. Click Save to save the workbook.

    The workbook is saved to the database, and is now available in Discoverer Plus Relational and Discoverer Viewer to all users with which it is shared (for more information about sharing workbooks, see "About sharing workbooks").

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. Choose 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 make a copy of a workbook:

  1. Open a workbook.

  2. Choose File | Save As.

  3. Enter a new name in the New name field.

Notes

How to copy workbooks

You copy a workbook when you want to save a previous version of a workbook. For example, you might want to make a backup copy of a sales workbook.

To copy a workbook:

  1. Open a workbook.

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

  3. Enter a new name in the New name field and click OK to save the copy of the workbook.

Discoverer saves the copy of the workbook using the name that you specified.

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 Tools | Manage Workbooks to display the "Manage Workbooks dialog".

    Description of manwb.gif follows
    Description of the illustration manwb.gif

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

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

    Note: You can only delete workbooks that you own. The Delete button is only grayed out if you do not own the currently selected workbook (for more information, see "About sharing workbooks and security").

  4. Click Yes at the confirmation dialog.

    The selected workbook is permanently removed from the database.

About worksheet titles and text

A worksheet title is the text that you display in the title area at the top of a Discoverer worksheet. Worksheet text is the text that you display in the text area at the bottom of a Discoverer worksheet. For example, you might want to display the name of the workbook owner in the title area or text area when you print a worksheet. Or you might want to display custom help text for a worksheet in the worksheet text area.

Worksheet titles and text can contain static text (e.g. text that you enter) and text variables (e.g. date and time that are updated at runtime).

The figure below shows an example of a worksheet with a worksheet title area and worksheet text. The worksheet title contains the worksheet name and the print date. The worksheet text area contains the workbook name, worksheet name, a list of worksheet items, and the name of the worksheet creator.

Figure 6-5 A Discoverer worksheet containing a worksheet title

Description of Figure 6-5  follows
Description of "Figure 6-5 A Discoverer worksheet containing a worksheet title"


Key to figure:
a. The worksheet title area.
b. Text added to the worksheet title area.
c. A text variable (i.e. &Date), which displays the current date.
d. The worksheet text area.
e. Worksheet text.

Notes

How to create or edit worksheet titles or text

You create or edit worksheet titles and text when you want to add text to the top or bottom of a worksheet. For example, you might want to display the current date at the top of a weekly report.

To create or edit a worksheet title or worksheet text:

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

  2. Display the worksheet that you want to edit.

  3. If you want to edit or create worksheet title text, choose Edit | Title to display the "Edit Title dialog" and use this dialog to specify a worksheet title, as follows:

    • Use the Font, Size, Color, and Background color buttons to format the worksheet title area.

    • Use the Title field to enter text that you want to display in the title area of the worksheet.

    • Use the Insert drop down list to add text variables (e.g. date and time that are updated at runtime).

    Description of edit_t.gif follows
    Description of the illustration edit_t.gif

    Hint: You can also double-click on the worksheet title area (if displayed) to display the "Edit Title dialog". For more information, see "How to display or hide worksheet titles or text".

  4. If you want to edit or create worksheet text, choose Edit | Text Area to display the "Edit Text Area dialog" and use this dialog to specify worksheet text, as follows:

    • Use the Font, Size, Color, and Background color buttons to format text in the worksheet text area.

    • Use the Text field to enter text that you want to display in the text area on the worksheet.

    • Use the Insert drop down list to add text variables (e.g. date and time that are updated at runtime).

    Description of edit_wt.gif follows
    Description of the illustration edit_wt.gif

    Hint: You can also double-click on the worksheet text area (if displayed) to display the "Edit Text Area dialog". For more information, see "How to display or hide worksheet titles or text".

  5. Click OK to save changes that you make.

    The worksheet is updated with changes that you specified.

How to display or hide worksheet titles or text

You can display or hide a worksheet title or worksheet text when the worksheet is displayed, printed, or exported. For example, you might want to hide the current date and current time in the title when you are designing a worksheet but display the current date and current time in the title when a Discoverer end user prints the worksheet.

To display or hide a worksheet title or text area:

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

  2. Display the worksheet that you want to edit.

  3. Choose the View menu to display a list of check boxes.

  4. Use the Title check box to specify whether the worksheet title area is displayed at the top of the worksheet when the worksheet is displayed, printed, or exported, as follows:

    • Select the Title check box to display the worksheet title area.

    • Clear the Title check box to hide the worksheet title area.

  5. Use the Text area check box to specify whether the worksheet text area is displayed at the bottom of the worksheet when the worksheet is displayed, printed, or exported, as follows:

    • Select the Text area check box to display the worksheet text area.

    • Clear the Text area check box to hide the worksheet text area.

The worksheet is updated with the changes that you specified.

Notes

How to rename worksheets and workbooks

You rename a workbook to change its name used in Discoverer. For example, you might want to create a more meaningful workbook name by changing a workbook name from My Workbook to Sales Reports.

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 (e.g. Sheet 2) to something more meaningful (e.g. Sales Report).

To rename a workbook:

  1. If it is not already open, open the workbook that you want to rename (for more information, see "How to open workbooks").

  2. Choose File | Save As to display the "Save Workbook to Database dialog" and enter a new name in the New Name field.

    Note: Do not include a colon (:) character.

  3. Click Save to save the workbook.

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

    The workbook's new name is displayed at the top of the Discoverer work area.

To rename a worksheet:

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

  2. Choose Edit | Worksheet Properties to display the "Worksheet Properties dialog: General tab".

    Note: You can also display the worksheet properties dialog by double-clicking the worksheet name tab at the bottom of the worksheet.

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

  4. Click OK to rename the worksheet.

    The worksheet's new name is displayed 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 Discoverer displays worksheets in a workbook. For example, you might want to put the most commonly used worksheet at the front of a workbook to access information more easily.

To re-order a worksheet in a workbook:

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

  2. Choose Edit | Move Worksheet... to display the "Move Worksheets dialog".

    Description of movewkst.gif follows
    Description of the illustration movewkst.gif

  3. To move the 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. 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 Tools | Refresh Sheet.

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

How to delete worksheets

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

To delete a worksheet

  1. Open the workbook containing the worksheet.

  2. Display the worksheet that you want to delete.

  3. Choose Edit | Delete Worksheet.

    Discoverer removes the worksheet from the workbook.

How to find data in a worksheet

If you are working with a large worksheet, you can use Discoverer's search facility to find data that matches a search term you specify. For example, to find cell values that contain New York.

To find data in a worksheet:

  1. Display the worksheet that you want to search.

  2. Choose Edit | Find to display the "Find dialog (in Worksheet)".

    Description of find.gif follows
    Description of the illustration find.gif

  3. Use the Search in and Search by drop down lists to specify search.

  4. In the Search for field, enter the search text that you want to find.

  5. Click Find Previous or Find Next.

    Note: The search starts from the currently highlighted worksheet cell (if a worksheet cell is highlighted) or the start of the worksheet.

    If the worksheet contains the search text that you specified, Discoverer highlights the first occurrence of this search term in the worksheet.

    Description of fiw.gif follows
    Description of the illustration fiw.gif

    If the worksheet does not contain the search text that you specified, Discoverer displays a 'No match found' dialog.

  6. (optional) Use the Find Previous and Find Next buttons to search for other occurrences of the search term.

    When the search reaches the end of the worksheet, Discoverer goes back to the start of the worksheet and resumes the search.

  7. Click Close to close the "Find dialog (in Worksheet)".

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.

Note: For more information about maximizing Discoverer performance, see "About designing workbooks for maximum performance".

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.

How to create new workbooks

While working with Discoverer, you might want to create a new workbook containing one or more worksheets. For example, you might want to distribute a new sales report 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" is displayed.

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

  2. Click Create a new workbook to display the "Edit Worksheet dialog: Worksheet Layout tab", which enables you to select the worksheet elements (e.g. title area, graph) to display on the first worksheet.

    Description of ww4.gif follows
    Description of the illustration ww4.gif

  3. Select the worksheet elements (e.g. title area, graph) to display on the first worksheet.

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

    Description of ww_2.gif follows
    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: If you cannot see the business area that you want to analyze, make sure that you selected the correct connection when you started Discoverer Plus Relational. If necessary, exit Discoverer Plus Relational and re-start the Discoverer Plus Relational using a different connection that has access to the business area you want to analyze.

    Note: Click the Find button to the right of the List field to display the "Find dialog (in Item Navigator)", where you search the business area for folders and items that you want to add to the worksheet. 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 than 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 Worksheet Wizard to start analyzing the worksheet data.

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

  8. Click Next to display the "Edit Worksheet dialog: Sort tab", where you can change the default sort order of worksheet items.

  9. Click Next to display the "Edit Worksheet dialog: Parameters tab", where you can add parameters to the worksheet that prompt Discoverer end users to enter dynamic values to customize the worksheet.

  10. Click Finish to save the layout and close the worksheet wizard.

    Discoverer displays the new worksheet. Now you are ready to begin analyzing data using Discoverer!

  11. (optional) You can add new items to the worksheet as required. For example:

    Now that you have created a worksheet, you might want to do one or more of the following:

Notes

Figure 6-6 Join Folders dialog

Description of Figure 6-6  follows
Description of "Figure 6-6 Join Folders dialog"

How to add 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 sales workbook to which you want to add a new worksheet on sales for a particular region.

You can add a worksheet in two ways:

To create a completely new worksheet:

  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 Edit | Add Worksheet... to display the Worksheet Wizard.

  3. Follow the instructions on the Worksheet Wizard.

The new worksheet is added to the workbook.

How to view and update a workbook's properties

You update a workbook's properties to store additional information about the workbook. For example, you might want to store information about what the workbook should be used for to help potential users of the workbook. You can only update the properties of a workbook that you own and currently have open.

You might also want to view a workbook's properties to find out information about the workbook. For example, you might want to find out the name of the workbook owner or the date that the workbook was created.

To update a workbook's properties:

  1. Open the workbook.

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

    Description of props2.gif follows
    Description of the illustration props2.gif

  3. In the Description field, type additional information about the workbook.

    The description that you type is displayed to Discoverer users when they open workbooks in Discoverer Plus Relational and Discoverer Viewer.

  4. Click OK to save any changes and close the Workbook Properties dialog box.

To view the properties of a workbook:

  1. Choose Tools | Manage Workbooks to display the "Manage Workbooks dialog".

  2. Select a workbook in the Workbooks list.

  3. Click Properties to display the "Workbook Properties dialog".

To view the properties of a workbook when you open a workbook:

  1. Choose Tools | Open to display the "Open Workbook from Database dialog".

  2. Right-click on a workbook in the Workbooks list to display a right-click menu.

  3. Choose Properties in the right-click menu to display the "Workbook Properties dialog".

Notes

About worksheet properties

Discoverer's worksheet properties determine the appearance and behavior of a worksheet. You can change worksheet properties to suit your requirements. For example, you might want a table worksheet to display headings and row numbers. Or you might want to specify how a null value is displayed.

When you change worksheet properties, the changes take effect immediately. For more information about editing worksheet properties, see "How to view and edit worksheet properties".

Note that when you change a worksheet's properties, you are only changing the properties of the current worksheet. For more information about the default properties that apply to new worksheets, see "Changing default settings".

Hint: In addition to opening the Worksheet Properties dialog from the Edit menu, you can also use the Properties button in the Edit Worksheet dialog tabs.

How to view and edit worksheet properties

You edit a worksheet's properties to change the appearance and behavior of the worksheet. For example, you might want to:

To view and edit worksheet properties:

  1. If it is not already open, open the workbook that contains the worksheet you want to view or edit and display the worksheet.

  2. Choose Edit | Worksheet Properties to display the "Worksheet Properties dialog: General tab".

    Description of d_wsprnm.gif follows
    Description of the illustration d_wsprnm.gif

  3. Display the appropriate tab for the properties that you want to edit as shown below.

    Tab Use to
    "Worksheet Properties dialog: General tab"
    Specify the worksheet name and description, and view the worksheet identifier.
    "Worksheet Properties dialog: Sheet/Crosstab Format tab"
    Specify how Discoverer displays the current worksheet (e.g. with a text area, null values, grid lines, row numbers).
    "Worksheet Properties dialog: Aggregation tab"
    Specify how Discoverer displays aggregated values for the worksheet (for more information, see "What are aggregated values in Discoverer").

  4. Click OK to save the changes that you have made and close the Worksheet Properties dialog.

The changes to worksheet properties that you have made take effect immediately.