Discoverer 4i Release 4.1 A8673101 

Discoverer provides a series of data analysis techniques that you can apply to your worksheets.
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 Highwhich is A to Z or 1 to 10, or High to LowZ 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
See also:
Pivoting Data
Drilling Into and Out of the Data
Duplicating Tables and Crosstabs
Adding Calculations to Worksheets
Totaling Numeric Data
Calculating Percentages
Choosing Font Options
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.
The Sort Table dialog box appears. It shows the sorting options currently selected for the table.
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.
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.
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.
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.
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 AZ and numbers are sorted from lowest to highest, but you can reverse the sort order.
The following example illustrates a crosstab sorted by City (AZ order) within Region (AZ order).
The Sort Crosstab dialog offers a full range of options for sorting Crosstab worksheets.
Above the datashows the data items on the top axis.
Along left side of datashows the data items on the left axis.
The Column dropdown 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 dropdown list identifies the row that contains the data for sorting when sorting is based on data from the top of the crosstab.
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 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 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:
See also:
Sorting Data
Drilling Into and Out of the Data
Duplicating Tables and Crosstabs
Adding Calculations to Worksheets
Totaling Numeric Data
Calculating Percentages
The layout shows the items on the table and their current positions on the table.
The following example shows how to pivot the Region column to the Page Axis.
The Region column moves to the Page Axis.
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 dropdown list, as shown in the following figure.
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 draganddrop 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.
The example below shows a Crosstab Worksheet and its Crosstab Layout arrangement.
In the example below, the Year Item has been pivoted to the lefthand 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 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:
See also:
Pivoting Data
Sorting Data
Duplicating Tables and Crosstabs
Adding Calculations to Worksheets
Totaling Numeric Data
Calculating Percentages
A dropdown menu appears for the item. For example, if you click the drill icon for City, the dropdown 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.
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.
If you select a data item to which you have already drilled down, you can collapse the levels back to their previous state.
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 dropdown menu.
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.
See also:
Pivoting Data
Drilling Into and Out of the Data
Sorting Data
Adding Calculations to Worksheets
Totaling Numeric Data
Calculating Percentages
The dialog box appears with the Table Layout tab or Crosstab Layout tab selected depending on the duplication you're doing.
Show Page Itemsshow/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.
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
See also:
Pivoting Data
Drilling Into and Out of the Data
Duplicating Tables and Crosstabs
Sorting Data
Totaling Numeric Data
Calculating Percentages
Displaying Existing Percentages
Calculation Examples
Here is an example of a simple calculation.
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.
You use the Calculations dialog to create calculations.
This dialog shows calculations already created for the worksheet. Checkmarked calculations are active and apply to the worksheet.
FunctionsLists a wide range of mathematical functions that you can apply to the formula.
Selected ItemsLists 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 ItemsLists all the items available for the worksheet even if the items are not currently used on the worksheet.
CalculationsLists the calculations defined for the worksheet in case you want to use an existing calculation as part of your new calculation.
ParametersLists the parameters defined for the worksheet.
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 subtotal 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
See also:
Pivoting Data
Drilling Into and Out of the Data
Duplicating Tables and Crosstabs
Adding Calculations to Worksheets
Sorting Data
Calculating Percentages
You can define totals for a worksheet and then display them on the worksheet or not.
Creating a new totals definition has three steps:
You can also create totals for all the data points on the worksheet by selecting All Data Points from the dropdown list.
The options are:
SumAdds all the values.
AverageAdds all the values and divides by the number of values.
Average DistinctAdds 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.
CountCounts the total number of values.
Count DistinctCounts the number of unique values.
MinimumFinds the lowest value.
MaximumFinds the highest value.
Standard DeviationCalculates the standard deviation. Standard deviation is the square root of the variance of the values.
Standard Deviation DistinctCalculates the standard deviation, but only using unique, unduplicated values.
Sum DistinctAdds 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.
VarianceCalculates 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 DistinctCalculates the variance, but only using unique, unduplicated values.
Percentage of GrandCalculates 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 DistinctCalculates the percentage of the Grand Total of the row or column, but only using unique, unduplicated values.
Grand total at bottomCalculates 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 inclick the dropdown 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 Itemsdisplays 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 senseit 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 nonnumeric data point (such as Region in the example)when you select a nonnumeric set of data points, the options for the totals in the first dropdown list are limited to only those options that apply to nonnumeric data points. For example, if you select Region, sum of regions does not make sense. The only totals that make sense for nonnumeric data points are Count, Count Distinct, Maximum, and Minimum.
Don't display subtotal for a single rowIf 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).
You can click the dropdown list for labels and choose additional options for the title from it. The options from the dropdown 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.
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.
To edit a totals definition:
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
See also:
Pivoting Data
Drilling Into and Out of the Data
Duplicating Tables and Crosstabs
Adding Calculations to Worksheets
Totaling Numeric Data
Sorting Data
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.
Discoverer now computes the percentages and displays them on the worksheet.
Creating a new percentage definition has three basic steps:
The following table lists your choices:
The illustration on the right side of the dialog shows a representative worksheet containing percentage columns based on your selections.
The options from the dropdown 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.
To remove options from the labels, click in the label text in the dialog and edit it as you would regular text.
To edit a percentage definition:
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
See also:
To create a graph
To set your font options
To position your graph
Saving Your Graph
To delete a graph
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:
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.
The terminology below appears in the Graph Wizard and in the documentation.
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 subtypes. For example, the Area Graph has three subtypes: Area, Percent Area, and Stacked Area.
Most graph subtypes have a threedimensional effect that you can switch on and off as required (using the 3DEffect check box).
Note that the 3DEffect should not be confused with threedimensional graphs, such as 3DCube and Surface, which are used to represent multidimensional data.
Some graphs also have a dualY subtypes, which have two Yaxes. DualY graphs are useful for showing the following types of data:
(See also notes in Creating DualY Charts.)
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 subtype 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 subtype 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 filledin 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 threedimensional 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 subtypes: 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. 
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.
When you create Bubble Graphs, follow these guidelines:
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 31 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.
When you create HighLowClose Stock Graphs, follow these guidelines:
For example, Figure 32 shows a Worksheet configuration for charting a stock price over time, (January, February and March).The Worksheet data arranged 'Series by row'.
When you create graphs with DualY series, follow these guidelines:
In Figure 33 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 Yaxis is used for each series.
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.
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:
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.
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).
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.
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.
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.

Copyright © 2000 Oracle Corporation. All Rights Reserved. 
