1.4 Table Summarization using the "Drill Up" button

The Drill Up feature of the Web Data System gives you the ability to summarize a large table by eliminating certain columns, and summing or counting records in specified fields. Any table, regardless of filter conditions, may be summarized. Filter conditions specified on the Query Page will persist into the Summary Table. To summarize a Results Table a user must stipulate the following:

(1) One or more Key Fields.

(2) One or more Summary Fields, i.e., Numeric fields that are to be summed. (Zero summary fields is a
special case described below.)

Key Fields and Summary Fields are used to generate summarized tables from the low-level detail
presented in the Results Table. The "drilled up" table consists of all summary records, in sorted order.
Fields other than the key, summary and count fields will not appear in the summary table. A summary
table with NO summary fields is useful in some cases because it lists the distinct values in the key
fields.

The key fields must be specified in a particular order: Key Field 1, Key Field 2, Key Field 3.

You may summarize a summary table; however, the new summarized table will be generated from
the original filter conditions chosen on the Query Page.

• Selecting Key Fields and Summary Fields
  Key fields and summary fields are used to generate summarized tables from the low-level detail presented in the Results Table. The "drilled up" summary table consists of all summary records, in sorted order. Fields other than the key, summary and count fields will not appear in the summary table.
  
  1.4.1 Key Fields
  Key fields provide the boundaries used to partition and organize the Summary Table; this is initially sorted according to Key Field 1. Records with identical values in Key Field 1 are sorted according to Key Field 2. Records with the same values in both first and second key fields are sorted according to the Key Field 3, etc. Records with the same values in all key fields are left in any order. The summarized table will display only the unique key field records, beginning with Key Field 3 and working back to Key Field 1.
  
  To summarize a table you must select at least one key field. If you have not selected a Key Field, the software will choose the first field of CHARACTER data type.
  
  
  1.4.2 Summary Fields
  Summary fields include sums of numeric records and counts of non-numeric records, divided according to the boundaries (unique entries) established by the key fields. After sorting, each group of consecutive records with the same values in all key fields is replaced by a summary record. The summary record contains the same key field values, but each summary field is replaced by the sum of all values for that field in the group. A summary record may also have a new "Count" field, a Numeric field whose value is the number of records in the group.
  
  NOTE: With the Summarize check box selected, the highlighted field will be treated as a Summary Field; with the Summarize check box unselected the field will be treated as a Key Field. The field labeled "Key Field" will always be treated as a key field; the field labeled "Summary Field" will always be a Summary Field. If a summary field is not selected you can drill up using only a Key Field 1. This condition will produce a two column table that contains (1) all unique values from the Key Field 1, and (2) the total count for each instance found.
  
• 1.4.3 Example:
  Your Results Table contains Year-to-Date sales information by with several fields: Territory, Division, Customer, Category, Totals, etc. You want to find the grand totals for each territory, regardless of customer or category; simply select Territory as Key Field 1, Division as Key Field 2, and Totals as your Summary Field. After clicking the Drill Up button, you will be presented with a summarized table containing three fields. The first field, Territory, holds all of the unique entries contained in the Territory field of the Results Table, grouped by Division. (Each Territory record must have a unique entry under Division.) The second field, Division, contains all of the unique Division entries within their corresponding Territory. The third field, Totals, shows a numeric sum of all records, grouped by Territory and Division.

1.5 Graphing

The Query page of the Web Data System includes a drop down menu for selecting a one of nine chart types.

Histogram	Pie	Column Bar
Column Bar (Multiple)	Column Bar (Stacked)	Row Bar (Multiple)
Row Bar (Stacked)	Line	Scatter

After highlighting a selection, press the Create Chart button.

This brings up the Chart Options page. The selectable items on the Chart Options page vary depending on which chart type you selected. A description of each available chart type appears in the following sections.

1.5.1 Histogram

The Histogram chart is a bar graph based on the frequency of the items in your data. It can be useful when you want to make comparisons on the number of times one item appears in a table field as compared to another item. For example, a histogram can display the the number of purchases made by customers based on the frequency of customers listed, or which months garner the most sales by the frequency of months listed.

The data for your histogram chart is taken from a single Key Field. The Key Field is selectable either from the Query Page or the Histogram Chart Options page. The number of fields you can select as the Key Field depends on the table you selected and the fields you chose to View (on the Field Selection page).

The options for a histogram chart include:

• Logarithmic Scale
• Numeric Sort Order
• Start Scale Value
• Title
• Label with Field Names
• Label x-axis
• Label y-axis
• Grid Lines

Select Key Field
The Select Key Field drop down menu displays all fields selected from the Fields to View selection box on the Field Selection Page. From this menu you should select the field that contains the items you want to compare.

Logarithmic Scale
The Logarithmic Scale option will create a scale with values that increase logarithmically. The tick marks on the scale will be spaced on the scale using log base 10. So if the scale values range from 1 to 10,000, the tick marks and values will be evenly spaced with the numbers 1, 10, 100, 1000, and 10,000. This option is selected by checking the Logarithmic Scale box.

A Logarithmic Scale can make some graphs more readable; it will even out bar heights that would otherwise vary by extreme changes.

Numeric Sort Order
Numeric Sort Order will display by either increasing or decreasing values. Increasing Sort Order places the smallest bar next to the scale on the left side of the image and progressively larger bars to the right. Decreasing sort order arranges the bars left to right from largest to smallest.

