SPM - Analytical Results Report

Report Name: SPM - Analytical Results (example)

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

Dependencies: Licensed SPM and SPM Schema

Software: SPM

Source: SPM install

Language: English

Output Type: Report Grid1

Description: This report is an Analytical Results report with additional fields specific to SPM and the SPM Schema.

Installation Instructions: This report requires a licensed SPM and the SPM Schema applied to the database.

•For SPM usage, no additional steps are necessary.

•For Enterprise usage, this report should be published to the database. The report will be available for selection once it has been published.

•For Professional usage, this report should be published to the database or have the .dll added to the local '.../Earthsoft/EQuIS/' folder.

Overview: The SPM - Analytical Results report has a feature that allows you to preview of the number of records. The feature currently only works in EQuIS Professional. To use this feature, hold the CTRL (Control) key on your keyboard while clicking the green Go button to run the report. When the CTRL key is pressed, the report will return a single row, single column value that is the number of records the report will return with the currently selected parameters. To run the report, click the Refresh button. If the CTRL key is not pressed while running the report, then the report will function normally and will return the actual records.

Note: The Analytical Results II report presents RESULT_TEXT to REPORT_RESULT_TEXT in its output whenever the RESULT_TEXT is not numeric and not null, and presents null to REPORT_RESULT_LIMIT of a row where a RESULT_TEXT is not numeric and not null and if RESULT_TEXT is selected as the reporting limit.

Source Tables FACILITY_ID, FACILITY_CODE, SYS_LOC_CODE, LOC_NAME, SAMPLE_ID, SYS_SAMPLE_CODE, SAMPLE_NAME, SAMPLE_DATE, SAMPLE_TYPE_CODE, START_DEPTH, END_DEPTH, DEPTH_UNIT, MATRIX_CODE, TASK_CODE, 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_METHOD, LEACHATE_DATE, LAB_SDG, PERCENT_MOISTURE, DILUTION_FACTOR, TEST_ID, CAS_RN, CHEMICAL_NAME, ORGANIC_YN, REPORT_RESULT_TEXT, REPORT_RESULT_VALUE, REPORT_RESULT_UNIT, REPORT_RESULT_LIMIT, 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, LONGTITUDE, LATITUDE, LOC_GROUP_CODE, LOC_REPORT_ORDER.

