Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Oracle BI Discoverer Keyboard Navigation
• Related Documents
• Conventions
• JGoodies License Agreement

Part I Getting started

1 Introducing Oracle Business Intelligence Discoverer

• 1.1 What is Oracle BI Discoverer?
• 1.2 Why should I use Oracle BI Discoverer?
• 1.3 How do I access information using Oracle BI Discoverer?
• 1.4 What is a typical workflow with Oracle BI Discoverer?
  • 1.4.1 Stage 1: Getting the data you want
  • 1.4.2 Stage 2: Analyzing data
  • 1.4.3 Stage 3: Sharing data with other people
• 1.5 What are the different components of Oracle BI Discoverer?
• 1.6 Who is the Discoverer manager and what do they do?

2 Using Discoverer Plus with a relational data source

• 2.1 What is Discoverer Plus Relational?
• 2.2 What is a relational data source?
• 2.3 What are business areas?
• 2.4 What are folders?
• 2.5 What are items?
• 2.6 What is the Discoverer Plus Relational sample database?
• 2.7 What are the new features in Discoverer Plus 11.1.1?
• 2.8 Where can I find out more about Discoverer Plus Relational?

3 Using Discoverer Plus with a multidimensional data source

• 3.1 How can I tell when I am using Discoverer Plus with a multidimensional data source?
• 3.2 What is Discoverer Plus OLAP?
• 3.3 What is online analytic processing (OLAP)?
• 3.4 What are multidimensional data sources?
• 3.5 What are multidimensional cubes?
• 3.6 What are measures?
• 3.7 What are dimensions and dimension members?
• 3.8 What are dimension hierarchies?
• 3.9 What are dimension attributes?
• 3.10 About aggregating and filtering multidimensional data
• 3.11 About applying filters after aggregating multidimensional data
• 3.12 About filters and dimensions
• 3.13 What is the Discoverer Catalog?
• 3.14 About the Discoverer Plus OLAP Tutorial
• 3.15 Where can I find out more about Discoverer Plus OLAP?

4 Starting Discoverer

• 4.1 What is a Discoverer connection?
• 4.2 About starting Discoverer
• 4.3 How to display the Discoverer Plus connections page
• 4.4 How to start Discoverer using an existing connection
• 4.5 How to start Discoverer by connecting directly
• 4.6 How to exit Discoverer Plus
• 4.7 Frequently asked questions
  • 4.7.1 What is the End User Layer (EUL)?
  • 4.7.2 What are database accounts?
  • 4.7.3 How do I create and save login information in a Discoverer connection?
  • 4.7.4 How do I edit a Discoverer connection?
  • 4.7.5 How do I delete a Discoverer connection?
  • 4.7.6 What are public connections?
  • 4.7.7 What are user-defined connections?
  • 4.7.8 When and why should I change my password?
  • 4.7.9 How do I change the password for a connection?
  • 4.7.10 How do I change the End User Layer for a Discoverer connection?
  • 4.7.11 What is Single Sign-on?
  • 4.7.12 What is the difference between Single Sign-on and Discoverer passwords?
  • 4.7.13 How do I know what Single Sign-on ID is being used in Discoverer Plus?

5 About the Discoverer Plus Relational work area

• 5.1 About the work area
• 5.2 About Discoverer's drag and drop features
  • 5.2.1 About dragging and dropping items in the Discoverer work area
  • 5.2.2 About repositioning items in the Discoverer work area
• 5.3 About Discoverer menus
  • 5.3.1 About the Standard Discoverer menu
  • 5.3.2 About Discoverer's right-click menus
• 5.4 About Discoverer toolbars
  • 5.4.1 About the Standard toolbar
  • 5.4.2 About the Formatting toolbar
  • 5.4.3 About the Graph toolbar
  • 5.4.4 About the Available Items toolbar
  • 5.4.5 About the Selected Items toolbar
  • 5.4.6 About tool tips
• 5.5 About icons used in the Discoverer item navigator
• 5.6 What are axis items?
• 5.7 About data points
• 5.8 What is the Discoverer Worksheet Wizard?

