<< Click to Display Table of Contents >> Navigation: EQuIS Library > Reports > EarthSoft Standard Reports > Action Level Reports > Water Level - Action Level Exceedance |
Report Name: Water Level - Action Level Exceedance (example)
File Name: EarthSoft.Reports.Library.52702.dll
Dependencies: published Water Levels II Report
Software: EQuIS Professional and EQuIS Enterprise
Source: EQuIS Professional install
Language: English
Output Type: Excel1 spreadsheet (chart[s], data table[s])
Description: The Water Level - Action Level Exceedance (WLALE) Report generates:
1.an Excel export of the results of the Water Level II Report (WLII),
2.action level information if action level parameters are selected, and/or
3.charts if the "Plot Water Level" parameter is selected.
The report compares a numerical field of WLII, e.g. exact_elev etc., against the applicable action levels, when action level(s) input is included.
Installation Instructions: This report assembly is part of the EQuIS Professional installation. No additional installation steps are necessary. For EQuIS Enterprise use, copy the Water Levels II Report assembly (EarthSoft.Reports.Library.64102.dll) from the EQuIS Professional installation directory into the Enterprise bin folder.
Overview:
The Water Level - Action Level Exceedance Report accommodates various action level configurations, as described below. Users may implement any one or each of these. Since the data structure is flexible and EQuIS does not enforce any single approach, a careful consideration of naming conventions and related best practices is highly recommended.
•Action Level Parameter Configuration
•Water Level Action Level Configuration
oAvailable Water Level Parameters (PARAM_CODEs)
oTypes of Action Levels and Overlapping Rules
oHierarchy of Water Level Action Levels
•Chart Parameter Configuration
Source Tables DT_WATER_LEVEL, DT_WELL, RT_ANALYTE_DETAIL, DT_LOCATION, RT_GROUP, RT_GROUP_MEMBER, DT_FACILITY, DT_MEASURE_DATUM, RT_UNIT, RT_UNIT_CONVERSION_FACTOR, VW_LOCATION, DT_ACTION_LEVEL, DT_ACTION_LEVEL_PARAMETER, DT_ACTION_LEVEL_LOOKUP. Additional Fields tables: DT_WELL, DT_DRILL_ACTIVITY, DT_FACILITY, DT_LOCATION, DT_MEASURE_DATUM, DT_TASK, DT_WATER_LEVEL, VW_LOCATION, DT_WELL, DT_WELL_SEGMENT
Report Parameters |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
* This report contains hidden fields for: *ebatch, *Water Level Approval Code(s), *Water Level Default Significant Figure: 6 (default), *Water Level Default specific gravity: 0.8 (default), *Water Level Extra Selects, *Water Level User-estimated product density.
** This table shows partially expanded parameters. Refer to related report articles for complete input parameter lists.
*** The Additional Fields parameter may be customized as explained in the help article How To Customize Additional Fields Parameter.
|
Report Output |
||
---|---|---|
FACILITY_ID FACILITY_CODE SYS_LOC_CODE LOC_NAME LOC_GROUP LOC_REPORT_ORDER MEASUREMENT_DATE REFERENCE_ELEV WATER_LEVEL EXACT_ELEV MEASURED_DEPTH_OF_WELL DEPTH_UNIT |
BATCH_NUMBER TECHNICIAN DRY_INDICATOR_YN MEASUREMENT_METHOD DIP_OR_ELEVATION REMARK EQUIPMENT_CODE LNAPL_CAS_RN LNAPL_DEPTH LNAPL_THICKNESS LNAPL_DENSITY |
WATER_DEPTH DNAPL_CAS_RN DNAPL_DEPTH DNAPL_THICKNESS TASK_CODE APPROVAL_CODE X_COORD Y_COORD LONGITUDE LATITUDE
|
By default, the Action Level > Action Level(s) parameter only looks at action level codes that are assigned to the active facility or facility group. To change this:
1.Publish the EarthSoft.Reports.Library.52702.dll report to the database, if it has not been published already in your current build of EQuIS.
2.Copy the REPORT_ID for the ‘Water Level – Action Level Exceedance’ report from the ST_REPORT table.
3.In the ST_REPORT_PARAMETER table, turn on the filter row and paste in the REPORT_ID.
4.Change the DATA_SOURCE for the entry where PARAM_NAME = ‘@action_level_codes’ as required. The following example DATA_SOURCE expands the list to consider action levels not assigned to a facility (i.e. where the FACILITY_ID is null):
select action_level_code from dt_action_level where (facility_id is null or facility_id=@facility_id or facility_id in (select facility_id from equis.facility_group_members(@facility_id)) or @facility_id in (select facility_id from equis.facility_group_members(facility_id))) union select '' order by action_level_code
The Action Level > Comparison > Operator parameter can be changed from the default of “>=”, which flags values that exceed or are equal to the specified action/warning levels, to flag values below (or below and equal to) the action/warning levels, instead – effectively, turning an action level configured as a maximum into a minimum.
Action level data can be prepared in DT_ACTION_LEVEL and either DT_ACTION_LEVEL_PARAMETER or DT_ACTION_LEVEL_LOOKUP, depending on purpose.
•Use or create an action level where the water level parameter will be the only param_code – water level action levels cannot be combined with other action levels. •The COMPARISON_OPERATOR and EUID of DT_ACTION_LEVEL_LOOKUP are required by the schema to be populated when DT_ACTION_LEVEL_LOOKUP is edited. The two fields are not used by this report. Therefore, in preparing action level data or editing DT_ACTION_LEVEL_LOOKUP for the report, populate: oCOMPARISON_OPERATOR with anything but null, and oEUID with distinct integers (e.g. ‘1’). The EUID column will be auto-corrected when the table is saved. |
After a water level variable, e.g. exact_elev, is defined, one or more action level codes for the water level at a location can be prepared. If an action level code is prepared in DT_ACTION_LEVEL + DT_ACTION_LEVEL_PARAMETER, a single seasonal action level can be created for all locations.
A basic action level code can be prepared in either DT_ACTION_LEVEL plus DT_ACTION_LEVEL_PARAMETER or DT_ACTION_LEVEL_LOOKUP tables but should not be in both of the latter tables.
If an action level code is prepared in DT_ACTION_LEVEL_LOOKUP, it can have various types of action levels, where types further down in the list override types further up the list:
1.general (applied to all locations),
3.location-seasonal, and
4.seasonal (applied to all locations).
Also, one or more seasonal action levels of the action level code can be created.
The water level parameters that are available for comparison in an action level are based on available fields of the Water Levels II report, such as:
•water_level
•exact_elev
•water_depth
•water_level_depth
Other fields of the DT_WATER_LEVEL table can be included in the report and in the action level by means of the Additional Fields and Extra Selects parameters. The following example shows how the CORRECTED_DEPTH field from DT_WATER_LEVEL could be added by an EQuIS Professional user with administrative rights. Refer to the Customize Report Parameters page and the examples provided there for further information on viewing and configuring report parameters.
1.Publish the EarthSoft.Reports.Library.52702.dll report to the database, if it has not been published already in your current build of EQuIS.
2.Copy the REPORT_ID for the ‘Water Level – Action Level Exceedance’ report from the ST_REPORT table.
3.In the ST_REPORT_PARAMETER table, turn on the filter row and paste in the REPORT_ID.
4.For param_name = ‘@extra_fields’ and ‘@extra_selects’, set Visibility to ‘Y’ and save.
5.Open the report and set the parameters:
a.Under Water Level > Additional Fields, select “DT_WATER_LEVEL.CORRECTED_DEPTH”
b.Under Water Level > Extra Selects, add the following line:
DT_WATER_LEVEL_CORRECTED_DEPTH as corrected_depth
(iIf there are multiple extra selects needed, they can be separated by a comma)
This adds the column for the corrected depth parameter to the report (as ‘DT_WATER_LEVEL_CORRECTED_DEPTH’), then creates a new column with the same data called ‘corrected_depth’ in order to match the DT_WATER_LEVEL table and meet the PARAM_CODE character limits.
Start by preparing an ACTION_LEVEL_CODE either in DT_ACTION_LEVEL or via the Action Level GUI. This ACTION_LEVEL_CODE will be used in populating the water level action levels in the DT_ACTION_LEVEL_LOOKUP or DT_ACTION_LEVEL_PARAMETER table, as appropriate. Other action levels cannot be included in water level action level codes!
A basic action level code can be prepared in DT_ACTION_LEVEL with either, but NOT both, ACTION_LEVEL_PARAMETER or DT_ACTION_LEVEL_LOOKUP tables. If, accidentally, an action level code is present in both DT_ACTION_LEVEL with DT_ACTION_LEVEL_PARAMETER *and* DT_ACTION_LEVEL_LOOKUP tables but the data do not match, the report gives an information message and does not run.
Preparing basic action levels in DT_ACTION_LEVEL_PARAMETER:
Required: param_code = exact_elev or other numeric fields of Water Levels II results (e.g. water_level)
action_level_code, e.g. "WaterLevelAL"
action_level, e.g. "100"
unit, e.g. "m"
Optional: warning_level
Or preparing basic action levels in DT_ACTION_LEVEL_LOOKUP:
Required: param_code = exact_elev or other numeric fields of Water Levels II results (e.g. water_level)
lookup_source = action_level_code, e.g. "WaterLevelLookupAL"
comparison_id = "ANY"
lookup_method="non-seasonal"
action_level
action_level_unit
Others required but not used by this report: comparison_operator and euid (see Important Notes)
Optional: warning_level
Preparing DT_ACTION_LEVEL_LOOKUP:
Required: param_code = exact_elev or other numeric fields of Water Levels II results (e.g. water_level)
lookup_source = action_level_code, e.g. "WaterLevelLookupAL"
comparison_id = sys_loc_code
lookup_method="any string" and not used by this scenario
action_level
action_level_unit
Others required but not used by this report: comparison_operator and euid (see Important Notes)
Optional: warning_level
Preparing DT_ACTION_LEVEL_LOOKUP:
Required: param_code = exact_elev or other numeric fields of Water Levels II results (e.g. water_level)
lookup_source = action_level_code, e.g. "WaterLevelLookupAL"
comparison_id = sys_loc_code
lookup_method indicates how action levels change over time and may be any of the following date formats. The date formats are case sensitive. The hyphen is used as a separator; do not use an underscore or else certain report functionality may be lost.
1.yyyy
2.MM
3.MM-MM
4.MMdd-MMdd
5.yyyyMM
6.yyyyMMdd-yyyyMMdd
and its corresponding value in comparison_value = (4 digits) year, (2 digits) month, ... or (less than 19 digits) date range, e.g. 2010, 02, 20100101-20100330
etc., which must match the format defined in lookup_method.
action_level
action_level_unit
Others required but not used by this report: comparison_operator and euid (see Important Notes)
Optional: warning_level
A seasonal action level code can be prepared in DT_ACTION_LEVEL and either DT_ACTION_LEVEL_PARAMETER or DT_ACTION_LEVEL_LOOKUP tables but should not be in both tables. If, accidentally, an action level code is present in both the DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP tables, but its seasonal data do not match, the report returns a message and does not run.
Preparing DT_ACTION_LEVEL and DT_ACTION_LEVEL_PARAMETER:
Required: param_code = exact_elev or other numeric fields of Water Levels II results (e.g. water_level)
action_level_code, e.g. "WaterLevelLookupAL"
action_level
Unit
DT_ACTION_LEVEL.season_start_month, or season_start_day. Note that if only season_start_month and no season_end_month are populated, the report assumes season_end_month is the end day of the season_start_month.
Optional: warning_level, DT_ACTION_LEVEL.season_end_month/season_end_day
re
Or preparing DT_ACTION_LEVEL_LOOKUP:
Required: the parameters are the ones in C above except:
lookup_method =MM-MM or MMdd-MMdd
comparison_id = “ANY”
Others required but not used by this report: comparison_operator and euid (see Important Notes)
Note that WLALE processes DT_ACTION_LEVEL_LOOKUP data without using the advanced action level API (i.e. no reliance on the object EarthSoft.ActionLevels.Library.105851.dll).
When an action level code includes different types of action levels, the following rules apply.
1.For non-seasonal action levels, location-specific overrides non-location-specific action levels. In other words, when both location-specific and non-location-specific action levels are provided for a location, the report ignores the latter.
2.Location-season-specific overrides season-specific action levels. In other words, when both location-specific + season-specific and seasonal only action levels are provided for a location, the report ignores the latter.
3.Seasonal overrides non-seasonal action levels, for the seasonal duration specified. In other words, when both seasonal and non-seasonal action levels are provided, the report will apply the seasonal action level for the specified time period and ignore the non-seasonal action level for the same time period.
If an action level is overridden, its warning level is also ignored.
If, accidentally, an action level code is present in DT_ACTION_LEVEL and both DT_ACTION_LEVEL_PARAMETER and DT_ACTION_LEVEL_LOOKUP tables but its seasonal data do not match, the report outputs a message and does not run.
For chart layout options, select Plot/Plot group type of "one worksheet with separate charts for each location " to access the “Plot\The number of plots per print page:” and the “Chart Layout/Layout” input parameters. Layout1 includes a legend at top and no axis titles.
The Plot/Series chart type options apply to the water level series. The action levels are plotted as red line series.
The Logo file name input is for including a logo image in the upper-left header of each worksheet of the Excel export. If the input logo file (default demo name - artp_logo.jpg) is uploaded to DT_FILE, the logo will be in the Excel output file.
1Excel report output requires a valid license of Microsoft Excel to view the output.
Copyright © 2023 EarthSoft, Inc • Modified: 25 Nov 2021