Oracle Discoverer Desktop User's Guide 10g (9.0.4) for Windows Part Number B10272-01 |
|
This chapter explains how to find and arrange the data that you want to analyze. The topics covered in this chapter include:
Laying out data on a table or crosstab organizes it so you can easily compare results side by side, spot trends when you see progressions, track progress over weeks, months and years, and so on. In other words, how the data is arranged is an important aid to analysis because it reveals data relationships that may not be apparent.
Pivoting data is how you move the data from one axis to another to arrange it for efficient analysis. For example, you can pivot data from the body of the table or crosstab to the page axis and back again to see new data relationships.
One way to think about pivoting data is to visualize the data plotted on a graph, and then switching data from the x-axis to the y-axis.
Pivoting a column on a table from the body to the page axis or from the page axis to the body rearranges the order of the data as it is presented and helps you organize the data. On a crosstab, however, pivoting a column has a much different meaning because the positions of data columns determine the data interrelationships of the items on the crosstab. Therefore, pivoting a column on a crosstab not only changes the positions of the data--you may see different numerical values as the data relationships vary. Figures 4-1 and 4-2 provide examples of pivoting on tables and crosstabs.
Key to Figure 4-1
Key to Figure 4-2
Key to Figure 4-3
Key to Figure 4-4
To pivot an Item from a table to the Page Items axis:
As you drag the pointer into the page axis area, it changes to an arrow with a horizontal column marker indicating you're moving a vertical column (in the table body) to a horizontal position (the page axis item).
Key to Figure 4-5
To pivot an Item from the Page Items axis to a table:
The pointer changes to a vertical arrow indicating that you can move the item down to the table body:
Key to Figure 4-6
The pointer changes to a left-right arrow indicating you can put the page axis item into the table body.
You can insert the page axis item into a table at three locations:
Left-most column--drag the pointer onto the first column in the table. The page axis item is inserted to become the first column.
Middle columns--drag the pointer onto a column head. The page axis item is inserted to the left of that column.
Right-most column--drag the pointer onto the blank space after the last column. The page axis item is added as the right most column of the table.
Key to Figure 4-7
Because the data relationships on a crosstab depend on the intersection of the rows and columns, pivoting data from one axis to another creates a new set of data relationships. In addition, the new arrangement can add levels of data to an axis. For example, if the data on the side axis is organized into three levels such as Region, City, and Store Name, pivoting the Year item to the side axis adds a fourth level of data to that axis, (see Figure 4-8 for an example).
To pivot data on a crosstab:
The direction of the pointer indicates how the item will pivot from horizontal to vertical, or vice versa.
Key to Figure 4-8
Notice that the new crosstab now has only one column for the Profit SUM. The Calendar Date Year 2000 data is further down on the side axis. Scroll down the page to see it.
As you can see, it is easy to pivot data in Discoverer. However, sometimes this ease of use also makes it easy to produce erroneous results. For example, in a Crosstab layout, if you remove everything from your left axis or your top axis, you can produce a meaningless table, see example Figure 4-9.
Key to Figure 4-9
When you have a Worksheet, you can duplicate it as another Worksheet of the same type, or you can convert the Worksheet to a different type, for example, into a Table or Crosstab.
For example, if your data is currently displayed as a table, but you want to analyze it using the pivoting features of a crosstab, you can duplicate the table as a crosstab.
To convert a Worksheet table to another Worksheet type:
The dialog box for duplicating the table or crosstab appears, with the Crosstab Layout tab visible.
Figure 4-10 shows the Duplicate as Crosstab dialog box. The dialog box for duplicating tables is similar.
The tabs across the top of the dialog are used as follows:
A new Worksheet is created in your Workbook containing the duplicate table or crosstab.
Drilling into data shows more details about the data. Drilling out of data (or collapsing) consolidates the data for a broader overview. Typically, you drill into data to analyze it at a finer level of detail, and drill out to get the "bigger picture."
In addition to drilling into and out of data on the current table or crosstab, you can also drill to another worksheet, or to a related item that is not on the worksheets. See Section 4.2.5, "Drilling with the Drill Dialog Box" for details.
Discoverer provides two ways to drill into and out of data:
Key to Figure 4-11
This section explains how to use the Drill icon to drill down into data.
Key to Figure 4-12
This section explains how to use the Drill icon to drill out of data.
Key to Figure 4-13
Key to Figure 4-14
Key to Figure 4-15
Key to Figure 4-16
Key to Figure 4-17
To drill into or out of data from the table or crosstab:
You can also double-click on a crosstab data item's heading to drill quickly. See Section 4.2.4, "Quick Drilling by Double-Clicking" for details.
To simultaneously drill all the columns or rows at the same level (Figure 4-18), select them first by clicking the selection box for that level of data. Then put the pointer on the drill icon in any one of the selected columns or rows and click.
Key to Figure 4-18
If you select a level that has already been drilled down, you can collapse the levels back to their previous state.
Key to Figure 4-19
Note that the Quarter level does not appear on the sample menu because the data is already drilled to that level and you cannot collapse a level into itself.
Hint: Use Collapse after you drill data down several levels and want to get back to the original level in one step.
You can also collapse drilled data by selecting the level to collapse to and then choosing Sheet | Collapse.
Key to Figure 4-20
Key to Figure 4-21
The data on the side axis in the Discoverer Tutorial database is organized in the hierarchical sequence of Region, City, and Store Name. Typically, you would drill from Region to City and then from City to Store Name. However, in some instances, you may want to drill from Region directly to Store Name.
The following figure shows how to drill to a sub-level on the side axis. This can also be thought of as skipping a hierarchical level.
Key to Figure 4-22
You can quickly drill down from one level to the next on a crosstab by clicking in a data item's column heading. The drill automatically drills to the next level in the data hierarchy. For example, clicking on the column heading Region automatically drills to City because that's the next level in the hierarchy--Region, City, Store Name.
Key to Figure 4-23
Note: Double-clicking on a column head on a table does not drill down. Instead, it displays the data item's properties.
Double-clicking on an individual cell displays the Drill dialog box.
Use the Drill dialog box to drill up and down from columns and rows on the tables and crosstabs and additionally, to related items on the worksheet or to related items on another worksheet.
To drill with dialog box:
The Drill dialog box appears.
Key to Figure 4-24
Up/Down--drills down to details or up to an aggregate level in the data's predefined hierarchy, such as Year to Quarter to Month.
Drill to a Related Item--drills down to details or up to an aggregate level to data that is related but that is not in the data's predefined hierarchy, such as City to Quarter or Region to Product.
Drill to Detail in another Sheet--drills to data that is in the workbook, but that isn't in the data's hierarchy; another sheet is created to show the details.
The row or column you selected appears here. You can also drill from another starting point by clicking the drop-down arrow and selecting from the list that appears. The choices in the list pertain to the data item to start the drilling and the type of drill option you selected.
For example, if you selected a column heading and Drill Up/Down, the other options are in the column's data hierarchy. The drop-down arrow is not available if you selected a data item that does not have other levels for drilling.
The choices in the list show where you can drill to. The choices depend on the other selections on the dialog box.
The following figures show examples of drilling from the Drill dialog box.
In this example, the drilling starts at the City data, which is in the hierarchy of data--Region, City, Store Name. However, Quarterly data is related to City, so you can drill from City to Quarter.
Key to Figure 4-25
The Drill Options dialog box offers additional ways to customize the drilling results.
Click the Options button on the Drill dialog box to open the Drill Options dialog. Choose from the following options:
Expand to include new item--the table or crosstab is enlarged to fit in the new level of data (see Figure 4-27 for an example).
Replaced with new item--the data level where the drill started is replaced with the new level of data (see Figure 4-27 for an example).
Current Sheet--adds the new level of data on the current worksheet.
New Sheet--creates a new worksheet to display the drilled data.
Table--display the results in a table.
Crosstab--display the results in a crosstab.
Let me modify query before proceeding--displays the New Sheet dialog box so you can format the new layout, add conditions and calculations before the drilling takes place. See Section 7.1, "Building a New Workbook or Worksheet" for a description of the options.
Figure 4-27 shows the difference between expanding and replacing data.
Key to Figure 4-27
Sorting arranges text data in alphabetical order and numeric data in numerical order. Creating an ordered list of customers, or employees, or product part numbers are typical uses of sorting. However, sorting is also helpful for analyzing data. For example, sorting sales data from most profitable sales to least profitable sales shows the relative standing of your company's best selling products or the most effective salespeople.
You can also group data and then sort within the group. For display and reporting purposes, each group of data can start on a new page. See Section 4.3.1.2, "Group Sorting" for details.
Figure 4-28 shows an example of sorting data on a table and the results.
Key to Figure 4-28
To sort data on a table:
The Sort Table dialog box appears. It shows the sorting options currently selected for the table.
You can choose from these options in the Sort Table dialog box:
Column--The name of the column containing the data you want to sort. You can add additional columns to sort at several levels on the table. See the next section for details.
Direction--The sort order of the data in the column. Click the up and down buttons in the Direction box to choose the sort order:
Group--The manner of grouping and displaying sorted data. Click the drop-down button in the Group box and choose one of the options:
Line--The thickness of the line separating the data groups. Click the drop-down button in the Line box and choose a line thickness. The figures in this section show a line for subtotals; you can also use a line when displaying totals.
Spaces--The number of cell spaces between groups. Click the up and down buttons in the Spaces box to choose the spacing.
By adding more sort columns, you can sort data within data.
For example, if you sort data first by Region and then by City, data is sorted alphabetically by Region (Central, East, West) and then within the Regions alphabetically by City (Atlanta, Boston, Miami, and so on). However, if you sort first by Region and then by a numeric column, such as Profit SUM, the data is sorted alphabetically by Region and then numerically by the Profit.
Here is an example showing sorting applied to two columns:
Key to Figure 4-30
To add and rearrange columns to sort:
For example, in the following figure you can add the Department or Profit SUM columns for sorting.
Key to Figure 4-31
The column is added to the list of sorting columns.
Note: The order of the columns on the Sort Table dialog box is important. The order of the columns determines which data is sorted first, second, third, and so on. You can move the columns up and down to put them in the proper order on the dialog box.
If you want to move a column up or down on the list, click the column's handle (just to the right of the column number) to select it. The pointer becomes an up/down arrow indicating you can move the selected column up or down in the order.
Key to Figure 4-32
To remove the selected column for sorting, click the Delete button. Delete removes the column only from the list of sort columns. It does not remove it from the table.
Data in tables is often organized into groups. For example, listings by address are often grouped according to City or Zip Code; tables listing business products might group them by type, such as Office Furniture, Computers, and Supplies. One key reason to group sort data is to find subtotals for groups of numbers.
Note: You can group sort on tables, but not on crosstab displays.
Columns with Group Sort selected always precede those with no group sorting (None) to assure that the sorting is done correctly on the table. You cannot move a column without group sorting above a column with group sorting.
The names of group sorted columns appear in italics on the dialog box.
Here is an example of two tables, one group sorted, the other not:
Key to Figure 4-33
The table without group sorting does not include a subtotal for groups. Ungrouped data cannot have subtotal rows because the data to total can appear anywhere in the table. See Section 5.3, "Totaling Numeric Data" for steps to add subtotals and totals to data sorted by groups.
Group sorting is also pertinent when finding percentages. When you specify percentages for numbers (for example, the percentage of each region's profits of the total profits), the data is automatically group sorted for that section of data (e.g., the regions) so the percentages can be displayed properly. See Section 5.4, "Using Percentages" for more information.
The actual grouping of data is based on the design of the business area and workbook developed by your database administrator. In the previous examples, the grouping is Region and City, but it could also have been State and City or Country and City. See your database administrator if you want to group data in some other way.
Three buttons on the tool bar apply the sort direction and grouping to selected columns on the table:
Key to Figure 4-34
To use the sorting tools:
If a column is already sorted or group sorted, clicking the tool returns the data to its previous unsorted state.
Because the location of data on a crosstab determines the relationship of one data item to another, sorting crosstab data is different to sorting tabular data. In particular, you normally want to maintain those data relationships while rearranging the data.
The way to maintain the data relationships is to sort data on the left axis relative to a specific column on the top axis, or sort data on the top axis relative to a specific row on the left axis. The sorting tools and Sort Crosstab dialog box (Figure 4-37) automatically sort the data in that manner and maintain the data relationships.
Note: Some data on a crosstab layout is already sorted by default. For example, text items automatically sort alphabetically.
Most typical crosstab data sorts can be handled with the sorting tools on the tool bar (Figure 4-34). Sorting a crosstab with the tools on the tool bar automatically sorts all of the items on an axis.
To sort crosstab data from the tool bar:
Key to Figure 4-35
Note: You can also sort on the left axis by clicking the selection handle of the item on that axis and then click one of the sorting tools on the tool bar.
After sorting a crosstab, the data on the top axis or left axis is reordered relative to the column or row you used for sorting. To return the crosstab to it previous "unsorted" state, you must manually resort it.
In the example above (Figure 4-35), you would resort the data by the items in the left axis, Region and City. Click on the Region item's selection box and then click the sorting tool on the tool bar to resort. Repeat with the City item. The crosstab reverts to its previous state.
Data on each axis is sorted by default:
You can reverse the sort order by selecting the item on the axis and clicking the Sort Order tool on the tool bar.
For example, in the following figure, Regions are sorted from high to low (Z-A). To reverse this order, select the item handle as shown in the figure, then click the A-Z tool on the tool bar.
Although the sorting tools on the tool bar provide most of the types of sorting that you will want to do on a crosstab, the Sort Crosstab dialog box offers additional features and more control over the precise aspects of the sorting.
For example, in Figure 4-35, the Region and Cities are both sorted according to the Profit SUM amounts. By using the Sort Crosstab dialog box, you could sort only the Regions or only the Cities and thus display sort results that are more "finely tuned."
To sort data on a crosstab with the dialog box:
The Sort Crosstab dialog box appears.
For example, after Cities are sorted in the example shown in Figure 4-37, you could repeat the process and sort by Region. Being able to successively sort lets you create a crosstab that orders the data in precisely the way you want.
The Sort Crosstab dialog box includes the following options:
Above the data--Sorts the crosstab based on data from items on the top axis.
Along left side of data--Sorts the crosstab based on data from items on the left axis.
Item to sort--Lists the items for either the top axis or side axis depending on the selected sort location (above the data or along the left side). Click the drop-down arrow and select the item that you want to use to sort the data.
Data Point--The name of the item that will be rearranged as the data is sorted.
Column/Row--Identifies the column that contains the data for sorting when sorting based on data from the left side of the crosstab, or identifies the row that contains the data for sorting when sorting based on data from the top of the crosstab. Click the down arrow to select the specific column or row.
Direction--Click the up or down arrows to select the sort direction.
Add--Click to add a data point for sorting. (See below for more information.)
Delete--Select an item in the dialog box and click to remove it. You cannot delete all of the items on the left axis on a crosstab.
When you click the Add button in the Sort Crosstab dialog box, a drop-down list shows you the data points on the crosstab that can be used for sorting. For example, in the figure above, the added data point is Profit SUM. Without adding that data point to the sorting, the crosstab in the example would simply be sorted alphabetically by City down the left side of the crosstab. With the added data point, however, the cities are sorted by Profit SUM from lowest to highest. In other words, the City entry on the dialog box identifies which data you want to sort, and the added data point (Profit SUM) identifies how you want to sort the cities (by profit from lowest to highest).
Added data points must always be the first item for sorting. (City cannot be above Profit SUM in the example.) This is because sorting items by data points makes logical sense, but sorting data points by items does not.
To illustrate this concept, it makes sense to sort Cities by Profit because each City has a Profit amount associated with it. However, it does not make sense to sort Profit by City because each profit has only one city associated with it. It would be like trying to sort the profit amounts by "New Yorks" or "Phoenixes" which doesn't make logical sense.
You can add the data point two or more times. This is useful with duplicate data points. In the example, if two cities had exactly the same amount of profit, you could specify how to sort those two duplicated pieces of data (low to high or high to low). This type of "sorting within sorting" on a crosstab is helpful for text or other data likely to have duplicate values. For financial data or other variable numeric items, however, sorting within sorting is usually not necessary.
|
![]() Copyright © 1996, 2003 Oracle Corporation. All Rights Reserved. |
|