21 Advanced Discoverer Plus Relational Features

This chapter explains how to use some of Discoverer Plus Relational's advanced features, and includes the following topics:

• "Using scheduled workbooks"

• "Using lists of values (LOVs)"

• "Changing default settings"

• "Using SQL"

21.1 Using scheduled workbooks

This section describes how to improve productivity using Discoverer's scheduling facilities, and includes the following topics:

• "What are scheduled workbooks?"

• "When to use scheduled workbooks"

• "An example of using a scheduled workbook"

• "About accessing scheduled workbook results sets"

• "About how scheduled workbooks are processed"

• "About scheduled workbooks and aggregation"

• "How to schedule workbooks"

• "How to edit scheduled workbooks"

• "How to copy a scheduled workbook"

• "How to unschedule a scheduled workbook"

• "How to delete scheduled workbooks and scheduled workbook results sets"

21.1.1 What are scheduled workbooks?

A scheduled workbook is a read-only copy of a workbook that Discoverer processes at a particular time and frequency. For example, you might want to process worksheets in a workbook called Monthly Sales Report at midnight on the first day of each month.

You typically use scheduled workbooks to produce regular reports or to produce complex reports that must be processed at off-peak times.

Each time a scheduled workbook is processed (or run), Discoverer produces a scheduled workbook results set. A scheduled workbook results set is a version (or snapshot) of the source workbook made at a particular time. You can schedule a whole workbook or particular worksheets in a workbook. For example, if you choose to process Worksheet A and Worksheet B in a workbook, the scheduled workbook results set contains a copy of worksheet A and a copy of worksheet B.

In the example below, Discoverer processes the Sales Report workbook once per month, which produces a set of scheduled workbook results each month.

Figure 21-1 Scheduling a workbook in Discoverer

When you create a scheduled workbook, Discoverer makes a copy of the workbook that you schedule and processes this copy. If you update the original workbook, you must create a scheduled workbook to implement the updates. For example, you create a scheduled workbook to process Workbook 1 once per month. Discoverer takes a copy of Workbook 1 and processes it once per month. If you update Workbook 1 (for example, add an item to a worksheet) you must create a scheduled workbook to process Workbook 1.

Once Discoverer processes a workbook, you can use the scheduled workbook results sets produced just like an ordinary Discoverer workbook. For example, you can:

• open scheduled workbook results sets in Discoverer Plus Relational and Discoverer Viewer

• print and export scheduled workbook results sets

• save scheduled workbook results sets in the database with a new workbook name

• share scheduled workbook results sets with other Discoverer users

  Note: If you share scheduled workbook results sets with other Discoverer users, each user can see all of the data in the results set, regardless of the database access privileges they have been granted. For more information about sharing and security, see "About sharing workbooks and security".

You manage scheduled workbooks using the Scheduling Manager (choose File | Scheduling Manager to display the "Scheduling Manager dialog").

You do not have to be running Discoverer for Discoverer to process scheduled workbooks. For more information about how workbooks are processed, see "About how scheduled workbooks are processed".

Notes

• You must have the required privileges to schedule workbooks. Contact the Discoverer manager for more details.

• If you share a workbook with a Discoverer user and then create a scheduled workbook based on that workbook, you must explicitly share the scheduled workbook with that user. For example, you create a workbook called Workbook 1 and share Workbook 1 with Discoverer user A. If you create a scheduled workbook based on Workbook 1 that you want Discoverer user A to access, you must share the scheduled workbook with Discoverer user A.

21.1.2 When to use scheduled workbooks

Typically you schedule workbooks in any of the following circumstances:

• If a workbook takes a long time to process. Scheduling a workbook to be processed at off-peak times avoids overburdening the server during peak times.

• To archive or produce a snapshot of data at specific points in time.

• If you process a large or complex workbook or worksheet that exceeds the time limit set by the Discoverer manager. When this happens, a schedule option is displayed informing you that the worksheet query time exceeds the time limit. You can then click the Schedule button and schedule the workbook to process the workbook at off-peak times that are not restricted by the time limit.

• To process a workbook at regular intervals (for example, a weekly report of sales figures).

• If the Discoverer manager has specified that you can only open scheduled workbooks results sets (that is, you are not allowed to open ordinary workbooks).

