Create a Report that Uses Calculated Members
Lesson 2: Create a Report that Uses Calculated Members
This tutorial teaches you how to create and use Calculated
Members in a report. Calculated Members is one of the new features for Oracle
Business Intelligence that is contained in the AS/DS 10G R2 PATCH SET 2 (10.1.2.2).
You can create calculated members through a wizard in OracleBI Discoverer Plus
OLAP and view them in Discoverer and other products.
The Prerequisites topic in the previous lesson in this focus
area (Lesson 1: Creating Analytic Reports Using Discoverer Plus OLAP 10.1.2.2)
contains information on the 10.1.2.2 Patch Set requirements.
Approximately 30 minutes
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor
over an individual icon in the following steps to load and view only the screenshot
associated with that step. You can hide an individual screenshot by clicking it.
In this lesson, you will create a product analysis
report that uses Calculated Members.
A Calculated Member enables the summation (simple plus or
minus) or aggregation (using the Aggregation operator for the cube) of a set
of dimension members that are specified by a user.
A Calculated Member is created and edited through wizards
that are integrated with query builder in Discoverer Plus OLAP. The dimension
members that comprise a Calculated Member set can be selected manually, or can
be selected using a dynamic condition step.
Calculated Members are saved and shared in the Discoverer
catalog, just like custom measure calculations or saved selections. Therefore,
they can be viewed by any product that can access the Discoverer catalog, such
as:
- Discoverer Viewer
- Discoverer Plus OLAP
- OracleBI Spreadsheet Add-in
- Oracle Portal (through Discoverer Portlet Provider)
Back to Topic List
In this lesson, you create a report that targets a set
of products for which a particular product category manager is responsible.
This report will automatically select all products in the manager's category
areas, and will provide calculated summary information for the entire set of
products by using the Calculated Members feature.
Back to Topic List
Before starting this tutorial, you should have:
 |