6 Using workbooks and worksheets

• 6.1 What are workbooks?
• 6.2 What are worksheets?
• 6.3 About Discoverer worksheet types
• 6.4 About table worksheets
• 6.5 About crosstab worksheets
• 6.6 About designing workbooks for maximum performance
• 6.7 How to open workbooks
• 6.8 About opening workbooks in a non-Oracle database
• 6.9 How to edit workbooks
• 6.10 About saving a workbook to the database
• 6.11 How to save workbooks
• 6.12 How to copy workbooks
• 6.13 How to delete workbooks from the database
• 6.14 About worksheet titles and text
• 6.15 How to create or edit worksheet titles or text
• 6.16 How to display or hide worksheet titles or text
• 6.17 How to rename worksheets and workbooks
• 6.18 How to re-order worksheets within a workbook
• 6.19 How to refresh worksheets
• 6.20 How to delete worksheets
• 6.21 How to find data in a worksheet
• 6.22 About creating new workbooks
• 6.23 How to create new workbooks
• 6.24 How to add worksheets to a workbook
• 6.25 How to view and update a workbook's properties
• 6.26 About worksheet properties
• 6.27 How to view and edit worksheet properties

7 Editing worksheets and worksheet items

• 7.1 About editing worksheets
• 7.2 How to edit worksheets using the Edit Worksheet wizard
• 7.3 How to add items to worksheets
• 7.4 How to create new worksheet items
• 7.5 How to remove items from worksheets
• 7.6 How to duplicate a worksheet
• 7.7 How to change the format of worksheet items
• 7.8 How to set the currency symbol for numeric items
• 7.9 How to change the format of worksheet item headings
• 7.10 How to change the text in item headings

8 Creating graphs in Discoverer

• 8.1 What is a Discoverer graph?
• 8.2 About using graphs in Discoverer
• 8.3 About components of a Discoverer graph
• 8.4 About graph types and sub-types
• 8.5 About graph types available in Discoverer
• 8.6 Notes about creating bubble graphs
• 8.7 Notes about creating high-low-close stock graphs
• 8.8 Notes about creating dual-Y graphs
• 8.9 Notes about creating pie graphs
• 8.10 How to edit a graph
• 8.11 How to hide and display a graph
• 8.12 How to change the position of a graph

Part II Analyzing data

9 Pivoting data

• 9.1 About pivoting worksheet items
• 9.2 About pivoting data on a crosstab worksheet
• 9.3 How to pivot worksheet items in the Discoverer work area
• 9.4 How to pivot worksheet items using the Edit Worksheet dialog
• 9.5 About unexpected results with pivoting

10 Drilling to analyze data

• 10.1 About drilling in Discoverer worksheets
• 10.2 What are drill hierarchies?
• 10.3 How to drill up and down
  • 10.3.1 How to drill up and down using a drill icon in worksheet data
  • 10.3.2 How to drill up and down using a graph label in graph data
  • 10.3.3 How to drill up and down using the Drill dialog
• 10.4 About drilling to related items
• 10.5 How to drill to a related item
  • 10.5.1 How to drill to a related item using a drill icon
  • 10.5.2 How to drill to a related item using the Drill dialog
• 10.6 About drilling to detail
• 10.7 How to drill to detail
• 10.8 About drill links
  • 10.8.1 About user defined drill links
  • 10.8.2 About drill links defined by the Discoverer manager
  • 10.8.3 About using drill links
• 10.9 How to create drill links
  • 10.9.1 How to create drill links to Discoverer worksheets
  • 10.9.2 How to create drill links to Internet URLs
• 10.10 How to use drill links
  • 10.10.1 How to drill out to a worksheet or Internet URL using a drill link icon
  • 10.10.2 How to drill out to a worksheet or Internet URL using the Drill dialog

11 Using parameters