• To share workbooks with Discoverer users without those Discoverer users having to have explicit database access privileges to the data. For more information about sharing and security, see "About sharing workbooks and security".

21.1.3 An example of using a scheduled workbook

In this example, you want to process a workbook called Sales Report once per week at 1.00 A.M. You want to keep all scheduled workbook results sets so that Discoverer users can access reports for any week.

In the Schedule Wizard, you specify the following:

• In the When do you want to schedule this workbook? field, you specify the date on which you want to process the workbook for the first time. In the time area of the field, you specify 1.00 AM.

• In the How often do you want to repeat this schedule? field, you specify 1 and Weekly in the Repeat every fields to specify once per week.

• In the Do you want to keep all versions of results? field, you select the Yes, keep all results check box.

The figure below shows how you use the Schedule Wizard to schedule a workbook to be processed once per week at 1.00 A.M, and keep all scheduled workbook results.

Figure 21-2 Scheduling a workbook in Discoverer

The figure below shows how to use the "Open Workbook from Database dialog" dialog to open a scheduled workbook results set.

Figure 21-3 Opening scheduled workbooks

21.1.4 About accessing scheduled workbook results sets

Scheduled workbook results sets can be opened in Discoverer Plus Relational or Discoverer Viewer. You can open scheduled workbook results sets at any time when connected to Discoverer.

Hint: When you connect to Discoverer, Discoverer can inform you when scheduled workbook results sets have been processed and are ready to open. To enable this automatic alert, ensure that the Don't show the new results window after initial connection check box is cleared on the "Options dialog: General tab".

21.1.5 About how scheduled workbooks are processed

Discoverer processes scheduled workbooks as follows:

• Once workbooks are scheduled, the workbook processing is done automatically in the database.

• You do not need to have Discoverer Plus Relational running or connected to process scheduled workbooks.

• Scheduled workbook results sets are saved in the database and are available when you start Discoverer.

21.1.6 About scheduled workbooks and aggregation

Because Discoverer does not support Enhanced Data Aggregation for scheduled workbooks, be aware of the following issues when using scheduled workbooks and aggregated data:

• "Non-aggregable values are not displayed for scheduled workbooks"

• "ALL page item value is missing for tabular worksheets"

• "How to disable Enhanced Data Aggregation"

21.1.6.1 Non-aggregable values are not displayed for scheduled workbooks

Discoverer does not display non-aggregable values for scheduled workbooks. In other words, Discoverer processes scheduled workbooks as if you selected the "Show values that cannot be aggregated as: <Non-aggregable label>" option on the "Worksheet Properties dialog: Aggregation tab".

Non-aggregable values include those based on the following SQL functions:

• A CASE SQL statement

• A DECODE SQL statement

• A PL/SQL function

• A DISTINCT SQL statement

• An analytic function

21.1.6.2 ALL page item value is missing for tabular worksheets

When you display a scheduled worksheet that contains a table, the ALL page item value is not available. When you display the non-scheduled version of the workseet, the ALL page item value is available. For a worksheet that contains a crosstab, the ALL page item is available for the scheduled and non-scheduled versions of that worksheet.

21.1.6.3 How to disable Enhanced Data Aggregation

As you create a workbook that contains totals, the original version of the workbook displays different total values than the scheduled version of the workbook. This difference occurs because scheduled workbooks do not use Enhanced Data Aggregation.

To disable Enhanced Data Aggregation:

1. Display the Properties dialog for the worksheet both before and after scheduling.

2. Display the Aggregation tab.

3. Deselect the Use the aggregation behavior selected by the Discoverer manager box.

4. Select the Show the sum of the values displayed in the contributing cells button.

21.1.7 How to schedule workbooks

You schedule a Discoverer workbook when you want Discoverer to process a workbook at a particular time and frequency (for example, weekly). For example, you might want to process a workbook called Monthly Sales Report at midnight on the first day of each month (because that is when your computer system has spare capacity).

Note: You must have the required privileges to schedule workbooks. Contact the Discoverer manager for more details.

To schedule a workbook:

1. Choose Tools | Manage Schedules to display the "Scheduling Manager dialog".

   
   

2. Click Schedule to display the "Select Workbook from Database dialog", which displays a list of workbooks to which you have access.
   

