Discoverer Tips & Tricks

The table below will provide you with a summary of the main headings for the tips and tricks available for Discoverer. You may also navigate below to each listing which will be broken down with specifics items for that title or category.

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Related Documents
• Conventions
• JGoodies License Agreement

Part I: Getting started

1. Introducing Oracle Business Intelligence Discoverer

• What is OracleBI Discoverer?
• Why should I use OracleBI Discoverer?
• How do I access information using OracleBI Discoverer?
• What is a typical workflow with OracleBI Discoverer?
• • Stage 1: Getting the data you want
  • Stage 2: Analyzing data
  • Stage 3: Sharing data with other people
• What are the different components of OracleBI Discoverer?
• Who is the Discoverer manager and what do they do?

2. Using Discoverer Plus with a relational data source

• What is Discoverer Plus Relational?
• What is a relational data source?
• What are business areas?
• What are folders?
• What are items?
• What is the Discoverer Plus Relational sample database?
• What are the new features in Discoverer Plus 10.1.2.
• Where can I find out more about Discoverer Plus Relational?

3. Using Discoverer Plus with a multidimensional data source

• How can I tell when I am using Discoverer Plus with a multidimensional data source?
• What is Discoverer Plus OLAP?
• What is online analytic processing (OLAP)?
• What are multidimensional data sources?
• What are multidimensional cubes?
• What are measures?
• What are dimensions and dimension members?
• What are dimension hierarchies?
• What are dimension attributes?
• About aggregating and filtering multidimensional data
• About applying filters after aggregating multidimensional data
• About filters and dimensions
• What is the Discoverer Catalog?
• About the Discoverer Plus OLAP Tutorial
• Where can I find out more about Discoverer Plus OLAP?

4. Starting Discoverer

• What is a Discoverer connection?
• About starting Discoverer
• How to display the Discoverer Plus connections page
• How to start Discoverer using an existing connection
• How to start Discoverer by connecting directly
• How to exit Discoverer Plus
• Frequently asked questions
• • What is the End User Layer (EUL)?
  • What are database accounts?
  • How do I create and save login information in a Discoverer connection?
  • How do I edit a Discoverer connection?
  • How do I delete a Discoverer connection?
  • What are public connections?
  • What are user-defined connections?
  • When and why do I need to change my password?
  • How do I change the password for a connection?
  • How do I change the End User Layer for a Discoverer connection?
  • What is Single Sign-on?
  • What is the difference between Single Sign-on and Discoverer passwords?
  • How do I know what Single Sign-on ID is being used in Discoverer Plus?

5. About the Discoverer Plus Relational work area

• About the work area
• About Discoverer's drag and drop features
• • About dragging and dropping items in the Discoverer work area
  • About repositioning items in the Discoverer work area
• About Discoverer menus
• • About the Standard Discoverer menu
  • About Discoverer's right-click menus
• About Discoverer toolbars
• • About the Standard toolbar
  • About the Formatting toolbar
  • About the Graph toolbar
  • About the Available Items toolbar
  • About the Selected Items toolbar
  • About tool tips
• About icons used in the Discoverer item navigator
• What are axis items?
• About data points
• What is the Discoverer Worksheet Wizard?

6. Using workbooks and worksheets

• What are workbooks?
• What are worksheets?
• About Discoverer worksheet types
• About table worksheets
• About crosstab worksheets
• About designing workbooks for maximum performance
• How to open workbooks
• About opening workbooks in a non-Oracle database
• How to edit workbooks
• About saving a workbook to the database
• How to save workbooks
• How to copy workbooks
• How to delete workbooks from the database
• About worksheet titles and text
• How to create or edit worksheet titles or text
• How to display or hide worksheet titles or text
• How to rename worksheets and workbooks
• How to re-order worksheets within a workbook
• How to refresh worksheets
• How to delete worksheets
• How to find data in a worksheet
• About creating new workbooks
• How to create new workbooks
• How to add worksheets to a workbook
• How to view and update a workbook's properties
• About worksheet properties
• How to view and edit worksheet properties

7. Editing worksheets and worksheet items

• About editing worksheets
• How to edit worksheets using the Edit Worksheet wizard
• How to add items to worksheets
• How to create new worksheet items
• How to remove items from worksheets
• How to duplicate a worksheet
• How to change the format of worksheet items
• How to set the currency symbol for numeric items
• How to change the format of worksheet item headings
• How to change the text in item headings

8. Creating graphs in Discoverer

• What is a Discoverer graph?
• About using graphs in Discoverer
• About components of a Discoverer graph
• About graph types and sub-types
• About graph types available in Discoverer
• Notes about creating bubble graphs
• Notes about creating high-low-close stock graphs
• Notes about creating dual-Y graphs
• Notes about creating pie graphs
• How to edit a graph
• How to hide and display a graph
• How to change the position of a graph