Mark either the Increasing or Decreasing radio button to choose this option.

Start Scale Value
Start Scale Value allows you to set the bottom or smallest scale value. This feature can produce a smaller graduation of scale values for viewing the larger bars.

Add Labels (Title, Field Names, x-axis, y-axis)
The Add Labels section allows you to label the vertical and horizontal axis, and give a title to the chart. Enter a title for the chart in the Title text box. To display the name of the Key Field, check the box Label with Field Names. To label the y-axis, x-axis (or both), enter some descriptive text in their respective boxes.

Grid Lines
Grid Lines extend lines from each scale tick mark to the end of the chart.

1.5.2 Pie Chart

The Pie chart can display the same information as the histogram chart but within a circle divided into slices proportional to the frequency of items in the table's Key Field. It can also display a summary based on numeric data from the Summary Field.

The selectable items for the Pie chart include:

• Select Key Field
• Sum As
• Display Sum As
• Summary Field

Select Key Field
The Select Key Field drop down menu displays all fields selected from the Fields to View selection box on the Field Selection Page. From this menu you should select the field that contains the items you want to compare.

Sum As
The Sum As drop down menu gives the options Count and Summary. The Count option will draw the pie chart using only the Key Field and will proportion the pie slices based on the frequency of items in the Key Field. The Summary option specifies that a numeric field will be selected from the Summary Field drop down menu. With this option chosen, the pie chart will be drawn based on two fields from the table. Pie slices will be labeled with the items from the Key Field and will be proportional to the summary of related values from the Summary Field.

Summary Field
The Summary Field drop down menu displays the numeric fields selected from the Fields to View selection box on the Field Selection Page. A selection must be made here if you selected Summary from the Sum As menu.

1.5.3 Column Bar

The Column Bar is a vertical bar chart based on a Key Field and a Summary Field. Unlike the Histogram, where bar heights are scaled by frequencies, the Column Bar scales bar heights based on the corresponding values in the Summary Field.

The options for the Column Bar include:

• Select Key Field
• Summary Field
• Logarithmic Scale
• Numeric Sort Order
• Start Scale Value
• Title
• Label with Field Names
• Label x-axis
• Label y-axis
• Grid Lines

Review the Histogram section above for a description of these features.

1.5.4 Column Bar (Multiple)

The Multiple Column Bar is like the Column Bar, however, you may select up to 20 summary fields.

The options for the Multiple Bar include:

• Select Key Field
• Summary Fields
• Logarithmic Scale
• Start Scale Value
• Display By
• Title
• Label x-axis
• Label y-axis
• Grid Lines

Display By
The Display By option presents data either by Row or Column. Selecting Row will group Key Field data by each Summary Field. The horizontal axis will list the different Summary Fields and colored bars will represent Key Field items. Selecting Column will group Summary Field data by each item in the Key Field. Drawn bars will represent values for each Summary Field.

1.5.5 Column Bar (Stacked)

The Stacked Column Bar is a vertical bar chart generated from a Key Field and multiple Summary Fields. The Stacked Bar chart type is like the Multiple Bar chart type except that grouped bars, either by row or column, are stacked instead of displayed side by side.

The options for the Stacked Column Bar chart include:

• Select Key Field
• Summary Fields
• Logarithmic Scale
• Start Scale Value
• Display By
• Title
• Label x-axis
• Label y-axis
• Grid Lines

See the Multiple Column Bar and Histogram sections above for a description of these features.

1.5.6 Row Bar (Multiple)

The Multiple Row Bar chart is a horizontal bar chart based on a Key Field and Summary Fields. It is like the Multiple Column Bar chart except that the bars are drawn horizontally.

The options for the Row Bar include:

• Select Key Field
• Summary Field
• Logarithmic Scale
• Start Scale Value
• Title
• Label x-axis
• Label y-axis

Review the Column Bar and Histogram sections above for a description of these features.

1.5.7 Row Bar (Stacked)

The Stacked Row Bar chart is a horizontal bar chart version of the Stacked Column Bar chart. See the Column Bar (Stacked) section above for a description of the Stacked Row Bar options.

1.5.8 Line Chart

The Line chart draws lines that connect points taken from a single or multiple Summary Fields. It has the same chart selection options as Multiple Bar.

The options for the Line Chart include:

• Select Key Field
• Summary Fields
• Logarithmic Scale
• Start Scale Value
• Display By:
• Title
• Label x-axis
• Label y-axis
• Mark Data Points
• Grid Lines

Mark Data Point
The Mark Data Point option plots points along the line chart where the data points exist.

1.5.9 Scatter Plot

The Scatter Plot draws points based on a Key Field and its corresponding Summary Field values. Multiple Summary Fields can be selected.

The options for the Scatter Plot include:

• Select Key Field
• Summary Fields
• Logarithmic Scale
• Start Scale Value
• Display By:
• Title
• Label x-axis
• Label y-axis
• Grid Lines
  

1.6 Notes on Graphing Features

NULL Field Items
If a cell is empty or NULL in the Key Field, it will be graphed as an item named NULL. If the Summary Field has a NULL value it will be calculated as the floating-point value 0.0.

Key Field Item Limit of 320
The bar charts are limited to 320 bars. If greater than 320 unique items appear in the Key Field, a warning message will be displayed.

Minimum Scale Values
Logarithmic scale values must be greater than zero. If you specify minimum scale values for the bar charts below these limits an error message will be displayed.

Top