<< Click to Display Table of Contents >> Navigation:  Professional > Reports > Crosstab Reports > Aggregate Feature

 

Use the Aggregate feature in a Crosstab Report with any of the standard EQuIS reports to include a statistical computation of average, maximum, minimum, etc. in the report output:

•Tabbed Data Aggregates populate the aggregate values in the main cells of the report output grid.

•Row Header Aggregates add row header columns populate with the aggregate values for each row, for easy reference alongside the raw data of the main output grid.

 

The following aggregate options are available for both Tabbed Data and Row Header aggregates.

 

The following aggregate options are available for Tabbed Data aggregates:

•Maximum (Detects Only)

•Minimum (Detects Only)

•Sum (Detects Only)

 

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.

 

Aggregate Average Option

 

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.

 

 

 

Aggregate Maximum Option

 

1.Open an Analytical Results Report.

 

 

2.Select input parameters. The example shown here uses the following Springfield example data.

 

 

3.Click Go to run the report.

4.Click Crosstab on the Analytical Results Report toolbar.

5.In the Crosstab window, make the following selections. This will populate the tabbed data cells with the maximum result in the method analyte group for each sample, date, and locations (column headers) combination.

 

 

 

6.Click Save and name the configuration MONIT_PERC_AGGREGATE_CROSSTAB.

7.Click Go to display the report.

 

Aggregate Maximum (Detects Only) Option

 

Reference Aggregate Maximum Option. Note that when a cell of the tabbed data contains multiple values and some are non-detected, the Maximum (Detects Only) calculates the maximum value after excluding the non-detected values.

 

Aggregate Sum Option

 

1.Click the Crosstab button in the Analytical Results II Report toolbar.

2.Set up the pictured Row Header(s) and Tabbed Data fields, then any other preferred options (such as SYS_LOC_CODE and REPORT_RESULT_UNIT for Column Headers).

3.In the Tabbed Data section, click Sum in the Aggregate column for REPORT_RESULT_VALUE. Side Note: To remove the Aggregate: Sum selection, select None.

 

 

 

5.Click Go .

 

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

 

Aggregate Sum (Detects Only) Option

 

Reference Aggregate Sum Option. Note that when a cell of the tabbed data contains multiple values and some are non-detected, the Sum (Detects Only) calculates the summation of the multiple values after excluding the non-detected values.

 

Referenced Aggregate Feature

 

The Referenced Aggregate feature 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.

 

 

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

 

8.Click Back 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 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 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 chemicals with this value will be shown as is seen with the B-30 SAMPLE_ID.

 

Multiple Aggregate Columns - Tabbed Data

 

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 to verify the aggregate settings in the Crosstab Report.

 

Row Header Aggregates also support multiple aggregate columns.

 

 

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

 

 

9.Click Go to verify the aggregate settings in the crosstab report.

 

 

Row Header Aggregates

 

Row Header Aggregates generate the average, maximum, minimum, etc. of a tabbed data row.  The following conditions are required:

 

1.No cell of the tabbed data contains duplicated values. Otherwise, the following error message occurs:
 
When an Aggregate of a Row Header is configured, the aggregated-Row-Header-related Tabbed Data cannot include duplicates.
 
Please update the configuration of the Row Header and/or Column Header to avoid multiple values in a single cell of Tabbed Data output and try again. To identify the duplicates in the Tabbed, run the crosstab by removing the Aggregate(s) from Row Header(s).
 
Tip:  If the above error occurs, run the crosstab using the Average Aggregate option for the Tabbed Data to process the duplicates.
 

2.The aggregate column must be selected only once in the Tabbed Data section, and the column must also be present in the Row Header(s) section. Otherwise, one of the two errors below may occur.
 
Error 1:
The Column name xxx in the aggregated Row Header(s) must also be included in the Tabbed Data section. Please update the crosstab configuration and try again.
 
Error 2:
The Column name xxx in the aggregated Row Header(s) may only occur once in the Tabbed Data section. Please update the crosstab configuration and try again.
 
Example Crosstab with Row Header(s) Aggregate configuration:

 
Example Row Header(s) Aggregate Output:

 

Notes:

Rounding Average: its final value is rounded by taking the maximum of the significant figures of all the values of a tabbed data row. For example, an average of 3.46 is computed from 1.4, 4 and 5 (in a row of tabbed data). Because the maximum significant figure is 2, the final average of 3.5 is present.

 

Processing empty (or null or missing) value: an empty value is ignored in processing Avg, Max or Min. For example, when a row of tabbed data has the 4 values of 1.4, empty, 4, 5, the average equals (1.4+4+5)/3 = 3.5.

 

Processing non-detects: non-detect and detect value are not differed in calculating avg, max or min.