This lesson describes how to connect to Oracle Business Intelligence
Discoverer Plus Relational (Discoverer Plus Relational) to view the results
of an existing report. It describes how to create a new workbook, how to edit
the results of a query by adding or removing items to or from a worksheet, how
to add a new worksheet to a workbook, and how to duplicate an existing one.
Place the cursor over this icon to display all the screenshots. You can also
place the cursor over each individual icon in the following steps to see only
the screenshot associated with that step.
Prerequisites
In order to be able to do the hands-on practices that are
part of this series of OBE lessons, your Discoverer manager must load the sample
data set into the database. For instructions, see Installing
the OracleBI Sample.
Getting
Started
In this topic you learn how to connect to Discoverer Plus
Relational and how to search for an existing workbook and select a specific
worksheet to open.
To open a worksheet in an existing workbook, perform the following steps:
1.
After you have successfully connected to Discoverer
Plus Relational, Step 1 of theWorkbook Wizard appears. You can
either open an existing workbook or create a new one. By default, the
Create a new workbook option is selected.
Select Open an existing workbook.
2.
Click Browse. The Open Workbook from Database
window appears. A list of all workbooks to which you have access appears
in the Workbooks area.
3.
You can filter the list of workbooks by entering a name
or any portion of the name for a workbook, containing the worksheet that
you want to open. As you enter a text string in the Name contains
text field, the list of workbooks is automatically filtered.
You use the View drop-down list to do the following:
All Workbooks: Display the names of all
workbooks to which you have access
My Workbooks: Display only the workbooks
that you own
Database Workbooks: Display the database
workbooks
Scheduled Workbooks: Display the scheduled
workbooks
You use the View as drop-down list to organize the list
of workbooks in the following manner:
Workbook Tree
User Tree
Table
4.
Click the plussign (+)
next to the Sales Analysis workbook.
Click the Category Margins worksheet to select it.
Click Open. A progress window appears while the results
are being retrieved.
After the query execution process ends, the worksheet appears.
Creating a
New Workbook Using the Workbook Wizard
In this topic, you learn how to create a new workbook
comprising one or more queries. Discoverer Plus Relational includes a Workbook
Wizard to simplify this process. When you first log in to Discoverer Plus Relational
, the first page of the Workbook Wizard appears.
If the Sales Analysis workbook is still open,
click File > New to display the Workbook
Wizard. Answer No to Save changes to workbook
Sales Analysis?
You must select a worksheet layout and the objects to display in the
worksheet. You can select from either of two possible layouts: Table
or Crosstab. You can also select any one of the following
objects for the worksheet:
Title
Page Items
Graph (and where to place it in relation
to the table or crosstab)
Text Area
Select the Page Items check box and the Table
option.
Click Next to advance to Step 2 of
the Workbook Wizard.
2.
In this step, you select the items to include
in your query. You can select items from any of the business areas to
which you have access. Items in a business area are organized in folders.
Click the plus sign (+) next to the
Sales Analysis folder to view the included items.
Note: You may select items from only one folder or from
multiple folders. To select items from multiple folders, a join
relationship must exist between the two.
3.
To search for folders and items, click the flashlight
icon. The
Find dialog box appears. Use the Search in,
Search by, and Search for drop-down
lists to specify your search criteria.
Enter Profit in the Search for text
field and click Go.
4.
Select the Profit data point in the Profits
Analysis folder from the Results area and click
OK to locate the item.
Click the Add
icon to include the item in your query.
Alternatively, if you know the location of the items that you want to
include in your query, expand the corresponding folder or folders, click
an item to select it, and drag the item to the Selected area.
Note: You can select multiple items at once, if you
press and hold down the Ctrl
key.
Based on the type of information that you want to display in the Selected
area, you can choose either the Show or Hide
Folders display options.
5.
From the Profits Analysis folder, first
select and then drag the following items to the Selected
area:
Year
Prod Category
The default aggregate for data points, such as Profit, is commonly set
to SUM by the Discoverer manager. Click the plus
sign (+) next to the data point to view the
different aggregates.
You can select from the following: SUM, AVG,
MIN, MAX, COUNT, or
Detail.
6.
When you select an item to add to your query, you can
include all its values or just a subset of its values. Click the plus
sign (+) next to Region in
the Profits Analysis folder.
Select the following values from the list of values (LOV)
that appear and drag them to the Selected area.
Americas
Asia
7.
When a data point that you want to include in your query
is not available for selection in the business area, you can compute it
by defining an appropriate calculation.
Click the Calculations tab in the Available
area.
Right-click My Calculations, and click New Calculation.
Alternatively, select New Calculation from the New
drop-down list.
8.
The New Calculation dialog box appears.
Enter Profit Margin as the calculation name in the What
do you want to name this calculation? text field.
Select Profit SUM and click Paste to
add it to the Calculation area.
9.
Click the division sign ( / ).
Select Available Items from the Show
drop-down list and expand the Profits Analysis folder.
Scroll down and expand Sales Revenue. Select SUM
and click Paste.
10.
Click OK to close the New Calculation
dialog box and return to Step 2 of the Workbook
Wizard. The Profit Margin calculation is added
to the Selected area.
11.
Click Next.
In Step3 of the Workbook Wizard,
you specify how you want to display the results and the order in which
to display the results.
Drag Region to the Page Items area
(also called the page axis).
Arrange the remaining items on the top axis (column headings area of
the table) in the order shown below by dragging each item to the appropriate
position:
Page Items:
Region
Top Axis:
Prod Category, Year, Profit SUM, Profit Margin
Note: Page Items allows you to view the results of a
query in slices (or pages). For example, by placing Region on the page
axis, you can display the results one page at a time for each region.
12.
Click Finish to execute the query and
view the results.
Note: If you do not see the column headings on your
query, select Edit > Worksheet Properties
from the menu bar. Click the Table Format tab, select
Show column headings, and click OK.
If the table or crosstab is larger than the viewing area, automatic vertical
and horizontal scroll bars make it easy to scroll in either direction
to view all information.
Optionally, to maximize the results area, you can deselect both the
Available and Selected Items Pane optionsin the View drop-down list.
Deselecting these options hides both panes.
13.
Each time you create a new query, a worksheet is added
to the workbook to display the results. To change the default name of
the new worksheet from Sheet 1, select Edit >
Worksheet Properties from the menu bar.
The Worksheet Properties window appears with the General
tab automatically selected. Enter Profits Vs Margins by Prod Category
as the name of the new worksheet.
14.
Click the Table Format tab.
Select the Show row numbers check box.
Click the Gridline color
icon and select a light gray color from the color palette.
Click OK to close the Worksheet Properties
window.
15.
At this point, you are ready to save the new workbook.
Select File > Save from the menu
bar. The Save Workbook to Database dialog box appears.
Enter Profit Margins by Category into the New
name text field and click Save.
Modifying a Workbook
In this topic, you learn how to modify a workbook.
Specifically, you learn how to edit an existing worksheet and add a new worksheet
to a workbook.
In the Profits
Vs Margins by Prod Category worksheet, you only want to display
data for the years: 1999, 2000, and 2001.
To filter out the information for 1998 from the
worksheet, do the following:
Click
any cell containing the value 1998
in the Year column.
Select
the Not Equals
icon from the New Conditions drop-down list.
The worksheet automatically refreshes and the rows that correspond to
the value 1998 in the Year column are removed from the table.
2.
Next, you want to display data by product
category (one product category at a time). To do this, you change the
item layout by moving Prod Category to the page axis.
Drag the column heading Prod Category to the Page
Items area to the right of Region.
The worksheet automatically refreshes. The new table displays the annual
profit and the corresponding profit margin for 1999, 2000, and 2001 from
the Electronics product category.
3.
To display profits and profit margins from a different
product category (for example, Photo), click the down-arrow icon for Prod
Category on the page axis and select Photo from
the LOV.
The worksheet refreshes automatically.
4.
To change the information displayed in the table from
annual to regional profits and profit margins, you need to swap the positions
of Year with Region.
Drag the column heading Region from the Page
Items area to the position of Year on the top
axis. (Note: When you place the cursor on top of the
Year item heading, the Year color changes to light gray as shown in the
screenshot).
The worksheet refreshes automatically.
5.
Next, you want to see how each channel contributes to
profits and profit margins for each region. To do this, you need to add
Channel Class to the worksheet.
Select View from the menu bar and select
the Available and Selected Items Pane
options.
From the Available Items pane, drag Channel
Class to the right of Region on the top axis
of the results area.
The Channel Class item is added to the worksheet.
6.
Right-click the Region IN ('Americas', 'Asia')
filter in the Selected Items pane and click the Remove
from Worksheet
icon on the shortcut menu.
The worksheet automatically refreshes and the remaining values of Region
are added to the table.
7.
Click the Save
icon on the Standard toolbar (or select File
> Save from the menu bar) to save the changes
that you made to the Profit Margins by Category workbook.
Adding
a New Worksheet
Having looked at profits and profit margins by product category
for each time and geography, you now want to look at total sales revenue by
product category along the same dimensions. To accomplish this task, you decide
to add a new worksheet to the Profit Margins by Category workbook by performing
the following steps:
Select Edit >
Add Worksheet from the menu bar to launch the Workbook
Wizard.
Alternatively, you can launch the Workbook Wizard by
clicking the Add Worksheet
icon on the Standard toolbar.
2.
Select the Table option (if not already selected by
default) and clear all checkboxes except for Page Items.
3.
Click Finish. A blank worksheet is added to the workbook.
4.
In the Available Items pane, expand the Profits
Analysis folder.
Select Sales Revenue, Prod Category,
and Channel Class. Drag these items to the top axis of
the table (at the location shown in the screenshot).
Prod Category, Channel Class, and Sales
Revenue SUM are added to the worksheet.
5.
Select Year and Region and drag them
to the Page Items area of the worksheet.
Year and Region are added to the page
axis of the worksheet.
6.
Click the Calculations tab in the Available
Items pane.
Expand My Calculations and drag Profit Margin
to the right of Sales Revenue SUM on the top axis of
the table.
Profit Margin is added to the worksheet.
7.
Right-click the worksheet name tab and select Worksheet Properties
from the shortcut menu.
8.
The Worksheet Properties window appears with the General
tab automatically selected. Enter Sales Vs Margins by Prod Category
in the Name text field.
Click OK.
9.
Click the Save
icon to save the changes that you made to the Profit Margins by
Category workbook.
Duplicating a Worksheet
You can display the same information in different ways, using
multiple worksheets. When you launch the Workbook Wizard, it adds a new worksheet
that uses the same set of items, but with a different layout. Instead, you can
duplicate a worksheet and change its layout to create multiple views of the
data.
To duplicate a table worksheet as a crosstab, perform the
following steps:
1.
If not already open, open the Sales
Vs Margins by Prod Category worksheet in the Profit Margins
by Category workbook. Select Edit >
Duplicate Worksheet > As Crosstab from the
menu bar.
Alternatively, click the down-arrow
icon next to the Add Worksheet
icon on the Standard toolbar and select Duplicate
as Crosstab from the drop-down list.
2.
Ignore the warning message and click OK
to close the alert dialog box.
3.
Drag Prod Category to the
side axis of the crosstab and Channel Class to the top
axis below Data Points (as shown in the screenshot).
4.
Click OK. A new worksheet,
Sales Vs Margins by Prod Category 2, is added to the
workbook.
Note: Displaying information as a crosstab has many
advantages. You can use it to create multidimensional views and explore
the interrelationships of the data immediately.
5.
Rename the new worksheet Channel
Sales Vs Margins by Prod Category.
6.
Click Save to save the changes
that you made to the Profit Margins by Category workbook.
Summary
In t his lesson you learned how to connect to Discoverer Plus
Relational and view the results of an existing report. You also learned how
to create a new workbook, how to edit the results of a query, how to add a new
worksheet to a workbook, and how to duplicate an existing one.
Place
the cursor over this icon to hide all screenshots.
Bookmark
icon. The
Find dialog box appears. Use the Search in,
Search by, and Search for drop-down
lists to specify your search criteria. 
icon to include the item in your query.
key. 
icon and select a light gray color from the color palette. 
icon from the New Conditions drop-down list.
icon for Prod
Category on the page axis and select Photo from
the LOV. 
icon on the shortcut menu. 
icon on the Standard toolbar (or select File
> Save from the menu bar) to save the changes
that you made to the Profit Margins by Category workbook.
icon on the Standard toolbar. 
