3
Analyzing Data

Discoverer provides a series of data analysis techniques that you can apply to your worksheets.

Sorting--arranges data into meaningful, useful sequences so that you can easily analyze the information and prepare reports. For example, to prepare a report showing sales figures by store name, you would first sort the data by store name. You can then summarize the sales figures for each store. Click to learn more about Sorting Data.
Pivoting rows and columns--reorganizes data to gain different perspectives on it. When pivoting data, you move the data from one axis to another to arrange it for efficient analysis. That is, you can move rows to become columns, or columns to become rows. For example, you might want to view annual sales data side-by-side on a worksheet for easy comparison. You might then want to compare the data on a store-by-store basis for the same periods. In most cases, just by pivoting the data you can quickly and easily switch between different views of the data. Click to learn more about Pivoting Data.
Drilling into and out of levels of detail--you use drilling to either consolidate data to see it at an aggregate level, or to see finer levels of detail. You drill out to get the big picture; you drill in to see the details. Click to learn more about Drilling Into and Out of the Data.
Adding calculations to data--calculations produce additional data for worksheets. Simple calculations based on the data in a worksheet can produce typical business answers, such as sales commissions per salesperson, royalty fees paid to a supplier, and so on. Complex calculations can find the answers to more complicated questions including "what if" scenarios. In other words, instead of merely viewing data to find trends and answers, you can use calculations to rigorously analyze the data using mathematical techniques. Click to learn more about Adding Calculations to Worksheets.
Totaling Data--numeric data on worksheets is presented in rows and columns. You can sum their data to produce totals and sub-totals. Click to learn more about Totaling Numeric Data.
Finding Percentages--finding percentages is a typical data analysis task. Percentages are added in a new column on a table. Click to learn more about Calculating Percentages.
Graphing your data--create a variety of graphs based upon your worksheet data. Choose from standard graph types, such as bar or pie, and customize graph elements such as the legend and Marker colors. Click to learn more about Graphing Your Data.

Sorting Data

Sorting arranges data into either alphabetical or numerical order. For example, a list of parts might be sorted by part number, and a list of customers might be sorted by last name. You normally sort alphabetic information (store names, customer names, and so on) into alphabetical order; you sort numeric information (sales figures, quantity of units sold, and so forth) into numerical order. In either case, you can sort the data from Low to High--which is A to Z or 1 to 10, or High to Low--Z to A or 10 to 1.

NOTE: Although examples in this section show sorting from A to Z, Discoverer sorts data according to the alphabetical sequence most appropriate for the language you selected to use with Discoverer. Please see your Discoverer Administrator about setting up the appropriate sort for your language.

Sorting is also helpful for analyzing data. For example, sorting sales data from most profitable sales to least profitable sales shows the your company's best selling products or the most effective salespeople. In addition to simple sorting, you can also sort data within data. For example, companies often use targeted mailing campaigns based on customer addresses in particular zip codes. You could sort the data by zip code, and then by address within the zip code.

Click to learn about:
Simple Table Sorting
Group Sorting
About the Sort Order
Sorting Data on Crosstabs

Simple Table Sorting

You use the Sort dialog to select the data to sort and the sort order.

In the following example, the primary sorted data is Region, which is sorted alphabetically so the three regions appear on the table as Central, East, and West. Region is sorted Lo to Hi, which for text data is alphabetical from A to Z.

To sort a single column of data on a table:

Open the table with the data you want to sort.
From the menu, choose Tools | Sort or click the sort icon on the tool bar.

The Sort Table dialog box appears. It shows the sorting options currently selected for the table.

Click the Add button and choose the data item from the drop-down list that you want to use for sorting the data. The item is added to the Column.
Click the Directions drop-down list and select the sort order:
- Lo to Hi--A to Z for text; increasing for numbers (e.g., 1 to 10).
- Hi to Lo--Z to A for text; decreasing for numbers (e.g., 10 to 1).
Click the Group drop-down list and select an option for group sorting. See the section "Group Sorting" for details.
Click the Hidden box to hide the data item being used for sorting. For example, you could designate a sort order by year, but not show the year column.
Click OK. The data is sorted on the table.

Group Sorting

Applying group sorting displays each data value at the top row of a group. In the following figure the table on the left is group sorted by Region so the Region name only appears at the first city in the region. The table on the right is also sorted by Region, but is not group sorted. In this case the Region name appears next to each city name.

One key reason to sort data by groups is to find subtotals for groups of numerical data. See Section , "Creating a New Total" 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 the section, Creating a New Percentage, for more information.

NOTE: You can sort data by groups on table worksheets, but not on crosstab worksheets.

You can also sort data within the groups. The following sample shows data is first sorted by Region, and then within each Region group by Profit Sum from low to high. You can easily see the relative profit standings of each city.

To sort data by groups:

Open the table with the data you want to sort.
From the menu, choose Tools | Sort or click the sort icon on the tool bar.

The Sort Table dialog box appears. It shows the sorting options currently selected for the table. If you do not want to sort by that data item, click its column handle and click Delete.
Click the Add button and choose the data item from the drop-down list that you want to use for sorting the data. The item is added to the Column.
To add a data item to be sorted within the Group, click the Add button again and add another data item. In the following example, the Region is the group, and the Profit SUM is the data item to be sorted with each Region.

