SPM - Completeness Report

Report Name: SPM - Completeness Report - Detailed (example)

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

Dependencies: Licensed SPM and SPM Schema

Software: SPM

Source: SPM install

Language: English

Output Type: Excel1

Description: The SPM - Completeness Report - Detailed report gives a comparison between the activities, samples and analytical results that had been scheduled to occur and the data that has been received in the EQuIS data tables. The report uses color coding to show complete and incomplete tasks at a glance.

Note: Tasks that have been manually marked as Complete (i.e., through editing a scheduled task) will not show details in this report. Only Summary information will be exported.

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 file added to the local '.../Earthsoft/EQuIS/' folder.

Overview:

This report does a comparison of the following tables and fields for the following statuses, divided by section:

Field Sample Section: DT_SPM_SCHEDULED_SAMPLE and DT_SAMPLE: TASK_CODE, SYS_LOC_CODE, SAMPLE_TYPE, MATRIX (if not null), and DEPTH (if not null)

▪Completed: DT_SPM_SCHEDULED_SAMPLE needs a matching record in DT_SAMPLE.

▪Incomplete: No matching record for DT_SPM_SCHEDULED_SAMPLE in DT_SAMPLE.

▪SRN Issues: If using the SRN Manager and SRN issues have been received, added in DT_SRN_ISSUE. See SPM Sample Receipt Notification for more information.

▪Not Taken: The sample type in DT_SAMPLE is changed to NST (no sample taken). See No Sample Taken for more information.

Analytical Results Section: The comparison may use the SYS_SAMPLE_CODE if 'Check by SPM sys_sample_code'= True. If DT_SAMPLE.SYS_SAMPLE_CODE do not match DT_SPM_SCHEDULED_SAMPLE.SYS_SAMPLE_CODE because the sample codes are often not determined until the date and time the sample is actually collected, then set that parameter to False. If false, the comparison is made by comparing the following fields from DT_SPM_SCHEDULED_SAMPLE and DT_SAMPLE: TASK_CODE, SYS_LOC_CODE (optional based on check box in Parameters), SAMPLE_TYPE, MATRIX (if not null), and DEPTH (if not null); and the following fields from DT_SPM_SCHEDULED_RESULTS and DT_TEST/DT_RESULT: CAS_RN, FRACTION, METHOD (optional based on check box in Parameters), and LAB (optional based on check box in Parameters). It is also possible for samples to matched based on DT_SAMPLE.SPM_SAMPLE_ID, which can be populated using Collect.

▪Completed: DT_SPM_SCHEDULED_RESULT needs matching records in DT_TEST/DT_RESULT.

▪Incomplete: No matching record for DT_SPM_SCHEDULED_RESULT in DT_TEST/DT_RESULT.

▪SRN Issues: If using the SRN Manager and SRN issues have been received, added in DT_SRN_ISSUE.

▪Not Taken: The sample type in DT_SAMPLE is changed to NST (no sample taken).

▪Validated: DT_RESULT.VALIDATED_YN is set to 'Y'.

Activities Section: DT_SPM_SCHEDULED_ACTIVITY with the corresponding table depending on the activity. Such as DT_FLOW, DT_WATER_LEVEL, and DT_PURGE.

▪Completed: Matching records for TASK_CODE in DT_SPM_SCHEDULED_ACTIVITY and the related table, such as DT_WATER_LEVEL, DT_PURGE, DT_FLOW, and so on.

▪Incomplete: No matching records in DT_SPM_SCHEDULED_ACTIVITY and the corresponding table.

▪SRN Issues: If using the SRN Manager and SRN issues have been received, added in DT_SRN_ISSUE.

▪Not Taken: The sample type in DT_SAMPLE is changed to NST (no sample taken)

Note: The "Unplanned" status will be assigned if a sample or activity is added to the database referencing a task code that does not have the sample or activity planned in SPM.

Source Tables DT_FACILITY, DT_FIELD_SAMPLE, DT_FLOW, DT_LOCATION, DT_LOCATION_PARAMETER, DT_PURGE, DT_RESULT, DT_SAMPLE, DT_SOIL_GAS_SURVEY, DT_SPM_CONTRACT, DT_SPM_CONTRACT_CONTAINER, DT_SPM_CONTRACT_MAG, DT_SPM_CONTRACT_METHOD, DT_SPM_PLAN, DT_SPM_SCHEDULED_ACTIVITY, DT_SPM_SCHEDULED_CONTAINER, DT_SPM_SCHEDULED_RESULT, DT_SPM_SCHEDULED_SAMPLE, DT_SPM_SCHEDULED_TASK, DT_SPM_SCHEDULED_TASK, DT_TASK, DT_TEST, DT_WATER_LEVEL, RT_ANALYTE, RT_COMPANY, RT_GROUP, RT_GROUP_MEMBER, RT_LOOKUP, RT_MTH_ANL_GROUP, RT_MTH_ANL_GROUP, RT_SPM_ACTIVITY

Report Parameters*

Plan Code	Parameter to select the sample plan code. Only plans with applicable tasks are shown.
Sample
Task Filter
Date Range
Start	Select a start date.
End	Select an end date.
Department(s)	Parameter for selecting department(s).
Monitoring Program	Parameter for selecting monitoring program.
Monitoring Type	Parameter for selecting monitoring type.
Authority	Parameter for selecting authority.
Authority Type	Parameter for selecting authority type.
Permit Number	Parameter for selecting permit number.
Task Type	Parameter for selecting task type.
Priority Ranking	Parameter for selecting priority ranking.
Person Name(s)	Parameter for selecting person(s).
Company Code(s)	Parameter for selecting company code(s).
Project(s)	Parameter for selecting project(s).
SPM Task Code(s)	Parameter for selecting SPM task code(s).
Type(s)	Select one or more sample types.
QC Type(s)	See Plan QC Samples in SPM for additional information.
Matrix(es)	Select one or more sample matrix.
Delivery Group(s)	Parameter to select one or more sample delviery goups (field or lab).
Result Type(s)	Parameter to select result type(s).
Test Type(s)	Parameter to select test type(s).
Non-detect Symbol	Input parameter for non-detect symbol. Default: <.
Reporting Limit	Select which reporting limit you desire to use (reporting_detection_limit, method_deteciton_limit, etc.)
Location
Individual(s)	Parameter for selecting individual locations.
Group(s)	Parameter for selecting location groups.
Check by Method	Check box for enabling check by method. Default: False.
Check by Lab	Check box for enabling check by lab. Default: False.
Check by Matrix	Check box for enabling check by matrix. Default: True.
Check by Fraction	Check box for enabling check by fraction. Default: True.
Check by Sample depth	Check box for enabling check by Sample depth. Default: True.
Check by SPM sys_sample_code	Check box for enabling check by SPM SYS_SAMPLE_CODE. Default: False.
Add Task Client	Check box to add task client. Default: False.
A Simple Lab Results Summary Report	Check box to create a simple lab results summary report with C and I status. Default: False. (If selected, Include Unplanned Data will be ignored)
Include Unplanned Data	Check box to include unplanned data. Default: False.
* This report includes the following hidden parameter(s) that could affect the output of the report: •Match on SYS_LOC_CODE – The default for this parameter is True. See Plan QC Samples in SPM for additional information. If listed, the Additional Fields parameter may be customized as explained in How To Customize the Additional Fields Parameter. Consult an EQuIS administrator if hidden parameters should be visible or use different default values in a published report (a report that allows saving of user reports). Hidden parameters cannot be viewed or changed in unpublished reports outside of a report parameter file.

1Excel report output requires a valid license of Microsoft Excel to view the output.