3. Select the workbook you want to schedule in the Workbooks list and click Select to display the "Schedule Wizard dialog: General tab".
   

   Note: To process the currently opened workbook, you can also choose File | Schedule to display the "Schedule Wizard dialog". You can schedule the currently opened workbook even if it is not saved in the database.

4. Use the "Schedule Wizard dialog: General tab" to define a name and description for the scheduled workbook, and specify which worksheets you want to process.

5. If parameters are required for the worksheets that you have selected, use the "Schedule Wizard dialog: Parameter Values page" to specify values for parameters.

   
   

6. Use the "Schedule Wizard dialog: Schedule tab" to specify when to process the workbook and how frequently it is processed.

   
   

   Hint: Click the calendar icon to the right of the When do you want to schedule this workbook? field to specify a date using the graphical "Calendar dialog".

7. Click Finish to create the scheduled workbook.

   Discoverer processes the workbook at the specified time and frequency.

   Each time the workbook is processed, Discoverer creates a new scheduled workbook results set. To open a scheduled workbook results set, choose File | Open to display the "Open Workbook from Database dialog" and look for workbooks with a clock icon in the workbooks list. For more information, see "How to open workbooks".

Notes

• To publish scheduled workbook results sets to other users, you must share the scheduled workbook (for more information, see "About sharing workbooks").

• To monitor the progress of scheduled workbooks, choose Tools | Manage Schedules to use the "Scheduling Manager dialog".

21.1.8 How to edit scheduled workbooks

You edit a scheduled workbook when you want to change the instruction to process a workbook. For example, you might want to change the frequency of a scheduled workbook from weekly to monthly.

You cannot edit the read-only copy of a workbook that Discoverer processes.

Note: Changing the instruction does not affect the Discoverer workbook that is being processed.

To edit a scheduled workbook:

1. Choose Tools | Manage Schedules to display the "Scheduling Manager dialog".

   
   

2. Select the scheduled workbook you want to edit in the Scheduled Workbook list.

3. Click Edit to display the "Schedule Wizard dialog: General tab".

   
   

4. (optional) Use the "Schedule Wizard dialog: General tab" to change the description of the scheduled workbook.

5. If parameters are required for the worksheets that you specify, use the "Schedule Wizard dialog: Parameter Values page" Values tab page to specify values for parameters.

6. Use the "Schedule Wizard dialog: Schedule tab" to change the instruction to process the workbook (for example, change the time or frequency).

7. Click OK to save changes to the scheduled workbook.

   Hint: To monitor the progress of scheduled workbooks, choose Tools | Manage Schedules to use the "Scheduling Manager dialog".

21.1.9 How to copy a scheduled workbook

You copy a scheduled workbook when you want to quickly create a instruction to process a workbook based on an existing scheduled workbook. For example, to process the same workbook and worksheets as an existing scheduled workbook.

To copy a scheduled workbook:

1. Choose File | Scheduling Manager to display the "Scheduling Manager dialog".

   
   

2. Select the scheduled workbook you want to copy in the Scheduled Workbooks list.

3. Click Copy to display the "Schedule Wizard dialog: General tab".

   
   

4. Use the "Schedule Wizard dialog: General tab" page to change the default name for the scheduled workbook.

5. If parameters are required for the worksheets that you specify, use the "Copy Scheduled Workbook dialog: Parameter Values tab" to specify values for parameters.

   
   

6. Use the "Schedule Wizard dialog: Schedule tab" to change the instruction to process the workbook (for example, change the time or frequency).

7. Click OK to save the scheduled workbook.

   The new scheduled workbook is displayed in the "Scheduling Manager dialog".

21.1.10 How to unschedule a scheduled workbook

You unschedule a scheduled workbook to stop the scheduled workbook but keep its scheduled workbook results sets. For example, you might want to stop a scheduled workbook that is processed monthly, but keep monthly reports that have been produced.

When you unschedule a scheduled workbook, you update the instruction to process a particular Discoverer workbook. You do not delete the Discoverer workbook that is being processed (for more information, see "What are scheduled workbooks?").

Note: To stop a scheduled workbook and remove its scheduled workbook results, use the Delete button (for more information, see "How to delete scheduled workbooks and scheduled workbook results sets").

When you unschedule a scheduled workbook, you do the following:

• If the scheduled workbook has not yet been processed, you delete the instruction to process the workbook and the read-only copy of the workbook that Discoverer processes. The scheduled workbook is removed from the list of scheduled workbooks in the Scheduling Manager dialog.

