<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports > Analytical Results > Analytical Results II

Report Name: Analytical Results II (example)

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

Dependencies: N/A

Software: EQuIS Professional and EQuIS Enterprise

Source: EQuIS Professional install, EQuIS Enterprise install

Language: English

Output Type: Report Grid1

 

Description - Analytical Results II: The primary report for analytical data in EQuIS, combining data from the Sample, Test, Result, Field Sample, Location and Analyte tables at a minimum. Data from other tables may be included based on report parameter selections.

 

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

 

Overview:

•Additional columns, with names that begin with "REPORT_RESULT_*", are included in the report output to provide consistent, configurable formatting for final presentations. (See logic used to populate those extra columns here).

•In EQuIS Professional, the report outputs to the standard grid with toolbar menus that can display data in formats such as XY Chart, Trend Plot, Crosstab, and customizable RDLs.

•The Additional Fields input parameter enables supplementing the report output with data from additional table/column(s).

•Analytical Results II is a 'class' report, while the original Analytical Results is a 'function' report. Distinctions between these reports include:

oImproved Performance: Analytical Results II is designed to execute faster than the legacy Analytical Results Report.

oThe Analytical Results II Report must be published before it can be saved as a User Report (formerly Pick Report).

oThe saved User Report from Analytical Results II can only be used by other 'class' type reports.

 

Source Tables DT_FACILITY, VW_LOCATION, DT_SAMPLE, DT_TEST, DT_RESULT, RT_ANALYTE, DT_LOCATION, DT_SUBFACILITY, DT_WELL, DT_FIELD_SAMPLE, DT_STATIC_PROPS, DT_LITHOLOGY, DT_TASK, DT_RESULT_QC, RT_MATRIX, RT_SAMPLE_TYPE, RT_ANALYTIC_METHOD, RT_GROUP, RT_GROUP_MEMBER, ST_EDD_BATCH, RT_MTH_ANL_GROUP, RT_MTH_ANL_GROUP_MEMBER, RT_ANALYTE_VAR, DT_WATER_LEVEL, DT_PURGE

 

Report Parameters:

•Location

•Sample

•Test

•Result

oReporting Non-Detects

•Additional Fields

•Workflow Status(es)

•Hidden Parameters

•Transformation(s)

•Unit Conversion

oREPORT_* Column Logic

 

 

 

Location

 

•Location | Individual(s): Multi-select for individual SYS_LOC_CODE(s) to filter report output.

•Location | Group(s): Multi-select Location Group codes to filter report output. Can be combined with the individual location selections. In the location group, RT_GROUP_MEMBER.MEMBER_TYPE = sys_loc_code is required for that location to be included in the report output.

•Location | Use DT_GEOGRAPHY for coordinate data: Check box to toggle between using data from DT_COORDINATE and DT_GEOGRAPHY to populate X_COORD, Y_COORD, LONGITUDE and LATITUDE columns. When the check box parameter, "Use DT_GEOGRAPHY for coordinate data" is selected, the report will look for POINT data in DT_GEOGRAPHY to fill the X_COORD, Y_COORD, LONGITUDE, and LATITUDE columns.

 

 

Sample

 

     

 

The columns referred to below are from DT_SAMPLE, unless otherwise noted.

•Sample | Type(s): Multi-select for SAMPLE_TYPE_CODE(s) to filter report output.

•Sample | Date Range | Start, End: Enter or select date range to filter report output. Defaults to first and last dates in DT_SAMPLE for the facility.

•Sample | Most Recent Sampling: Check box to only include results from the most recent sampling event for each location (based on a unique list of [FACILITY_ID, SYS_LOC_CODE, CAS_RN, FRACTION, most recent sample_date(s)]).

•Sample | Use Date Range in Finding Most Recent Sampling: Check box to activate the dates in Date Range to restrict the data used to determine the Most Recent Sampling event. If this check box is not checked and Most Recent Sampling is checked, then all of the samples for the facility are used to determine the most recent sampling events.

•Sample | Matrix(es): Multi-select for MATRIX_CODE(s) to filter report output.

•Sample | Task(s): Multi-select for TASK_CODE(s) to filter report output.

•Sample | Task_Code_2(s): Multi-select for TASK_CODE_2(s) to filter report output.

•Sample | Field SDG(s): Multi-select for DT_FIELD_SAMPLE.FIELD_SDG(s) to filter report output.

