Aggregate Feature

<< Click to Display Table of Contents >>

Navigation:  Professional > Reports > Crosstab Reports >

Aggregate Feature

 

Use Aggregate Feature

Crosstab Report with Referenced Aggregate Feature

Crosstabs with Multiple Aggregate Columns

Aggregate Average Details

Aggregate Count Options

Aggregate Sum Feature

Row Aggregates

 

Use the Aggregate feature in a Crosstab Report with any of the standard EQuIS reports. The following aggregate options may be selected.

oAverage

oCount, Count (Distinct), Count (Non-Null), Count (Null)

oFirst

oLast

oMaximum

oMinimum

oMedian

oStandard Deviation

oSum

 

XY and XYZ charts can also be created from these aggregated results.

 

For datasets that contain a mix of numeric and non-numeric values, arithmetic aggregations (all aggregates except First, Last, and the Count aggregates) will proceed with the appropriate calculation while ignoring the non-numeric values. These results will be flagged with an asterisk (*) in the final Crosstab report.

 

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

 

The Referenced Aggregate option allows the user to perform an aggregate based on another aggregate. More specifically, the Referenced Aggregate field is used to create a column in the crosstab based on an Aggregate (such as maximum results) which includes the referenced value from a related field (such as the chemical name or date or location of the maximum value in the aggregate column). Add the row with the Referenced Aggregate immediately following the row being referenced. Note that this feature does not work with the Average or Count aggregates, since they do not produce a data point to reference that exists in the dataset.

 

For example, selecting the CHEMICAL_NAME field in the Tabbed Data section while setting the Aggregate setting to "Maximum", and setting the Referenced Aggregate setting to "REPORT_RESULT_VALUE" will display the CHEMICAL_NAME value with the maximum REPORT_RESULT_VALUE value for that corresponding row/column.

 

Tabbed Data with Maximum Report_Result_Values reference with Chemical_Name

Tabbed Data with Maximum Report_Result_Values reference
with Chemical_Name

 

Following from the example in the previous section, a referenced aggregate may be added as follows:

 

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.

 

Referenced 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 Average Details

 

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

 

The Crosstab aggregate Average function, in including consideration for significant figures, 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.

 

Aggregate Count Options

 

There are four count options for Crosstab Tabbed Data Aggregate.

 

Count Option

Definition

Count (All)

Count the number of rows.

Count (Distinct)

Count the number of distinct values.

Count (Non-null)

Count the number of non-null values.

Count (Null)

Count the number of null values.

 

Aggregate Sum Feature

 

1.Click Crosstab in the Analytical Results Report toolbar.

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

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

4.In the Tabbed Data section, click Sum in the Aggregate column. Side Note: 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

 

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

 

In the resulting report, note how the sum of these individual chemical results are returned.

 

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 such as the following will be displayed and the Crosstab will not be generated.

 

Warning:

 

Row Aggregates do not match Tabbed Aggregates

 

Could not find [field] with matching Aggregate value in Tabbed data.

Please include the same Aggregates in Tabbed data as in Row data to ensure accurate results.