This chapter explains how to find and arrange the data that you want to analyze.
The topics 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.
Select the column heading and drag it up to the page axis area.
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).
Release the mouse button. The column moves to the page axis.
The pointer changes to a vertical arrow indicating that you can move the item down to the table body:
Drag the pointer down onto the table where you want the page axis item to go.
The pointer changes to a left-right arrow indicating you can put the page axis item into the table body.
Release the mouse button. The page axis item becomes a column in the table.
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.
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).
Click the axis item marker of the item to pivot.
Drag the pointer to its position on the new axis. You can drag the pointer onto the axis marker or onto one of the rows at that level.
The direction of the pointer indicates how the item will pivot from horizontal to vertical, or vice versa.
Let go of the mouse button. The item moves to the new axis.
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 Desktop. 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.
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:
Choose Sheet | Duplicate a Table or Sheet | Duplicate as Crosstab.
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.
Click OK now if you want to accept the default table or crosstab configuration.
If you want to change the default table or crosstab configuration, choose from the four tabs and configure your duplicate table.
The tabs across the top of the dialog are used as follows:
Select Items—adds new items to the new table or crosstab (see Section 7.1.2, "Selecting the Data" for details).
Conditions—adds conditions to the new table or crosstab (see Section 5.5, "Finding Data that Meets Conditions" for details).
Sort—(available for tables only) sorts the data on the new table (see "Key to Figure 4-27:" for details).
Calculations—defines calculations on the new table or crosstab (see Section 8.3, "Creating Calculations" for details).
Click OK to create your duplicate table.
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 Desktop provides two ways to drill into and out of data:
Drilling directly from a table or crosstab - clicking a Drill Point to display a Drill Icon is the quickest way to drill, and covers many types of drilling typical in routine data analysis, (see Figure 4-11).
Drilling from the Drill dialog box - using this menu option provides even more options for drilling. Select an Item or cell and choose Sheet ¦ Drill from the main Discoverer Desktop menu. You can also choose the Drill option from the Discoverer Desktop tool bar.
This section explains how to use the Drill icon to drill down into data.
This section explains how to use the Drill icon to drill out of data.
Click the drill icon in the column or row with the data that you want to drill. Drill icons are arrowheads that point to the right.
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.
From the drop-down menu, choose the level of data that you want to analyze.
If you select a level that has already been drilled down, you can collapse the levels back to their previous state.
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.
The data on the side axis in the Discoverer Desktop sample data 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.
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.
Note: Double-clicking on a column head on a table does not drill down. Instead, it displays the data item's properties.
To drill with dialog box:
Display the table or crosstab with the data you want to drill.
On the table or crosstab, click the data item where you want to begin drilling.
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.
Select where to drill from.
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.
Select where to drill to.
The choices in the list show where you can drill to. The choices depend on the other selections on the dialog box.
Click OK. The data on the table or crosstab is then drilled to the level you specified 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.
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).
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.
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 18.104.22.168, "Group Sorting" for details.
Figure 4-28 shows an example of sorting data on a table and the results.
Display the table with the data you want to sort.
Select the sorting options and click OK.
You can choose from these options in the Sort Table dialog box:
Hidden - Sub-group the data at the next hierarchical level, but do not display the sub-grouped item. For example, if the hierarchy is Year, Quarter, Month, sorting on Year with the Hidden option selected sorts on Quarter within Year but does not display the Quarter Item, or line separators for each Quarter. To display sub-grouped Items, change this option to Group.
None—The data in the column is not grouped and is all sorted as a unit.
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.
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:
To add and rearrange columns to sort:
On the Sort Table dialog box, click the Add button to see additional columns for sorting.
For example, in the following figure you can add the Department or Profit SUM columns for sorting.
Choose the additional column.
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.
Drag the column up or down or click the move up or move down buttons.
When the order of the columns is correct, select options for Direction, Group, Line, and Spaces for each column (Figure 4-29), then click OK.
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.
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:
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.
Select the table column(s) to sort.
Click the tool to sort the column.
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.
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.
Select either the column on the top axis or the row on the left axis to use for sorting.
Click the A-Z tool or the Z-A tool. The data on the crosstab is sorted relative to the selected column or row.
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.
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:
Display the crosstab with the data you want to sort.
Select the options (described below) from the dialog box and click OK to sort the data according to the selections on the dialog box.
Repeat the process to add other sorting to the data.
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.
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.)
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.