•Sample | Depth | Min Start; Max End; Max Start; Min End; Unit: Enter depth range and unit to filter report output.

•Sample | DQM Status: Multi-select for DQM_STATUS(s) to filter report output.

 

Test

 

The columns referred to below are from DT_TEST, unless otherwise noted.

•Test | Method(s): Multi-select for ANALYTIC_METHOD(s) to filter report output.

•Test | Fraction(s): Multi-select for FRACTION(s) to filter report output.

•Test | Column Number(s): Multi-select for COLUMN_NUMBER(s) to filter report output.

•Test | Type(s): Multi-select for TEST_TYPE(s) to filter report output.

•Test | Lab Matrix(es): Multi-select for LAB_MATRIX_CODE(s) to filter report output.

•Test | Prep Method(s): Multi-select for PREP_METHOD(s) to filter report output.

•Test | Lab SDG(s): Multi-select for LAB_SDG(s) to filter report output.

 

Result

 

Result

    Analyte

               Individual(s):

               Group(s):

               Exclude Analyte(s): None (default)

            Unit:

            Type(s):                                        Reportable:

            Detect Flag(s):                               Validated:

            Non-Detect Mutliplier: 1                Reporting Limit: reporting_detection_limit

            Non-Detect Symbol: < ?                Format Results

            Non-Detect Qualifier:                        Format code:

 

 

The columns referred to below are from DT_RESULT unless otherwise noted.

•Result | Analyte | Individual(s): Multi-select individual for CAS_RN(s) to filter report output.
 

 

•Result | Analyte | Group(s): Multi-select for Method Analyte Group Code(s) (MAGs) to filter report output. Within a chosen MAG, if there is a reporting_unit specified for an analyte (either within the Groups GUI - see below - or RT_MTH_ANL_GRP_MEMBER.REPORTING_UNITS), that unit will be used for that analyte instead of the default unit set by report parameters. Can be combined with the individual location selections.

 

•Result | Analyte | Exclude Analyte(s): Single-select: None (default) will not exclude any analytes. Without detects under the selected parameters will exclude analytes which do not have at least one detected value under the selected parameters from the output.

•Result | Unit: Single-select for a UNIT to which all results will be converted. See sections on Unit Conversions and Skip Missing Unit Conversions below.

•Result | Type(s): Multi-select for RESULT_TYPE_CODE(s) to filter report output.

•Result | Detect Flag(s): Multi-select for DETECT_FLAG(s) to filter report output.

•Result | Non-Detect Multiplier: Enter number to multiply the Reporting Limit by when populating the REPORT_RESULT_TEXT and REPORT_RESULT_VALUE columns for Non-Detect results.

▪See Non-Detect Multiplier to Report_Result_Value Only under Hidden Parameters below to only apply the multiplier to the REPORT_RESULT_VALUE column.

•Result | Non-Detect Symbol: Enter the symbol and a '?' to have the symbol placed in front of the number used when populating the REPORT_RESULT_TEXT column for Non-Detect results. Enter text without the '?', such as 'ND', to have the text itself used when populating the REPORT_RESULT_TEXT column for Non-Detect results.

•Result | Non-Detect Qualifier: Enter a qualifier, such as 'U', to be used, in addition to DETECT_FLAG, to determine if a result is a detected concentration or Non-Detect. This allows a data validator's qualifier (in either the VALIDATOR_QUALIFIER or the INTERPRETED_QUALIFIER column) to supercede the lab's DETECT_FLAG. If a value is entered into this parameter, then the report will search for that value in all three qualifier columns (only in the results with a DETECT_FLAG = 'Y' or 'TR'). If found, then the detect_flag will be changed to "Q" (in the report output only, not in the database) and the result will be displayed as a non-detect value," e.g., <0.1 where 0.1 is the reporting_detection_limit.

•Result | Reportable: Multi-select for REPORTABLE_RESULT(s) to filter report output.

•Result | Validated: Multi-select for VALIDATED_YN(s) to filter report output.

•Result | Reporting Limit: Single-select for which column should populate the REPORT_RESULT_LIMIT output column.

•Result | Format Results | Format Code: This parameter can be used to format REPORT_RESULT_TEXT, REPORT_RESULT_LIMIT, REPORT_METHOD_DETECTION_LIMIT, REPORT_REPORTING_LIMIT, REPORT_QUANTITATION_LIMIT and in some circumstances REPORT_RESULT_VALUE. For more information, see the Format Code page.

 

 

Reporting Non-Detects:

1. Determine the original unit (nd_unit) as in the order below:  

= dt_result.result_unit if the Result/Reporting Limit parameter selection is result_text, or
= dt_result.result_unit if
 the initial REPORT_RESULT_LIMIT (see below) is null or not numeric and
 REPORT_RESULT_UNIT is determined by Result\Unit or rt_mth_anl_group_member.reporting_units, or
= dt_result.detection_limit_unit if
 result_unit is null or
 if the Result/Reporting Limit parameter selection is not result_text, or
= dt_result.result_unit if dt_result.detection_limit_unit is null

2. Determine the final unit (REPORT_RESULT_UNIT) as in the below order based on the Result/Unit parameter selection and nd_unit:

= the Result/Unit parameter selection, or
= RT_MTH_ANL_GROUP_MEMBER.REPORTING_UNITS if MAG is selected and REPORTING_UNITS is provided, or
= nd_unit if not any of the above.

3. Calculate REPORT_RESULT_LIMIT based on the Result/Reporting Limit parameter selection, nd_unit and REPORT_RESULT_UNIT.

A. Set initial REPORT_RESULT_LIMIT = the Result/Reporting Limit parameter selection which can be one of

i. reporting_detection_limit if the Result/Reporting Limit parameter is not selected, or
ii. quantitation_limit
iii. method_detection_limit
iv. mag_reporting_detect_limit (RT_MTH_ANL_GROUP_MEMBER.REPORTING_DETECT_LIMIT) or
v. result_text

If any of ii-v is selected and that field is null, the default REPORTING_DETECTION_LIMIT will be used.

B. Convert REPORT_RESULT_LIMIT from nd_unit to REPORT_RESULT_UNIT.

C. Calculate the final REPORT_RESULT_LIMIT as in the below order:  

= the converted REPORT_RESULT_LIMIT if the unit conversion succeeds or
= the initial REPORT_RESULT_LIMIT if
 the unit conversion fails and
 @skip_missing_unit_conversions = true

4. Calculate REPORT_RESULT_VALUE: REPORT_RESULT_LIMIT * the value of the “Non-Detect Multiplier” parameter.

5. Calculate REPORT_RESULT_TEXT based on dt_result.detect_flag, the value of the “Non-Detect Symbol” parameter and REPORT_RESULT_LIMIT.

A. Determine the format of displaying REPORT_RESULT_TEXT:

= {dt_result.detect_flag?} if detect_flag is populated with one of (<, >, >= or <=), or
= {value} of the “Non-Detect Symbol” parameter, which should include ?, or
= {?} if the “Non-Detect Symbol” parameter is empty or not the above.

B. Replace the ? of the format with one of the below values:

i. dt_result.result_text if
 result_text is not empty and
 result_text is not numeric, or
ii. dt_result.result_numeric if
 dt_result.detection_limit_unit is not numeric and
 there are no unit conversions, or
iii. converted dt_result.result_numeric if
 dt_result.detection_limit_unit is not numeric and
 no unit conversion errors occur, or
 if  unit conversion error occurs but @skip_missing_unit_conversion = True, or
iv. AR II outputs ‘MISSING UNIT CONVERSION …’ if
 dt_result.detection_limit_unit is not numeric and
 unit conversion errors occur and
 @skip_missing_unit_conversion = False, or
v. REPORT_RESULT_LIMIT * Non-Detect-Multiplier if none of the above.

For example, an ND has dt_result_detect_flag of N, dt_result.detection_limit_unit of 0.05 and dt_result.result_text of null. AR II has the parameters Non-Detect Multiplier = 1.0 and Non-Detect Symbol = > ?.

•AR II calculates REPORT_RESULT_LIMIT to 0.05.  

•AR II calculates the REPORT_RESULT_VALUE of the ND to 0.05 * 1.0 = 0.05

•After getting the format of {> ?}, AR II displays the REPORT_RESULT_TEXT of the ND as “> 0.05” after replacing the ? of the {> ?} format with REPORT_RESULT_LIMIT * Non-Detect-Multiplier.

 

Additional Fields

 

Additional Fields: Multi-select for additional table/column(s) to include in the report output. This does not include additional filtering (i.e. the additional fields are not available as input parameters for further input selections). Certain tables and fields are availlable by default. Read here for information on how to add tables and fields to the Additional Fields Parameter.

•In Additional Fields for this report, DT_WELL_SEGMENT uses a default SEGMENT_TYPE of SCREEN. An administrator can change this setting in ST_CONFIG with the following entry:

oCONFIG_SECTION = ar

oCONFIG_KEY = SEGMENT_TYPE

oSTRING_VALUE = the SEGMENT_TYPE to use, e.g. CASING

•Additional Fields' columns may be renamed using RT_REMAP_DETAIL:

oremap_code = en-US

ointernal_value = an Additional Field of AR II, such as DT_RESULT.APPROVAL_A

oexternal_field = a new name, such as remapped_A

ostatus_flag = A
 
After DT_RESULT.APPROVAL_A is selected from the Additional Fields parameter, remapped_A is included in the report output.

•To select a field of RT_GROUP, the Location – Group(s) parameter must be selected.

 

 

Workflow Status(es)

 

This multi-select parameter list dynamically populates from RT_WORKFLOW_STATUS joined to XT_TABLE.

•Only data tables (DT_*) that appear within the Additional Fields report parameter will be eligible to appear in the selections for this report parameter.

•For any table where you select one or more values, the workflow_status must match one of the values selected for that table, and the table's workflow_status field will be included in the report output.

•If values in multiple tables are selected, the workflow_status for EACH table with any values selected must match one of the values selected.

 

 

Hidden Parameters

 

The following Analytical Results II Report input parameters are hidden by default (ST_REPORT_PARAMETER.VISIBILITY=N). If needed, these parameters may be made visible as described in Hide or Show Parameters.

 

ebatch

Populating ebatch runs the report only for that specific EDD.

 

Location | Individual(s)-Facilities

The Location\Individual(s)-facilities parameter lists locations with facility associations. This enables distinguishing between SYS_LOC_CODEs when connected to a facility group that includes the same SYS_LOC_CODE(s) in more than one facility member. The Location\Individual(s) parameter is ignored when Location\Individual(s)-facilities is selected. To use this parameter, change the ST_REPORT_PARAMETER.VISIBILITY of PARAM_NAME = @location_euids for the target REPORT_ID from N to Y. It is recommended that this be done after publishing the Analytical Results II Report with a different REPORT_NAME; the Location\Individual(s) parameter may then be removed from display to avoid confusion, since it is ignored when the Location\Individual(s)-facilities parameter is used.

 

Sample | Delivery Group(s) [ignored if Field SDG and/or Lab SDG are selected]

Select Sample Delivery Groups (SDG) for the facility from DT_SDG, compared to Sample\Field SDG(s) drawing from DT_FIELD_SAMPLE or Test\Lab SDG(s) drawing from DT_TEST.

 

Sample | Geologic Unit(s)

Select GEOLOGIC_UNIT_CODEs for the facility from DT_STATIC_PROPS.

 

Result | Use Non-Detect Multiplier to Report_Result_Value Only

When this is checked, the non detect multiplier is applied only to REPORT_RESULT_VALUE and not REPORT_RESULT_TEXT.

 

Result | Format Results | Custom (Overwrites Format Code if Format Code is also selected)

The "Result\Format Results\Custom" accommodates any valid values for user-specified Format Code to be applied as discussed above. Note that the Custom parameter overwrites the Format Code parameter if both are populated.

 

Extra Selects

The Extra Selects or @extra_select is available for advanced users to configure in ST_REPORT_PARAMETER, and allows for supplementing report output with a combination result or formatted result using the fields available in the Additional Fields parameter. Please see additional documentation regarding Extra Selects here.

 

Skip Missing Unit Conversions

Enable hidden parameter Skip Missing Unit Conversions for the AR II Report to skip failed result unit conversions and generate REPORT_RESULT_VALUE, REPORT_RESULT_TEXT, REPORT_RESULT_UNIT and REPORT_RESULT_LIMIT with original values and units for the results that fail unit conversions. The Skip Missing Unit Conversions parameter defaults to unchecked (default_value=false) and can be set in the database (ST_REPORT_PARAMETER) and made visible, if necessary.

 

 

Transformation(s)

The Transformation(s) parameter lists Standard ARII Transformation and Custom ARII Transformations from ST_MODULE that can be run on the report. For transformation setup, see the online help article Report Transformations in Analytical Results II.

 

Unit Conversion

 

Unit Conversion is handled by the unit conversion function within the database, which relies on the conversion factors stored in the RT_UNIT_CONVERSION_FACTOR table. If the report fails to convert units, it generates an informative error message of either "MISSING UNIT CONVERSION FACTOR - cannot convert ..." or "Actual unit value EQUALS the special "NO CONVERSION" value specified..." Note only the first encountered missing unit conversion factor will be included in the error message; additional errors may occur after the initial error is resolved.

 