• If the scheduled workbook has been processed, you stop the instruction to process the workbook in the future but you do not delete the scheduled workbook. The scheduled workbook remains in the list of scheduled workbook in the Scheduling Manager dialog, with the Status column set to 'Unscheduled'. You keep the scheduled workbook results sets that have been produced.

To unschedule a scheduled workbook:

1. Choose File | Scheduling Manager to display the "Scheduling Manager dialog".

   
   

2. Select the scheduled workbook you want to unschedule in the Scheduled Workbooks list.

3. Click Unschedule, then click Yes at the Confirm Unschedule dialog.

   Note: If the Unscheduled button is grayed out, the scheduled workbook is a single run workbook that has been processed (that is, the value in the Status column is Report Ready); or, it has been unscheduled already (that is, the value in the Status column is Unscheduled).

4. Click Close to close the Scheduling Manager dialog.

21.1.11 How to delete scheduled workbooks and scheduled workbook results sets

You delete a scheduled workbook to stop the scheduled workbook and remove its scheduled workbook results sets. For example, you might want to stop a scheduled workbook that is processed monthly, and remove monthly reports that have been produced.

You delete scheduled workbook results sets when you no longer need those results generated by scheduled workbooks. For example, you might have monthly reports that have accumulated over the previous year that you know you would never use again.

When you delete a scheduled workbook, you remove the instruction to process a particular Discoverer workbook. You do not delete the Discoverer workbook that is being processed (for more information, see "What are scheduled workbooks?").

Note: To stop a scheduled workbook but keep its scheduled workbook results, use the Unschedule option (for more information, see "How to unschedule a scheduled workbook").

To delete a scheduled workbook or scheduled workbook results set:

1. Choose File | Scheduling Manager to display the "Scheduling Manager dialog".

   
   

2. To delete a scheduled workbook:

   • Select a scheduled workbook in the Scheduled Workbooks and click Delete.

   • Click Yes at the confirmation dialog to remove the scheduled workbook and its scheduled workbook results sets.

3. To delete a scheduled workbook results set:

   • Select a scheduled workbook in the Scheduled Workbooks list and click the + symbol to expand the list of scheduled workbook results sets.

   • Select one or more scheduled workbook results sets.

     Note: You can select multiple scheduled workbook result sets by pressing the Ctrl key and clicking another scheduled workbook results set.

   • Click Yes at the confirmation dialog to remove the scheduled workbook results sets.

4. Click Close to close the Scheduling Manager dialog.

The specified scheduled workbooks or results sets are removed from the database.

21.2 Using lists of values (LOVs)

This section describes how to improve productivity using Lists of Values (LOVs) in Discoverer, and includes the following topics:

• "What is a list of values (LOV)?"

• "LOV examples"

• "About using long LOVs"

• "How to select single values from long LOVs"

• "How to select multiple values from long LOVs"

21.2.1 What is a list of values (LOV)?

A list of values (LOV) contains a list of predefined values for a worksheet item. For example, a LOV for a year item might contain the values 1998, 1999, and 2000 (see figure below).

Figure 21-4 A LOV on a year item, containing 1998, 1999, 2000, and <All>

LOVs are created for you by the Discoverer manager.

You use LOVs in:

• parameters

• conditions

• the Discoverer item navigator

• the Discoverer Export wizard

• the Discoverer Print wizard

LOVs offer the following benefits:

• When used in parameters, conditions, and export, LOVs enable you to select predefined values rather than enter arbitrary values in a text field.

• When used in the Discoverer item navigator, LOVs enable you to apply conditions to worksheets without defining conditions criteria. For example, choosing January from a LOV in the Discoverer item navigator filters a worksheet to display only data for the January.

• LOVs work differently with parameters and conditions:

  • With parameters, the Discoverer user that creates the workbook specifies whether single or multiple values are allowed. For example, when setting a parameter, a user might choose January and February from a LOV (for more information, see "A LOV used to specify worksheet parameters").

  • With conditions, the condition type determines whether you can select single or multiple values. Only the following condition types allow multiple values:

    • Like

    • Not like

    • In

    • Not in

    • Is null

    • Is not null

    • Between

21.2.2 LOV examples

This section contains examples of using LOVs.

