Aggregate Feature

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Reports > Crosstab Reports >

Aggregate Feature

Use the Aggregate feature in a Crosstab Report with any of the standard EQuIS reports. This feature provides the option to display records from numeric fields or average, count, sum, minimum, maximum, first or last. In version 6.3+ it is possible to create XY and XYZ charts from these aggregated results.

 

The average Aggregate feature determines the appropriate number of significant figures using the fn_significant_figures_function in the EQuIS Database.

 

Use Aggregate Feature

 

1.Open an Analytical Results Report.

 

Tip: If report parameters were saved from Reporting Create and Format Crosstab Reports, open this report with the Open File button (if saved locally) or the User Report drop-down menu (if saved to database).

 

2.Make sure the following report parameters are selected.

 

Parameter

Selection

Location Group

MonitWells

Sample Date Range Start

01/1/1997

Sample Matrix(es)

WG

Result Analyte Group(s)

_PERC DAUGHTERS

Result Unit

ug/l

 

3.Click Go Arrow right - 08(1) to run the report.

4.Click Crosstab on the Analytical Results Report toolbar and in the Crosstab window, select the following.

 

Parameter

Selection

Caption

Display

Column Header(s)

SAMPLE_DATE

SYS_LOC_CODE

SYS_SAMPLE_CODE

Sample Date

Location Code

Sample Code

Default

Row Header(s)

METHOD_ANALYTE_GROUP

Method Analyte Group

Default

Tabbed Data

REPORT_RESULT_VALUE



 

5.Select Maximum from the Aggregate feature drop-down menu in the Tabbed Data area.

 

Tip: To remove an Aggregate selection, highlight the selection and press ESC. If the selection was saved, click the row and press Delete to remove the values from the Tabbed Data column. Drag and drop new values from the Available Columns list back to the Tabbed Data column.

 

6.Click Save and name the configuration MONIT_PERC_AGGREGATE_CROSSTAB.

7.Click Go Arrow right - 08(1) to display the report.

 

Crosstab Report with Referenced Aggregate Feature

 

8.Click Back Arrow left -08(1) to return to the crosstab configuration.

9.Add CHEMICAL_NAME to the Tabbed Data pane.

10.In the Referenced Aggregate field for CHEMICAL_NAME, enter REPORT_RESULT_VALUE.

11.Click Go Arrow right - 08(1) and find the first column with a 'B-38' SAMPLE_ID.

 

The maximum value for this sample on this date is 19.01, and the chemical from the _PERC Daughters method analyte group that has this maximum value is Trichloroethyene. If more than one chemical has the same maximum REPORT_RESULT_VALUE for a particular location, sample, and date, all of the chemicals with this value will be shown as is seen with the B-30 SAMPLE_ID.

 

Crosstabs with Multiple Aggregate Columns

 

If desired, a crosstab may be constructed that displays multiple aggregates, for example, one column that displays the Maximum value on each date, and a second column that displays the Minimum value on each date. To build a crosstab with multiple aggregate columns, follow the instructions below.

 

1.Add REPORT_RESULT_VALUE into the Tabbed Data pane from the Available Columns list, as many times as number of columns desired, in the crosstab configuration screen.

2.Set the desired Aggregate for each REPORT_RESULT_VALUE in the design, for example, Minimum for one, and Maximum for another.

3.Change the Caption of each REPORT_RESULT_VALUE field to reflect the aggregate displayed in that field.

4.Click Go Arrow right - 08(1) to verify the aggregate settings in the Crosstab Report.

 

Reference Aggregates may still be used with multiple Aggregated Tabbed Data fields. To add multiple referenced aggregate columns, follow the instructions below.

 

5.Click Back Arrow left -08(1) to return to the crosstab configuration.

6.Add the desired referenced aggregate fields from the Available Columns list (such as CHEMICAL_NAME or SAMPLE_DATE) multiple times, one for each aggregate to be referenced.

7.In the Reference Aggregate field, enter REPORT_RESULT_VALUE: "Aggregate" where "Aggregate" is the aggregate selected in number two above. Two examples follow.

a.REPORT_RESULT_VALUE:Minimum

b.REPORT_RESULT_VALUE:Maximum

8. Change the Caption of each Referenced Aggregate field to reflect the aggregate displayed in that field.

 

Tabbed Data Pane with Multiple Aggregates and Referenced Aggregates

Tabbed Data Pane with Multiple Aggregates and Referenced Aggregates

 

9.Click Go Arrow right - 08(1) to verify the aggregate settings in the crosstab report.

 

Crosstab Report with Multiple Aggregates and Referenced Aggregates

Crosstab Report with Multiple Aggregates and Referenced Aggregates

 

Aggregate Sum Feature

 

1.Right-click the Analytical Results Crosstab tab of the current window and select New Vertical Tab Group. This allows the creation of a second crosstab report for comparison.

2.Click Crosstab in the Analytical Results Report toolbar.

3.Click Load selections from file and click Open to load the MONIT_PERC_AGGREGATE_CROSSTAB configuration.

4.Select CHEMICAL_NAME for the Row Header, and delete METHOD_ANALYTE_GROUP from the Row Header.

5.In the Tabbed Data section, click Sum in the Aggregate column, to remove the Aggregate:Sum selection, select None.

 

Note: It is possible to delete REPORT_RESULT_VALUE from the Tabbed Data column and re-enter without adding an Aggregate value.

 

Revised Header and Tabbed Data

Revised Header and Tabbed Data

 

6.Click Go Arrow right - 08(1).

 

In the resulting report, note how individual results are shown for each chemical.

 

By comparison, the first Crosstab in this exercise displays the sum of these individual chemical results.

 

Aggregate Average Details

 

The Crosstab aggregate Average function includes consideration for significant figures, and therefore differs from the Excel Average function that users may be familiar with. For example, if a series of values is included in the date to be aggregated, and the series includes a mix of three and four significant figures, the calculation will be performed and rounded to the greater number of significant figures present, in this case, four.

 

If significant figures and rounding should not be considered in the calculation, the Aggregate Average function should not be used. An alternate means to calculate the average would include both the Sum and Count Aggregates, allowing Excel to perform the Average function on the resulting data.

 

Row Aggregates

 

In order to create data with Row Aggregates, the same data must be included in the Tabbed Data section. If a Row Aggregate is included in the data that is not also in the Tabbed Data, a warning message will be displayed and the Crosstab will not be generated.

 

15328-Agg_Warning_Msg