Action Level Exceedance II with Parameters

<< Click to Display Table of Contents >>

Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports > Action Levels >

Action Level Exceedance II with Parameters

Report Name: Action Level Exceedance II with Parameters (example)

File Name: EarthSoft.Reports.Library.51729.dll

Dependencies: Saved Analytical Results II Report

Software: EQuIS Professional and EQuIS Enterprise

Source: EQuIS Professional install, EQuIS Enterprise install

Language: English

Output Type: Report Grid1

 

Description: The Action Level Exceedance II with Parameters Report displays all of the parameters from the Analytical Results II Report and allows you to create the Analytical Results Report and the Action Level Exceedance Report together (displayed once in the Action Level Exceedance format).

 

Installation Instructions: This report assembly is part of the EQuIS Professional installation. No additional installation steps are necessary.

 

Overview:

Action levels may apply to the entire database or they may be specific to a facility or subfacility, a limited time period, a particular matrix, land-use, analytic method, fraction, depth and/or geologic unit. For further information on defining action levels, see Action Level Definition and Overview.

 

Report Parameters:

Analytical Results Report Parameters

Action Level Report Parameters

oComparison

oFlagging

Unit Conversions

oMatching

oColumn Configuration

 

Report Parameters

Analytical Results section

Action Level section

Column Configuration

Column Configuration File

* This table shows partially expanded parameters. Refer to related report articles for complete input parameter lists (e.g. Analytical Results II.)

** The Additional Fields parameter may be customized as explained in the help article How To Customize Additional Fields Parameter.

 

 

Comparison

Comparison | Operator: There are four options for the Comparison | Operator parameter.

1."<" Within Limits (Exclusive): The analytical result is flagged as an exceedance if it is less than the action level and, if one exists, greater than the ACTION_LEVEL_MIN. Equalities are not considered exceedances.

2."<=" Within Limits (Inclusive): Same as above, but equalities are considered exceedances.

3.">" Exceeds Limits (Exclusive): The analytical result is flagged as an exceedance if it is greater than the action level or, if one exists, less than the ACTION_LEVEL_MIN. Equalities are not considered exceedances.

4.">=" Exceeds Limits (Inclusive): (the default) Same as above, but equalities are considered exceedances.

 

See also Using ACTION_LEVEL_MIN field.

 

Comparison | Unit: There are two options for the Comparison | Unit parameter.

1."REPORT_RESULT_UNIT": (the default) Values will be displayed in the units of the REPORT_RESULT column. Other columns will be converted to matching units as necessary.

2."ACTION_LEVEL_UNIT": Values will be displayed in the units of the ACTION_LEVEL column. Other columns will be converted to matching units as necessary.

When "REPORT_RESULT_UNIT" is selected and a REPORT_RESULT_VALUE is converted using a Reporting Formula unit conversion, the associated ACTION_LEVEL value — with an ACTION_LEVEL_UNIT different from the formula unit — will be converted to the formula unit. Note that when DT_FIELD_SAMPLE.AIR_VOLUME is used as the factor or parameter of a formula, it must be selected in the ‘ADDITIONAL FIELDS’ parameter of ARII parameter section.

 

Note: The Comparison | Unit parameter is also used to determine the unit for the MAG sum calculation.

 

REPORT_RESULT_UNIT: The MAG sum calculation will convert unites to the first REPORT_RESULT_UNIT in the report output for each SYS_SAMPLE_CODE.

 

ACTION_LEVEL_UNIT: The MAG sum calculation will convert units to the DT_ACTION_LEVEL_PARAMETER.UNIT for the selected Advanced Action Level.

 

Comparison | Significant Figures: There are four options for the Comparison | Significant Figures parameter.

1."calc": (the default) Calculates the significant figures in the ACTION_LEVEL column and applies that to the report formatting, which is the way the original ALE Report functions.

2."calc_result": Calculates the significant figures in the REPORT_RESULT_TEXT column and applies that to the report formatting, including the ACTION_LEVEL column.

3.1, 2, 3, 4, 5, and 6 significant figures. The report uses the selected number for output formatting.

4."ignore": When this is selected, the data is presented in the same format that the data is in the database.

 

Example:

Action Level = 5 ug/L