Part II. Analyzing data

9. Pivoting data

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

10. Drilling to analyze data

• About drilling in Discoverer worksheets
• What are drill hierarchies?
• How to drill up and down
• • How to drill up and down using a drill icon in worksheet data
  • How to drill up and down using a graph label in graph data
  • How to drill up and down using the Drill dialog
• About drilling to related items
• How to drill to a related item
• • How to drill to a related item using a drill icon
  • How to drill to a related item using the Drill dialog
• About drilling to detail
• How to drill to detail
• About drill links
• • About user defined drill links
  • About drill links defined by the Discoverer manager
  • About using drill links
• How to create drill links
• • How to create drill links to Discoverer worksheets
  • How to create drill links to Internet URLs
• How to use drill links
• • How to drill out to a worksheet or Internet URL using a drill link icon
  • How to drill out to a worksheet or Internet URL using the Drill dialog

11. Using parameters

• What are parameters?
• What are the benefits of using parameters?
• About using parameters
• About creating parameters
• About using parameters to collect dynamic user input
• About filtering lists of parameter values based on selected conditions (cascading parameters)
• How to set parameters
• How to activate parameters
• How to deactivate parameters
• How to create parameters
• How to delete parameters
• Examples of parameters
• • Example 1: Using a parameter to filter a worksheet
  • Example 2: Using a parameter that enables multiple values to filter a worksheet
  • Example 3: Using a parameter to collect dynamic user input

12. Using conditional formatting

• What is conditional formatting?
• • What is a conditional format?
  • What is a stoplight format?
• About managing conditional formatting
• Notes on using conditional formats and stoplight formats
• How to activate and deactivate conditional formats and stoplight formats
• How to create conditional formats
• How to create stoplight formats
• How to edit conditional formats and stoplight formats
• How to delete conditional formats and stoplight formats
• How to change the color of stoplight formats
• Examples of conditional formatting
• • Example 1: Conditional format to highlight Profit SUM values greater than 30000
  • Example 2: Stoplight format to categorize Profit SUM values on a table worksheet
  • Example 3: Stoplight format to categorize hidden Profit SUM values on a crosstab worksheet

13. Using conditions

• What are conditions?
• What are multiple conditions?
• What are nested conditions?
• About using conditions
• About applying more than one condition
• How to activate and deactivate existing conditions
• How to create single conditions
• How to create multiple conditions
• How to create nested conditions
• How to edit conditions
• How to delete conditions
• Notes on how Discoverer applies conditions to roll-ups
• Example of how Discoverer applies conditions to roll-ups
• Examples of conditions

14. Using totals

• What are totals?
• About totals on worksheets
• About SUM and Cell SUM
• When to use SUM instead of Cell SUM
• • Example - using SUM to calculate the average sales per employee
• When to use Cell SUM instead of SUM
• • Example - using Cell SUM to calculate an increase in sales
• About migrating workbook totals to OracleBI Discoverer
• What are aggregated values in Discoverer
• What are linear and non-linear totals
• How to display or hide totals
• How to create totals
• How to edit totals
• How to delete totals
• Examples of totals
• Examples of worksheet aggregation in Discoverer
• • Example 1: Example of a Rank calculation using an Oracle9i database
  • Example 2: Example showing how Discoverer does not aggregate repeated values using an Oracle9i database

15. Using percentages

• What are percentages?
• How to display or hide percentages
• How to create percentages
• How to edit percentages
• How to delete percentages
• Example of percentages

16. Sorting data

• What is sorting?
• About sorting on table worksheets
• About sorting on crosstab worksheets
• What is group sorting?
• How to sort data on a table worksheet
• How to sort data on a crosstab worksheet
• How to change how worksheet data is sorted
• How to remove sorting from a worksheet
• Examples of sorting

17. Using calculations

• What are calculations?
• About using calculations
• What are analytic functions?
• What analytic function templates are available in Discoverer?
• How to display or hide worksheet calculations
• How to create calculations
• How to create a new calculation using an analytic function template
• How to edit calculations
• How to delete calculations
• Examples of calculations

Part III. Sharing results with others

18. Printing worksheets and graphs

• About printing in Discoverer Plus Relational
• About elements of a printed worksheet in Discoverer Plus Relational
• What are Page Setup options
• About Page Setup options for graphs
• How to change Page Setup options for a worksheet
• How to print Discoverer workbooks
• How to print Discoverer worksheets
• How to print Discoverer workbooks and worksheets to PDF files

19. Exporting data to other applications

• About exporting Discoverer data to other applications
• About exporting worksheets to Oracle Reports
• About exporting worksheets to Microsoft Excel
• • About using Internet Explorer v6.0 when exporting to Microsoft Excel format
• About exporting worksheets to Microsoft Excel Web Query format
• • About Discoverer support for Microsoft Excel Web Query format
  • About Microsoft Excel Web Query format and Discoverer security