• 11.1 What are parameters?
• 11.2 What are the benefits of using parameters?
• 11.3 About using parameters
• 11.4 About creating parameters
• 11.5 About using parameters to collect dynamic user input
• 11.6 About filtering lists of parameter values based on selected conditions (cascading parameters)
• 11.7 How to set parameters
• 11.8 How to activate parameters
• 11.9 How to deactivate parameters
• 11.10 How to create parameters
• 11.11 How to delete parameters
• 11.12 Examples of parameters
  • 11.12.1 Example 1: Using a parameter to filter a worksheet
  • 11.12.2 Example 2: Using a parameter that enables multiple values to filter a worksheet
  • 11.12.3 Example 3: Using a parameter to collect dynamic user input

12 Using conditional formatting

• 12.1 What is conditional formatting?
  • 12.1.1 What is a conditional format?
  • 12.1.2 What is a stoplight format?
• 12.2 About managing conditional formatting
• 12.3 Notes on using conditional formats and stoplight formats
• 12.4 How to activate and deactivate conditional formats and stoplight formats
• 12.5 How to create conditional formats
• 12.6 How to create stoplight formats
• 12.7 How to edit conditional formats and stoplight formats
• 12.8 How to delete conditional formats and stoplight formats
• 12.9 How to change the color of stoplight formats
• 12.10 Examples of conditional formatting
  • 12.10.1 Example 1: Conditional format to highlight Profit SUM values greater than 30000
  • 12.10.2 Example 2: Stoplight format to categorize Profit SUM values on a table worksheet
  • 12.10.3 Example 3: Stoplight format to categorize hidden Profit SUM values on a crosstab worksheet

13 Using conditions

• 13.1 What are conditions?
• 13.2 What are multiple conditions?
• 13.3 What are nested conditions?
• 13.4 About using conditions
• 13.5 About applying more than one condition
• 13.6 How to activate and deactivate existing conditions
• 13.7 How to create single conditions
• 13.8 How to create multiple conditions
• 13.9 How to create nested conditions
• 13.10 How to edit conditions
• 13.11 How to delete conditions
• 13.12 Notes on how Discoverer applies conditions to roll-ups
• 13.13 Example of how Discoverer applies conditions to roll-ups
• 13.14 Examples of conditions

14 Using totals

• 14.1 What are totals?
• 14.2 About totals on worksheets
• 14.3 About SUM and Cell SUM
• 14.4 When to use SUM instead of Cell SUM
  • 14.4.1 Example - using SUM to calculate the average sales per employee
• 14.5 When to use Cell SUM instead of SUM
  • 14.5.1 Example - using Cell SUM to calculate an increase in sales
• 14.6 About migrating workbook totals to Oracle BI Discoverer
• 14.7 What are aggregated values in Discoverer
• 14.8 What are linear and non-linear totals
• 14.9 How to display or hide totals
• 14.10 How to create totals
• 14.11 How to edit totals
• 14.12 How to delete totals
• 14.13 Examples of totals
• 14.14 Examples of worksheet aggregation in Discoverer
  • 14.14.1 Example 1: Example of a Rank calculation using an Oracle9i or later database
  • 14.14.2 Example 2: Example showing how Discoverer does not aggregate repeated values using an Oracle9i or later database

15 Using percentages

• 15.1 What are percentages?
• 15.2 How to display or hide percentages
• 15.3 How to create percentages
• 15.4 How to edit percentages
• 15.5 How to delete percentages
• 15.6 Example of percentages

16 Sorting data

• 16.1 What is sorting?
• 16.2 About sorting on table worksheets
• 16.3 About sorting on crosstab worksheets
• 16.4 What is group sorting?
• 16.5 How to sort data on a table worksheet
• 16.6 How to sort data on a crosstab worksheet
• 16.7 How to change how worksheet data is sorted
• 16.8 How to remove sorting from a worksheet
• 16.9 Examples of sorting

17 Using calculations

• 17.1 What are calculations?
• 17.2 About using calculations
• 17.3 What are analytic functions?
• 17.4 What analytic function templates are available in Discoverer?
• 17.5 How to display or hide worksheet calculations
• 17.6 How to create calculations
• 17.7 How to create a calculation using an analytic function template
• 17.8 How to edit calculations
• 17.9 How to delete calculations
• 17.10 Examples of calculations

Part III Sharing results with others

18 Printing worksheets and graphs