Result = 5.02 ug/L

 

Display with:     calc   calc_result     1      2        3       ignore

Action Level:       5           5.00           5     5.0    5.00         5

Result:                5           5.02          5     5.0    5.02       5.02

 

Exceedance?:     calc   calc_result     1    2     3   ignore

Before and '>':     x             x            x     x     x        x

After and '>':                      x                         x        x    

After and '>=':      x            x            x    x      x        x

 

Comparison | Compare to Action Level Before Applying Significant Figures: This parameter has the following options:

"Yes": (the default) compares the Action Level to the Result before the values are adjusted for significant figures.

"No": will make the comparison after adjusting for the significant figures selected in the previous parameter. Note that this only makes a difference when the Action Level\Comparison\Operator parameter is ">" (instead of the default ">=").

 

Comparison | Display in Scientific Notation: This parameter has the following options:

"Yes": displays the ACTION_LEVEL and REPORT_RESULT_TEXT columns in scientific notation.

"No": (the default) does not use scientific notation in the display of data.

 

 

Flagging

Flagging | Show Results:

 

The Show Results options allow users to expand or limit the results included in the report output, and whether to include notes, based on matching and exceedances. The matching logic depends on if/which Matching options are selected. At a minimum, a "match" is an analytical result for which the analyte (CAS_RN) is present in the action level. At most, the analytical result and action level also have matching dates, subfacility, land use, depths, fractions, methods, facility, and matrix values specified. See the Matching section later in this article for further details.

 

Show All options (there are three): lists all rows from the Analytical Results II (AR) Report at least once, even if there is no matching Action Level (AL). When there is a match, then the ACTION_LEVEL_CODE and related columns are populated and the exceedance determination is made and displayed in the FLAG column.

 

oShow All:

If there is no match between the AR and any of the selected AL(s), then that AR row is included once with no Action Level columns populated.

If there are multiple selected ALs with complete AR matches, then the AR row is repeated for each additional matching AL.

If there are multiple selected ALs and some ALs do not match the AR, then only the row or rows with matching Action Levels are displayed.

 

oShow All Plus:

If there is a matching AL based on CAS_RN, but additional input criteria do not match (i.e. an incomplete match), then the ACTION_LEVEL_CODE and related columns are populated, but there is no exceedance determination. A note is placed in the FLAG column to explain that no match was found.

When there are complete matches and/or complete non-matches, additional AR rows are included or excluded as described under Show All.

 

oShow All Plus Remark:

If there is a matching AL based on CAS_RN only, and additional input criteria do not match (i.e. an incomplete match), then the ACTION_LEVEL_CODE and related columns are populated, but there is no exceedance determination. A note is placed in the AL_REMARK and ALP_REMARK columns to explain that no match was found. Storing the notes in these columns, rather than the FLAG column (used in Show All Plus above) enables crosstab output with a single column for each chemical.

When there are complete matches and/or complete non-matches, additional AR rows are included or excluded as described under Show All.

 

30006-ALE_II_Show_All_options_table

 

Show Only Exceedances: Lists only those Analytical Results that 1) match an Action Level and 2) exceed an Action Level. If a result exceeds one Action Level, but not a second matching Action Level, it is only listed once.