• About how worksheets and graphs are exported
• About exporting worksheets that contain page items
• About accessing exported files
• How to export Discoverer data

20. Sharing workbooks

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

21. Publishing worksheets in OracleAS Portal

• What is OracleAS Portal?
• What is Discoverer Portlet Provider?
• What are the different types of Discoverer portlet?
• • About the Discoverer List of Worksheets portlet
  • About the Discoverer Worksheet portlet
  • About the Discoverer Gauges portlet
• What you need to publish Discoverer content in OracleAS Portal
• About creating Discoverer portlets
• About publishing Discoverer content in portlets
• About choosing database connections for Discoverer portlets
• About choosing refresh options for Discoverer portlets
• About using worksheet parameters in Discoverer portlets
• About using OracleAS Portal as a personalization framework for Discoverer
• How to connect to OracleAS Portal and display the Add Portlets page
• How to add a Discoverer List of Worksheets portlet to an Oracle Portal page
• How to add a Discoverer Worksheet portlet to an Oracle Portal page
• How to add a Discoverer Gauges portlet to an Oracle Portal page
• How to edit a Discoverer portlet
• How to customize a Discoverer portlet
• How to customize the Discoverer worksheet in a portlet
• How to map worksheet parameters to Page Parameters in Oracle Portal
• • How to map worksheet parameters to Generic Page Parameters
  • How to create a Page Parameter
  • How to map a Generic Page Parameter to a Page Parameter

Part IV Advanced Discoverer Plus Relational features

22. Advanced Discoverer Plus Relational Features

• Using scheduled workbooks
• • 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
  • 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
• Using lists of values (LOVs)
• • What is a list of values (LOV)?
  • LOV examples
  • • A LOV used to specify worksheet parameters
    • A LOV used in a condition
    • A LOV used in the Discoverer item navigator
  • About using long LOVs
  • • How to select single values from long LOVs
    • How to select multiple values from long LOVs
• Changing default settings
• • 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
  • • About automatic querying
    • About fan traps
    • About multiple join paths
• Using SQL
• • What is SQL
  • Why should I be interested in SQL?
  • What are summaries?
  • What are summary folders
  • What is an execution plan?
  • About the Discoverer execution plan
  • How to view SQL
  • How to view a SQL execution plan

Part V. Discoverer Plus Relational Reference

23. 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

• How to find more information about Oracle analytic functions
• About the examples in this chapter
• How do I create calculations?
• About using parameters to provide dynamic input to calculations
• Simple calculation examples
• Oracle analytic function examples
• • About analytic function categories
  • About analytic functions and drilling into and out of data
  • About creating analytic functions
  • Ranking function examples
  • • About ranking
    • Example: Assign ranks to sales figures
    • Example: Assign ranks to sales figures within region
    • Example: Show the top three selling cities per region
    • Example: Show the top three and bottom three selling cities per region
  • Banding function examples
  • • About banding
    • Example: Banding by value (1)
    • Example: Banding by value (2)
    • Example: Banding by rank
  • Windowing function examples
  • • About windowing
    • Example: Calculate a three month moving sales average
    • Example: Show the cumulative values of sales
    • Example: Compare sales figures across time using windowing
  • Reporting function examples
  • • About reporting functions
    • Example: Calculate annual sales
    • Example: Calculate annual sales by region
    • Example: Calculate percentage of annual sales by region
    • Example: Calculate city sales as a percentage of total sales
  • LAG/LEAD function examples
  • • About LAG/LEAD functions
    • Example: Compare sales figures across time using LAG/LEAD
    • Example: Calculate sales growth across time
    • Example: Rank sales growth
  • Statistical function examples
  • • About statistics functions
    • Example: Calculate linear regression
  • More about analytic functions expressions
  • About analytic functions and sequencing
  • Examples of sequencing
  • • Example of applying single conditions on analytic function items
    • Example of applying multiple conditions on analytic function items
  • About inverse percentile examples
  • About differences between PERCENTILE_CONT and PERCENTILE_DISC
  • • Example: Compute the median profit using the PERCENTILE_DISC function
    • Example: Compute the median profit using the PERCENTILE_CONT function
  • Hypothetical rank and distribution examples
  • • Example: Calculate hypothetical rank
  • Banding example
  • • Example: Producing equi-width bands using WIDTH_BUCKET
  • FIRST/LAST aggregate examples
  • • Example: Find the largest sales transactions in area with most sales trans'
    • Example: Find the average sales transaction in area with least sales trans'
• Examples of using row-based and time-based intervals
• • Example: Creating a Difference calculation using a row-based interval
  • Example: Creating a Difference calculation using a time-based interval
  • Example: Creating a Preceding value calculation using a time-based interval

B. Discoverer support for Oracle Applications

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

Glossary

Index