Columns with Group Sort selected always precede those with no group sorting (Group="None") to assure that the sorting is done correctly on the table. If you move a column without group sorting above a column with group sorting, the column you're moving is automatically set to group sort. Similarly, if you move a column with group sorting below a column without group sorting, the column on top is automatically set to group sort.
Select the Direction options for each column. The sort direction does not have to be the same for each column. For example, sorting Region from Lo-Hi arranges the regions alphabetically, but sorting Profit SUM from Hi-Lo arranges them with the most profitable at the top (the highest Profit SUM) and the least profitable at the bottom.
From the drop-down list in the Group column select a group sorting option.
- None--data in the column is not grouped and is all sorted as a unit. Usually the last data item in a group sorting has the None option selected.
- Group Sort--data is sorted within each group. The group name appears once at the beginning of the grouped data.
- Page Break--data is sorted within each group. The group name appears once at the beginning of each new page.
Click OK. The data is sorted on the table.

About the Sort Order

The order of the columns on the Sort dialog is important because it affects how you can compare the data quickly based on the sorting. 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 order that you want on the dialog box. 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.

In the example below, the table on the left is sorted in Profit SUM, then Region. This enables you to see the Profit SUM figures in the order lowest first, highest last. In the table on the right, the sort order is Region, then Profit SUM. This enables you to compare Departments in the same Region.

Sorting Data on Crosstabs

Because the location of data on a crosstab determines the relationship of one data item to another, sorting crosstab data is somewhat different from 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 Sort Crosstab dialog automatically sorts the data in that manner and maintains the data relationships.

NOTE: Data on a crosstab layout is already sorted by default. Text items are automatically sorted alphabetically from A-Z and numbers are sorted from lowest to highest, but you can reverse the sort order.

The following example illustrates a crosstab sorted by City (A-Z order) within Region (A-Z order).

Sorting Crosstabs Data on the Sort Crosstab Dialog

The Sort Crosstab dialog offers a full range of options for sorting Crosstab worksheets.

To sort data on a crosstab:

Open the crosstab with the data you want to sort.
From the menu, choose Tools | Sort. The Sort Crosstab dialog appears.

Select the location on the crosstab of the items you want to use to sort the data.

Above the data--shows the data items on the top axis.

Along left side of data--shows the data items on the left axis.
Click the drop-down list of Item to Sort and select the data item. The list includes 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 Column/Row drop-down list and select the specific column or row in the crosstab to use for sorting.

The Column drop-down list identifies the column that contains the data for sorting when sorting is based on data from the left side of the crosstab. The Row drop-down list identifies the row that contains the data for sorting when sorting is based on data from the top of the crosstab.
Click the Direction drop-down list and select the sort direction.
If you want to change the sort order, select one of the data items and click Move Up or Move Down.
Click OK. The data is sorted on the crosstab.

Repeat the process to add other sorting to the data.

For example, after Region are sorted in the example shown above, you could repeat the process and sort by Cities. Being able to successively sort lets you create a crosstab that orders the data in precisely the way you want.

NOTE: 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.

Adding a Data Point

Adding a data point to a crosstab sorting enables you to sort the data in some other arrangement. Added data points must always be the first item for sorting. 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 the City item by the Profit data point because each City has a Profit amount associated with it. However, it does not make sense to sort Profit by City because each profit value has only one city associated with it. It would be like trying to sort the profit amounts by "New York" or "Phoenix," 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.

Pivoting Data

Pivoting organizes your data by moving items from the main body of a table worksheet to the page axis. On a crosstab worksheet you have even more control over the elements you can pivot. For example, you can move data items from the main body of the crosstab worksheet to the page axis, side axis or top axis.

Click to learn how
To pivot an item on a table:

To pivot an item on a table:

Open the table with the data you want to sort.
From the menu, choose Sheet | Table Layout, or click the Layout icon on the toolbar. The Edit Sheet dialog appears with the Table Layout tab selected.

The layout shows the items on the table and their current positions on the table.
Select the column to pivot. You can pivot from the page axis to the table or vice versa.
Drag the column to its new location, represented by a black bar, and release the mouse button.

The following example shows how to pivot the Region column to the Page Axis.

The Region column moves to the Page Axis.
Click OK.

The Region column moves to the Page Axis on the Worksheet.

The following example shows what the worksheet looks like before and after pivoting the Region item to the page axis.

As you can see, putting the Region on the Page axis means that only one Region at a time appears on each page of the worksheet. To see the data from other Regions, select a new Region from the Region drop-down list, as shown in the following figure.

Pivoting Data on a Crosstab

Because the data relationships on a crosstab depend on the intersection of the axis items, 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 for Region, pivoting the Year data item to the side axis add another level of data to that axis.

Use the same drag-and-drop process to move a data item from one axis to another on a crosstab, just as you do to move the columns on a table as shown above.

To pivot an item on a crosstab:

Open the crosstab with the data you want to sort.
From the menu, choose Sheet | Crosstab Layout. The Edit Sheet dialog appears with the Crosstab Layout tab selected.

