Using Discoverer Plus Relational, you can create ad hoc queries
to analyze data interactively. Furthermore, you can format the results of your
queries and use conditional and stoplight formatting to create dashboard quality
reports. You can add totals and percentages to summarize data and create advanced
reports. In this lesson you will learn how to sort and format the results of
a query and how to create conditional and stoplight formatting. You will also
learn how to use totals and percentages to analyze data. Finally, you will learn
how to use drilling to explore data in greater detail.
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.
Sorting and Formatting the Results of a Query
In this topic you will learn how to sort
the results of a query to present data in an organized manner. You will also
learn how to format the results of your queries and highlight pertinent information,
using conditional and stoplight formatting to draw attention to important details
in your reports.
Close all workbooks that are currently open. In
a new workbook create a table query by selecting the
following items from the Sales Analysis folder:
Region
Year
Country Name
Sales Revenue (SUM)
Place Region on the page axis and Year,
Country Name, and Sales Revenue SUM
(in that order) on the top axis.
Hint: To create a new workbook, click File >
New.
Note: If you do not see the column headings on your
query, select Edit > Worksheet Properties
from the menu bar. Click the Table Format tab, select
Show column headings, and click OK.
2.
You can sort the results in a table by using
any single column. Standard sort sequences are available: low to high
or high to low.
For example, right-click Year and select the Sort
Low to High option from the shortcut menu.
Year now appears sorted in ascending sequence.
3.
To organize and make the results easier to read or analyze, you can group sort the results by any column that includes repeated values.
For example, right-click Country Name and select the
Group Sort option from the shortcut menu.
Drag Country Name to the left of Year.
4.
Select Tools > Sort from the menu
bar.
The Edit Worksheet window appears with the Sort
tab automatically selected.
You can view all sorts that you have applied to the table already and
modify them, if necessary. For example, you can delete a sort, change
the order of the columns by which the table rows are sorted, or hide a
column from the table by selecting the Hidden check box.
You can also add new sorts and format data in the sorted columns.
5.
Select the sort applied to Country Name (click anywhere
in that row) and click Format. The Format Data
window appears with the Breaks tab automatically selected.
Change the Line thickness to 4 and
the color to light gray. This inserts
a light gray line with a thickness of 4 after each change in Country Name.
6.
Click OK twice to close the Format Data
and the Edit Worksheet windows.
7.
Double-click the worksheet name tab, Sheet 1. The Worksheet
Properties window appears with the General tab
automatically selected. Enter Annual Revenue by Country
in the Name text field.
8.
Select the TableFormat tab. Deselect
the Show vertical gridlines and Show horizontal
gridlines check boxes.
9.
Click OK to close the Worksheet Properties
window.
10.
Select File > Save from the menu
bar. Save the new workbook to the database as Revenue Analysis
Reports.
Sorting Options for Crosstab Queries
To sort the query results in a crosstab, perform
the following steps:
Create a new crosstab query
in the Revenue Analysis Reports workbook by selecting
the following items from the Sales Analysis folder:
Region
Year
Prod Category
Sales Revenue (SUM)
Hint: Click Edit > Add Worksheet.
Place Region and Prod Category (nested
in that order) on the side axis of the crosstab. Place Sales Revenue
SUM (Data Point) and Year (nested in that order)
on the top axis of the crosstab.
Click Finish.
Note: If the crosstab labels are not visible, right-click
any item heading within the query. From the shortcut menu select Format
> Crosstab. Select Show item labels
from the Worksheet Properties window, Crosstab
Format tab and click OK.
2.
Items in a crosstab are automatically group sorted in the order in which
items are nested on the top and side axes of the crosstab. By default,
item headings are sorted low to high. To change the default sort order
of Region, for example, do the following:
Right-click the Region item heading and select Sort
High to Low from the shortcut menu.
The row headings for Region are sorted alphabetically high to low (descending
sequence).
Alternatively, select Tools > Sort
from the menu bar. The Sort Crosstab dialog box appears.
In the Item to Sort area, click Region
and select High to Low from the Default sort
on row headings drop-down list.
Data points in a crosstab can be sorted in the same way by selecting
a single column or row, using the Sort Crosstab dialog box shown in this
screenshot. You can define the sort on any item of the side or top axes.
Note: Sorts defined on data points take precedence
over default sorts defined on column or row headings.
3.
Select Edit > Worksheet Properties
from the menu bar and name the new worksheet Regional Annual Revenue
by Category.
Select the Crosstab Format tab and select <blank>
from the Show null values as drop-down list.
4.
Click OK to close the Worksheet Properties window.
5.
Click the Save
icon to save the workbook, but do not close it.
Formatting Data and Headings in a Table or Crosstab
To format data and headings in a table or crosstab,
perform the following steps:
Open the Annual Revenue by Country
worksheet. Press and hold down the Ctrl
key (to select multiple items) and click the Country Name
and the Year column headings.
Click the Background Color
icon on the Formatting toolbar and select medium-yellow
from the color palette.
Your changes appear.
2.
Click the Sales Revenue SUM
column heading to highlight the data in the corresponding column. Click
the Number Format
icon on the Formatting toolbar.
Select the Currency Format
icon from the drop-down list.
Your changes appear.
3.
Right-click the Sales Revenue SUM column heading and
select Edit Heading from the shortcut menu.
Enter Sales Revenue in the Heading
text field.
Click OK.
4.
Open the Regional Annual Revenue by Category worksheet.
Remove the Available Items and Selected Items
Panes. (Hint: Click View and
deselect both items.)
Click the tile located at the top-left corner of the
data grid to select all the data values as shown in the screenshot).
Selectthe Left justified
and Currency Format
icons from the Formatting toolbar.
5.
Select Middle East in the Region column
of the side axis.
Click the down-arrow
icon next to the New Condition
icon on the Standard toolbar and select the Not
Equals
icon from the drop-down list.
Middle East region data point values are filtered from the crosstab.
6.
Save the workbook but do not close
it.
Applying Conditional and Stoplight Formatting
To apply conditional or stoplight formatting options to the query results, perform the following steps:
Click the Conditional Format
icon on the Formatting toolbar.
The New Conditional Format dialog box appears. Select
Sales Revenue SUM from the Item drop-down
list.
Select greater than (>)from the Condition drop-down list and enter
2500000 (2,500,000) in the Value text
field.
2.
Click Format. The Format dialog box
appears.
In the Color grouping, select medium-blue from
the Background color palette.
3.
Click OK twice to close both the Format
and New Conditional Format dialog boxes.
All the cells with Sales Revenue SUM values exceeding $2,500,000 are
highlighted in medium blue.
4.
Select Format > Conditional Formats
from the menu bar.
The Conditional Formats dialog box appears.
5.
Click New Stoplight Format to open the New Stoplight
Format dialog box. Enter 250000 (250,000) in
the Unacceptable text field and 1000000
(1,000,000) in the Desirable text field.
6.
Click OK. An alert message appears, warning you that
an overlap exists between the ranges in the previously defined conditional
format and the new stoplight format.
Click OK to close the alert dialog box.
7.
Deselect the check box that corresponds to the Sales Revenue
SUM > 2500000 conditional format to deactivate it.
Click OK to close the Conditional Formats
dialog box. The data is highlighted accordingly, as shown in the screenshot.
8.
Save the workbook but do not
close it.
Analyzing Query Results Using Totals and Percentages
In this topic, you will learn how to add totals and
percentages to your reports. By adding totals or percentages to your reports,
you can summarize and analyze information in several ways. For example, you
can use percentages to find out the percent profit contribution of a product
category based on a subtotal or a grand total. By using totals, you can create
averages to reveal trends over time.
Open the Annual Revenue by Country worksheet
in the Revenue Analysis Reports workbook. From the Add
Worksheet
icon drop-down list on the Standard toolbar, select Duplicate
as Table.
The Duplicate as Table window appears with the Table
Layout tab automatically selected. Drag Year
to the Page Items area and Region to the top axis of
the table, to the left of Country Name.
2.
Select the Sort tab. Use the Column
drop-down list to replace Country Name with Region.
3.
Click Format.The Format Data
window appears with the Breaks tab automatically
selected.
Select the Format tab and select medium-yellow
from the Background color palette.
4.
Click OK to close the Format Data window.
Click Properties at the bottom of the Duplicate
as Table page. The Worksheet Properties window
appears. Enter Regional Revenue by Country in the Name
text field.
5.
Click OK twice to close the Worksheet Properties
and Duplicate as Table windows. The new table worksheet,
Regional Revenue by Country, is added to the Revenue Analysis Reports
workbook.
6.
Click the Sales Revenue column heading.
Click the down-arrow
icon next to the New Total
icon on the Standard toolbar and select Sum from the drop-down list.
A subtotal is inserted after each change in region, and a grand total (for the year shown on the page axis) is added at the bottom of the table.
7.
Right-click the grand total for Sales Revenue SUM and
select EditTotal from the shortcut
menu.
The Edit Total window appears. Deselect Generate
label automatically and enter Grand total for the year
in the What label do you want to be shown? text field.
8.
Click Format Data. The Format Data
window appears with the Format tab automatically selected.
Select Bold and Underline from the Style
selections.
9.
Click OK twice to close the Format Data
and Edit Totals windows. (Note: You
may have to resize the Sales Revenue column.)
10.
Right-click the subtotal for any Sales Revenue
SUM and select EditTotal from
the shortcut menu.
The Edit Total window appears. Deselect Generate
label automatically and enter Subtotal for
followed by a space in the What label do you want to be shown?
text field.
Click the down-arrow icon to the right of this field and select Insert Value
from the drop-down list.
11.
Click Format Data. The Format Data window
appears with the Format tab automatically selected. Select
Italic and Underline from the
Style selections.
12.
Click OK twice to close the Format Data and
Edit Totals windows.
13.
Save the workbook, but do not
close it.
14.
Select Edit > Add Worksheet from
the menu bar. Select the Crosstab option and deselect
the Title, Page Items, Graph, and Text
Area check boxes.
Click Next. From the Sales Analysis
folder select the following items:
Region (Europe, Americas)
Year
Quarter
Sales Revenue (SUM)
Click Next. Place Region followed by
Year on the side axis of the crosstab.
Place the Sales Revenue SUM data point and Quarter
(nested in that order) on the top axis of the crosstab.
Click Finish.
Select the entire data grid and select Currency Format
from the Formatting toolbar.
Name the new worksheet Quarterly Sales for Europe and America.
15.
Select Tools > Totals from the menu
bar.
The Edit Worksheet window appears with the Calculations
tab of the Select Items page automatically selected.
On the bottom of the Edit Worksheet window, click New
and select New Total from the drop-down list.
16.
The New Total dialog box appears. Select the Grand
total on right option.
Deselect Generate label automatically and enter
Year Total in the What label do you want to be shown?
text field.
Click OK to close the New Total dialog
box. Do not close the Edit Worksheet window.
17.
Click New and select New Total
again. The New Total dialog box appears.
Select f(x) Average from
the What kind of total do you want? drop-down list.
Select the Subtotal at each change in option, then select
Region from the drop-down list.
Deselect Generate label automatically and enter
Quarterly Average for followed by a space in the What
label do you want to be shown? text field.
Click the down-arrow
icon and select Insert Value from the drop-down list.
18.
Click OK twice to close the New Total
dialog box and the Edit Worksheet window. Year Total
and Quarterly Average for the Americas and Europe appear on the crosstab.
19.
Save the workbook, but do not
close it.
Adding Percentages
To add a percentage calculation to a table or crosstab, perform the following steps:
Open the Quarterly Sales for Europe and America worksheet.
Select Edit > Duplicate Worksheet >
As Crosstab from the menu bar. The Duplicate as Crosstab
window appears with the Crosstab Layout tab automatically
selected.
Select the Show Page Items check box and drag Region
to the Page Items area.
Drag Quarter from the top axis of the crosstab to the
side axis below Year.
2.
Click Properties at the bottom of the Duplicate
as Crosstab window. Enter Quarterly % Contributions
in the Name text field.
3.
Click OK twice to close the Worksheet Properties
and Duplicate as Crosstab windows. TheQuarterly
% Contributions worksheet is added to the Revenue Analysis Reports workbook.
4.
If the Selected Items Pane is not visible,
click View > Selected Items Pane.
In the Selected ItemsPane, click each
of the item totals that were copied to the new worksheet as shown in the
screen capture.
Click the Remove from Worksheet
icon on the Selected Items toolbar.
5.
Click the New Percentage
icon on the Standard toolbar.
The New Percentage dialog box appears.
Deselect Generate name automatically and enter %
Contribution for Year Total in the What do you want to
name this percentage? text field.
Select the Subtotal at each change in option and select
Year from the drop-down list.
Deselect Show the percentage of the grand total for each subtotal.
Select Show subtotal and subtotal percentage (this check
box should be selected by default) and enter Year Total
in the Label text field.
6.
Click OK to close the New Percentage
dialog box. The % Contribution for Year Total column is added to
the crosstab showing the percent contribution to the year total for each
quarter.
7.
Save the workbook.
Analyzing Data Using Drilling
In this topic you will learn how to use the different
drilling facilities to further analyze data.
You can analyze data incrementally by drilling into data.
You start with a small number of items and then you drill to a related item
to expand on the results of your initial findings. The following exercise illustrates
this idea.
In a new workbook, create a crosstab query by selecting the following
items from the Profits Analysis folder:
Prod Category
Quantity Sold (SUM)
Sales Revenue (AVG)
Profit (SUM)
Place Prod Category on the side axis and Quantity
Sold SUM, Sales Revenue AVG, and Profit
SUM on the top axis of the crosstab as shown in the screen capture.
Click Finish.
The new workbook appears.
Based on past performance, Peripherals and Accessories have contributed
the most to the company's bottom line. To determine how well this category
of products performed in each of the years from 1998 through 2001, continue
with Step 2.
2.
Click the drill
icon next to Peripherals and Accessories
and select Year from the Drill to Related
list.
Though demand for these products peaked in the year 1999, overall profits
declined from the prior year. You can surmise that profit for the year
1999 went down, while demand increased due to the decline in the average
price per unit sold (Sales Revenue AVG). This is evidenced in the query;
average price per unit sold in 1998 was approximately $130, whereas in
1999, it declined to approximately $90.
3.
To investigate further, you decide to look into how individual products have performed over the years for which you have data.
Right-click in the data cell, Sales Revenue AVG, which
shows the average price per unit sold for 1999, and select
Drill from the shortcut menu.
The Drill dialog box appears.
4.
From the Where do you want to drill to? drop-down list,
select Drill to a Related Item.
Select Prod Name.
5.
Click OK. Prod Name is added to the
worksheet.
Observe that there was no significant change in the average price per
unit sold (Sales Revenue AVG) for individual products in the Peripherals
and Accessories category in each of the four years for which you have
data. Also, observe that demand (Quantity Sold SUM) for the two most expensive
products (17" LCD w/ built-in HDTV Tuner and 18" Flat Panel
Graphics Monitor) in the category, fell to almost half in 1999 compared
to the demand from the year before.
Though more products were sold from the Peripherals and Accessories category
in 1999, the fact that demand for the more expensive products fell to
almost half compared to 1998, explains both the lower average price per
unit sold ($90) in 1999 and the decrease in the overall profit for the
year.
6.
Name the worksheet Peripherals and Accessories by Year
and save the workbook as Product Demand Analysis.
Using Drill Up or Drill Down
The Drill Up or Drill Down feature in Discoverer Plus Relational
is enabled whenever an item is a member of a predefined hierarchical path. For
example, the Discoverer manager may define the hierarchy: Year
> Quarter > Month > Day.
This hierarchy allows you to drill to any level of this hierarchy.
Bookmark
icon to save the workbook, but do not close it.
key (to select multiple items) and click the Country Name
and the Year column headings. 
icon on the Formatting toolbar and select medium-yellow
from the color palette. 
icon on the Formatting toolbar. 
icon from the drop-down list. 
and Currency Format 
icon next to the New Condition 
icon on the Standard toolbar and select the Not
Equals 
icon from the drop-down list. 
icon on the Formatting toolbar. 
icon drop-down list on the Standard toolbar, select Duplicate
as Table.
icon on the Standard toolbar and select Sum
from the drop-down list.
icon on the Selected Items toolbar.
icon on the Standard toolbar.
icon next to Peripherals and Accessories
and select Year from the Drill to Related
list.