21.2.2.1 A LOV used to specify worksheet parameters

In the figure below, a LOV has been created on the Region item, containing the regions Central, East, and West. If a LOV was not defined on the Region item, you might enter 'South' here, which would result in an empty worksheet because the database does not contain data on the South region. The figure below shows the value West being selected from a LOV in the Select Values dialog.

Figure 21-5 A LOV being used to specify worksheet parameters

21.2.2.2 A LOV used in a condition

LOVs are also used when you create conditions. For example, in the figure below, the LOV containing months is used to choose values against which to match worksheet data. The figure below shows the value Feb (that is, February) being selected from a LOV in a condition dialog

Figure 21-6 A LOV being used in a condition

21.2.2.3 A LOV used in the Discoverer item navigator

LOVs are also used in the Discoverer item navigator. For example, in the figure below, the LOV values Video Sales and Video Rentals are selected for display on a worksheet. In other words, the LOV values are used to filter the worksheet. The figure below shows the values Video Sale and Video Rental being selected in the Discoverer item navigator.

Note: LOVs in the Discoverer item navigator might be turned off. Contact the Discoverer manager for more details.

Figure 21-7 A LOV used in the Discoverer item navigator

21.2.3 About using long LOVs

When LOVs contain a large number of values, Discoverer displays a dialog that enables you to search LOV values and select the values that you want. For example, if a LOV contains hundreds of values, you can select only values that begin with the letter 'A', or select only values that contain 'CPM'.

Note: For more information about Discoverer dialogs used to search LOVs, see "Select Value dialog" and "Select Values dialog".

When using long LOVs, the following rules apply:

• When LOVs are used with parameters, you can use the Select Multiple Values option to display a dialog that enables you to search and select LOV values.

• When LOVs are used with conditions, you can use the Select values option to display a dialog that enables you to search and select LOV values.

• The Discoverer manager might configure Discoverer Plus Relational to always display a search dialog for selecting LOV values, even when the LOV fits on the screen.

For more information about selecting values from long LOVs, see "How to select single values from long LOVs" and "How to select multiple values from long LOVs".

• LOV values are displayed in groups. The size of groups is configured by the Discoverer manager (maximum 100).

21.2.3.1 How to select single values from long LOVs

When LOVs contain a large number of values, you select single LOV values using the "Select Value dialog". For example, to analyze data from the year 2000, you select 2000 from the LOV.

To select single values from long LOVs:

1. Display the "Select Value dialog".

   • from the "Edit Parameter dialog" or "New Parameter dialog", click the down arrow next to the What default value do you want to give this parameter? field, then choose Select values.

   • from the "Edit Condition dialog" or "New Condition dialog", choose Select values from the Values drop down list.

   
   

2. If the Displayed values list contains the value that you want, select the value from the Displayed values list.

3. (optional) If you cannot see the value that you want in the Displayed values list, do one of the following:

   • Use the scroll bar to navigate up and down the values in the current group.

   • Use the Next and Previous buttons to display the next or previous group of values in the LOV.

4. (optional) Limit the values in the Displayed values list using the Search by and Search for fields, as follows:

   1. Use the Search by drop down list to specify how you want to match LOV values.

      For example, Starts with or Equals.

   2. Enter a search term in the Search for field.

      For example, if you choose 'Starts with', type A to find LOV values that begin with A.

   3. (optional) Select the Case sensitive check box to match upper and lowercase letters exactly. For example, when selected the value 'CPM' would not find details containing 'Cpm' or 'cpm'.

      Note: For quicker searches, select the Case-sensitive check box.

   4. Click Go to start the search.

      Values that match the search criteria are displayed in the Displayed values list. Values are displayed in groups. For example, groups of 50 or groups of 100.

   5. Select the value that you want from the Selected values list.

5. Click OK to choose the selected LOV value and close the dialog.

The LOV value that you specify is selected.

21.2.3.2 How to select multiple values from long LOVs

When LOVs contain a large number of values, you select multiple LOV values using the "Select Values dialog". For example, to analyze data from the values beginning with 'CPM', you select CPM from the LOV.

To select multiple values from long LOVs:

1. Display the "Select Values dialog".

   • from the "Edit Parameter dialog" or "New Parameter dialog", click the down arrow next to the What default value do you want to give this parameter? field

   • from the "Edit Condition dialog" or "New Condition dialog", choose Select multiple values from the Values drop down list

   
   