• 18.1 About printing in Discoverer Plus Relational
• 18.2 About elements of a printed worksheet in Discoverer Plus Relational
• 18.3 What are Page Setup options
• 18.4 About Page Setup options for graphs
• 18.5 How to change Page Setup options for a worksheet
• 18.6 How to print Discoverer workbooks
• 18.7 How to print Discoverer worksheets
• 18.8 How to print Discoverer workbooks and worksheets to PDF files

19 Exporting data to other applications

• 19.1 About exporting Discoverer data to other applications
• 19.2 About exporting worksheets to Oracle Reports
• 19.3 About exporting worksheets to Microsoft Excel
• 19.4 About exporting worksheets to Microsoft Excel Web Query format
  • 19.4.1 About Discoverer support for Microsoft Excel Web Query format
  • 19.4.2 About Microsoft Excel Web Query format and Discoverer security
• 19.5 About how worksheets and graphs are exported
• 19.6 About exporting worksheets that contain page items
• 19.7 About accessing exported files
• 19.8 How to export Discoverer data

20 Sharing workbooks

• 20.1 About sharing workbooks
• 20.2 About sharing workbooks and security
• 20.3 How to share workbooks
  • 20.3.1 How to share the currently open workbook with other Discoverer users
  • 20.3.2 How to share multiple workbooks with a single Discoverer user

Part IV Advanced Discoverer Plus Relational features

21 Advanced Discoverer Plus Relational Features

• 21.1 Using scheduled workbooks
  • 21.1.1 What are scheduled workbooks?
  • 21.1.2 When to use scheduled workbooks
  • 21.1.3 An example of using a scheduled workbook
  • 21.1.4 About accessing scheduled workbook results sets
  • 21.1.5 About how scheduled workbooks are processed
  • 21.1.6 About scheduled workbooks and aggregation
    • 21.1.6.1 Non-aggregable values are not displayed for scheduled workbooks
    • 21.1.6.2 ALL page item value is missing for tabular worksheets
    • 21.1.6.3 How to disable Enhanced Data Aggregation
  • 21.1.7 How to schedule workbooks
  • 21.1.8 How to edit scheduled workbooks
  • 21.1.9 How to copy a scheduled workbook
  • 21.1.10 How to unschedule a scheduled workbook
  • 21.1.11 How to delete scheduled workbooks and scheduled workbook results sets
• 21.2 Using lists of values (LOVs)
  • 21.2.1 What is a list of values (LOV)?
  • 21.2.2 LOV examples
    • 21.2.2.1 A LOV used to specify worksheet parameters
    • 21.2.2.2 A LOV used in a condition
    • 21.2.2.3 A LOV used in the Discoverer item navigator
  • 21.2.3 About using long LOVs
    • 21.2.3.1 How to select single values from long LOVs
    • 21.2.3.2 How to select multiple values from long LOVs
• 21.3 Changing default settings
  • 21.3.1 About default Discoverer settings
  • 21.3.2 How to change default Discoverer settings
  • 21.3.3 How to revert to the default format settings
  • 21.3.4 How to change default worksheet formats
  • 21.3.5 Notes on setting Advanced options
    • 21.3.5.1 About automatic querying
    • 21.3.5.2 About fan traps
    • 21.3.5.3 About multiple join paths
• 21.4 Using SQL
  • 21.4.1 What is SQL
  • 21.4.2 Why should I be interested in SQL?
  • 21.4.3 What are summaries?
  • 21.4.4 What are summary folders
  • 21.4.5 What is an execution plan?
  • 21.4.6 About the Discoverer execution plan
  • 21.4.7 How to view SQL
  • 21.4.8 How to view a SQL execution plan

Part V Discoverer Plus Relational Reference

22 Dialog reference