ST_REPORT_PARAMETER.DATA_SOURCE for SPM - Analytical Results lists all fields from the following 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
Individual(s)	Parameter for selecting individual locations.
Group(s)	Parameter for selecting location groups.
Use DT_GEOGRAPHY for coordinate data	Toggle to use DT_GEOGRAPHY for coordinate data. Default: False.
Sample
Type(s)	Select one or more sample types.
Date Range	Section to select the start date, end date, most recent sampling (False), and the option to use date range to select the most recent sampling events (False).
Matrix(es)	Select one or more sample matrix
Task Filter	Select one or more tasks
Department(s)	Parameter for selecting department(s).
Monitoring Program(s)	Parameter for selecting monitoring program(s).
Monitoring Type(s)	Parameter for selecting monitoring type(s).
Authority(s)	Parameter for selecting authority(s).
Authority Type(s)	Parameter for selecting authority type(s).
Task Type	Parameter for selecting task type.
Priority Ranking	Parameter for selecting priority ranking.
Parent Task(s)	Parameter for selecting parent task(s).
Task Code(s)	Parameter for selecting task code(s).
Task_Code_2(s)
Field SDG(s)	Select one or more Field SDGs.
Depth	Section for selecting the minimum and maximum start and end depths as well as depth unit
Minimum Start Depth	If a value is entered, only samples that start (DT_SAMPLE.START_DEPTH) above that value will be included. Sample with null START_DEPTH will be excluded.
Maximum End Depth	If a value is entered, only samples that end (DT_SAMPLE.END_DEPTH) below that value will be included. Sample with null END_DEPTH will be excluded.
Maximum Start Depth	If a value is entered, only samples that start (DT_SAMPLE.START_DEPTH) below that value will be included. Sample with null START_DEPTH will be excluded.
Minimum End Depth	If a value is entered, only samples that end (DT_SAMPLE.END_DEPTH) above that value will be included. Sample with null END_DEPTH will be excluded.
Unit	Select the desired unit for sample depth min/max.
DQM Status	Parameter for selecting DQM Status.
Test
Method(s)	Select one or more test method.
Fraction(s)	Select one or more fraction.
Column Number(s)	Select one or more column number.
Type(s)	Select one or more test type.
Lab Matrix(es)	Select one or more lab matrixes.
Prep Method(s)	Select one or more prep methods.
Lab SDG(s)	Select one or more Lab SDGs.
Result
Analyte	Section for selecting analytes and/or analyte groups.
Unit	Selection for report result units.
Type(s)	Select one or more result type.
Detect Flag(s)	Select one or more options for detect flag.
Non-Detect Multiplier	Enter the desired non-detect multiplier value.
Non-Detect Symbol	Enter the desired non-detect symbol ("< ?", "ND", ...). "?" will be replaced with reporting limit multiplied by non-detect multiplier.
Non-Detect Qualifier	Enter the desired non-detect qualifier (e.g., "U") to have that qualifier override the DETECT_FLAG column.
Reportable	Select one or more options for reportable result.
Validated	Selection for whether the result is validated or not
Reporting Limit	Select which reporting limit you desire to use (reporting_detection_limit, method_deteciton_limit, etc.)
Format Results\Format Code:	Select one format code (e.g. 0.########, 0.0E-00 etc.) predefined or defined in rt_lookup.lookup_desc with lookup_type='ARII_FORMAT'. It applies to REPORT_RESULT_TEXT, REPORT_RESULT_LIMIT, REPORT_METHOD_DETECTION_LIMIT, REPORT_REPORTING_LIMIT, REPORT_QUANTITATION_LIMIT and defaults to empty with no formats applied. It also applies to REPORT_RESULT_VALUE when it can be used to control rounding decimals of a value.
Additional Fields	Additional fields parameter

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

Reporting Non-Detects: This report uses specific logic for reporting non-detects. For more information, see the Non-Detect Logic for Analytical Results Report Diagram.

Unit Conversion: Unit Conversion is handled by the unit conversion function within the database, which uses the conversion factors stored in the RT_UNIT_CONVERSION table. If the function attempts to do a conversion for which no conversion factor exists, the result value is 999999. When this value appears in any report, review the unit conversion for that result to see why the conversion failed.

The fields which report the unit converted analytical and their respective source fields are:

•REPORT_RESULT_TEXT (DT_RESULT.RESULT_TEXT)

•REPORT_RESULT_VALUE (DT_RESULT.RESULT_VALUE)

•REPORT_RESULT_UNIT (unit parameter selection)

•REPORT_RESULT_LIMIT (DT_RESULT.RESULT_LIMIT)

Most Recent Sampling: When "Most Recent Sampling" is checked, the report gets the results of a unique list of (facility_id, sys_loc_code, cas_rn, fraction, most recent sample_date(s)).

The Use Date Range in Finding Most Recent Sampling (UDR) parameter focuses the report to a specified date range to speed up the searching process. If UDR is checked and MRS is not, the report finds the most recent samples in the entire sample list of the selected facility. If both UDR and MRS are checked and the user narrows down the date range, the report finds the most recent samples within the selected date range.

Extra Selects: The Extra Selects or @extra_select is a hidden parameter available for advanced users to configure in ST_REPORT_PARAMETER. It allows for supplementing report output with a combination result or formatted result using the fields available in the Additional Fields parameter. For example, for PARAM_NAME=@extra_select, if ST_REPORT_PARAMETER.DEFAULT_VALUE of a SQL database is populated with “start_depth + '-' + end_depth as depth_range”, a column of date_range with data like “15-20” is added to the report output grid. In a SQL database, if “CONVERT(VARCHAR(8),sample_date,108) AS STime” is added to the DEFAULT_VALUE, a STime field with results like “18:58:00” will be included in the output.

The @extra_select parameter may be also used to generate 'permanent' output fields. For example, if Location Group(s) or Result>Analyte>Group(s) (aka MAGs) parameters is not selected in the Analytical Results II Report, MAGs or Location Group(s) related information such as MAG_REPORT_ORDER or LOC_REPORT_ORDER will not be in the output. After “NULL as LOC_REPORT_ORDER, NULL as MAG_REPORT_ORDER” is added to the DEFAULT_VALUE of the @extra_select, the fields of LOC_REPORT_ORDER and MAG_REPORT_ORDER will always occur in the output, regardless of whether there are any MAGs or Location Group(s) selected.

Additional Fields

•DT_WELL_SEGMENT is added to the Additional Fields drop-down list of Analytical Results II. The default value of SEGMENT_TYPE is SCREEN. The value can be modified over ST_CONFIG by populating a new SEGMENT_TYPE in STRING_VALUE of ST_CONFIG where CONFIG_SECTION=ar and CONFIG_KEY=SEGMENT_TYPE.

Read here for information on how to add tables and fields to the Additional Fields Parameter.

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