2. If the Displayed values list contains the values that you want, move the values from the Displayed values list to the Selected values list.

3. (optional) If you cannot see the values that you want in the Displayed values list, do one of the following:

   • Use the scroll bar to navigate up and down the values in the current group.

   • Use the Next and Previous buttons to display the next or previous group of values in the LOV.

4. (optional) Limit the values in the Displayed values list using the Search by and Search for fields, as follows:

   1. Use the Search by drop down list to specify how you want to match LOV values.

      For example, Starts with or Equals.

   2. Enter a search term in the Search for field.

      For example, if you choose 'Starts with', type A to find LOV values that begin with A.

   3. (optional) Select the Case-sensitive check box to match upper and lowercase letters exactly. For example, when selected the value 'CPM' would not find details containing 'Cpm' or 'cpm'.

      Note: For quicker searches, select the Case-sensitive check box.

   4. Click Go to start the search.

      Values that match the search criteria are displayed in the Displayed values list. Values are displayed in groups. For example, groups of 50 or groups of 100.

   5. To select the values that you want in the Displayed values list, move LOV values from the Displayed values list to the Selected values list.

5. Click OK to choose the selected LOV values and close the dialog.

The LOV values that you choose are selected.

Notes

• To deselect LOV values, move LOV values from the Selected list to the Displayed list.

21.3 Changing default settings

This section explains how to use Discoverer's default settings, and contains the following topics:

• "About default Discoverer settings"

• "How to change default Discoverer settings"

• "How to revert to the default format settings"

• "How to change default worksheet formats"

• "Notes on setting Advanced options"

21.3.1 About default Discoverer settings

Discoverer's default settings determine the appearance and behavior of Discoverer worksheets. You can change the default settings for new worksheets to suit your requirements. For example, you might want new worksheets to have a gray background and blue text. Or you might want to limit the amount of data returned by a query so that worksheets are not too large.

You are advised to only change your default settings in one of the following circumstances:

• You want to change only the default color and style of headings and data in your worksheets.

• You are an experienced Discoverer user.

• You have been asked to change your default settings by the Discoverer manager

Default settings are applied in the following manner:

• Default options apply when you start working with Discoverer.

• Changes to default settings apply to all new workbooks.

• Changes to default settings do not affect workbooks created previously.

Note: You can change properties in specific worksheets using the "Worksheet Properties dialog: General tab", "Worksheet Properties dialog: Sheet/Crosstab Format tab" and the "Worksheet Properties dialog: Aggregation tab". Here you apply new settings to the current worksheet without changing the default settings.

21.3.2 How to change default Discoverer settings

You can change the default appearance and behavior of Discoverer worksheets. For example, you might want to:

• change the default background color for worksheets to blue

• turn on automatic querying so that worksheets are refreshed with up-to-date data when you change a worksheet layout

To change default settings:

1. Choose Tools | Options to display the "Options dialog: General tab".

2. Display the appropriate tab for the properties you want to edit.

   The table below describes the tabs available on the Options dialog.

   Tab	Use to
   "Options dialog: General tab"	Specify how Discoverer displays data when worksheets are first opened.
   "Options dialog: Query Governor tab"	Specify how Discoverer uses summaries, and how Discoverer manages execution of queries.
   "Options dialog: Sheet tab"	Specify how Discoverer displays worksheet headers, titles, text area, gridlines.
   "Options dialog: Formats tab"	Specify the format that Discoverer displays new worksheet headings, titles, text area, data, totals, stoplight colors, and conditional formats.
   "Options dialog: Advanced tab"	Specify how Discoverer queries the database, and uses joins.
   "Options dialog: EUL tab"	Specify a default End User Layer (EUL) if the Discoverer manager has given you access to multiple EULs.

   
   

3. Click OK to save the changes that you have made and close the Options dialog.

The changes to Discoverer's default appearance and behavior take effect when you close the dialog.

Notes

• After changing format settings on the "Options dialog: Formats tab", you can revert to the original default format settings using the Reset button (see "How to revert to the default format settings").

• Do not change settings on the "Options dialog: Advanced tab" or "Options dialog: EUL tab" unless asked to do so by the Discoverer manager.

21.3.3 How to revert to the default format settings