• Available Items pane
• Available Items pane: Calculations tab
• Available Items pane: Conditions area
• Available Items pane: Items tab
• Band by Rank dialog
• Band by Value dialog
• Calendar dialog
• Choose a responsibility dialog
• Color Chooser dialog
• Color Palette Editor dialog
• Choose Worksheet dialog
• Column Width dialog
• Conditional Formats dialog
• Confirm Threshold dialog
• Data Format dialog (Default Options)
• Data/graph pane
• Delete Scheduled Workbook Results dialog
• Delete Workbooks from Database dialog
• Difference dialog
• Drill dialog
• Edit Calculation dialog
• Edit Condition dialog
• Edit Graph dialog: Font dialog tab
• Edit Graph dialog: Legend tab
• Edit Graph dialog: Pie Chart Options tab (column)
• Edit Graph dialog: Pie Chart Options tab (row)
• Edit Graph dialog: Plot Area tab
• Edit Graph dialog: Style tab
• Edit Graph dialog: Titles, Totals, and Series tab
• Edit Graph dialog: Type tab
• Edit Graph dialog: X-Axis tab
• Edit Graph dialog: Y-Axis tab
• Edit Heading dialog
• Edit Parameter dialog
• Edit Parameter Values dialog
• Edit Percentage dialog
• Edit Text Area dialog
• Edit Title dialog
• Edit Total dialog
• Edit Worksheet dialog
• Edit Worksheet dialog: Crosstab Layout tab
• Edit Worksheet dialog: Parameters tab
• Edit Worksheet dialog: Select Items tab
  • Edit Worksheet dialog: Select Items tab: Items tab
  • Edit Worksheet dialog: Select Items tab: Conditions tab
  • Edit Worksheet dialog: Select Items tab: Calculations tab
• Edit Worksheet dialog: Sort tab
• Edit Worksheet dialog: Table Layout tab
• Edit Worksheet dialog: Worksheet Layout tab
• Export Log dialog
• Export Wizard dialog: Format and Name page
• Export Wizard dialog: Graph page
• Export Wizard dialog: Page Item Prompts page
• Export Wizard dialog: Parameter Prompts page
• Export Wizard dialog: Select page
• Export Wizard dialog: Supervise page
• Find dialog (in Item Navigator)
• Find dialog (in Worksheet)
• Following Value dialog
• Format dialog
• Format Data dialog
• Format Data dialog: Breaks tab
• Format Data dialog: Date tab
• Format Data dialog: Format tab
• Format Data dialog: Number tab
• Format Data dialog: Text tab
• Format heading dialog
• Group Total dialog
• Header/Footer Font dialog
• Heading Format dialog (Default Options)
• Join folders dialog
• Manage Links dialog
• Manage Workbooks dialog
• Move Worksheets dialog
• Moving Total dialog
• New Calculation dialog
• New Condition dialog
• New Conditional Format dialog
• New Link dialog
• New Parameter dialog
• New Percentage dialog
• New Scheduled Workbook Results dialog
• New Stoplight Format dialog
• New Total dialog
• Open Workbook from Database dialog
• Options dialog: Advanced tab
• Options dialog: EUL tab
• Options dialog: Formats tab
• Options dialog: General tab
• Options dialog: Query Governor tab
• Options dialog: Sheet tab
• Page Setup dialog
• Page Setup dialog: Graph tab
• Page Setup dialog: Header/Footer tab
• Page Setup dialog: Margins tab
• Page Setup dialog: Table/Crosstab tab
• Page Setup dialog: Worksheet tab
• Parameters dialog
• Percent Contribution dialog
• Percent Difference dialog
• Percent Rank dialog
• Percent Running Contribution dialog
• Preceding Value dialog
• Print dialog
• Print Preview dialog
• Rank dialog
• Reference Lines dialog
• Running Total dialog
• Save Workbook to Database dialog
• Schedule Wizard dialog
• Schedule Wizard dialog: General tab
• Schedule Wizard dialog: Schedule tab
• Scheduling Manager dialog
• Select Condition dialog
• Select Item dialog
• Select item to drill dialog
• Selected items pane
• Select Parameter dialog
• Select User dialog
• Select Value dialog
• Select Values dialog
• Select Workbook from Database dialog
• Share Workbooks dialog: User -> Workbook tab
• Share Workbooks dialog: Workbook -> User tab
• Show Condition dialog
• Sort Crosstab dialog
• Stoplight colors dialog
• Text area
• Title area
• Total Format dialog (Default Options)
• Workbook Properties dialog
• Workbook Wizard: Create/Open Workbook dialog
• Worksheet Properties dialog: Aggregation tab
• Worksheet Properties dialog: General tab
• Worksheet Properties dialog: Sheet/Crosstab Format tab