Show With Any Exceedance: Lists only those Analytical Results that 1) match an Action Level and 2) exceed an Action Level. If a result exceeds one Action Level, but not a second matching Action Level, it is listed twice (once for each Action Level). This enables crosstab output with a single column for each chemical (instead of one column or row for results that exceed one Action Level and another column or row for results that exceed both Action Levels.

 

Flagging | Flag Non-Detects: There is a Flag Non-Detects option which allows switching between flagging non-detect results when the detection limit exceeds the action level, and not flagging non-detect results regardless of the detection limit. If the detection limit is less than the action level for a non-detect, the non-detect will be flagged as an exceedance if a non-detect multiplier large enough to cause the detection limit to exceed the action level is chosen in the parameters.

 

Example: a detection limit of 6 mg/L, an action level of 10 mg/L, and a non-detect multiplier of 2 (which will display, due to rounding, REPORT_RESULT_TEXT = < 10 and AL_RESULT_TEXT = < 10) will lead to that result being flagged.

 

Flagging | Exceedance Flag: This allows the symbol used as the exceedance flag to be changed from the default value of "x".

 

Flagging | Warning Flag: The warning level flag allows results to be tested against the warning levels (and WARNING_LEVEL_MIN) for the selected action level code. When crosstabbing the report, the two flags allow for exceedances of the action level and the warning level to be highlighted differently.

 

Note: Using the Action Level Exceedance II Report in Crosstab will now allow data that exceeds the WARNING_LEVEL to be highlighted with one format and/or color, while data that also exceeds the ACTION_LEVEL is highlighted with another format and/or color.

 

Assuming the normal relationship where the WARNING_LEVEL is less than the ACTION_LEVEL, the WARNING_LEVEL field must be placed before the ACTION_LEVEL field in the column or row header section of the Crosstab interface to get the desired results.

 

Otherwise, the "Exceeds Action Level" color will be set first and then be over-written by the "Exceeds Warning Level" color, since any value that exceeds the higher Action Level will also exceed the Warning Level.

 

For more information, please see Action Level Exceedance - Display Formatting in the Crosstab Reports section.

 

Flagging | Action Level Unit Conversion Errors: This parameter has three options:

 

"Stop Report"

oA unit conversion error message is displayed in the report grid with no other report output

oThis is the current functionality and the default value for this new report parameter

"Flag Errors"

oThe unit conversion error message is displayed in the FLAG column of each result with a unit conversion error

oThis allows the report to run for all of the other results that do not have the error

oThis allows the user to see exactly which results have the error

oThis prevents an accidental "No Exceedances" report due to a data entry issue

"Skip Errors"

oResults with a unit conversion error between the result unit and the action level unit are treated as if there was no match between the result and the action level

oThe report output will contain no values in any of the action level columns for the results with a unit conversion error (just like results that do not have a matching action level).

oThis could result in accidental "No Exceedances" report output which are not accurate. Therefore, this option is hidden in the standard release DLL.

oThis option can be added, at the user's own risk, by changing the DATA_SOURCE column in ST_REPORT_PARAMETER. (Contact EarthSoft Support for instructions.)

 

Unit Conversions

 

Analytical Results Section

If you select the result unit under Result -> Unit and a conversion does not exist in RT_UNIT_CONVERSION between the result unit and the selected unit, then the report does not run and you get the “Missing Unit Conversion Factor” error displayed in the report grid.

If you select the parameter Result -> Skip Missing Unit Conversions to be “True”, then the report will run without converting to the selected unit for the records that have errors.  

 

Action Level Section

The parameter Action Level –> Flagging – > Action Level Unit Conversion Errors can affect how unit conversion errors are handled

oParameter text: “For unit conversion errors between result units and the action level units. Please select Flag Errors to write error to FLAG column OR Stop Report to stop execution of ALE report with an error message in the report output grid. (The Skip Errors option – the option to skip errors without flagging – may be added to the report parameter DataSource list.)”

Flag Errors: report will continue to run but will add the error “Missing Unit Conversion Factor” in the “Flag” and “alp_remark” columns.  

Stop Report: report will not run and display an error message in the report grid notifying the user “Missing Unit Conversion Factor” in the report grid.

Skip Errors: report will run and overlook the errors while also not adding them to the “Flag” and “alp_remark” columns.

 

Matching

Analytical results are compared to action levels only when both the Matching report parameter is selected (check in the check box) and the column for that criteria in either DT_ACTION_LEVEL or DT_ACTION_LEVEL_PARAMETER is populated. See the help article Action Level Definition and Overview for further information on defining action levels.

 

Matching - Default:

 

The following match options are selected by default:

Facility

Matrix

Fractions

Methods

Depths

 

Match Depths: When Match Depths is selected, the Depth Type will be compared to the min_start_depth and max_end_depth of the action level. For example, min_start_depth ≤ start_depth and max_end_depth ≥ end_depth.

 

Depth Type:

Sample Depth: This is the default setting that matches DT_SAMPLE start and end depths to the minimum and maximum depths of the action level(s).

Water Level Depth (unconfined): This selection uses DT_WATER_LEVEL.WATER_LEVEL_DEPTH as the start and end depths for matching the minimum and maximum depths of the action level(s). The most recent non null water level depth for a given location of a sample is included in the output, provided that the measurement_date is prior to the SAMPLE_DATE + 5 days.

Water Table Depth (confined): This selection uses DT_WATER_TABLE depth values as the start and end depths for matching the minimum and maximum depths of the action level(s). The water table record must have type of 'CONFINED' and sequence of 'STABILIZED'. The depth value from DT_WATER_TABLE for the location of the sample will be included in the report output.

 

Matching - Special: The following match options are also available.

Subfacility

Start-End Dates

Seasonal Dates

Land Use

Geologic Unit Code

 

Match Land Use: When Match Land Use is selected, Action Levels will only apply to Locations where DT_ACTION_LEVEL.LAND_USE = DT_LOCATION.LAND_USE.

 

Match Geologic Unit: When matching geologic_unit_code, the following three additional selections are required.

 

Geologic Unit Code or Type: This determines whether the actual GEOLOGIC_UNIT_CODE (as defined in the Geologic Unit Code Column parameter) will be used in matching with the action level or if the GEO_UNIT_TYPE from AT_GEO_UNIT for the chosen GEOLOGIC_UNIT_CODE should be used instead.

 

Geologic Unit Code: Use the actual geologic unit code for matching.

Geologic Unit Type: Use the GEO_UNIT_TYPE from AT_GEO_UNIT for matching. In order for this option to work, the GEO_UNIT_TYPE must also be added as a geologic_unit_code in RT_GEOLOGIC_UNIT so that it can be set as the GEOLOGIC_UNIT_CODE for the action level. The GEOLOGIC_UNIT_TYPE must also be assigned to the locations/samples in the relevant data tables (DT_STATIC_PROP, DT_WELL or DT_LITHOLOGY as listed below).

 

Geologic Unit Code Columns: This is a single-select parameter to identify which column should be used as the geologic_unit_code when matching with the action level. The column selected will be included in the output.

 

DT_STATIC_PROP.GEOLOGIC_UNIT_CODE

DT_WELL.GEOLOGIC_UNIT_CODE

DT_LITHOLOGY.GEO_UNIT_CODE_1

DT_LITHOLOGY.GEO_UNIT_CODE_2

DT_LITHOLOGY.GEO_UNIT_CODE_3

DT_LITHOLOGY.GEO_UNIT_CODE_4

DT_LITHOLOGY.GEO_UNIT_CODE_5

 

The Include: Depths Fields from DT_ACTION_LEVEL option must be checked for the Geologic Unit matching to work properly. It adds the following columns from DT_ACTION_LEVEL to the report output: MIN_START_DEPTH, MAX_END_DEPTH, DEPTH_UNIT, and GEOLOGIC_CODE_UNIT.

 

 

Column Configuration

Column Configuration | Column Configuration File: The Column Configuration input parameters, hidden by default, enable entering partial SQL commands or scripts to customize report output as described below.

 

Notes:

The SQL “from”/insert/update/delete commands are not supported. Input data sources are limited to those included with the standard ALEII output.

Due to the additional data processing, column configuration inputs may significantly affect report performance.

If both Column Configuration input parameters are populated, the Column Configuration File (below) will be applied.

 

Column Configuration: (hidden by default); Enter partial SQL commands or scripts of less than 4,000 characters into this parameter to customize report output, as described below.  

 

1)Add additional output columns after the standard ALEII output by entering partial SQL scripts. Using Example 1, the ALEII output will include my_flag and my_criteria columns after the standard columns.
 
