In this lesson you will learn how to use graphs to present and explore data visually, or to uncover trends over time. You will also learn how to create advanced calculations to analyze data, how to filter query results using conditions, and how to create parameterized reports.
Place
the cursor over this icon to show all screenshots. You can also place the cursor
over each individual icon in the following steps to see only the screenshot
associated with that step.
Using Graphs to Present Data
In this topic you will learn how to use graphs to present data. There are many graph types and subtypes in Discoverer that you can select from to create the type of graph that you want. The following examples point out the main graph features and illustrate the use of some of these types of graphs.
Each time you create a new query, you are asked to select
which objects to display on the worksheet. For example, you can select to display
the results in a table or crosstab with or without page items, whether to display
a worksheet title or a text area, and also whether you want to display the results
graphically.
Select File > New from the menu bar to launch the
Workbook Wizard. In step 1 of the wizard, select the Table
option, select the Page Items and Graph
check boxes, and select Graph above Table from the Placement
drop-down list.
2.
In step 2 of the wizard, select the following items from the Profits
Analysis folder.
Region
Year
Channel Class
Sales Revenue (SUM)
Profit (SUM)
3.
In step 3 of the wizard, place Region on the page axis and Year, Channel
Class, Sales Revenue SUM, and Profit SUM (in that order) on the top axis
of the table.
4.
Continue to step 4 of the wizard and add a group sort on Year.
Click the Finish button to execute the query. The worksheet that is created contains both a table and a graph placed above the table.
5.
To change the position of the graph, select View > Graph Placement
from the menu bar and select the Graph below Table option.
6.
Drag Year to the Page Items area; now you can compare
at a glance sales and profits for the three channel classes for any given.
7.
Name the worksheet Sales by Region and Year and save the workbook as Sales by Channel.
Changing the Graph Type
Discoverer remembers the last graph type you used and uses it as the default graph type for any new worksheets that you create. To change the graph type, perform the following steps:
Select Edit > Graph from the menu bar to display
the Edit Graph window.
Alternatively, click the Edit Graph icon on the Standard
toolbar.
2.
The Type tabbed page is displayed. Select the graph type and subtype
that you want to use from the Graph Type and Graph Subtype areas. For
this example, select the Split Dual -Y Bar subtype to
display each series (Sales Revenue SUM and Profit SUM) separately using
two different Y-axis scales.
3.
Click the OK button to close the Edit Graph window; the graph changes to reflect the selections that you made.
4.
Swap the positions of Year and Channel Class by dragging one of them to the position of the other.
The graph refreshes automatically to display Sales Revenue SUM and Profit SUM by Year for the selected values of Region and Channel Class.
Toggle between the values of Region and Channel Class on the page axis to see the different trends for Sales Revenue SUM and Profit SUM over the years for which you have data.
5.
Select the value <All> for both Region and Channel Class on the page axis to see the overall trend for total sales and profits over the years for which you have data.
Notice the difference in the trends for total Sales Revenue and Profit over time. For example, both total sales and profits for the company decreased in 1999. Total sales recovered the next year to its 1998 level, while profit continued its downward trend through the year 2000.
6.
Right-click any of the graph bars for either one of the series to open
a shortcut menu.
7.
Select Edit Graph; the Edit Graph window opens with the Type tab automatically selected. Select Line from the Graph Type area and Split Dual-Y Line from the Graph Subtype area.
8.
Select the Style tab. In the Style tabbed page, select
Executive from the Style drop-down list and then select
the 3D Effect check box.
9.
Click the OK button to close the Edit Graph window and
apply the changes that you made to the graph. Note again the differences
in the trend that total sales and profits followed over time.
10.
Toggle between the values of Channel Class on the page axis to see the trends that total sales and profits followed over time for each different Channel Class.
11.
Select File > Save from the menu bar to save the changes that you made to the Sales by Channel workbook.
Editing a Graph Using the Graph Wizard
If you want to make changes to a graph, you can edit the graph by invoking the Graph Wizard. For example, you can add a title to your graph, label the graph's axes, and edit the plot area.
Duplicate the Sales by Region and Year worksheet as
a crosstab. Ignore the warning, and drag Year item to side axis in the Crosstab Layout page. Click Properties to open Worksheet Properties dialog. Name the new worksheet Annual Sales & Profits.
(Hint: Select Edit > Duplicate Sheet >
As Crosstab from the menu bar.)
2.
Select Edit > Graph from the menu bar (or click the
Edit Graph icon on the Standard toolbar.) Select Combination
from the Graph Type area, and then select Dual-Y Combination
from the Graph Subtype area.
3.
From the Style tabbed page, select Default from the Style drop-down list.
4.
Select the Titles, Totals, and Series tab. Select the
Show Title check box, and then select Sheet Name
from the Insert drop-down list.
5.
Select the Plot Area tab. Under "Select options
for the series displayed in your graph," select Area
for Sales Revenue SUM and Bar for Profits SUM. Use the
Graph As column to make these selections.
6.
Click Apply to preview the graph; then click the OK button to close the Edit Graph window.
7.
Select File > Save from the menu bar to save the changes that you made to the Sales by Channel workbook. Note: If you want to display item labels, and gridlines in the
crosstab, select Edit>Worksheet Properties menu option. (Alternatively, double click the worksheet name to open the Worksheet Properties dialog). Click the Crosstab Format tab, select the Show item labels, and Show heading gridlines options. Click OK.
Using the Graph Toolbar
You can use the Graph toolbar to make quick changes to the
graph. For example, you can hide or show the legend or horizontal and vertical
grid lines, or you can apply a gradient and 3D effect to the graph.
Duplicate the Annual Sales & Profits worksheet as
a crosstab again. Drag Channel Class from the Page Items area to the position
of the Data Points on the top axis of the crosstab.
From the Graph toolbar, select the Pie Graph icon from
the Graph Type drop-down menu.
2.
Right-click anywhere on the pie chart and select Edit Graph from the shortcut menu that appears. The Edit Graph window opens with the Type tab automatically selected. Select Multiple Pie from the Graph Subtype area.
3.
Click Apply to preview the graph, and then click the OK button to close the Edit Graph window.
4.
Change the name of the new worksheet (Annual Sales & Profits 2) to Sales & Profits by Channel and observe the graph title change to match the new worksheet name.
5.
Right-click the graph title text and select Edit Title
from the shortcut menu that opens.
The Title dialog box opens. Enter Sales & Profits by:
followed by a space directly into the text field, and then select Axis
Items from the Insert drop-down list.
6.
Click the OK button to close the Title dialog box. The
graph title changes to Sales & Profits by: Channel Class,
Year.
7.
Select File > Save from the menu bar to save the changes that you made to the Sales by Channel workbook. Do not close the workbook.
Drilling
into Data Using Graphs
With Discoverer Plus Relational you can also drill into data from a graph.
When an item belongs to a predefined hierarchy, its data labels, which are shown
in the graph area, are automatically underlined. To illustrate drilling into
data from a graph, use the predefined hierarchy: Year >
Quarter > Month.
In the Sales & Profits by Channel worksheet, click the 2001 data label inside the graph area.
Quarter (for 2001) replaces the Year item on the side axis of the crosstab. The graph refreshes automatically to match the new set of data displayed in the crosstab.
2.
Click the Q3 data label inside the graph area. Month (for Q3) replaces Quarter on the side axis of the crosstab. Again, the graph refreshes automatically to match the data displayed in the crosstab.
3.
As you drill down into data from a graph, a link to the previous level of the hierarchy is added at the bottom of the graph area.
Use this link to roll back the data displayed in the crosstab and the
graph to its previous state. You can use this link to drill up to the
top level of the hierarchy (one level at a time.)
Using Reference lines
You want to find out in which calendar quarters total sales
dropped below $1.5 million in Europe. Here is how you can use reference lines
to help you do this.
In the Sales by Channel workbook, add a new tabular worksheet that includes a graph using the following items from the Profits Analysis folder.
Region (Europe)
Year
Quarter
Sales Revenue (SUM)
Place Region on the page axis and Year, Quarter, and Sales Revenue (in
that order) on the top axis of the table. Group sort the results by year
and name the worksheet Quarterly Sales for Europe. Change
the graph type to Line and edit its title to match the
worksheet name.
2.
Right-click a Y-Axis label and select Format Axis from
the shortcut menu that opens.
The Edit Graph window opens with the Y-Axis tab automatically selected.
3.
Click the Reference Lines button; the Reference Lines
dialog box opens. Select the Reference Line 1 check box.
From the corresponding Thickness column drop-down list , select a medium
line of thickness. Enter Low Sales as the line label,
and enter 1500000 ($1.5 million) in the corresponding
cell of the "Cross at" column.
4.
Click the OK button twice to close the Reference Line
dialog box and the Edit Graph window. The Low Sales reference line is
added to the graph. Observe that in Q2 of 1999, sales in Europe fell below
the Low Sales line.
5.
Select File > Save from the menu bar to save the changes that you made to the Sales by Channel workbook.
Creating Advanced Calculations and Conditions, and Using Parameters
In this topic you will learn how to use advanced calculations and conditions to analyze data, and how to add parameters to your reports.
Discoverer Plus Relational provides a variety of tools
that you can use to analyze data. It leverages the power of the analytic functions
that are native to the Oracle database, and it allows you to create advanced
calculations with the click of a button. For example, you are interested in
finding out the percentage by which quarterly profits grew in each year compared
to the same quarter from the year before. Here is how Discoverer Plus Relational can help
you do this.
In a new workbook, create a Crosstab worksheet that
includes the following items from the Profits Analysis
folder:
Year
Quarter
Profit (SUM)
Place both Year and Quarter (first Year, then Quarter) on the side axis of the crosstab. Click Properties to open the Worksheet Properties dialog, and name the worksheet Profit Growth by Quarter.
2.
Click the New Calculation icon on the Standard toolbar.
The New Calculation window opens. Enter % Growth (Yr Prior)
in the "What do you want to name this calculation?" field.
3.
Click the Insert Formula from Template button. Select Percent Difference from the list of available analytic function templates that appears.
The Percent Difference window opens.
4.
Enter 4 in the "Preceding values of:" field.
Select Year from the "Order rows by:" drop-down
list, and then select Quarter from the "Then order
rows by:" drop-down list. Clear the Year check box
in the "Restart calculation at each change in:" area.
5.
Click the OK button to close the Percent Difference
window; the analytic calculation formula is copied to the Calculation
area of the New Calculation window.
Click the OK button to close the New Calculation window. The % Growth (Yr Prior) column is added to the crosstab showing the fraction by which profits grew (or decreased) compared to the same quarter from the year before.
6.
Select File > Save from the menu bar and save the workbook as Profits Analysis. Do not close the workbook.
Creating Advanced Conditions
Your analysis of profit margins for individual products has shown that products with large profit margins are not always coupled with high profits. You are interested in finding out the products with profit margins greater than 20% for which overall profit (total profit over the years that you have data) is less than $200,000. Here is how you can use Discoverer Plus Relational to answer this question.
Use the Formatting toolbar to change the format type of the data in the
% Margin column to percentage.
3.
Click the New Condition icon on the Standard toolbar.
The New Condition window is displayed. Select % Margin
from the Item drop-down list, and then select greater than (
>) from the Condition drop-down list. Enter .20
in the Values field.
4.
Click the Advanced button; the Formula area of the New Condition window expands and a set of new buttons is added to it.
5.
Click the And button; a new row is added to the Formula
area. This time, select Profit SUM from the Item drop-down list, and then
select less than (<) from the Condition drop-down
list. Enter 200000 ($200,000) in the Values field.
6.
Click the OK button to close the New Condition window.
The worksheet is refreshed automatically; it now displays only the products
with % Margin greater than 20% and Profit SUM less than $200,000.
7.
Name the new worksheet Margins by Product and save the changes that you made to the Profits Analysis workbook. Do not close the workbook.
Adding a Parameter to a Worksheet and Using Cascading LOVs
A parameter allows you to pass a value to a condition when
you run a query. For example, if you create a query and define a parameter based
on the Prod Category item, then each time you run the query you are prompted
to select the value of the product category from the Prod Category list of values
(LOV) for which you want to see data.
A worksheet may include one or more parameters. If a worksheet
includes two or more parameters with dependent lists of values (for example,
Region and Country), then when you select values for one, the LOV of the other
is automatically cascaded based on the values that you select for the first
parameter. For example, if you select Europe for Region, then the LOV for country
is truncated to include only the countries from Europe.
To create a parameter and use cascading LOVs, perform the following steps:
1.
In the Profits Analysis workbook, create a new crosstab
worksheet by selecting the following items from the Profits Analysis
folder.
Prod Category
Prod Subcategory
Region
Year
Sales Revenue (SUM)
Place Region on the page axis. Place Prod Category and Prod Subcategory (nested in that order) on the side axis of the crosstab. Place Sales Revenue SUM and Year (nested in that order) on the top axis of the crosstab.
2.
Select Edit > Graph from the menu bar. The Edit Graph window opens with the Type tab automatically selected. Select Bar from the Graph Type area and Stacked Bar from the Graph Subtype area.
Open the Titles, Totals, and Series tabbed page. Clear the Show
Title check box, and select the Graph series by: >
Rows option.
Open the Y-Axis tabbed page. Click the Reference Lines button and clear the Reference Line 1 check box.
Click the OK button to close the Edit Graph window; the graph is displayed along with the crosstab.
3.
Select Tools > Parameters from the menu bar to display
the Edit Worksheet window with the Parameters tab automatically selected.
Then, click the New button to open the New Parameter
window.
Alternatively, click the New Parameter icon on the Standard
toolbar.
The New Parameter window opens. Create a new parameter as follows:
Enter Category in the "What do you want to name
this parameter?" field.
Select Prod Category from the "Which item do
you want to base this parameter on?" drop-down list.
Select the Create condition with operator check box
and select IN from the
drop-down list.
Enter Select a Category in the "What prompt
do you want to show for this parameter?"' field.
Select the Require users to enter a value and Enable
users to select multiple values check boxes.
4.
Click the OK button to close the New Parameter window.
The Edit Parameter Values window is displayed. Click the search (flashlight
) icon to open the Select Values window.
Select the categories Electronics and Photo
in the "Displayed values" list and copy them to the "Selected
values" list. (Hint: Press and hold the [Ctrl]
key to select multiple values.)
5.
Click the OK button twice to close the Select Values and Edit Parameter Values windows. The worksheet is automatically refreshed; the results are filtered to display data for the Electronics and the Photo product categories.
6.
Open the New Parameter window again. (Follow the instructions
in step 3 above.) Create a new parameter by doing the following:
Enter Subcategory in the "What do you want to
name this parameter?" field.
Select Prod Subcategory from the "Which item
do you want to base this parameter on?" drop-down list.
Select the Create condition with operator check box
and select IN from the
drop-down list.
Enter Select a Subcategory in the "What prompt
do you want to show for this parameter?" field.
Clear the Require users to enter a value check box.
Select the Enable users to select multiple values
check box.
Select the Filter the list of values based on the selected
conditions option.
Select the Prod Category IN (:Category) condition
check box.
7.
Click the OK button to close the New Parameter window. Then, select Tools > Refresh Sheet from the menu bar.
Alternatively, click the Refresh icon on the Standard
toolbar.
The Edit Parameter Values window is displayed with the Electronics and Photo categories automatically selected for the Category parameter.
Click the search (flashlight) button to select values for the Subcategory
parameter. The Select Values window is displayed. Select the Selected
values option. Select Camcorders, Cameras,
Home Audio, and Y Box Games from the
"Displayed values" list and copy them to the "Selected
values" list. (Hint: Press and hold the [Ctrl]
key to select multiple values.)
8.
Click the OK button twice to close the Select Values and Edit Parameter Values windows. The worksheet is refreshed automatically and the results are filtered based on the subcategory values that you selected.
9.
Name the new worksheet Sales by Subcategory and save the changes that you made to the Profits Analysis workbook. Do not close the workbook.
Summary
In this lesson you learned how to use graphs to present data, how to create advanced calculations to analyze data, how to filter query results using conditions, and how to create parameterized reports.
Place the cursor over this icon to hide all screenshots.
Bookmark