ARII generates a unit conversion error message when unit conversion fails on start_depth, end_depth, Z_COORD_AVG, ZFROM or ZTO even if Skip Missing Unit Conversions is set to TRUE. The Z_COORD_AVG, ZFROM and ZTO columns require a valid conversion between DT_SAMPLE.DEPTH_UNIT and VW_LOCATION.ELEV_UNIT, where the latter is determined using the following logic:

•Use DT_FACILITY.ELEV_UNIT if present

•If not, use DT_LOCATION.UNITS if present

•If not, use DT_COORDINATE.ELEV_UNIT if present

•If not, a NULL value will be returned.

 

 

For User Reports

 

•Save input parameters to the database

•Load input parameters by selecting named report from User Reports drop-down list

•Available in EQuIS Enterprise User Reports list (formerly called Pick Reports)

•Available as input for other (report-dependent) reports

 

For Trend Plots

 

•Group charts by

•Group series by

•Advanced properties include:

oIndividual Chart

oAll charts

oAll series

•Set of series - this adds all series for a specific analyte to a secondary axis

 

 

The following is the logic that the ARII report uses for Z_COORD_AVG, ZFROM, and ZTO.

•Z_COORD_AVG: If VW_LOCATION.SURF_ELEV, DT_SAMPLE.START_DEPTH or DT_SAMPLE.END_DEPTH is NULL then Z_COORD_AVG returns NULL; otherwise, the report computes the value by averaging the START_DEPTH and END_DEPTH

 For example: (START_DEPTH + END_DEPTH)/2)  

•ZFROM: If either VW_LOCATION.SURF_ELEV or DT_SAMPLE.START_DEPTH is NULL then ZFROM returns NULL; otherwise, the report computes the value by subtracting the START_DEPTH from the SURF_ELEV

 For example: (SURF_ELEV - START_DEPTH)

•ZTO: If either VW_LOCATION.SURF_ELEV or DT_SAMPLE.END_DEPTH is NULL then ZTO returns NULL; otherwise, the report computes the value by subtracting the END_DEPTH from the SURF_ELEV

 For example: (SURF_ELEV - END_DEPTH)

 

Unit conversions are performed for each of the above fields as follows:

•ORIG_UNIT: Looks first at DT_SAMPLE.DEPTH_UNIT. If NULL, then looks at VW_LOCATION.ELEV_UNIT

•TARGET_UNIT: Looks first at VW_LOCATION.ELEV_UNIT. If NULL, then looks at DT_SAMPLE.DEPTH_UNIT

 

REPORT_* Column Logic: The table below shows the logic of the ARII output, whether original or converted values, as related to various input conditions. If there is more than one output listed, the logic applied is prioritized from high to low, based on detect_flag ('Y' or 'TR' identifies detects or D and others non-detects or ND) and other conditions:

 

 

For the "Use Non-Detect Multiplier to Report_Result_Value Only" parameter, check the box to apply Non-detect Multipliers to only report_result_value. Otherwise, Non-detect Multipliers is applied to both report_result_value and report_result_text.

 

*Means converted from original unit to report_result_unit.

 

The original unit of REPORT_RESULT_LIMIT is result_unit if @reporting_limit = 'result_text' and result_text is a text, one of (RT_MTH_ANL_GROUP_MEMBER.REPORTING_UNITS, DT_RESULT.DETECTION_LIMIT_UNIT or DT_RESULT.RESULT_UNIT in order) if @reporting_limit = 'mag_reporting_detect_limit' and one of (detection_limit_unit or result_unit) if @reporting_limit is one of other reporting_limit selections.

 

The original unit of REPORT_RESULT_VALUE is result_unit for D or one of (RT_MTH_ANL_GROUP_MEMBER.REPORTING_UNITS, DT_RESULT.DETECTION_LIMIT_UNIT or DT_RESULT.RESULT_UNIT in order) for ND if @reporting_limit = 'mag_reporting_detect_limit', or one of (detection_limit_unit or result_unit) for ND if @reporting_limit is one of other reporting_limit selections.

 

** If DT_RESULT.DETECT_FLAG is '<', '>', '>=' or '<=', then 'symbol' = DETECT_FLAG; otherwise 'symbol' = @nd_symbol.

 

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