Example 1:
my_flag = case when action_level > 0.8*report_result_value then '>0.8r' else flag end, 0.8*report_result_value my_criteria
 
30006_ALE2_columnconfig_add

 

2)Specify which standard columns to include in the report output by entering ! followed by  partial SQL scripts.
 
Example 2:
! sys_loc_code,  sys_sample_code, sample_date, cas_rn, flag, action_level, report_result_text, report_result_unit
 
30006_ALE2_columnconfig_set

 

3)Combine options 1) and 2) above: i.e. add additional custom logic columns in conjunction with specifying standard columns in the report output.
 
Example 3:
sys_loc_code, sys_sample_code, sample_date, cas_rn, flag, action_level, report_result_text, report_result_unit, my_flag = case when action_level > 0.8*report_result_value then '>0.8r' else flag end, 0.8*report_result_value my_criteria
 
30006_ALE2_columnconfig_addandset

 

Warning: Aliases are needed when fields with the same column names are added.

 

Column Configuration File (hidden by default): This parameter functions the same as the 'Column Configuration' parameter, except it supports the partial SQL scripts exceeding 4,000 characters.  

 

Prerequisites for the file input display/selection:

Upload .txt file(s) with the SQL script via Docs & Photos.

Set DT_FILE.PLACE_TYPE to “report” for the .txt file record(s).

 