Completed the first lesson in this Focus Area: Lesson
1: Creating Analytic Reports Using Discoverer Plus OLAP 10.1.2.2.
|
Back to Topic List
In this topic, you will add a new worksheet to the
Performance Reports workbook that you created in the first lesson.
Here, you will use the Worksheet Wizard to define the
worksheet layout and specify the dimension selections for the product analysis
report.
| 1. |
Ensure that the workbook you created in the previous lesson is open.
If the workbook is not open, select File > Open,
or click the New Worksheet icon. In the Open dialog box, select
the Performance Reports workbook and click OK.
With the Performance Reports workbook open, click the New Worksheet
icon.
|
| 2. |
In the Worksheet Layout step, select the following Worksheet Item
options: Page Items and Crosstab (only).
Click Next.
|
| 3. |
In the Items step of the wizard:
| A. |
Open the Electronics - KPIs folder. |
| B. |
Move Sales Revenue,
Profit, and Margin % to the
Selected list.
|
| C. |
Click Next. |
|
| 4. |
In the Crosstab Layout step, drag the report items to the following
locations and ensure that:
| A. |
Product is in the Row axis. |
| B. |
Measure is in
the Column axis. |
| C. |
Geography, Channel,
and Time are in the Page Items area. |
Click Next.
|
| 5. |
For the Time dimension, move the years 2004 and
2005 to the Selected list and then click Next.
|
| 6. |
For the Channel dimension, move All Channels, Direct,
Indirect, and Others to the Selected
list and then click Next.
|
| 7. |
For the Geography dimension, use a saved selection that you created
in the previous lesson.
| A. |
Click the Saved Selections tab, and open the Users\SCOTT
folder. |
| B. |
Select My Geographic Regions
and move it to the Selected list, as shown here:
|
| C. |
Click Next. |
|
| 8. |
For the Product dimension, create a multi-step selection that returns
the products that belong to several product subcategories. Then, save
that selection for later use.
| A. |
In the Members tab of the Available list drill on All
Products and then on the Electronics
category. |
| B. |
Select the Home Theatre
subcategory move it to the Selected list. |
| C. |
Back in the Available list, drill
on the Software/Other category, select the Recordable
CDs and Recordable DVD Disc sub category
members, and move them to the Selected list.
The current selection should look like this:
Now, add a second step to the selection criteria.
|
| D. |
In the Available list, click the
Conditions tab and open the Hierarchy folder. |
| E. |
Select the All Products
condition template, change the hypertext link to Product,
and then move the modified condition to the Selected list. |
| F. |
Click the condition step and change
the Add hypertext link to Keep,
like this:
|
| G. |
Click the Members
tab of the Selected list to preview the product members that will
be returned. |
| H. |
At the bottom of the Selected list,
click Save. |
| I. |
In the Save Selection As dialog box,
enter My Products as the name, accept the Save
Steps option, and click OK. |
|
| 9. |
Click Finish to create the worksheet, which should
look like this:
|
| 10. |
Add two decimal places to the Margin % column, by following these
steps:
| A. |
Select the Margin % column of data. |
| B. |
Click the Add Decimal
icon twice. |
|
Back
to Topic List
In this topic, you will create and use two Calculated Members:
one for the Product dimension, and one for the Time dimension.
Back to Topic List
Create a Calculated Member for
the Product Dimension
Create and use a Calculated Member for the Product dimension,
which returns the sum for all of the products in the My Products saved selection.
(Note: The My Products saved selection was created as part of the previous lesson.)
| 1. |
In the Available Items pane to the left of
the worksheet, click the Members tab and then select
Product in the Dimension box.
|
| 2. |
Select Tools > Calculated Members
> Product from the menu.
Hint: you can also launch the wizard by clicking the
New Calculated Member icon, as shown here:
|
| 3. |
In the New Calculated Member Wizard, perform the following:
| A. |
In Step 1 of the wizard, select the appropriate dimension members
by using the My Products saved selection, as
shown below:
Then, click Next.
|
| B. |
In Step 2 of the wizard, accept
the default calculation option (Add and Subtract Members) and
click Next.
|
| C. |
In Step 3 of the wizard, enter
Total of My Products as the Name, Short Label,
and Long Label. Then, click Finish to create
the Calculated Member.
|
The Calculated Member is automatically added it to the worksheet:
Also, a folder named Calculated Members appears in
the Available Items pane for Product. This folder provides future access
to your new Calculated Member.
|
| 4. |
Select other Geography, Channel, and Time members from the Page
Items area of the report.
Result: The calculated data for Total of My Products updates along
with the other product data. The Calculated Member is automatically
calculated for all selected measures, regardless of whether the measure
itself is stored (for example, Sales Revenue) or calculated (for example,
Profit and Margin %).
|
Back to Topic
Create a Calculated Member for
the Time Dimension
Next, you create a Calculated Member for the Time dimension
which returns the sum for the years 2004 and 2005.
| 1. |
Select Tools > Calculated Members
> Time from the menu.
|
| 2. |
In Step 1 of the Calculated Member Wizard, select 2004
and 2005 as shown here.
Then, click Next.
|
| 3. |
In Step 2 of the wizard, accept the default calculation option
(Add and Subtract Members) and click Next.
|
| 4. |
In Step 3 of the wizard, enter 2004 & 2005
for the Name, Short Label, and Long Label. Then, click Finish
to create the Calculated Member and add it to the worksheet.
|
| 5. |
Select the new Calculated Member in the Time dimension to view
the calculated data.
|
| 6. |
Drag the Time dimension to the Row axis and nest it inside the
Product dimension as shown here:
This layout enables viewing of all Time members for each Product.
|
| 7. |
You can drill on any of the hierarchical Time members (such as
2004 or 2005). However, the custom calculated dimension member does
not contain a drill symbol, because it is not part of the dimension
hierarchy.
|
| 8. |
Drag the Time dimension back to the Page Items area.
|
| 9. |
Add a Product Share measure to enhance the analytic content of
the report:
| A. |
In the Available Items pane, select Measures
from the Dimension box. |
| B. |
Open the Electronics
- Share Calculations folder. |
| C. |
Drag Product Share of
Total Profit to the worksheet.
|
| D. |
Format the Product Share measure
as a percentage with two decimal places. If necessary, format
the column headers so that the text wraps:
Hints:
To format the Product Share measure: select the data column,
click the Format As Percent icon, and then
click the Add Decimal icon twice.
To format the column headers to wrap text: right-click the
column bar just to the left of the first column header (Sales
Revenue). Select Format Header from the menu.
Then, on the Font tab, select the Wrap Words in Cell
option and click OK. |
|
| 10. |
Experiment with the report by selecting members from the Geography,
Channel, and Time dimensions. Each measure, calculated and stored,
updates automatically with each selection.
For example, the following image displays the data for Europe, the
Direct channel of distribution, and the year 2005:
|
Back to Topic
| 1. |
Right-click the worksheet (Sheet 3) and select Rename Worksheet
from the menu.
|
| 2. |
Name the worksheet My Product Analysis, and click
OK.
|
| 3. |
Save the workbook by selecting File > Save.
|
| 4. |
The workbook now contains three worksheets that enable different
types of analysis of the sample schema analytic workspace.
|
Back to Topic List
Summary
In this lesson, you learned how to:
|
Use saved
selections that were previously saved in the Discoverer catalog. |
|
Define a multi-step
selection that dynamically returns products for a selected set of product
categories. |
|
Create and use Calculated
Members, a new feature of Discoverer Plus OLAP 10.1.2.2. |
Back to Topic List
|
In the next lesson, you
will OracleBI Spreadsheet Add-In to access the same OLAP data. You will
use new features in the Spreadsheet Add-In that are available in the 10.1.2.2
release, including access to the Discoverer catalog.
|
|
To learn more about Oracle Business
Intelligence features of the AS/DS 10G R2 PATCH SET 2 (10.1.2.2) release,
refer to additional OBEs in the same focus area. |
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
Bookmark