The example below shows a Crosstab Worksheet and its Crosstab Layout arrangement.
Select the item to pivot. You can pivot among the page axis, top axis, and side axis.
Drag the item to its new location, represented by a black bar, and release the mouse button.
Click OK.

In the example below, the Year Item has been pivoted to the left-hand axis, and the Region Item has been pivoted to the top axis. You can then make more direct comparisons between Regions as the Regions appear side by side.

As you can see, pivoting items on a crosstab provides you a powerful means to analyze the data.

Drilling Into and Out of the Data

Drilling helps you easily locate related information in a worksheet. For example, suppose you're analyzing data showing activity at a quarterly (3 months) level. To see the data at a higher level, such as yearly, you can drill out of that information. Similarly, if you want to analyze the data at a monthly level, you can drill into that level.

Drilling out of data consolidates the data for a broader overview.

Drilling into data shows more details about the data.

So, drill into data to analyze it at a finer level of detail, and drill out to get the larger picture.

Discoverer provides drill icons to quickly and easily drill up or down in a table or crosstab.You can use drill icons to drill through data in several ways.

Any data item that permits drilling has a drill icon on the worksheet. You can use the drill icon to drill up or down through the data structure. The drill icons are the small arrowheads next to the column headings.

Click to learn how
To drill into or out of data from the table or crosstab:
To collapse drilled data:

To drill into or out of data from the table or crosstab:

Click the drill icon in the column or row with the data that you want to drill.

A drop-down menu appears for the item. For example, if you click the drill icon for City, the drop-down menu shows that you can drill down to the Store Names within the city or up to the Region in which the city is located.

From the drop-down menu, choose the level of data to which you want to drill up or down.
- To drill down, select one of the levels below the current level. In the example you would select Store Name.
- To drill back up, select one of the levels above the current level. In the example you would select Region.

If you're drilling down, Discoverer finds the more detailed data specified by the drill and displays it on the worksheet.

If you're drilling up, Discoverer consolidates the data into a more concise worksheet.

Collapsing Drilled Items

If you select a data item to which you have already drilled down, you can collapse the levels back to their previous state.

To collapse drilled data:

Click the drill icon of the data item.
Select a value above the current value from the drop-down menu.

Note: Data on a worksheet is often organized in the hierarchical sequence. Typically, you would drill, for example, from Region to City and then from City to Store Name. However, in some instances, you might want to drill to data out of that sequence. That is, you might want drill into the data from Region directly to Store Name while skipping the drill to City. This can also be thought of as skipping a hierarchical level. To drill to another level out of sequence, simply select the level you want from the drop-down menu.

Duplicating Tables and Crosstabs

Duplicating tables and crosstabs provides a quick, easy way to present still more perspectives on the data. You might, for example, want to duplicate an existing table so that you can use the analytical properties offered by pivoting on a crosstab layout or vice versa.

To duplicate a crosstab as a table, or vice versa:

Open the worksheet that you want to duplicate.
From the menu choose Sheet | Duplicate a Table or Sheet | Duplicate as Crosstab. The dialog box for duplicating the table or crosstab appears.

The dialog box appears with the Table Layout tab or Crosstab Layout tab selected depending on the duplication you're doing.
Indicate which items you want to display in the new table or crosstab:

Show Page Items--show/hide the page items box on the table or crosstab. If page items already exist for the worksheet, Discoverer disables this option and shows the page items portion of the worksheet.
Arrange the columns and page items so the duplicated table or crosstab appears as you want it.
Click OK.

Adding Calculations to Worksheets

Calculations can play an important part when analyzing data. Discoverer has a full range of common mathematical functions and operators to calculate results on your worksheets. Discoverer displays the results of calculations as new columns on a worksheet, or the calculations can be part of other calculations.

Click to learn more about
Creating and Editing Calculations

Here is an example of a simple calculation.

Example: Calculate a royalty of 6% on the sum of the profits.

This example uses the Profit SUM data item and multiplies it by .06 to produce the royalty rate. The answer appears in a new column with a name you type on the New Calculation dialog. In this example it is Royalty Fees.

The following figure shows the results of applying the calculation.

Not all calculations need to use Items or Functions as part of the calculation formula. You can type a formula directly into the Calculation box.

Creating and Editing Calculations

You use the Calculations dialog to create calculations.

To create or edit a calculation:

Open the worksheet on which you want to apply the calculation.
Choose Tools | Calculations. The Edit Worksheet dialog appears, open to the Calculations tab.

This dialog shows calculations already created for the worksheet. Checkmarked calculations are active and apply to the worksheet.
Click New or Edit. The New or Edit Calculation dialog appears.

Type a name for the calculation in the box at the top of the dialog. This name appears on the worksheet as the column header of the calculation results column.
Click the Show drop-down list to see the different expressions.

Functions--Lists a wide range of mathematical functions that you can apply to the formula.

Selected Items--Lists the items in the worksheet; this is helpful because you don't have to remember the name of an item in order to include it in a formula.

Available Items--Lists all the items available for the worksheet even if the items are not currently used on the worksheet.

Calculations--Lists the calculations defined for the worksheet in case you want to use an existing calculation as part of your new calculation.