Sometimes, after changing the default formats for a workbook, you might want to revert to the original default format settings. For example, if you change default text fonts to produce a particular style of report, you might want to set the text fonts back to their original default style.

Instead of changing each format setting individually, you can use the Reset facility.

Note: Default formats apply to all worksheets in a workbook.

To revert to original default format settings:

1. Choose Tools | Options to display the Options dialog.

2. Display the "Options dialog: Formats tab".

3. Select the default format you want to reset.

   For example, Data Format or Heading Format.

4. Click the Reset button to revert back to the default settings for the selected default format.

5. Click OK to close the Options dialog.

If you reset the default formats, these original default formats apply when you close the Options dialog.

21.3.4 How to change default worksheet formats

You change default worksheet formats when you want to change how new worksheets are formatted. For example, you might want to display all new worksheet items in blue text with a yellow background.

Note: Changing the default worksheet format does not affect the format of existing worksheet items. Only items that you add to the worksheet have the new default format.

To change the default worksheet format:

1. Choose Tools | Options to display the Options dialog.

2. Display the "Options dialog: Formats tab"

   
   

3. Select a format to change from the Name list.

4. Click Change to display the appropriate format dialog.

   For example, the "Data Format dialog (Default Options)", the "Heading Format dialog (Default Options)", or the "Total Format dialog (Default Options)".

5. Use the format dialogs to specify your changes.

   Hint: The Example field shows the effect of the changes that you make.

6. Click OK to save the changes you have made and close the Formats dialog.

   The default formats that you specified are used for new worksheets that you create and for new items that you add to existing worksheets. For example, if you set the default data format style to a blue font with a yellow background, any new items that you add to worksheets would have this format.

21.3.5 Notes on setting Advanced options

If you are a Discoverer manager, or an experienced Discoverer user, the following topics are relevant to the "Options dialog: Advanced tab".

21.3.5.1 About automatic querying

When automatic querying is turned on, Discoverer automatically re-queries the database to get the up-to-date data every time a worksheet is changed to display different data. For example, when you add or remove an item, or pivot items.

In some circumstances however, you might not want Discoverer to automatically re-query the database every time you change the worksheet layout. For example, you might want to make several changes at once and not perform a re-query until you have finished.

21.3.5.2 About fan traps

Fan traps occur when the data items in two folders are not directly related but do have a relationship based on the data items in a third folder.

For example, a database contains three tables:

• Departments

• Employees

• Locations

The figure below shows that the Department table is a master table to the Employees table and the Locations table in a relational one to many (1:M) relationship.

Figure 21-8 A database with three tables

Each employee is associated with a single department because each employee works in only one department. In addition, each employee can only be based in one location. However, departments are associated with multiple locations because departments can have offices in different cities. Consequently, because of the mutual association of employees and locations with the Departments table, employees become unintentionally associated with multiple locations. This is incorrect because employees can only be in one location.

For example, a query to count the number of employees at each location and department produces an incorrect result. The same employee is counted at multiple locations because the departments are at multiple locations. In the example below, the real number of employees is four, but the query produces a count of eight employees. Clark, Miller, and Scott are counted for both London and Tokyo, and King is counted for both Amsterdam and San Francisco. The figure below shows how a query to return the number of employees returns eight rows in a fan trap relationship instead of four rows.

Figure 21-9 Fan trap query results

Key to figure:
a. Query to count the number of employees returns this data.

When you create a worksheet, Discoverer automatically detects and resolves fan traps. If the fan trap is not resolvable, Discoverer disallows the worksheet and displays an error message.

For more information about enabling and disabling fan trap detection in Discoverer, see the "Options dialog: Advanced tab".

21.3.5.3 About multiple join paths

When you create new worksheets, the data items in the worksheets are often stored in multiple folders in the database. Discoverer checks to ensure that:

• multiple folders have an unambiguous relationship between them

• the relationships between data items is also unambiguous

Multiple join paths occur when two tables can be linked in multiple ways. For example, a sales order table might be linked to a customer table on the Customer ID field, because both tables contain the field Customer ID. If both tables also contain a field called Location, this provides an alternative join path for the two tables. This is an example of a multiple join path.

Multiple join paths occur when databases are organized so that the relationships between items in different tables are ambiguous. When you create new worksheets, Discoverer can automatically detect and warn you if potential multiple join paths exist. This makes sure that you always get the results that you expect, because you do not associate items in a way that you did not intend.

