Creating a Trend Analysis Report

Lesson 2: Creating a Trend Analysis Report

Purpose

This lesson teaches you how to use Discoverer Plus OLAP to create a report that enables trend and time based analysis.

Overview Topics

	Overview
	Prerequisites

Topics

This lesson will discuss the following:

	Create a New Worksheet
	Specify Dimension Member Selections
	Modify the Crosstab Layout and Add a Stoplight Format
	View and Modify the Graph
	Complete the Worksheet
	Summary

Time to complete

45 minutes

Viewing Screenshots

Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with it.

Overview

Executive users want to examine monthly sales and margin data. In addition, they want to see sales and margin trends over time. This data needs to be presented graphically.

Back to Topic List

Lesson Objectives

In this lesson, you will create a second worksheet that meets the business requirements stated above. You will create a new worksheet that contains a crosstab and a graph that enable trend and time-based analysis.

The primary question that executives want to answer using this report is:

How are my geographic regions performing in terms of sales margin over time?

The analytic requirements of the report include the following:

	Enable users to quickly identify the geographies that are not yielding acceptable margins. The target range for acceptable margins is between 18% and 22% (using the same geography dimension members as selected in the first lesson.)
	Show data for specific months in 2001. The report must also be able to show data for specific channels.

Data Model

For information on the data model used in the Discoverer Plus OLAP OBE lessons, please see the Data Model page.

High Level Objectives

The general business requirements of the Discoverer Plus OLAP OBE lessons are described in the Business Requirements page.

Prerequisites

Back to Topic List

In order for this lesson to work successfully, you will need to have performed the following:

1.	Completed the first lesson in this Focus Area: Creating a Product Category Analysis Report - Lesson 1.

Create a New Worksheet

In this topic, you will add a new worksheet to the Corporate Profitability workbook that you created in the first lesson.

Back to Topic List

Follow these steps to create the new worksheet:

Open the workbook you created in the previous lesson by selecting File > Open. In the Open dialog box, select the Corporate Profitability workbook.

Click OK.

From the main menu, select Edit > Add Worksheet.

In the Worksheet Wizard, select the Title and Page Items options. Choose the Crosstab radio button and deselect the Text Area option.

Click Next to continue.

In the Available list, select Sales Revenue from the Electronics – KPIs folder and the % Margin calculation from the /Users/SCOTT folder.

Remove the Promotion dimension from the Selected list.

Click Finish. A new worksheet is added to the workbook.

Specify Dimension Member Selections

You will specify dimension member selections for this worksheet with the OLAP Navigator, using techniques similar to those you learned in the previous lesson.

Back to Topic List

To modify the dimension member selections, follow these steps:

In the Members tab of the OLAP Navigator, choose Channel from the Dimension drop-down list.

Multiselect Channel total, Direct, Indirect, and Others. Right-click on the selection and choose Replace from the menu.

Choose Product from the Dimension drop-down list in the Navigator.

Multiselect Product total, Electronics, Hardware, Peripherals and Accessories, Photo, and Software/Other. Right-click on the selection and choose Replace from the menu.

For the Geography dimension, use the Saved Selection that you created in the previous lesson. Choose Geography from the Dimension drop-down list in the Members tab.

Then, click the Saved Selections tab and select My Geographic Regions from the /Users/SCOTT folder.

Right-click on the selection and choose Replace from the menu.

Change the selection for Time to be the months for 1998 through 2001 by creating a Condition. To create the condition, perform the following:

A.	From the main menu, choose Edit > Worksheet.
B.	In the Edit Worksheet dialog box, select the Dimension tab. In the Choose field, select Time.
C.	Click the Remove all Items button (‘<<’) to remove all steps in the Selected pane.
D.	Select the Conditions tab in the Available pane and open the Hierarchy folder.
F.	Select Calendar Year, then click the hyperlink to modify the selection. Choose Month from the drop-down list, as shown in the following figure:

G.	Click the Add Selected Items button (‘>’) to shuttle the Month step to the Selected pane.
H.	To view the members that will be returned from a given set of dimension query Steps, click the Members tab in the Selected region, as shown in the following figure:

Click OK to apply the new dimension selections.

Click the Members tab of the Navigator. Then, select Time from the Dimension box and open the Query Steps Pane in the Navigator by selecting View > Query Steps Pane.

In the Query Step pane in the Navigator, the selection for Time should look like this:

7 .

