Collect - Field EDD Report

Report Name: Collect – Field EDD (example)

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

Dependencies: Licensed Collect Module and EDGE Schema

Software: EQuIS Collect, EQuIS Enterprise

Source: EQuIS Collect install

Language: English

Output Type: Excel1

Description: This report creates an EDD to be used in Collect to assist field crews in recording field data. The report creates an EDD with a list of locations to be used as a starting point and/or a task list. If the AQS Schema is applied to the database, the report output will include the AT_EQUIPMENT_LOCATION table fields AQS_POC, AQS_PARAMETER_CODE, AQS_METHOD, and G_VALUE.

Installation Instructions: This report requires a licensed EQuIS Collect module and the EDGE Schema applied to the database. For use, this report should be published to the database. Download the EQuIS Collect xcopy zip from the community center, unblock the *.zip file, and extract the EarthSoft.Reports.Library.161941.dll file to the EQuIS Professional installation folder. For default installations, this directory is C:\Program Files\EarthSoft\EQuIS. The report will be available for selection once it has been published.

Overview:

When used in conjunction with the EQuIS Sample Planning Module (SPM), data fields relating to water levels, activities (or inspections), sampling, and chain of custody (COC) handling can also be pre-populated, reducing the amount of time spent in the field entering these data. Pass information from the Collect Field EDD report to Collect Forms by using the Pre-Population feature.

Note: There is a presumption that there is only one sample plan code within a date range per facility, if no plan code is selected. The Collect Field EDD will generate with or without a sample plan code based on the date range.

Tables: DT_ACTION_LEVEL_LOOKUP, DT_ACTION_LEVEL_PARAMETER, DT_COORDINATE, DT_EQUIPMENT, DT_FACILITY, DT_FILE, DT_MEASURE_DATUM, DT_SPM_COMMITMENT, DT_SPM_CONTRACT, DT_SPM_PLAN, DT_SPM_PLANNED_ACTIVITY, DT_SPM_SCHEDULED_ACTIVITY, DT_SPM_PLANNED_SAMPLE, DT_SPM_SCHEDULED_SAMPLE, DT_SPM_PLANNED_RESULT, DT_SPM_SCHEDULED_RESULT, DT_SPM_SCHEDULED_TASK, DT_SPM_SCHEDULED_TASK, DT_TASK

Report Parameters

Plan Code	Select a plan (only plans with applicable tasks are shown).
Sort Samples By	Parameter for setting the sort order of location/sample values. Default is NULL, which results in sorting by SYS_SAMPLE_CODE.
SPM Task Filter	All parameters under the SPM Task Filter section only affect the SPM Task Code(s) parameter by filtering down the task codes available to be selected based on selections in these parameters. These parameters do not affect any parameters outside of the SPM Task Filter section.
Date Range
Start	Select a start date.
End	Select an end date.
Dynamic Adjustment	Parameter for offsetting the start date so that forms can be created ahead of the task start date. Integer value.
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(s)	Parameter for selecting task type(s).
Priority Ranking(s)	Parameter for selecting priority ranking(s).
Person Name(s)	Parameter for selecting person(s).
Company Code(s)	Parameter for selecting company code(s). Filters scheduled tasks codes returned in the SPM Task Code(s) parameter based on the selected Company Code(s) matching DT_SPM_SCHEDULED_TASK.COMPANY_CODE.
Project(s)	Parameter for selecting project(s).
SPM Task Code(s)	Parameter for selecting SPM task code(s).
Sampling Company Code(s)	Select one or more sampling company code. Filters scheduled task codes returned in the SPM Samples section of the report output based on the selected Sampling Company Code(s) matching DT_SPM_SCHEDULED_SAMPLE.SAMPLING_COMPANY_CODE.
Only Task Locations	Check box to only include locations from the selected tasks. Default is True.
Include SPM COCs for Selected Tasks	Check box to include SPM COCs for selected tasks. Default is False.
User(s)	Select one or more user. Users selected can be available as list in collect forms.
Include Subfacilities	Check box to include subfacilities related to the selected locations. Default is False.
Only Selected Locations	Check box to only include locations from the selected locations below. Default is False.
Task exist check	Check box to generate report output only if tasks exist. Task table is not checked when parameter is set to false. Default is False.
Reference Locations
Individual(s)	Parameter for selecting individual location(s).
Group(s)	Parameter for selecting location group(s).
Coord_Type_Code	Parameter for selecting the COORD_TYPE_CODE.
Identifier	Parameter for selecting the coordinate identifier.
Status Flag	Selection parameter for STATUS_FLAG.
Exclude Locations Without Coordinates	Parameter for excluding locations that do not have COORD_TYPE_CODE configured. Default is False. NOTE: If the parameter is set to True and COORD_TYPE_CODE is populated, the record will be included in the report output whether or not values for X_COORD and/or Y_COORD are populated.
Task Code(s)	Option to prepopulate TASK_CODE from DT_TASK rather than use SPM.
Well Segments
First Well Segment
Select Segment Type for Top of Screen	Parameter for selecting the segment type for the top of the screen for the first well segment.
Select Top of Screen Material Type	Parameter for selecting the segment type for the top of the screen material type for the first well segment.
Second Well Segment
Select 2nd Well Segment Type	Parameter for selecting the segment type for the top of the screen for the second well segment.
Select 2nd Well Segment Material Type	Parameter for selecting the segment type for the top of the screen material type for the second well segment.
Reference Elevation	Parameter to select the reference elevation source: HISTORICAL_REFERENCE_ELEV, MEASURE_DATUM, or TOP_CASING_ELEV.
Historical
Water Levels
Depth Unit	Select the unit in which to display the water level depth. Default is DT_FACILITY.DEPTH_UNIT.
Years Back	Select how many years back to pull data. Value must be an integer.
Historical range outlier	The percent of outlier data that will be excluded from the minimum and maximum range used to check new depth to water values.
Include Most Recent Field Measurements	Check box to include most recent field measurements data in the output. Default is False.
Select Most Recent Data Type	Select one or more type of most recent field measurements data to include in the most_recent_measurements worksheet of the output.
Sample Type Code	Select sample type code for most recent field measurements.
Analytic Method	Select the analytical method.
Sample Remark Type	Select the source for sample remark.
Field Parameter(s)	Please select field parameter(s) for most recent measurements. If nothing is selected, then all parameters will be returned.
Action Levels
Aggregate Data	When enabled, action level data will be aggregated by COMPARISON_ID and PARAM_CODE in the DT_ACTION_LEVEL_LOOKUP table. Enabled by default.
Param Codes	Parameter selection for PARAM_CODES.
Action Level Codes	Action level code parameter selection.
Select list of ad hoc analysis	Parameter selection for ad hoc analysis.
Equipment Types	Parameter selection for equipment types.
Equipment Parameter Code(s)	Select one or more parameter code(s). Most recent data will be downloaded to Collect for selected equipment type(s) and equipment parameter code(s).
Select remap code(s)	Parameter selection for remap codes.
Scribe
Starting CLP Number	Starting CLP Number (without chars: I O U V).
Location Blank Rows	Location Blank Rows.
Output File Type	Parameter for report output type: XLS or XLSX.
Lab(s)	Select one or more lab(s) to include in the output.
Reference Table Export	Select one or more tables to export all content from the selected reference table(s) into JSON values to be used elsewhere in the Collect data forms.