A Discoverer calculation examples

• A.1 How to find more information about Oracle analytic functions
• A.2 About the examples in this chapter
• A.3 How do I create calculations?
• A.4 About using parameters to provide dynamic input to calculations
• A.5 Simple calculation examples
• A.6 Oracle analytic function examples
  • A.6.1 About analytic function categories
  • A.6.2 About analytic functions and drilling into and out of data
  • A.6.3 About creating analytic functions
  • A.6.4 Ranking function examples
    • A.6.4.1 About ranking
    • A.6.4.2 Example: Assign ranks to sales figures
    • A.6.4.3 Example: Assign ranks to sales figures within region
    • A.6.4.4 Example: Show the top three selling cities per region
    • A.6.4.5 Example: Show the top three and bottom three selling cities per region
  • A.6.5 Banding function examples
    • A.6.5.1 About banding
    • A.6.5.2 Example: Banding by value (1)
    • A.6.5.3 Example: Banding by value (2)
    • A.6.5.4 Example: Banding by rank
  • A.6.6 Windowing function examples
    • A.6.6.1 About windowing
    • A.6.6.2 Example: Calculate a three month moving sales average
    • A.6.6.3 Example: Show the cumulative values of sales
    • A.6.6.4 Example: Compare sales figures across time using windowing
  • A.6.7 Reporting function examples
    • A.6.7.1 About reporting functions
    • A.6.7.2 Example: Calculate annual sales
    • A.6.7.3 Example: Calculate annual sales by region
    • A.6.7.4 Example: Calculate percentage of annual sales by region
    • A.6.7.5 Example: Calculate city sales as a percentage of total sales
  • A.6.8 LAG/LEAD function examples
    • A.6.8.1 About LAG/LEAD functions
    • A.6.8.2 Example: Compare sales figures across time using LAG/LEAD
    • A.6.8.3 Example: Calculate sales growth across time
    • A.6.8.4 Example: Rank sales growth
  • A.6.9 Statistical function examples
    • A.6.9.1 About statistics functions
    • A.6.9.2 Example: Calculate linear regression
  • A.6.10 More about analytic functions expressions
  • A.6.11 About analytic functions and sequencing
  • A.6.12 Examples of sequencing
    • A.6.12.1 Example of applying single conditions on analytic function items
    • A.6.12.2 Example of applying multiple conditions on analytic function items
  • A.6.13 About inverse percentile examples
  • A.6.14 About differences between PERCENTILE_CONT and PERCENTILE_DISC
    • A.6.14.1 Example: Compute the median profit using the PERCENTILE_DISC function
    • A.6.14.2 Example: Compute the median profit using the PERCENTILE_CONT function
  • A.6.15 Hypothetical rank and distribution examples
    • A.6.15.1 Example: Calculate hypothetical rank
  • A.6.16 Banding example
    • A.6.16.1 Example: Producing equi-width bands using WIDTH_BUCKET
  • A.6.17 FIRST/LAST aggregate examples
    • A.6.17.1 Example: Find the largest sales transactions in area with most sales trans'
    • A.6.17.2 Example: Find the average sales transaction in area with least sales trans'
• A.7 Examples of using row-based and time-based intervals
  • A.7.1 Example: Creating a Difference calculation using a row-based interval
  • A.7.2 Example: Creating a Difference calculation using a time-based interval
  • A.7.3 Example: Creating a Preceding value calculation using a time-based interval

B Discoverer support for Oracle Applications

• B.1 What are Oracle Applications?
• B.2 What features does Discoverer support for Oracle Applications users?
• B.3 What are the prerequisites to run Discoverer with Oracle Applications?
• B.4 About using indexes and values in parameters
• B.5 How to start Discoverer in Oracle Applications mode using an existing connection

Glossary

Index