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.
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.
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.
2.
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.
3.
In the Available list, select Sales
Revenue from theElectronics – 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.
To modify the dimension member selections, follow
these steps:
1.
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.
2.
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.
3.
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.
4.
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:
5.
Click OK to apply the new dimension selections.
6.
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.
Perform the following to modify the crosstab layout:
1.
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:
2.
Apply a stoplight format for % Margin by following these
steps:
A.
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.
B.
On the Stoplight toolbar, choose %
Margin from the Format dropdown box.
C.
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.
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:
1.
From the main menu, choose View >
Graph. Your view of the worksheet will now show both the crosstab
and the graph.
2.
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.
3.
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.
4.
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.
5.
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.
C.
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.
6.
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.
C.
Click OK.
7.
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.
To complete the worksheet, perform the following steps:
1.
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.
2.
Right-click the worksheet tab that contains the worksheet
name and choose Rename Worksheet. Change the name to
Sales & Margin Trends.
3.
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.
Bookmark
