8
Advanced Discoverer Desktop Features

Discoverer Desktop includes several advanced features for working with data. This chapter describes those features and explains how to use them.

The topics covered in this chapter include:

• Retrieving All Rows and Counting the Number of Rows

• Creating Parameters

• Creating Calculations

• Creating Advanced Conditions

• Setting Options

• Using Command-Line Options

• Using SQL

8.1 Retrieving All Rows and Counting the Number of Rows

Rows for tables are fetched from the database incrementally in groups. The number of rows in each group is specified in the Options dialog box based on the value set in the option "Retrieve data incrementally in groups of." Click the Query Governor tab on the Options dialog box to see that option.

To override that setting you can retrieve all the rows at once instead of incrementally. Retrieving all rows applies only to tabular style reports.

• To retrieve all the rows at once instead of incrementally, choose
  Sheet | Retrieve All Rows. The table includes all the rows.

• To count the number of rows choose Sheet | Count All Rows.

  Figure 8-1 Number of Rows

  
  
  Text description of the illustration rowsnum.gif

The message shows the total number of rows that will be returned by the query. The number of rows may be greater than the actual number of rows currently displayed in the worksheet.

8.1.1 Refreshing the Worksheet

Refreshing a worksheet re-queries the database and displays the worksheet's data based on any new data. A primary use of refreshing a worksheet is when you're using Discoverer in conjunction with real-time data. For example, if the Discoverer worksheets query a database used for on-line transaction processing, you need to refresh the worksheet periodically to update the worksheet data with the latest transaction results.

To refresh a worksheet, choose Sheet | Refresh Sheet. Discoverer displays the worksheet results based on the updated data.

8.2 Creating Parameters

Parameters are placeholders used instead of specific values in the definition of a condition. Unlike regular conditions that find the same data each time they are applied, parameters offer choices at the time the data loads.

You can create Parameters at two levels:

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

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

For example, suppose one of your routine analysis jobs is to compare sales performance by evaluating sales figures for various products from different groups of cities. By creating two parameters--one for products and the other for cities--you can select the specific grouping of data for the analysis at the time the worksheet loads.

Parameters are particularly helpful if several people use the same workbook or worksheet. Each person can select the parameters that load only the data of interest on the worksheet.

Parameter selections appear when loading a workbook or worksheet. Here's an example that shows a parameter for limiting the available data to a specific year, (2000).

Figure 8-2 Sample Parameters for Video Sales

Text description of the illustration 8advan3a.gif

Key to Figure 8-2.

1. This dialog box appears when loading a workbook or worksheet.

2. Enter a Parameter in the text box, or select a value from the pull-down list by clicking on the down-arrow to the right of the text box and select a value.

3. Choose Finish to load the Worksheet for the selected values.

4. The Worksheet displays only data matching the value entered for the specified criteria.

To create a parameter:

1. Display the worksheet to which you want to apply the parameter.

2. Choose Tools | Parameters.

   The Parameters dialog box opens. It shows the parameters already created.

   Figure 8-3 Parameters Dialog Box

   
   
   Text description of the illustration para1a.gif