Parameters--Lists the parameters defined for the worksheet.
Click each part of the expression that you want to add to the calculation and click Paste. The item or function moves to the Calculation text box. You can also drag from the box on the left to the Calculation text box.
Click the operator (for example, + or -) button to add mathematical operations to the Calculation text box.
Continue to add items, functions, operators, and so forth until you complete your calculation expression.
Click OK to save the expression. The Edit Worksheet dialog appears and displays the name of the calculation you just created or edited.
To apply the calculation to your worksheet, make sure it has a checkmark in the box next to its name.
Click OK.

NOTE: To see more examples of Calculations, refer to Calculation Examples.

Totaling Numeric Data

When working with numeric information, you often need to see various summations of the data. Totals can sum rows and columns of numbers, find averages and standard deviation, compute subtotals and grand totals, and so on. When you add a Total to a worksheet, Discoverer automatically adds a column or row to the worksheet for the totals data.

In the example below, the Worksheet contains a sub-total for each Region and a grand total for all Regions.

Click to learn more about
Displaying Existing Totals
Creating a New Total
Editing a Totals Definition

Displaying Existing Totals

You can define totals for a worksheet and then display them on the worksheet or not.

To display totals or subtotals on a table or crosstab:

Open the worksheet to which you want to add a total.
From the menu, choose Tools | Totals. The Edit Worksheet dialog appears with the Totals tab selected. The list of totals shows all currently defined totals.

Click the box in front of the Total definition so that a checkmark appears.
Click OK. Discoverer now computes the totals and displays them in the table or crosstab.

To remove the totals from the data:

From the menu, choose Tools | Totals. The Totals dialog appears.
Click the checkmark box(es) to remove the checkmark.
Click OK. Discoverer removes the totals from the table or crosstab.

Creating a New Total

Creating a new totals definition has three steps:

Select the totals to calculate.
Select the type of total and where to place it in the table or crosstab.
Create a label for the totals column or row.

To create a new totals definition:

Open the worksheet to which you want to add a total definition.
From the menu, choose Tools | Totals. The Edit Sheet dialog appears with the Totals tab selected.
Click the New button. The New Total dialog appears.

Click the data point drop-down list and select the data point to use for totaling data; for example, Profit SUM.

You can also create totals for all the data points on the worksheet by selecting All Data Points from the drop-down list.
Click the drop-down list for the kind of total you want and select the calculation to use for totaling data.

The options are:

Sum--Adds all the values.

Average--Adds all the values and divides by the number of values.

Average Distinct--Adds all the unique values and divides by that number of values. Duplicated values are not included. For example, if a set of values includes 3, 3, 4, 5, 5, 6, and 7, the calculation of the distinct average is 3+4+5+6+7 divided by 5.The duplicate values of 3 and 5 are not included.

Count--Counts the total number of values.

Count Distinct--Counts the number of unique values.

Minimum--Finds the lowest value.

Maximum--Finds the highest value.

Standard Deviation--Calculates the standard deviation. Standard deviation is the square root of the variance of the values.

Standard Deviation Distinct--Calculates the standard deviation, but only using unique, unduplicated values.

Sum Distinct--Adds the values, but only using unique, unduplicated values. for example, the sum distinct of 3, 3, 4, 5 is 3+4+5=12. The duplicate value of 3 is not included.

Variance--Calculates the variance. Variance is the sum of the squares of the differences between each value and the arithmetic mean, all divided by the number of values.

Variance Distinct--Calculates the variance, but only using unique, unduplicated values.

Percentage of Grand--Calculates the Grand Total of the row or column, then finds the percentage of the current column or row of the Grand Total.

Percentage of Grand Distinct--Calculates the percentage of the Grand Total of the row or column, but only using unique, unduplicated values.

Select where you want the total to be shown.

Grand total at bottom--Calculates the Grand Total for a column and places it after the last row of the table or crosstab.

Grand total on right (crosstab only)--Calculates the Grand Total for a row and displays it in a column on the right side of the crosstab.

Subtotal at each change in--click the drop-down arrow to select the data item to use for the totals. For example, if you sort the data by Region, and want to see profits by region, select Region as the data item. Then, Discoverer automatically displays the total profit for each region on a separate line.

All Group Sorted Items--displays totals for items set to be group sorted. For example, if the table contains two columns of numeric data set to be group sorted, subtotals are displayed for both columns. Data points not appropriate for the type of total are not displayed.

For example, Region is a set of data points but summing Region by its data points doesn't make sense--it would be like trying to add "Central" to "East". In this case, Regions are not summed even if it is a group sorted item.

A specific numeric data point (such as Profit SUM in the example)--displays totals for the selected set of data points.

A non-numeric data point (such as Region in the example)--when you select a non-numeric set of data points, the options for the totals in the first drop-down list are limited to only those options that apply to non-numeric data points. For example, if you select Region, sum of regions does not make sense. The only totals that make sense for non-numeric data points are Count, Count Distinct, Maximum, and Minimum.

Don't display subtotal for a single row--If the group of data consists of a single row, do not display a subtotal for it (the row's data value and subtotal are the same).
Click one of the options for the current page or all the pages of the worksheet.

Click the option to generate the label automatically if you want Discoverer to generate a label based on the data items being totaled.

