Lesson 2: Create a Report that Uses Calculated Members

Purpose

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.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Create a New Worksheet
	Create and Use Calculated Members
	Rename the Worksheet and Save the Workbook
	Summary
	Related information

Viewing Screenshots

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.

Overview

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

Scenario

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

Prerequisites

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

Create a New Worksheet

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

Create and Use Calculated Members

In this topic, you will create and use two Calculated Members: one for the Product dimension, and one for the Time dimension.

	Create a Calculated Member for the Product Dimension
	Create a Calculated Member 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

Rename the Worksheet and Save the Workbook

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

Related Information

	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.