You can select other members for the Product or Time dimensions tiles in the Page Items area.

For example, select Dec01 from the Time tile. The crosstab updates with the appropriate data.

Modify the Crosstab Layout and Add a Stoplight Format

In this topic, you will modify the layout of the crosstab, and then add a stoplight format to the % Margin measure.

Back to Topic List

Perform the following to modify the crosstab layout:

Using drag-and-drop, change the layout of the crosstab so that the Channel dimension appears in the Page Items region, to the right of Time.

The crosstab layout should like this:

Apply a stoplight format for % Margin by following these steps:

From the main menu, choose View > Toolbars > Stoplight Toolbar. The Stoplight toolbar is displayed.

Hint: If you wish to apply the stoplight format without a toolbar being visible, you could choose New Stoplight Format from the Format menu. This invokes a format dialog, which disappears after the formatting has been applied.

On the Stoplight toolbar, choose % Margin from the Format dropdown box.

In the Unacceptable field, enter 0.18, and in the Desirable field, enter 0.22, as shown:

D.	Click Go to apply the formatting to the % Margin measure.
H.	The crosstab reflects the new stoplight formatting, and the % Margin formatting is updated automatically when you select new dimension members from the Page Items.

View and Modify the Graph

As in the first lesson, you have been working only with a crosstab in your worksheet. You will now make make the graph visible and modify the graph.

Back to Topic List

If you want to see the graph when you first create the worksheet, you can select the Crosstab and Graph option in the Worksheet Wizard.

To view and modify the graph, follow these steps:

From the main menu, choose View > Graph. Your view of the worksheet will now show both the crosstab and the graph.

On the Edit menu, deselect Link Crosstab and Graph Layouts. This allows you to provide different layouts for the graph and crosstab in the worksheet.

Right-click on the graph and select Edit Graph Layout from the menu. In the Edit Graph Type dialog, drag and drop the dimension tiles so the graph layout shows Time in groups, Measures in Series (Bars), and Product, Geography, and Channel in Page Items, as shown in the following screenshot:

Click OK. The graph layout is changed.

Right-click the graph again and choose Change Graph Type from the menu. Change the Type to Combination, with a Subtype of Dual-Y Combination.

Click OK to incorporate the changes.

Change the series color for the % Margin measure so that it stands out more clearly, by completing the following steps:

A.	Right-click the graph and choose Edit Graph.
B.	Select the Plot Area tab, then change the colors for % Margin by clicking on the line color box, as shown in the following screenshot. Choose your own color, but select something more distinctive than light blue.

Click OK.

Note: you can also change the color of graph elements by selecting the element and then using the Fill Color tool in the formatting toolbar.

Move the graph legend below the graph by following these steps:

A.	Right click the graph legend (Sales Revenue or % Margin) and choose Edit Legend from the menu.
B.	In the Location box, select Bottom.

Click OK.

Add axis labels to the graph by following these steps:

A.	Right click the graph and choose Edit Graph from the menu.
B.	On the Y1-Axis tab, set the title to Sales and then set the Title Font size to 12. On the Y2-Axis tab, set the title to % Margin and set the Title Font size to 12.
C.	Click OK. The graph should look like the screenshot below.

Complete the Worksheet

Back to Topic List

To complete the worksheet, perform the following steps:

Double-click the title area of the worksheet. Change the text for the title to Monthly Sales and Margin History. You may also want to modify the title font, alignment, size, and color.

Right-click the worksheet tab that contains the worksheet name and choose Rename Worksheet. Change the name to Sales & Margin Trends.

From the File menu, choose Save.

The worksheet should look like the one that is shown in the following screenshot:

Summary

In this lesson, you created a second worksheet in the Corporate Profitability workbook that enables analysis of monthly sales and margin data:

	The stoplight formatting in the crosstab highlights Margin results that fall in specified ranges. This enables users to identify quickly the geographies that are not yielding acceptable margins. The target range for acceptable margins was set between 18% and 22%, but the user can change these values to any range they want.
	Trends in the Sales and Margin data are easily identified in the graph.

When creating the query for this report, you learned how to:

	Create and apply a dimension condition for Time.
	Use a saved selection, which was created in the previous lesson, for the Geography dimension selection.

In the next lesson, you will use the knowledge from this lesson to create another report that meets some of the other business intelligence requirements of the Executive Reporting system.

Back to Topic List

Move your mouse over this icon to hide all screenshots