Multiple join path warnings are not error messages. The warnings merely advises you that the database contains ambiguous relationships. If warnings occur, contact the Discoverer manager who can determine whether the database's organization must be modified.

Note: To detect and resolve multiple join paths, ensure that the Disable Multiple Join Path Detection option is not selected on the "Options dialog: Advanced tab".

21.4 Using SQL

This section is aimed at experienced Discoverer users and managers who are familiar with SQL (Structured Query Language) and who are interested in Discoverer's advanced facilities.

This section contains the following topics:

• "What is SQL"

• "Why should I be interested in SQL?"

• "About the Discoverer execution plan"

• "How to view SQL"

• "How to view a SQL execution plan"

21.4.1 What is SQL

SQL is a generic programming language used to extract and manipulate data in a database. In other words, SQL enables you to ask a question (known as a query) of the database that the database answers by displaying data.

For example, you might use SQL to ask the question 'Which products sell more than 10,000 per year?'. The database uses SQL to return a list of products that sell more than 10,000, and might also perform other analysis such as sorting, grouping, and totalling of the data.

SQL is a powerful language, but is difficult to learn and use. Although Discoverer itself uses SQL to display and analyze worksheet data, Discoverer users are shielded from underlying SQL.

21.4.2 Why should I be interested in SQL?

Because Discoverer shields Discoverer users from underlying SQL, they do not need to know how SQL works. So, users with no technical database experience and no knowledge of underlying database structures can perform sophisticated data analysis.

However, in some circumstances, you might want to look at SQL being used by Discoverer. For example, to improve Discoverer performance you might have to look at underlying SQL to ensure that queries are being run efficiently.

21.4.3 What are summaries?

Summaries are database tables that contain commonly accessed, pre-processed data, which gives the following benefits:

• because data is pre-joined and pre-aggregated, Discoverer can access this data more quickly than by using ad hoc query

• this also means that the data is processed once and accessed many times, rather than re-processed every time it is needed

21.4.4 What are summary folders

A summary folder is how Discoverer represents an underlying summary or materialized view. Summaries and materialized views pre-compute and store aggregated data for use in SQL queries.

Summaries are created by the Discoverer manager to improve the performance of Discoverer, to help do your work more quickly and efficiently. Summary tables and materialized views are created as follows:

• a summary table is a table that Discoverer creates.

• a materialized view is the database server's own summary mechanism.

21.4.5 What is an execution plan?

An execution plan is a sequence of operations that the Oracle Server performs to execute a SQL statement.

21.4.6 About the Discoverer execution plan

When looking at the underlying SQL that Discoverer is using, use the Discoverer execution plan tab to look the underlying execution plan being used.

You can look at an execution plan to see how a SQL statement is being executed. For example, when using Summaries, you might want to check that a query is using a summary or materialized view created by the Discoverer manager.

21.4.7 How to view SQL

You view SQL created by Discoverer when you want to see the underlying SQL instructions that Discoverer is using to display the current worksheet.

To view SQL created by Discoverer:

1. Choose Tools | Show SQL to display the SQL Inspector dialog.

2. Display the SQL tab to look at the underlying SQL.

   
   

3. (optional) To copy the SQL text into memory, click Copy.

   You can then switch to a different application and paste in the text. For example, you might want to paste this text into a text editor, edit the text, then save in a SQL file to that you can execute the file using SQL*Plus.

4. Click OK to close the SQL Inspector dialog and return to the worksheet.

Note: The SQL Inspector dialog might show a shortened version of the SQL that Discoverer sends to the RDBMS. Depending on how Discoverer is configured, inline views might be removed to make the SQL statement more legible. Contact the Discoverer manager for more details about how the SQL Inspector dialog is configured.

21.4.8 How to view a SQL execution plan

You view a SQL Execution Plan when you want to see the underlying instructions that Discoverer is sending to the server.

To view the execution plan used by Discoverer:

1. Choose Tools | Show SQL to display the SQL Inspector dialog.

2. Display the Plan tab to look at the underlying execution plan.

   
   

3. (optional) To copy the execution plan text into memory, click Copy.

   You can then switch to a different application and paste in the text.

4. Click OK to close the SQL Inspector dialog and return to the worksheet.