Report Output

ACTION_LEVEL_CODE

CAS_RN

CHEMICAL_NAME

FLAG

ACTION_LEVEL

ACTION_LEVEL_MIN

AL_RESULT_TEXT

AL_RESULT_VALUE

ACTION_LEVEL_UNIT

WARNING_FLAG

WARNING_LEVEL

WARNING_LEVEL_MIN

FACILITY_ID

FACILITY_CODE

SYS_LOC_CODE

SAMPLE_NAME

SYS_SAMPLE_CODE

AL_SUBFACILITY_CODE

ACTION_LEVEL_TYPE

AL_MATRIX

AL_FRACTION

AL_ANALYTICAL_METHOD

AL_REMARK

OBJECT_NAME

ACTION_LEVEL_NOTE

ACTION_LEVEL_MIN_NOTE

LOC_NAME

LOC_GROUP

LOC_REPORT_ORDER

SAMPLE_DATE

SAMPLE_TYPE_CODE

START_DEPTH

END_DEPTH

DEPTH_UNIT

MATRIX_CODE

GEOLOGIC_UNIT_CODE

TASK_CODE

TASK_DESC

FIELD_SDG

PARENT_SAMPLE_CODE

ANALYSIS_LOCATION

LAB_SAMPLE_ID

LAB_MATRIX_CODE

LAB_NAME_CODE

ANALYTIC_METHOD

ANALYSIS_DATE

COLUMN_NUMBER

FRACTION

TEST_TYPE

PREP_METHOD

LEACHATE_DATE

LAB_SDG

PERCENT_MOISTURE

DILUTION_FACTOR

METHOD_ANALYTE_GROUP

REPORT ORDER

TEST_ID

ORGANIC_YN

REPORT_RESULT_TEXT

REPORT_RESULT_VALUE

REPORT_RESULT_UNIT

REPORT_RESULT_LIMIT

REPORT_METHOD_DETECTION_LIMIT

REPORT_REPORTING_LIMIT

REPORT_QUANTITATION LIMIT

REPORT

REPORTABLE_RESULT

DETECT_FLAG

INTERPRETED_QUALIFIERS

VALIDATOR_QUALIFIERS

LAB_QUALIFIERS

QUANTITATION_LIMIT

METHOD_DETECTION_LIMIT

REPORTING_DETECTION_LIMIT

DETECTION_LIMIT_UNIT

APPROVAL_CODE

RESULT_TEXT

RESULT_NUMERIC

RESULT_UNIT

RESULT_TYPE_CODE

X_COORD

Y_COORD

Z_COORD_AVG

ZFROM

ZTO

LONGITUDE

LATITUDE

AL_LAND_USE

ALP_REMARK

PERMIT_NUMBER

ACTION_LEVEL_DESC

LOOKUP_SOURCE

LOOKUP_CODE

LOOKUP_METHOD

SAMPLEDATE

SAMPLETIME

TASK_CODE_2

ACTION_LEVEL_VALUE

ACTION_LEVEL_MIN_VALUE

WARNING_LEVEL_VALUE

WARNING_LEVEL_MIN_VALUE

 

Extra fields: Any fields chosen
in this parameter

 

Tables: DT_ACTION_LEVEL, DT_ACTION_LEVEL_PARAMETER, DT_SAMPLE, DT_TEST, DT_RESULT, DT_SUBFACILITY, DT_LOCATION

 

1The Report Grid provides additional export options to Excel, Google Earth, Outlook, Shapefile, PDF, Text, Access, XML, or a printer.