3. Click New.

   The New Parameter dialog box appears.

   Note: You can also access this dialog box when creating advanced conditions. Select New Parameter from Value(s) drop-down list on the New Conditions dialog box.

   Figure 8-4 New Parameter Dialog Box

   
   
   Text description of the illustration param1.gif

   This is where you define new parameters.

   For Item--select the item for the parameter from the drop-down list. The list shows the items currently available to the worksheet.

   Name--the name of the Parameter, which appears on the list of available Parameters dialog.

   Create Condition with Operator--creates a condition with an operator. You can select the operator from the drop-down list. For example, select equals (=) to create a condition with the formula "For Item" = "Parameter's Name".

   Prompt--this text appears on the dialog box that opens prior to loading the worksheet; enter text that tells the person what to select.

   Description--this text also appears on the dialog box that opens prior to loading the worksheet; it explains the parameter.

   Let User Enter Multiple Values--select this option if you want the person using the worksheet to be able to select multiple values for the parameter when loading the worksheet. If this option is not selected, the person can choose only one value for the parameter.

   Default Value--the pre-selected value for the parameter. Click the drop-down arrow and select a value from the list, or type the default value directly into the box.

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

   4. Click OK. The new parameter now appears on the Parameters dialog box.

      Moving the parameters up and down on the Parameters dialog box changes their position on the dialog box that appears when loading a worksheet. To add a picture to that dialog box, click Bitmap and choose Set Bitmap.

   5. Click OK on the Parameters dialog box. Those parameters are now in force for the next time the worksheet is loaded.

   Parameters are activated when used in an active condition. If you check the option "Create condition with operator" in the New Parameter dialog box, a new condition is created and activated, therefore the parameters are also activated.

   To deactivate a parameter, deactivate the condition. Deleting the condition or deleting the parameter also deactivates the parameter.

   To edit parameter values:
   Here you display the Parameter dialog so that you can choose a different value to display on your worksheet(s).

   1. Choose Sheet | Edit Parameter Values.

      The Parameters dialog box appears.

   2. Select the new value(s) for the Parameter.

   3. Click OK.

   The data on the worksheet is revised to meet the Parameter conditions.
   
   NOTE: If you want to change the Parameter value for your worksheet, you can also use the Refresh option to display the Parameter dialog for active Parameters.
   

   8.2.1 Loading Multiple Values

   If the option Let User Enter Multiple Values is selected for a worksheet's parameters, the person opening the worksheet can select multiple values for the parameter.

   Here's an example:

   Figure 8-5 Choosing Multiple Parameters

   
   
   Text description of the illustration 8advan5a.gif

   Key to Figure 8-5.

   1. When you can load multiple values, the drop-down list includes the option to Select Multiple Values.

   2. The Values dialog box displays a list of values that can be selected. Selected values are marked with a selected check box. Click Select All to select all values in the list.

   8.3 Creating Calculations

   Discoverer Calculations are used to analyze the data in your worksheets. Discoverer provides a comprehensive range of pre-defined functions for use in your Calculations.

   Simple Calculations based on the data in a worksheet can produce typical business answers, such as sales commissions per salesperson, royalty fees paid to a supplier, and so on. Complex Calculations can find the answers to more complicated questions including "what if" scenarios.

   In other words, instead of merely viewing your data to find trends and answers, you can use Calculations to rigorously analyze the data using mathematical techniques.

   The results of Calculations are displayed as new columns on a worksheet, or the Calculations can be part of other Calculations.

   You can also pivot Calculation Items to the page axis, just like other Items.

   NOTE: To see examples of how you can use Calculations to analyze your Discoverer data, refer to Appendix A.2, "About the examples in this chapter".

   Here is a sample of the results of a simple Calculation on a table:

   Figure 8-6 Sample Calculation

   
   
   Text description of the illustration 8advan9a.gif

   Key to Figure 8-6.

   1. This is the dialog box that you use to define Calculations. The Calculation box is where you create the formula for the Calculation.

   2. After creating a Calculation, a new Item column is displayed on your Worksheet showing the calculated value. In this example, it shows the Profit SUM increased by 20%, (Profit SUM * 1.2).

   Calculations are based on items, not on individual data points. For example, you can multiply Profit SUM (an item) by a percentage to find a result for each product.
   

   However, you cannot, for example, subtract Year 2000 profits from Year 1999 profits because 2000 and 1999 are data points in the item, Year. To calculate results based on individual data points, use Discoverer's Analytic Functions LAG and LEAD, (see Appendix A.5.8, "Lag/Lead Function Examples").

   To create a Calculation:

   1. Display the worksheet on which you want to apply the Calculation.

   2. Choose Tools | Calculations.

   The Calculations dialog box appears.

   Figure 8-7 Calculations Dialog Box

   
   
   Text description of the illustration calcu1.gif

   This dialog box shows Calculations already created for the worksheet. Selected Calculations (where the check box is selected) are active and apply to the worksheet.

   3. Click New.

      The Edit Calculation dialog box for creating a new Calculation appears.

      Figure 8-8 Edit Calculation Dialog Box

      
      
      Text description of the illustration 8advanc9.gif

   Key to Figure 8-8.

   1. Calculation Box.

   2. Function Categories - you can build calculations using a pre-defined set of functions arranged into the categories.

   3. You can use these operators to build calculations.

   To help you create the Calculation formula with a minimum of typing, you can:

   • Paste expressions from the box on the left to the box on the right.

   • Add operators with the operator buttons.

   • Type the new Calculation directly into the Calculation box.

   • Mix typing with pasting and clicking the operators.

   4. Click the Show button to see the different expressions.

      Items--lists the items available to the worksheet. This is helpful because you don't have to remember the name of an item in order to include it in a formula. Parameters are also listed, and you can use the parameters in a formula as well.

      Functions--lists a wide range of functions that you can apply to your formula.

      Figure 8-9 Calculations in the Edit Calculation Dialog Box

      
      
      Text description of the illustration 8advan13.gif

   Key to Figure 8-9.

   1. Select an Item then click Paste to copy the Item into the Calculation box.

   2. Click the Functions button to show a list of function folders. To display a list of functions, click the plus symbol (+) next to each function folder. Use the Paste button to copy the selected Function into the Calculation box.

   5. Click OK and then on the Calculation dialog box, click OK.

      The Calculation is applied to the worksheet and results appear in a new column.

      Calculations use Oracle's standard syntax. See the Oracle SQL Reference Language Reference Manual for a complete description of the syntax.

   8.4 Creating Advanced Conditions

   The advanced condition option allows you to build complex conditions for filtering your worksheet data.

   To create an advanced condition:

   1. On the Edit Condition dialog box, click the Advanced button.

      The dialog box expands to show buttons to add and delete lines to the condition, and to include the Boolean operators (AND, OR, NOT).

      Figure 8-10 New Condition Dialog Box

      
      
      Text description of the illustration condadv1.gif

      The Item drop-down list shows the items currently available to the worksheet.

      Other options from the list are:

      Create Calculation--click to open the New Calculation dialog box. When you finish creating the Calculation, it appears in the Item section of the condition.

      For example, if you create a Calculation that computes a Royalty Fee, that Calculation is listed in the Item portion of the condition, and you can then filter the worksheet's data by the Calculation results.

      Select Condition--displays a dialog box that lists the conditions currently defined for the worksheet. Select a condition to become the first part of the advanced conditional expression. With this option you can filter the data using several sequential conditions. That is, condition 1 filters the data, and then based on the results from condition 1, condition 2 filters the data.

      Copy Condition--displays a dialog box that lists the conditions currently defined for the worksheet. Copying a condition inserts it on the line in the New Condition dialog box. You can then edit it, or add other features.

   2. To add another line to the condition, click Add.

      By default, the two conditional expressions are grouped with the Boolean AND.

   3. To change the grouping, click the AND in the expressions, and then click the Or or Not buttons.

      Figure 8-11 New Condition Dialog Box with AND Conditions

      
      
      Text description of the illustration condadv3.gif

   Note: As you create the condition formula, the box at the bottom of the dialog box shows its SQL syntax.

   4. Fill in the values for Item, Condition, and Value(s).

      To see options for the Value(s), click the drop-down arrow.

      Figure 8-12 New Condition Dialog Box with Values Displayed

      
      
      Text description of the illustration condadv2.gif

      The values on the list correspond to the selected item in the condition. Other options are:

      Select Multiple Values--displays a list of the values for the item. Click multiple values to include them in the values portion of the condition.

      Create Calculation--click to open the Calculation dialog box. When you finish creating the Calculation, it appears in the Value(s) portion of the condition.

      Select Item--displays a dialog box that lists the items currently defined for the worksheet. Select an item to become the value of the advanced conditional expression.

      Select Parameter--displays a dialog box that lists the parameters currently defined for the worksheet. Select an parameter to become the value of the advanced conditional expression.

      New Parameter--displays the Parameter dialog box, and you can create a parameter to be the value of the condition.

      Create Subquery--displays a dialog box for creating a subquery as the value portion of the condition. See the next section for details.

      Edit Subquery--this option only appears if you're using a subquery to determine the value; the Edit Subquery dialog box appears so you can edit the previous selections for the subquery.

      5. Click OK when the advanced condition is complete.

      8.4.1 Creating Subqueries

      A subquery for a condition uses a value that requires an intermediate step to determine the value. For example, suppose you want to create a condition that finds all the sales profits that exceed the median profit amount. The intermediate step is to find the median profit amount. Often, you create a separate worksheet to determine the intermediate value.

      The subquery then identifies the intermediate value on its worksheet as the value for the condition.

      To create a subquery:

      1. Choose Create Subquery from the Values drop-down list on the New Condition dialog box. See Figure 8-12 above.

         The Create Subquery dialog box appears.

         Figure 8-13 Create Subquery Dialog Box

         
         
         Text description of the illustration subq1.gif
      2. If you've already created a worksheet that contains the intermediate value, select it from the first drop-down list on the dialog box.

         If you need to create a new worksheet that calculates the intermediate value, click the New button. The New Sheet dialog box appears so you can create a new worksheet for the value.

      3. In the second drop-down list, select the original item to be used for the condition. For example, when finding sales profits that exceed the median, the item to select in the second drop-down list is profits.

         See the next section for a description of correlated items.

      4. Click OK. The name of the worksheet appears in the Values portion of the condition on the New Condition dialog box.

      8.4.1.1 Using Correlated Items

      Correlated items add another dimension to a subquery. For example, suppose you want to find all the sales profits that exceed the median profit amount by department. The "by department" portion of the value is the new dimension to the subquery.

      The items to correlate usually appear on both the original worksheet, and the worksheet used to create the intermediate value. For example, on the original worksheet you can find profit data for each department. On the worksheet for calculating the intermediate value, you can find a median value for each department. Correlating the two items matches them so each department median value corresponds to each department profit value.

      Correlating items is also necessary when the worksheet for determining the intermediate value has additional values on it. Correlating makes sure the condition uses the correct intermediate values for the items.

      As stated on the dialog box, you don't need to use the features for correlated items if the subquery does not include the extra dimension provided by items that correlate to one another.

      NOTE: Correlated Subqueries cannot contain filters using Oracle Analytical Functions. For example, if you restrict the values returned in a sheet to the top twenty items (assigned using a Rank function), you cannot use this sheet in a correlated subquery.

      To use correlated items:

      1. Click the Add button on the Edit Subquery dialog box.

      2. Choose the item from the Add drop-down list.

         The following figure shows an example.

         The item then appears in the box for correlated items.

         Figure 8-14 Correlated Items for a Subquery

         
         
         Text description of the illustration subquery.gif

      The Item column refers to the item on the original worksheet. The Subquery Item column refers to the item on the worksheet used to determine the intermediate value.

      Normally you correlate the same item on each worksheet, but you can also correlate two different items. To correlate different items, choose the new item from the Subquery Item drop-down list.

      3. To add additional dimensions to the list of correlated items, click the Add button again and repeat the process.

         To remove an item from the correlation list, select it and click the Remove button.

      4. Click OK.

         When creating a new workbook, do not create worksheets for sub-queries as part of the new workbook process. Instead, finish creating the new workbook first, and then create worksheets for the sub-queries. This ensures that you can select the proper worksheets for the sub-queries from the Conditions dialog box.

      8.4.2 Editing a Subquery

      If you change the worksheet that is used to generate the intermediate value for a subquery, the subquery condition is not automatically updated. You must edit the condition with the subquery first, and then update subquery to match the changed worksheet.

      To edit a subquery:

      1. Edit the condition with the subquery.

      2. Choose Edit Subquery from the Values drop-down list on the Edit Condition dialog box.

         A prompt asks if you want to update the subquery to match the changed worksheet.

      3. Update the subquery.

         The edited subquery and the changed worksheet now match.

      8.5 Setting Options

      The Options dialog box offers a wide variety of options for setting operating features, formatting, and other aspects of your worksheets.

      To select options:

      1. Choose Tools | Options, or click the Options button available on several dialog boxes.

         The Options dialog box appears.

         Figure 8-15 Options Dialog Box

         
         
         Text description of the illustration options1.gif

      The tabs across the top of the dialog box list the different option categories. If you accessed the dialog box by clicking the Options button on another dialog box, the tabs across the top may only apply to that dialog box.

      2. Click a tab to see its options. To see additional tabs, click the right or left arrows at the top right side of the dialog box.

         General--displays options for viewing different types of files, and for opening workbooks. Click the Viewer check boxes if the worksheets include files with videos, images, and sound (audio). Those viewers open automatically from within Discoverer to run the files.

         Query Governor--displays options for maximizing the efficiency of working with larger worksheets, limiting the amount of time a query runs, and limiting the number of rows retrieved. You can also choose whether to use Summary data to improve the performance of Discoverer.

         Table/Crosstab--displays options for the overall table or crosstab layout; you can add or remove gridlines, column headings, row numbering, and so forth. Removing various table or crosstab features is particularly helpful when printing a worksheet as a report. Tab options differ according to whether your worksheet is a Tabular or Crosstab worksheet.

         Formats--displays options for setting the default formats for worksheet headings, data, titles, totals, exceptions, and null values. To change a format, select it and click the Change button. The dialog box for setting the font, color, and backgrounds appears.

         Cache--displays options for the memory cache, disk cache, and cache directory for Discoverer. Generally these options should not be changed without assistance from the database administrator.

         Connection--displays options for setting the EUL types that can be used.

         Advanced--displays options for configuring Automatic Querying, Fan-trap Detection, and Multiple Join Path Detection. When you open Workbooks, Discoverer can run queries automatically or not, or can request confirmation before running queries.

         EUL--displays the default EUL setting. Use the option on this tab to select a default EUL (End User Layer). Check with your database administrator for the correct EUL to select.

      8.6 Using Command-Line Options

      You can run Discoverer from the command line and perform a limited number of tasks automatically, for example, opening or printing a Workbook.
      To use command-line options, type the command string for starting Discoverer, following by a command-line option, (see table 8-1 below).

      To run a command line option:

      1. From the Windows Start menu, choose Run.

         The Run dialog box appears.

      2. Type:

         <drive>\orant\bin\dis51usr.exe /connect me/mypassword@mydatabase <option>

         Where 'me' is your Discoverer ID, 'mypassword' is your Discoverer password, and 'mydatabase' is the Oracle database to which you want to connect, (see your Oracle Administrator for connect details). The following table displays some sample command line options.

         Option	Action	Effects	Notes
         /open <file>	Open a .DIS file	Opens a workbook from a file.	Skips the Connect dialog box and open the workbook.
         /p <file>	Print a Workbook	Prints workbook from the file to the default printer.	Print options are saved as part of the workbook.
         /sheet ALL	Activate all sheets	Runs queries for all sheets in the workbook.	When the workbook opens all queries on all sheets are already run.

      For example, to open a file Reports.DIS in your root directory, type:

      <drive>\orant\bin\dis51usr.exe /connect me/mypassword@mydatabase /open c:\Reports.DIS

      To print a file Reports.DIS in your root directory, type:

      <drive>\orant\bin\dis51usr.exe /connect me/mypassword@mydatabase /print c:\Reports.DIS

      8.7 Using SQL

      If you are familiar with SQL, you can analyze the SQL statements that Discoverer executes against the database. You can also open workbooks with your own SQL programming statements.

      8.7.1 Looking at the SQL Statements for Worksheets

      To see a worksheet's SQL statements:

      1. Choose View | SQL Inspector.

         The SQL Inspector dialog box appears. It shows the SQL statements used to create your current worksheet.

         Figure 8-16 SQL Inspector Dialog

         
         
         Text description of the illustration sql1.gif
      2. Click Copy to copy the statements and paste them to another SQL program.

         The SQL statements Discoverer uses to open a workbook or worksheet involve complex programming. Therefore, you cannot simply copy a worksheet's SQL and use it to open another workbook or worksheet. You must write your own programs.

      3. Click Export to export the statements to another file for use later with another SQL program.

      4. Click OK to close the SQL Inspector dialog box.

      8.7.2 Importing SQL

      If you have written an SQL program to open a workbook, importing the program executes the SQL statement and opens the Discoverer workbook.

      NOTE: If you are importing an SQL script that contains join definitions, the joins must have been created first by your Discoverer manager using Discoverer Administrator.

      To import SQL and open a workbook:

      1. Choose File | Import SQL.

         The Open dialog box appears.

         Figure 8-17 Open SQL File Dialog

         
         
         Text description of the illustration sql3.gif
      2. Locate and select the file that contains the SQL statement, then click Open.

         A new workbook is then created from the query defined by the SQL statement.

      8.7.3 Using the Discoverer Execution Plan

      The Plan tab displays the Oracle Server Execution Plan chosen by the Oracle Server for a query request. The Execution Plan defines the sequence of operations that the Oracle Server performs to execute the SQL statement. This facility is useful when using Summary tables and Materialized Views.

      8.7.3.1 About Summaries

      Summary tables and Materialized Views store precomputed aggregated data, which is used where possible instead of data retrieved directly from the database. Because Summary tables and Materialized Views are much quicker to access, this enhances the performance of Discoverer.

      Summary Management is handled automatically by Discoverer, and is transparent to most Discoverer users. However, you may wish to use the SQL Inspector feature to look at SQL statements being generated. For example, when using Summaries, you may wish to check that a query is using a Summary or Materialized View created by your Discoverer manager.

      8.7.3.2 Types of Summary

      Summaries are created by your Discoverer manager to help do your work more quickly and efficiently.

      Two types of Summary are used:

      • A Summary table is a table created by Discoverer.

      • A Materialized View is the Oracle 8.1.6+ database server's own summary mechanism.

      NOTE: For more information on Summaries and Materialized Views, see Oracle8i Data Warehousing Guide Release 2 (8.1.6) (Part Number A76994-01).

      8.7.4 Looking at an SQL Execution Plan

      To see a worksheet's Execution Plan:

      1. Choose View | SQL Inspector.

         The SQL Inspector dialog appears.

      2. Click the Plan tab.

         Figure 8-18 SQL Inspector Dialog - Plan Tab

         
         
         Text description of the illustration sql2.gif

      8.7.5 Viewing the SQL and Execution Plan with an 8.1.6+ database

      When running Discoverer against an Oracle 8.1.6+ database, the server controls query redirection by rewriting the SQL to use a Materialized View. If a server rewrite occurs, the server Execution Plan indicates the Materialized View name.

      You can use the Plan tab in the SQL Inspector dialog to see the SQL statement that Discoverer sends to the server.

      Figure 8-19 SQL Inspector tab displaying an SQL statement

      
      
      Text description of the illustration mv_big_s.gif

      Although the Discoverer manager has created a Summary for the Items City, Region, and Profit SUM, the SQL statement displayed in the SQL Inspector SQL tab does not indicate that a Summary, (in this case a Materialized View) is being used, see Figure 8-19 above).

      Figure 8-20 Plan tab displaying the Execution Plan (using a Materialized View)

      
      
      Text description of the illustration mv_big_p.gif

      In Figure 8-20, you can see from the SQL Inspector Plan tab that a Materialized View Summary is being used by the database, identified by the table name EUL5_MV<Summary Identifier>.

      8.7.6 Viewing SQL with a pre-8.1.6 database (not using a Materialized View)

      When running against a pre-8.1.6 database Discoverer controls redirection to a summary table. The SQL can be viewed at the SQL tab and the server Execution Plan can be viewed on the Plan tab in the SQL Inspector dialog in Discoverer Desktop.

      Figure 8-21 shows a crosstab worksheet of items from the Video Analysis folder (created as part of the Video Stores and the resulting SQL statement in the SQL Inspector dialog. The SQL statement shows that the summary table EUL5_SUM100750 is referenced.

      Figure 8-21 Summary Redirection in Progress

      
      
      Text description of the illustration inspec2.gif

      Discoverer Desktop automatically chooses the most appropriate Summary Table to process your query efficiently.

      Figure 8-22 shows the same worksheet as before after the user has drilled down from Year to Month. Notice that Discoverer Desktop has redirected the second part of the query to EUL5_SUM100775 instead of EUL5_SUM100750.

      Figure 8-22 Summary Redirection in Progress

      
      
      Text description of the illustration inspec1.gif

      Figure 8-23 shows the same worksheet again. This time the user has drilled down from Region to City. Again, Discoverer Desktop automatically chooses the most efficient Summary Tables for each part of the query.

      Figure 8-23 Summary Redirection in Progress

      
      
      Text description of the illustration inspec4.gif

      8.7.7 Configuring the SQL type used

      Discoverer uses Inline Views in its SQL generation. This SQL has Inline Views removed to make external editing easier - this process is also known as 'flattening'. When you use the SQL Inspector dialog, typically you are looking at SQL with the Inline Views removed, (or flattened SQL).

      To set the default variant, there is registry entry called HKEY_CURRENT_USER\Software\Oracle\Discoverer 5\Database\SQLType.
      Set this value to configure how SQL is displayed in the SQL Inspector dialog. The valid values for this entry are 0 and 2, used as follows:

      0 - Show SQL with no Inline Views (flattened SQL). This is the default.

      2 - Show SQL with Inline Views.

      To configure the SQLType registry setting:

      1. From the Windows Start menu, choose Run.

      2. Type regedit then choose OK.

      3. Navigate to the registry setting in:
         HKEY_CURRENT_USER\Software\Oracle\Discoverer 5\Database\SQLType

         Figure 8-24 Setting the SQLType Registry Setting

         
         
         Text description of the illustration registry.gif
      4. Double click on SQLType, change the setting as required to either 0, 1, or 2, (see notes above), then click OK.

      8.7.8 Exporting SQL without running a Workbook

      You can export Workbook SQL from the command line, without running Discoverer. Refer to the Command Line Interface section of the Oracle Discoverer Administrator Administration Guide for more details, or contact your Discoverer manager.