SPM Samples tab

Last Custom Data:

SPM_Samples worksheet in the Excel output file has custom field data of the latest sample in columns last_custom_1, last_custom_2, last_custom_3, last_custom_4, and last_custom_5. These columns are mapped from the CUSTOM_FIELD_1, CUSTOM_FIELD_2, CUSTOM_FIELD_3, CUSTOM_FIELD_4 and CUSTOM_FIELD_5 fields of the DT_SAMPLE table, respectively.

For each unique set of FACILITY_ID, SYS_LOC_CODE and MATRIX_CODE, the latest sample is found among samples whose DT_SAMPLE.TASK_CODE matches one of the selected SPM task code(s). By default, the report identifies matching sample records from a list of all SCHEDULED_TASK_CODEs within the same PLANNED_TASK_CODE. However, it is possible to configure the report to identify matching sample records from a list of all SCHEDULED_TASK_CODEs across different PLANNED_TASK_CODEs by grouping the planned tasks in RT_GROUP_MEMBER.

The logic used by the report to find the last_sample_date value is outlined below:

1.SPM Task Code(s) (that is, SCHEDULED_TASK_CODE) is selected in the report parameter.

2.The SCHEDULED_TASK_CODE is used to identify the parent PLANNED_TASK_CODE in DT_SPM_SCHEDULED_TASK.

3.The parent PLANNED_TASK_CODE is used to identify all other PLANNED_TASK_CODE(s) grouped together in RT_GROUP_MEMBER (see Note below for more information).

4.The group of PLANNED_TASK_CODEs is used to identify all related SCHEDULED_TASK_CODEs from DT_SPM_SCHEDULED_TASK.

5.The list of all related SCHEDULED_TASK_CODEs is used to find all samples with matching DT_SAMPLE.TASK_CODE values.

6.The sample with the most recent DT_SAMPLE.SAMPLE_DATE is identified as the "latest sample", for each unique set of FACILITY_ID, SYS_LOC_CODE, and MATRIX_CODE. This "latest sample" record(s) is used to populate the last_sample_date and related last_custom_x fields in the SPM_Samples report section.

Note: To group different PLANNED_TASK_CODEs in RT_GROUP_MEMBER, an administrator user should follow the steps below:

1.In ST_GROUP_TYPE, create a record with GROUP_TYPE = 'spm_task' (if it does not already exist).

