This module describes how to use Oracle Business Intelligence Discoverer Viewer (OracleBI Discoverer Viewer) to open and view relational workbooks created by using Discoverer Plus or Discoverer Desktop. This module also describes how you can perform operations, such as sorting, pivoting, formatting, drilling, stoplighting, and exporting on worksheets.
Place the cursor on this icon to display all the screenshots in the tutorial.
You can also place the cursor on each individual icon in the following steps
to see only the screenshot that is associated with that step.
In order for this lessons to work successfully, the OracleBI Sample workbooks must be installed.
The OracleBI Sample workbooks help you learn how to use the product, and this lessons illustrate key features of OracleBI Discoverer Viewer with the help of these sample workbooks. If the Sample workbooks are not yet installed, follow the instructions found here.
To begin with, you must connect to Discoverer Viewer. Discoverer Viewer has a common connections page to access relational and OLAP data sources.
| 1. |
In the Web browser, enter the URL for Discoverer Viewer. The URL is in the following format: http://<hostname>.<domain>:<port>/discoverer/viewer
The Connect to OracleBI Discoverer page appears. Note: Consult with the Discoverer manager for the correct URL. An example of the URL can be the following: http://bi-server.mycompany.com:7778/discoverer/plus
|
| 2. |
From the Connect To drop-down list, you can select the following options based on whether you want to connect to a standard Discoverer End User Layer (EUL), an Oracle Applications EUL, or an OLAP catalog, respectively.
|
|
|
|
|
|
|
| 3. |
If you want to connect to a standard Discoverer EUL, you must specify the username, password, database connect string, the EUL name, and the locale.
|
| 4. |
If you want to connect to an Oracle Applications EUL, you must specify the username, password, database connect string, EUL name, and Oracle Applications responsibility.
|
| 5. |
If you want to connect to an OLAP catalog to perform multidimensional analysis, you must specify the username, password, database connect string, and locale.
|
| 6. |
Select OracleBI Discoverer from the Connect To drop-down list and enter the following as shown in the image: username - bi_user password - bi_user Database - As appropriate EUL - BI_USER (Note: The EUL name should be entered in uppercase letters.)
|
| 7. |
Click Go to connect.
|
|
Opening a database workbook | |
|
Opening a scheduled workbook | |
|
Opening a shared scheduled workbook | |
| 1. |
Select Database Workbooks from the Search drop-down list and then enter Profits in the text box next to it. Click Go.
This displays a list of workbooks with names that contain Profits.
|
| 2. |
To display a list of worksheets that a workbook contains, you click the
plus(
The workbook has four worksheets in it.
|
| 3. |
You can focus in on a workbook to list only that workbook along with
the list of worksheets it contains, on the Worksheet List page. To focus
in on a workbook, click the
|
| 4. |
To open a worksheet, click the
The Query Progress page appears indicating that the query is in progress, and finally the worksheet opens. Click the Workbooks link to go back to the Worksheet List page. |
You can schedule long-running reports to run overnight or at a time when there is relatively low network and resource usage. You can also schedule workbooks to run repetitively. For example, you can schedule a workbook to run every Sunday at 12 midnight, so that the report is ready when you come on Monday morning.
| 1. |
To open a scheduled workbook, select Scheduled Workbooks from the Search drop-down list, and then click Go.
A list of all the scheduled workbooks is displayed. |
| 1. |
You can share the results of a scheduled workbook with another user by using Discoverer Plus. After the scheduled workbook is ready with the results, it is also displayed on the Worksheet List page along with other workbooks. In the screenshot, the Sales & Profits by Time & Geography - Scheduled workbook has been scheduled and shared as is clear from the owner's name.
|
Items in a worksheet are placed on the Page axis, Top axis, or the Side axis. You can pivot the worksheet items to any of these axes depending on how you want the data to be displayed.
| 1. |
Select All Workbooks, and then click Go.
|
| 2. |
Click the
|
| 3. |
Click the Regional Sales & Profits by Year worksheet to open it.
|
| 4. |
The worksheet displays the Sales Revenue and Profit for each region per year. You want to view the same information for all years per region. To do this, you can move the Region item to the page axis and the Year item to the top axis. To pivot the items, click Layout.
|
| 5. |
Click More to display the Table Layout page.
|
| 6. |
Click the
Click Apply. The worksheet now displays the Sales Revenue and Profit data for all years per Region.
|
| 7. |
Select Europe from the Region drop-down list to view the Sales Revenue and Profit data for Europe.
|
Numeric items in a worksheet with tabular layout can be sorted in ascending or descending order. Character data can be sorted in alphabetical order.
| 1. |
Click Layout, and then move the Region item to the top axis on the right of the Year item. (Select Move > Region > Left of Sales Revenue SUM > Go). To sort worksheet items, click Sort. Click More to display the Multiple Sort page. Select Year from the Column Header drop-down in the First Sort group of drop-down lists. Select A - Z as the sort order. Select Group under Sort Type.
|
| 2. |
In the Second Sort group of drop-down lists, select Sales Revenue SUM from the Column Header drop-down list and Descending from the Sort Order drop-down list.
Click Apply. From the worksheet data, you can infer that the Americas region has the largest Sales Revenue SUM value for all the years.
|
You can apply different formatting to worksheet items or values of worksheet items to highlight them in a worksheet. Formatting includes applying bold, italic, or underline styles and changing background or font color.
| 1. |
Click Format to display the formatting toolbar.
|
| 2. |
The formatting toolbar appears.
To format an item, select the item from the worksheet, and then click the appropriate button on the formatting toolbar. To select an item, click any of the values of that item.
|
| 3. |
Click inside the Sales Revenue SUM and the Profit SUM columns
while pressing and holding the Shift key to select both columns.
Now click
|
| 4. |
The values in the Sales Revenue SUM and Profit SUM columns appear in bold.
Similarly, you can change the background color and font color.
|
Applying conditional formatting to items in the worksheet
enables you to display and easily identify exception data. When applied on an
item, the item is formatted as specified.
Stoplight formatting enables you to quickly and easily define thresholds that
automatically format worksheet data to indicate whether a particular value falls
into the Desirable, Acceptable, or Unacceptable ranges established by the thresholds
defined.
| 1. |
On the formatting toolbar, click Create Conditional Format.
|
| 2. |
The Create Conditional Cell Format page appears. Specify Profit SUM Format as the name for the conditional format. Under Selections, select Profit SUM from the Item drop-down list. Select Greater Than (>) from the Operator drop-down list. Enter 1500000 in the value field.
|
| 3. |
Under Format, select a background color and a font color from the respective color palette.
Click Apply.
|
| 4. |
The Profit SUM values that are greater than the specified value appear with the formatting specified.
|
| 5. |
You can also apply stoplight formatting to categorize the worksheet values into Desirable, Acceptable, or Unacceptable values. Click Stoplight from the Tools section.
|
| 6. |
The Stoplight toolbar appears. Select Sales Revenue SUM from the Format drop-down list.
|
| 7. |
In the Unacceptable field, enter 1000000.
|
| 8. |
In the Desirable field, enter 9500000.
|
| 9. |
Click Go. The values in the Sales Revenue SUM column are formatted as Unacceptable, Acceptable, or Desirable.
|
| 10. |
Save the workbook. To save the workbook, click Save under Actions.
To save the workbook under a different name, you can click Save As. After you have made changes to a worksheet, if you want to restore the original copy, click Revert to saved. |
You can dynamically filter data in a worksheet by creating parameters in Discoverer Plus. When you open the worksheet in Discoverer Viewer, you are prompted to select a value for the parameter. You can also set up cascading parameters, wherein the available values for the second parameter depend on the values that you select for the first parameter. For example, if you have two parameters (one for Region and another for Country), you can set up the parameters in such a way that the list of countries that are available will be based on the value that was selected for the parameter set for Region.
| 1. |
Open the Profit Margins by Country worksheet.
The Parameters Needed page appears.
|
| 2. |
Click the
|
| 3. |
Click the
Click Cancel.
|
| 4. |
Click the
Click Select.
|
| 5. |
Now click the
Click Cancel.
|
| 6. |
Select Americas as the value for the region parameter and Canada, United States of America as the value for the country parameter.
|
| 7. |
Click Go. The worksheet displays data for the countries Canada and United States of America in the Americas region.
|
You can perform drill operations in a worksheet to see detailed or summarized data. You can drill down on an item in a worksheet to see the next level of data. For example, you can drill down from the Year item to display quarterly information. To see summarized data, you can drill up from an item to one level above it. For example, you can drill up from Product Subcategory to Product Category to display summarized information for product categories. Drill hierarchies need to be defined by the Discoverer manager in the EUL, for you to perform Drill operations.
| 1. |
Open the Regional Profit Margins worksheet by selecting the worksheet name from the Worksheets list.
|
| 2. |
Swap the Region and Year items. To do this, click Layout. Select Swap > Year > With Region (Americas,...).
Click Go.
|
| 3. |
The worksheet now displays the Sales, Profit, and Profit Margin information for the Americas Region for all years.
You can see that the Profit Margin gradually decreased through the years 1998-2000. But increased in the year 2001. Now you may want to drill down to display quarterly information.
|
| 4. |
Click
The worksheet now displays quarterly information also. From the worksheet data, you can identify that there was an increase of around 9% in Profit Margin from Q4 2000 to Q1 2001.
|
| 5. |
Now you may want to analyze how each channel of sales has performed over
these years. You can drill to the Channel Class item from Quarter, even
though there is no hierarchy created to satisfy such a drill. This feature
is called "drill to a related item". Click the
The worksheet now displays Channel information also.
|
| 6. |
Not all rows are displayed because the current setting allows only 25 rows and 25 columns to be displayed. To display all the rows, you need to increase the rows displayed per page. To do this, click the Rows and Columns link under Tools. Enter 100 as the value for Rows, and then click Go.
|
| 7. |
Now swap Quarter and Channel Class items. To do this, click the Layout link under Tools, and then select Move > Channel Class > Left of Quarter. Click Go.
Now you can analyze the profit generated through each sales channel for each quarter of every year.
|
| 8. |
Observe the values for the year 2001. Compared with that in the previous years, the Others channel has shown an increased contribution for this year. This is more evident if you drill up from Quarter to Year.
|
| 9. |
Click the
|
| 10. |
Now the worksheet displays yearly information about performance of each channel. Clearly, the Others channel showed an increase in growth by around 6% between the years 2000 and 2001.
Save the workbook.
|
The visual presentation of data helps end users to easily see trends and abnormalities in their data. In addition to providing a powerful and visual representation of the data from your database, graphs also provide a quick and easy way to analyze data over time and over different geographies.
| 1. |
Open the Sales & Profits by Time & Geography workbook. Open the Sales Vs Profits by Year worksheet.
This worksheet displays Sales Revenue SUM and Profit SUM for each region for all years.
|
| 2. |
Scroll down to display the graph, and then select Americas for the Region page item. The graph displays the Sales Revenue SUM and Profit SUM for Americas region.
|
| 3. |
Now, you may want to drill down to quarterly information for the year 2001. Click the year 2001 on the X-axis of the graph.
The graph now displays quarterly information about Sales and Profits for the year 2001.
|
| 4. |
In the worksheet, click the
The worksheet now shows quarterly sales and profits information for each channel.
|
| 5. |
Drill and replace Quarter with Year. Click the
The worksheet and graph now display yearly sales and profit information for all channels.
|
| 6. |
You can also change the graph type and other settings for the graph. Click Graph under Tools.
The graph toolbar appears.
|
| 7. |
Select Bar as the graph type, Dual-Y Bar as the graph subtype, and click Go.
|
| 8. |
Deselect the 3D Effect checkbox.
|
| 9. |
Enter 600 as the graph width and 500 as the graph height, and then click Go.
|
| 10. |
Compare the bars for 2000, Others and 2001, Others.
|
Discoverer Viewer enables you to print your worksheets and graphs with high fidelity and control. You can use Discoverer Viewer's print options and page setup to specify how your worksheet should print. A preview of the worksheet as it would be printed helps you to change your print options. Discoverer Viewer utilizes the popular Adobe Acrobat ® PDF format to generate a high fidelity output of your worksheet, which you can then print.
| 1. |
Click Printable Page under Actions.
The Printable Page Options page appears.
|
| 2. |
Click Preview Sample to preview the page to be printed.
This generates a sample PDF document.
|
| 3. |
Click Open PDF to view sample. You need to have Acrobat Reader installed on your machine to see the sample PDF.
|
| 4. |
You can change the print and page settings by clicking Print Options.
The Printable Page Options page appears.
|
| 5. |
Click Page Setup.
You can change settings related to the page size, page margins, scaling, and column width here.
|
| 6. |
After you have changed the setting according to your requirements, click Printable PDF.
|
| 7. |
The PDF file is generated. Click Click to view or save. The PDF file appears in a different window.
Click File > Print to print the PDF file.
|
| 8. | Click Return to worksheet to close the preview page. |
You can export a Discoverer worksheet to numerous formats. Some of the common formats are HTML and Microsoft Excel. In addition, there are two new formats that you can export a Discoverer worksheet to: Portable Document Format (.pdf) and Web Query for Microsoft Excel 2000+ (.iqy).
| 1. |
To export to HTML format, click Export under Actions.
The Choose Export Type page appears, which lists all the formats that you can export a Discoverer worksheet to.
|
| 2. |
Select Hyper-Text Markup Language (archived) (*.zip) from the "Use the drop-down list to specify the export file format." list.
Click Export.
|
| 3. |
The Export Ready page appears. Click the Click to view or save button.
An .htm file and a .png --image-- file open. The worksheet is exported as an .htm file and the graph is exported as a .png file.
Click Return to worksheet to close the export dialog box.
|
There are two ways in which you can export a Discoverer worksheet to Excel format. You can export a table or a crosstab worksheet to Excel format. Also, you can export a crosstab worksheet to Microsoft Excel Pivot Table format. Exporting to Microsoft Excel Pivot format creates an Excel Pivot table that preserves the layout in your Discoverer worksheet. You can then toggle values of the page items and pivot the items by modifying the pivot table.
| 1. |
To export to Excel format, click Export under Actions.
The Choose Export Type page appears, which lists all the formats that you can export a Discoverer worksheet to.
|
| 2. |
Select Microsoft Excel Workbook (*.xls) from the "Use the drop-down list to specify the export file format." List
Click Export.
|
| 3. |
The Export Ready page appears. Click the Click to view or save button.
The worksheet is exported to Excel format, and it opens in your browser window.
|
| 4. |
Click Return to worksheet to close the Export Wizard.
|
| 5. |
Now click Export, under Actions.
The Choose Export Type page appears, which lists all the formats that you can export a Discoverer worksheet to.
|
| 6. |
Select Microsoft Excel Workbook with Pivot Table (*.xls) from the "Use the drop-down list to specify the export file format." List
Click Export.
|
| 7. |
The Export Ready page appears. Click the Click to view or save button.
The worksheet is exported to Excel format, and it opens in your browser window.
|
| 8. |
The Excel workbook that is displayed has three worksheets. Click the Sales Vs Profits by Year_Base0 worksheet.
This worksheet contains the entire data of the Discoverer query.
|
| 9. |
Now Click the Sales Vs Profits by Year worksheet. This worksheet contains the Excel Pivot table with the same layout as the Discoverer worksheet you exported.
|
| 10. |
Click the Region drop-down list. All the values of the Region item are displayed. Select Americas from the list, and then click OK.
The worksheet displays the data for the Americas region.
|
| 11. |
Now click the Sales Vs Profits by Year_Static worksheet.
This worksheet is the current snapshot of the Discoverer worksheet at the time of exporting. |
Exporting a Discoverer worksheet to portable document format (PDF) enables high fidelity printing of Discoverer worksheets and graphs.
| 1. |
To export to PDF format, click Export under Actions.
The Choose Export Type page appears, which lists all the formats that you can export a Discoverer worksheet to.
|
| 2. |
Select Portable Document Format (PDF) (*.pdf) from the "Use the drop-down list to specify the export file format." List
Click Export.
|
| 3. |
The Export Ready page appears. Click the Click to view or save button.
The worksheet is exported to PDF format, and it opens in your browser window. You must have the Acrobat Reader plug-in installed to view the exported PDF document. Observe that the formatting of the Discoverer worksheet is retained as it is.
|
| 4. |
Click Return to worksheet to close the Export Wizard.
|
Microsoft Excel Web Query format is an external data format in Microsoft Excel that enables you to dynamically include data from an Internet or intranet URL (for example, a Discoverer worksheet) in a Microsoft Excel worksheet. You can export your Discoverer worksheet to Microsoft Excel Web Query format. When you open the exported file in Microsoft Excel, it uses a reference to the Discoverer worksheet so that the data can be refreshed automatically in the Excel spreadsheet.
| 1. |
To export to Excel Web Query format, click Export under Actions.
The Choose Export Type page appears, which lists all the formats that you can export a Discoverer worksheet to.
|
| 2. |
Select Web Query for Microsoft Excel 2000+ (*.iqy) from the "Use the drop-down list to specify the export file format." List
. |
| 3. |
Select "Yes, require Excel users to enter connection information" under 'Do you want to Prompt Excel users for connection information?' so that the user information does not get saved. Everytime the user opens the Excel worksheet, the username, password, and database information will be prompted for.
|
| 4. |
In the Page Items section, you can specify whether or not Excel should prompt the user for the value of the page items. You can select the items for which you want Excel to prompt for values and leave the others unselected. While exporting, the current values for those items are preserved. Select Region, and then click Export.
|
| 5. |
The Export Ready page appears. Click the Click to view or save button.
The worksheet is exported to Excel Web Query format, and it opens with Excel.
|
| 6. |
Excel prompts you for the username, password, database information, and all the page item values one after the other.
|
| 7. |
After you have supplied all these values, the data is fetched and displayed in the Excel worksheet.
|
In Discoverer Viewer, you can specify preferences to control how the query is run and how the data is displayed. You can navigate to the Preferences page from anywhere in Discoverer Viewer, by clicking the Preferences button or Preferences link. You can specify the values for the following preferences on this page.
|
Query Governor | |
|
Measure Unit | |
|
Axis Label | |
|
Summary Data |
|
|
Fan Trap Detection | |
To change preferences, perform the following steps:
| 1. |
Click Preferences at the top of the page. The Preferences page appears.
|
| 2. |
Set the option values by selecting the appropriate check boxes or option buttons. Enter values in the fields where applicable. Click Apply to update the changes. |
Just like you invoke pages of any other Web site, Discoverer Viewer workbooks and worksheets can be invoked through a URL from the Web browser. Copy the URL from the browser's address bar when the worksheet is displayed and paste it on your personal portal. When the worksheet is invoked from the URL, the database is queried and the latest information is displayed.
When a Discoverer worksheet is displayed, you can use the bookmarking facility of your browser to bookmark the URL of the worksheet. Later, you can launch the worksheet by selecting the bookmark.
After you have prepared a Discoverer worksheet, you can e-mail it to your audience who want to study the data and perform analysis. You can e-mail your worksheet as an attachment in any of the common formats such as HTML, Microsoft Excel, Adobe Acrobat PDF, or even Web Query for Excel.
| 1. |
To e-mail a worksheet, click Send as email under Actions.
The "Choose attachment type" page appears.
|
| 2. |
Select the attachment type as Portable Document Format PDF (*.pdf), and then click Next.
The Send Email page appears.
|
| 3. |
Fill out the sender, recipient, and subject information. Type any message that you want to add in the body field, and then click Finish.
The SMTP host name should be specified in the middle tier earlier. |
In this lesson, you should have learned how to use Discoverer Viewer to open and view relational workbooks. You should have also learned various features of Discoverer Viewer.
Creating Business Intelligence Reports Using Discoverer Plus OLAP
Place the cursor on this icon to hide all the screenshots in the tutorial.
 |
||
| Copyright © 2003, Oracle Corporation. All rights reserved. | Contact Us • Legal Notices and Terms of Use • Privacy Statement | |
OracleBI Discoverer
) sign on the left
side of the workbook name. Click the plus (
icon next to the workbook name. Click the 
icon beside the Year item to move the Year item to the top axis. Click
the 
icon beside
the Region item to move the Region item to the page axis.
icon on the
formatting toolbar. You can select the entire column by clicking the bar
below the item heading.
icon
beside the region parameter. The "Search and Select: <parameter
name>" page appears. Notice that Americas is already selected.
This is the default value of the parameter that was defined while creating
the parameter in Discoverer. Click Select.
beside the
Year item, and then select Quarter from the list of items displayed.
This list of items is the one that you can drill down to. The Discoverer
manager has defined this hierarchy, which enables you to drill down.