You can click the drop-down list for labels and choose additional options for the title from it. The options from the drop-down list produce labels that can change as the data changes by adding text codes (such as "&Item" and "&Value") to the label. In the actual labels in the table or crosstab, the ampersand (&) will not appear, and appropriate names from the table or crosstab will be inserted in place of the words "Item" or "Value".

This table shows some examples.

Option	Example	Sample label
Insert Item Name	Latest Data from &Item	Latest Data from Region
Insert Data Point Name	Top Performers of &Data	Top Performers of Profit Sum
Insert Value	Total Income for &Value	Total Income for Central

If the total calculates for all data points (as selected at the top of the dialog), the labels can appear for each appropriate name. For example, when totaling two items, and you select Insert Item Name (&Name), labels for both item names appear in the data or crosstab.

To remove options from the labels, click in the label text in the dialog and edit it as you would regular text.

Click OK when you're finished creating the Total.

Editing a Totals Definition

To edit a totals definition:

Open the worksheet with the Total you want to edit.
From the menu, choose Tools | Totals. The Edit Sheet dialog appears with the Totals tab selected.
Select the definition you want to edit in the Totals dialog.
Click the Edit button. The Total dialog appears.
Make the changes you want.
Click OK. The totals definition is now edited.

Calculating Percentages

Calculating percentages of numbers is a typical data analysis task. Using the Percentages feature, you specify the data to use to calculate a percentage as well as the value to use to represent the percentage (Grand Total, Subtotal, and so on).

Note: Due to rounding of data, percentages might not add exactly to 100.

In the following example, Percent Profit SUM shows the Profit SUM for each Region as a percentage of total profit for all three Regions.

Click to learn more about

Displaying Existing Percentages
Creating a New Percentage
Displaying Existing Percentages

Displaying Existing Percentages

You can define many Percentages definitions and then display them if you want on the worksheet. You can also display the percentage of Subtotals and Grand Totals of the data.

To display percentages on a worksheet:

From the menu, choose Tools | Percentages. The Edit Worksheet dialog appear with the Percentages tab selected. It shows the percentages already defined for the worksheet.

Click the box in front of a percentage definition so a checkmark appears.
Click OK.

Discoverer now computes the percentages and displays them on the worksheet.

To remove the percentages from the worksheet:

From the menu, choose Tools | Percentages. The Percentages dialog appears.
Click the checkmark box(es) to remove the checkmark.
Click OK to remove the percentages from the worksheet.

Creating a New Percentage

Creating a new percentage definition has three basic steps:

Select the data item for calculating the percentage.
Choose to calculate the percentage of a total or of subtotals.
Create a label for the percentage column.

To create a new percentage definition:

From the menu, choose Tools | Percentages. The Edit Worksheet dialog appears with the Percentages tab selected.
Click the New button. The dialog for a new definition appears. The following example is for creating a new percentage for a crosstab worksheet. The dialog for a table worksheet is similar.

Click in the box for the name of the percentage definition and type a name for it.
Click the drop-down list to see the list of data points to use to calculate percentages. Select the data item from the list.
Select one of the options to calculate a percentage.

The following table lists your choices:

Option	Description
Grand total of all values	Calculates the percentage of the Grand Total for all the columns and rows.
Grand total for each column (crosstab only)	Calculates the percentage of the Grand Total for each column.
Grand total for each row (crosstab only)	Calculates the percentage of the Grand Total for each row.
Subtotal at each change in	Calculates the percentage and places it at each new value for the selected item. Select the item from the drop-down menu where you want the percentage to be displayed when the value changes. This option is usually used for data sorted as groups and you want to see the percentage amount for each group rather than for each individual item.

The illustration on the right side of the dialog shows a representative worksheet containing percentage columns based on your selections.

Select to display the percentages for all page items, or only for the current page items.
On the right side of the dialog, select whether to display the total amount as well as the percentage. For example, if you select to calculate percentages for subtotals at each change in the City data item, you can also select to display the subtotals and their percentages, and subtotals as a percentage of the Grand Total.
Type labels for the percentages, or click the drop-down lists for labels and choose additional options.

The options from the drop-down menu produce labels that can change as the data changes by adding text codes such as "&Item" and "&Value" where you insert them in the label text. In the actual labels in the table or crosstab, the ampersand (&) will not appear, and appropriate names from the table or crosstab will be inserted in place of the words "Item" or" Value".

The table below shows some examples.

Option	Example	Sample label(s)
Insert Item Name	Percent of Profit from &Item	Percent of Profit from Region
Insert Data Point Name	Top Performers of &Data	Top Performers of Profit Sum
Insert Value	Yearly Percent for &Value	Yearly Percent for Central

To remove options from the labels, click in the label text in the dialog and edit it as you would regular text.

Click OK to return to the Percentages dialog with the new definition. Click OK to display the new percentage to the worksheet.

To edit a percentage definition:

From the menu, choose Tools | Percentages. The Edit Worksheet dialog appears with the Percentages tab selected.
Select the definition you want to edit.
Click the Edit button. The Edit Percentage dialog appears.
Make the changes you want.
Click OK. The percentage definition is now edited.

Graphing Your Data

A graph is a pictorial presentation of numeric data. A graph is also an analysis tool that you use to visually highlight relationships or trends. Types of graphs include area, bar, line, pie, scatter graphs, and others. Values from worksheets, or data points, are displayed as bars, lines, pie slices, etc.