2.In RT_GROUP, create a record with GROUP_CODE = [any value to identify the group of planned tasks] and GROUP_TYPE = 'spm_task'.

3.In RT_GROUP_MEMBER, add a record for each PLANNED_TASK_CODE to be associated, where:

•GROUP_CODE = RT_GROUP.GROUP_CODE

•MEMBER_CODE = DT_SPM_SCHEDULED_TASK.PLANNED_TASK_CODE (for each planned task code the user intends to associate in the group)

•MEMBER_TYPE = 'spm_task'

"Only Task Locations" and "Only Selected Locations":

When "Only Task Locations" and "Only Selected Locations" are both True, the only locations returned in Location_v1 are those that are in both the SPM task and the individual(s) or location group(s) selected. An alternate table called "locationOnly" will still list all locations selected under Reference Locations.

Most Recent Measurements:

The Most_Recent_Measurements worksheet in the Excel output file contains the latest values for flow, water level, field results, and equipment parameters.

•Flow – Latest value of DT_FLOW.INSTANT_FLOW and DT_FLOW.CUMULATIVE_FLOW for each unique pair of FACILITY_ID and SYS_LOC_CODE in the “activities_all” worksheet with ACTIVITY_TYPE = "flow".

•Water Level – For each unique pair of FACILITY_ID and SYS_LOC_CODE in the "activities_all" worksheet of the report output where ACTIVITY_TYPE = "flow", the latest value from DT_WATER_LEVE_WATER_LEVEL_DEPTH will be populated in the 'Most Recent Measurement" worksheet where parameter_code = water_level_depth.

•Field Results – Most recent value of selected Historical/Field Parameter(s) from the DT_RESULT table for the latest 3 samples. Latest samples are found for each unique pair of FACILITY_ID, SYS_LOC_CODE and MATRIX_CODE in the "SPM_Samples" worksheet. Selected Analytic Method will be used to filter DT_TEST records.

Notes:

•The output of Field Results is dependent on samples scheduled in SPM coming from DT_SPM_SCHEDULED_SAMPLE, which populates the SPM Samples tab of the Collect - Field EDD. DT_SAMPLE is then filtered to find samples that match the samples in SPM Sample based on SAMPLE_TYPE, SYS_LOC_CODE, and MATRIX_CODE. The associated DT_RESULTS are then returned to pull the most recent result.

•The output of Field Results is limited to instances where DT_RESULT.REPORTABLE_RESULT is explicitly set to 'Yes'. Other values, such as 'Y', are not recognized by the report logic and therefore excluded from the report output.

•Equipment Parameter(s) – Most recent value of selected Equipment Parameter Code(s) and Equipment Code(s) from the DT_EQUIPMENT_PARAMETER table for each unique pair of FACILITY_ID and SYS_LOC_CODE in the "SPM_Samples" worksheet. Equipment Code(s) are selected from the DT_EQUIPMENT table using selected equipment type(s) and whose STATUS_FLAG = ‘A’.

Note: If FACILITY_ID is empty in the DT_EQUIPMENT_PARAMETER table, then FACILITY_ID of facility to which the user is logged in will be displayed.

How Historical Water Level Range is Calculated in the WATER_LEVEL Table:

The Collect - Field EDD report uses statistics to calculate historical ranges that exclued outlier data, using the following formula:

The DT_WATER_LEVEL.WATER_LEVEL_DEPTH must fall between (Mean - X*STDEVP) and (Mean + X*STDEVP), where X is the value determined by the Historical Range Outlier parameter selected by the user. The corresponded X value to Historical Range Outlier selection is displayed in the chart below:

% Under Normal Distribution	Report Parameter	X Value
100%	0%	7
99%	1%	2.275829
95%	5%	1.959964
90%	10%	1.644854
80%	20%	1.281552
70%	30%	1
50%	50%	.067449

Note: For data stored as mixed units, the data will be checked and converted into a single unit set by the Depth Unit report parameter before any calculations are made. By default, the report will use DT_FACILITY.DEPTH_UNIT for the chosen facility. For unit conversion to occur, the corresponding records in the RT_UNIT_CONVERSION_FACTOR table must be present and accurate.

Example:

The Historical Range Outlier is set to 5% and there are 15 water level readings recorded for a location.

Number of Readings	Depth to Water
1	44.2
2	48
3	48.2
4	49.2
5	49.2
6	49.3
7	49.3
8	49.3
9	49.3
10	49.8
11	50.1
12	50.2
13	50.5
14	51
15	55

Assume that any data that do not fall under 95% of the area of the normal distribution are to be excluded. The calculation follows:

Mean = 49.5

X*STDEVP = 4.136

Historical Range = between 45.4 and 53.6

The values 44.2 and 55 fall outside the range and will be excluded from the report.

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

Collect – Field EDD Report

Collect – Field EDD Report

SPM Samples tab

Example: