<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports > Action Levels > 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

▪Basic

▪Location-Specific

▪Location-Specific Seasonal

▪Seasonal

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

 

 

 

 

Action Level Parameter Configuration

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.

 

Water Level Action Level Configuration

Action level data can be prepared in DT_ACTION_LEVEL and either DT_ACTION_LEVEL_PARAMETER or DT_ACTION_LEVEL_LOOKUP, depending on purpose.

 

 

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),

2.location-specific,

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.  

 

Available Water Level Parameters (PARAM_CODEs)

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.

 

Types of Action Levels and Overlapping Rules

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 (not location-specific and not seasonal) Action Levels  

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

 

B. Location-Specific Action Levels  

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

 

C. Location-Specific Seasonal Action Levels

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

 

D. Seasonal (general or non-location-specific) Action Levels

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).

 

Hierarchy of Water Level Action Levels

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.

 

Chart Parameter Configuration

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.