Discoverer provides the Graph Wizard to help you create and edit graphs. A series of dialogs takes you through the processes of choosing the data that you want to graph, what kind of graph you want, and how the graph should look.

Click to learn more about
Graphing terminology
Choosing the best graph type for your data
Graph Types Described

About Worksheets and Graphs

Each Discoverer worksheet can have one graph. If you already have a graph in a worksheet and want create a completely new graph, you can either:

First delete the existing graph, then create a new graph.
Duplicate the worksheet to create a new worksheet, then create a graph for the new worksheet, (using the options Sheet ¦ Duplicate as Table and Sheet ¦ Duplicate as Crosstab).

For more information about deleting graphs, refer to Deleting Your Graph.

If you change the data in a worksheet, the graph automatically updates to show the new data. Graphs are also automatically saved with the worksheet. You do not have to save a graph, although you can edit it or delete it if you wish.

Graphing terminology

The terminology below appears in the Graph Wizard and in the documentation.

Group

In a graph, a group is a subset of the displayed data, generally Markers that are connected to each other or are aligned with each other. For example, in a stacked bar graph, each stack of bars is a group.

Marker

A Marker is a graphical object that represents data values. Data Markers can be bars (in bar graphs), lines (in line graphs), slices (in pie graphs), areas (in area graphs), or data points (in scatter graphs). Markers of the same shape and color are referred to as a data series.

Label

Labels are text attached to graph Markers. For example, if your graph shows four bars that represent sales for each quarter, the labels at the top of each bar could show the total sales amount in each quarter. Both the X-axis and the Y-axis can have labels. Even the individual slices of a pie chart can have labels.

Legend

A legend explains the Markers in a graph. A legend displays the data Markers for the data that the Markers represent. For example, if your graph shows three bars that represents the three months in a quarter, the legend explains which colored bar corresponds to which month.

Choosing the best graph type for your data

To present your worksheet data visually in Discoverer, you can choose from 12 graph types. For example, Bar Graph, Line Graph, and Pie Graph. Each graph type has one or more variations, or sub-types. For example, the Area Graph has three sub-types: Area, Percent Area, and Stacked Area.

Most graph sub-types have a three-dimensional effect that you can switch on and off as required (using the 3D-Effect check box).

Note that the 3D-Effect should not be confused with three-dimensional graphs, such as 3D-Cube and Surface, which are used to represent multi-dimensional data.

Some graphs also have a dual-Y sub-types, which have two Y-axes. Dual-Y graphs are useful for showing the following types of data:

data of different measures (such as Sales on the Y1-axis and Profit on the Y2-axis)
data of different scales (such as Region Sales on the Y1-axis and Percent of Total Sales on the Y2-axis).

(See also notes in Creating Dual-Y Charts.)

Graph Types Described

Bar graph

A graph that compares values using vertical bars. Each value is represented by a single bar. A bar graph shows variation over a period of time or illustrates comparisons between values. The stacked sub-type shows each value's relationship to a whole.

Horizontal Bar graph

Identical to a bar graph except that the bars lie horizontally, rather than standing vertically. Horizontal bars place more emphasis on comparisons and less emphasis on time. The stacked sub-type shows each value's relationship to a whole.

Line graph

A graph that shows trends or changes in data at even intervals. Data is represented as a line that connects a series of data points. Although similar to an area graph, a line graph emphasizes trends.

Point graph

Similar to a line graph in that data is represented by points, however the data points are not connected by a line.

Area graph

A type of graph in which data is represented as a filled-in area.

Pie graph

A graph in which data is represented as sections of a circle, making the circle look like a sliced pie. A pie graph shows the proportion of parts to a whole. It is useful for emphasizing a significant element, such as the highest value. Note that a pie graph always displays only one data series, that is, one row or one column of data at a time.

Polar graph

A circular scatter graph. The circular shape allows you to present cyclical data and is especially useful for showing directional data.

Scatter graph

A graph with points scattered over the plot area. Each point is a value whose coordinates are specified by two numeric measures. A scatter graph shows relationships between two measures, for example Sales and Cost. A scatter graph is useful for comparing two measures that both have many values. All points are the same size, regardless of their value.

Bubble graph

Bubble graphs add another measure to the points of a scatter graph because the size of the bubble is significant. Each bubble is a value whose coordinates are specified by three numeric measures. A bubble graph shows relationships between three measures, for example Quarter, Sales, and Profit. The third measure determines the size of the bubble. A bubble graph is useful for comparing three measures that have many values. (See also notes in Creating Bubble Graphs.)

Stock graph

A graph in which each data Marker typically shows three values, such as the high, low, and closing stock price. Stock graphs are useful for comparing the prices of different stocks or the stock price of an individual stock over time. (See also notes in Creating Stock Charts.)

3D graph

A true three-dimensional graph, where you can see an X edge, a Y edge, and a Z edge. 3D graphs have a floor, a wall, and a background. There are four 3D graph sub-types: 3D Bar, 3D Cube, 3D Area, and 3D Surface. These types of 3D graphs are useful for showing trends or to compare values. Note, this graph type is not the same as one created using the 3D Effect checkbox. The 3D Effect checkbox allows you to add depth to any graph type.

Notes on creating Graphs

To create meaningful graphs in Discoverer, you need to have the correct Worksheet configuration for the style of graph that you wish to use. This section contains advice on getting the best results when using graphs in Discoverer.

Creating Bubble Graphs

When you create Bubble Graphs, follow these guidelines:

You need at least three Items.
- the X Item- the Bubbles's location on the X-axis.
- the Y Item- the Bubbles's location on the Y-axis.
- the Z Item - the size of the Bubbles, (which should be positive numbers).

For example, if the Marker Bubble is Sales, the X and Y axes could show Advertising costs and Store Size in square metres (M²). You could then see whether the largest stores with the most advertising generated the highest Sales revenue. Figure 3-1 below shows how the Worksheet data arranged 'Series by row' is represented on a Bubble Graph. The bubbles represent Sales. The larger the bubble, the larger the Sales revenue.

Figure 3-1 Example Data Configuration for a Bubble Graph

Creating Stock Charts

When you create High-Low-Close Stock Graphs, follow these guidelines:

You need at least three Items in the following order:
- High price
- Low price
- Closing price
Stock values for High, Low, and Closing prices must appear on the same row or column series as groups of three.
To display data for more than one period, the data must be in multiples of three, such as three columns for period 1, three columns for period 2, and so on.
High-low-close stock graphs usually have only one series of data. The series should be the name of the stock whose prices you show in the graph.
If a high-low stock graph contains more that one series of data, and prices overlap, some stock Markers will obscure other stock Markers.

For example, Figure 3-2 shows a Worksheet configuration for charting a stock price over time, (January, February and March).The Worksheet data arranged 'Series by row'.

Figure 3-2 Example Data Configuration for a High-Low Stock Chart

Creating Dual-Y Charts

When you create graphs with Dual-Y series, follow these guidelines:

The Dual-Y facility can be used with the following types of Graph:
- Bar
- Line
- Area
Dual-Y Graphs require at least two series of data.
By default, the series are displayed in the following way:
- Series 1 is displayed on the Y1 axis.
- Series 2 is displayed on the Y2 axis.
- All subsequent series are displayed on the Y1 axis.

In Figure 3-3 below, the Y1 axis represents Sales on the scale 0 to 1 Million. The Y2 axis represents Costs on the scale 0 to 50,000. The Plot Area tab of the Graph Wizard can be used to change which Y-axis is used for each series.

Figure 3-3 Example Data Configuration for a Dual-Y Bar Graph

Creating a Graph

Discoverer provides the Graph Wizard to help you create a graph of your worksheet data. Each time you use the Graph Wizard, Discoverer saves your settings for the next graph you create. If at any time you want to use your previous settings for the remaining steps, simply click the Finish button.

To create a graph

From the Graph menu, choose New Graph. The Graph Wizard appears.

Choose the type of graph that you want by clicking an icon in the Graph type box on the left side. The corresponding graph sub-types appear in the Graph sub-type box on the right side. Choose a graph sub-type (for example, Bar or Dual-Y bar).

The Description box at the bottom of the Graph Wizard describes the purpose of each graph type. If you are unsure what type of graph to use, see "Choosing the best graph type for your data". If active, click the 3D Effect checkbox to add depth to any graph type.

Click Next. The Titles, Totals, and Layout dialog appears:

If you want a title on your graph, put a checkmark in the Show Title checkbox. Type the title you want in the text box. If you want to add the date, time, or other worksheet information to the title, click the Insert drop-down menu and select the element you want to insert. Click the Font button to choose the font size and color for your title, (refer to "Choosing Font Options" for more information about setting fonts).
Answer the question, "What would you like to display in your graph?" by clicking one of the radio buttons:
- Data Only to graph all the data point values of your worksheet but exclude any totals.
- Totals Only to graph only the data in the Totals columns or rows of your worksheet.
- Both Data and Totals to graph everything in your worksheet, both the individual data points and their totals.
Put a checkmark in the Show null values as zero checkbox if you want a Marker with a zero value for all null values. Otherwise, null values are not represented in the graph.
Click a radio button to choose whether you want to graph a column or a row of data. Note that a Pie chart shows values as parts of a whole, so you can graph only one column or row at a time. If you are not creating a Pie chart, skip to step 7.
Click the Pie Chart Options button for additional controls. The Pie Chart Options dialog appears.

The Pie Chart Options dialog lists the columns or rows that you currently have in your worksheet. Click the one that you want to graph, and then click OK to return to the Titles, Totals, and Layout dialog.
Click Next. If you are creating a Pie chart, skip to step 17. If you are not creating a Pie Chart, the X-Axis dialog appears.

Like the graph itself, the X-axis can also have its own title. If you want a title on the X-axis, click the Show X-Axis Title checkbox. Type the title you want in the text box. If you want to add a data item name to the title, click the Insert drop-down menu and select the item that you want to insert. Click the Axis Title Font button to choose the font size and color for your X-axis title, (refer to "Choosing Font Options" for more information about setting fonts).
If you want a thicker line to indicate the X-axis, select the line thickness that you want from the Line thickness drop-down menu. Choose a color for the line from the Color palette.
If you also want a label for the tick marks on the X-axis, click a radio button and select how often you want labels to appear. Click the Axis Label Font button to choose a font size and color for labels, (refer to "Choosing Font Options" for more information about setting fonts).
Click the Next button. The Y-Axis dialog appears.

Like the X-axis, the Y1-axis can also have its own title. If you want a title on the Y1-axis, click the Show Y1-Axis Title checkbox. Type the title you want in the text box. If you want to add the name of the data item to the title, click the Insert drop-down menu and select the item that you want to insert. Click the Axis Title Font button to choose the font size and color for your Y1-axis title, (refer to "Choosing Font Options" for more information about setting fonts).
If you want a thicker line to indicate the Y1-axis, select the line thickness that you want from the Line thickness drop-down menu. Choose a color for the line from the Color palette.
Discoverer will automatically set the scale for your Y1-axis data by measuring the lowest and highest values. However, if you want to choose your own scale, uncheck the Set Automatically checkboxes, and then type the scales that you want for your data, for example, Revenues in Thousands from 0 to 60 in increments of 10. Or check the Logarithmic scale checkbox, and then choose a Log base (example, log 10) from the drop-down menu.
Click the Axis Label Font button to choose a font size and color for the axis labels, (refer to "Choosing Font Options" for more information about setting fonts).
Click the Next button. If you are creating a Dual-Y graph, the Y2-Axis dialog appears. Repeat steps 12 through 16 for the second Y-axis. Otherwise, continue to step 17.
The Plot Area dialog appears. Do any of the following:

Click the color palette icon to choose a background color.
Click the Horizontal and Vertical gridlines checkboxes, and then choose a line width and color for each.
Insert a checkmark to add labels for data Markers. To decide whether the label should appear on top of a Marker or inside a Marker (for example, on top of a bar or inside a bar), click the Options button next to this selection.
Insert a checkmark to add text that pops-up whenever you hover the mouse over a data Marker. Click the Options button next to this selection to decide what text to display in the pop-up.
Select options, such as color, for series (rows or columns from your worksheet) that are displayed in your graph. For a dual-Y graph, select which axis to use for each series.

Click the Next button. The Legend dialog appears.

If you want to show a Legend on your graph, click the Show legend checkbox. Choose where you want to position the legend on the graph from the Location drop-down menu. Select a border color and background color from their color palettes. Click the Legend Font button to choose a font size and color for text that appears in your legend.

NOTE: Once you have created your graph, you can reposition the legend by dragging it with the cursor.
To change any of your choices, click the Back button until you return to the dialog that you want. When you are ready to create the graph, click the Finish button. After a short delay, the graph appears. To position the graph, see Positioning Your Graph with Your Worksheet.

Choosing Font Options

The Font Options dialog is used to set the font style for the various components of your graph. You can call this dialog from the following Discoverer dialog boxes, (see example screen shot of the Title Font dialog below).

Table 3-1 How to call the Font Dialog from the Graph Wizard

Discoverer Dialog Box	Button Option used to call the Font Options dialog
Graph Wizard: Titles, Totals, and Layout	Font
Graph Wizard: X-Axis	Axis Title Font
Graph Wizard: X-Axis	Axis Label Font
Graph Wizard: Y1-Axis	Axis Title Font
Graph Wizard: Y1-Axis	Axis Label Font

To set your font options

Select a font style from the Font drop-down menu. Select a font size from the Size drop down menu. Click any combination of Style buttons for bold, italic, and underlined styles. Select a color for your text from the Text color palette.
Also, click one of the Alignment buttons to align your text to the left, center, or right. The Example area on the right shows you how your text will appear in your graph. (Click the Actual font size checkbox to see how large the text will look on your graph.) Click OK to return to the previous dialog.

Positioning Your Graph with Your Worksheet

Positioning the graph with your worksheet affects how they appear together on screen. Positioning the graph does not affect the order that the worksheet and graph print.

To position your graph

From the Graph menu, choose Display Graph.
From the Display Graph sub-menu, click one of the following:
- Separate Window to display the graph in a window that floats above the worksheet window. You can move the graph window to any location on your screen by dragging it with the mouse.
- Right of Data to display the graph in a window that is connected to the right side of the worksheet window.
- Left of Data to display the graph in a window that is connected to the left side of the worksheet window.
- Above Data to display the graph in a window that is connected to the top of the worksheet window.
- Below Data to display the graph in a window that is connected to the bottom of the worksheet window.
- Hide/Unhide Graph to display the graph or hide the graph. The Hide option does not delete the graph.
Click Fit to window if your graph is too large to fit completely inside its window panes without scroll bars. The graph resizes so that it is completely visible inside its window.

Using the Graph Toolbar

When working with graphs, you can use the Graph Toolbar to quickly make cosmetic changes to the look of your graphs without using the Graph Wizard. For example, you can change fonts, colors, and text alignment.

Saving Your Graph

When you save a worksheet, Discoverer saves the graph automatically for you as part of the worksheet.

If the data in your worksheet changes, the graph updates automatically. Any changes you make to the graph are also saved automatically when you save the worksheet.

Deleting Your Graph

To delete a graph

From the Graph menu, choose Delete Graph. A warning message appears.
Click Yes to delete the graph.

3 Analyzing Data