The Analytical Results II Report features an API for applying standard or custom logic (transformations) to report results:
The Transformation(s) input parameter, hidden by default, provides for selection of one or more transformations. This parameter will be available if the build of the Analytical Results II published to the database is 7.0.0.19144 or later. To enable the Transformation(s) parameter, filter for the Analytical Results II REPORT_ID (as found in ST_REPORT) and the PARAM_NAME column for '@postProcessor' in ST_REPORT_PARAMETER. Set VISIBILITY to 'Y' for this record.
Standard Transformations
Standard transformations for MAG Sum and TEQ Sum, detailed below, are included with the Analytical Results II Report.
Notes:
•The sum transformations match analytic results with the analytic method, matrix, and fraction specified in RT_MTH_ANL_GROUP_MEMBER; results that do not match are excluded from the sum.
•Some samples may have more than one result for a given analyte due to different analytic methods being run on the same sample or due to different fractions being tested for the same sample. If the ANALYTIC_METHOD and FRACTION columns in RT_MTH_ANL_GROUP_MEMBER are both NULL for that analyte, then the first result listed in the report output table will be used in the sum.
•Whichever results are used in the sum for a given analyte can be controlled by setting one or more of the following values in the RT_MTH_ANL_GROUP_MEMBER table:
oANALYTIC METHOD:
▪If the ANALYTIC_METHOD column is not NULL,
•then only results with a matching DT_TEST.ANALYTIC_METHOD are included in the sum.
oFRACTION:
▪If the TOTAL_OR_DISSOLVED column is not NULL,
•then only results with a matching DT_TEST.FRACTION are included in the sum.
•(Note that the RT_MTH_ANL_GROUP_MEMBER.TOTAL_OR_DISSOLVED column is displayed under the FRACTION column header in the Analyte Group form.)
oMATRIX:
▪If the MATRIX column is not NULL,
•then only results with a matching DT_SAMPLE.MATRIX_CODE are included in the sum.
MAG Sum Transformation
For each sample in the analytical results, a new row is added to the Analytical Results II output for the Method Analyte Group (MAG) specified. Basic values are copied to the new row, with new data populated as follows.
1.REPORT RESULT_TEXT and REPORT_RESULT_VALUE = the sum of the results of the members of the Method Analyte Group (MAG). REPORT RESULT_TEXT includes comparison operator where applicable.
2.CAS_RN and CHEMICAL NAME = method analyte group for the sum calculation, as defined at DT_ACTION_LEVEL_LOOKUP.PARAM_CODE.
3.DETECT_FLAG = Y if any sample results within that MAG include detections; N if all results are non-detects.
To make the MAG Sum transformation option available within Analytical Results II, follow these steps:
1.Create a Method Analyte Group or open the Groups Form to find an existing MAG.
2.Copy the MAG Code from within the Groups Form.
3.Populate DT_ACTION_LEVEL_LOOKUP. The columns below are required unless otherwise noted.
oEUID: populate with a distinct integer (e.g. '1'). The EUID column will be auto-corrected when the table is saved.
olookup_source (25 char): EQuIS_AR_II
olookup_code (40 char): optional, can use to create different categories for each source.
olookup_method (255 char): MAG_SUM
oparam_code (20 char): the method analyte group code for the sum calculation from Step 2.
ocomparison_operator (10 char): This must be ND_X for the Non-Detect Multiplier to be activated.
▪If not using the Non-Detect Multiplier, enter NONE or NA or any other value since this column may not be NULL.
ocomparison_value (19 char): optional; if ND_X is populated for the comparison_operator field, this value can be filled in for a multiplier to be applied to detection limits for non-detect results prior to adding them to the MAG_SUM. For a non-detect multiplier of X:
▪If X ≤ 0, then 0 is used.
▪If 0 < X < 1 (between 0 and 1), then the value entered (X) is used.
▪If X ≥ 1, then 1 is used.
▪If no value is entered in the COMPARISON_VALUE field, the ND_X multiplier defaults to 1.
oaction_level_unit (15 char): The output unit for the sum calculation.
4.Save.
5.If the Transformation(s) report parameter is not already visible within Analytical Results II, have an EQuIS administrator set ST_REPORT_PARAMETER.VISIBILITY = Y for the @postprocessor entry, as shown here.
DT_ACTION_LEVEL_LOOKUP output showing transformations such as a MAG_SUM for a "BTEX" MAG is available here .
TEQ Sum Transformation
For each sample in the analytical results, while performing the MAG Sum, each member result is multiplied by a Toxic Equivalency Factor (TEF) before adding the result to the sum. The original member result is replaced with the Toxic Equivalency Quotient (TEQ) Sum.
1.REPORT RESULT_TEXT is replaced with “TEQ” for those results where the multiplier is applied for the sum.
2.REPORT_RESULT_VALUE is replaced with result value after multiplied by TEF.
3.REPORT_RESULT_UNIT is replaced with the action_level_unit.
Basic values are copied to the new row, with new data populated as follows.
1.REPORT RESULT_TEXT and REPORT_RESULT_VALUE = the sum of the results of the members of the Method Analyte Group (MAG). REPORT RESULT_TEXT includes comparison operator where applicable.
2.CAS_RN and CHEMICAL NAME = method analyte group for the sum calculation as defined at DT_ACTION_LEVEL_LOOKUP.PARAM_CODE.
3.DETECT_FLAG = Y if sample results include any detect; N if all results are non-detects.
To make the TEQ Sum transformation option available within Analytical Results II, follow these steps:
1.Create a Method Analyte Group or open the Groups Form to find an existing MAG.
2.Copy the MAG Code from within the Groups Form.
3.Populate DT_ACTION_LEVEL_LOOKUP. The columns below are required unless otherwise noted.
oEUID: populate with a distinct integer (e.g. ‘1’). The EUID column will be auto-corrected when the table is saved.
olookup_source (25 char): EQuIS_AR_II
olookup_code (40 char): optional; can use to create different categories for each source.
olookup_method (255 char): TEQ_SUM
oparam_code (20 char): the method analyte group code for the sum calculation from Step 2.
ocomparison_id (20 char): the CAS_RN of the analyte to multiply by the TEF.
ocomparison_operator (10 char): TEF
ocomparison_value (19 char): the TEF value to multiply by the result value for the CAS_RN (designated in comparison_id), before adding to the TEQ_SUM, e.g. 1 or 0.
oaction_level_unit (15 char): the output unit for the sum calculation.
DT_ACTION_LEVEL_LOOKUP output showing transformations such as a TEQ_SUM for a "PAHs" MAG is available here .
Custom Transformations
To use a custom transformation, the DLL with the transformation code must be placed as follows:
a)in the EQuIS Professional installation folder (typically C:\ProgramFiles\EarthSoft\EQuIS) for use with EQuIS Professional, and
b)in the Enterprise bin folder to use in Enterprise.
The custom transformations may then be configured as follows.
Example
The following steps will create a transformation for Analytical Results II that adds a thousands separator to REPORT_RESULT_TEXT:
1.Download this zip file, unblocking it and then extracting it to the EQuIS installation folder (typically C:\Program Files\EarthSoft\EQuIS or (for per-user installation) %localappdata%\Programs\EarthSoft\EQuIS\).
2.If using in Enterprise, copy the EarthSoft.Reports.Library.184911.dll file from the EQuIS installation folder into the Enterprise bin folder.
3.Populate ST_MODULE with a new record by adding the following:
▪name: Thousands Separator
▪module_type: ARII Transformation
▪Object_name:
EarthSoft.Reports.Library.184911, EarthSoft.Reports.Library._184911.AddThousandsSeparatorToREPORT_RESULT_TEXT
4.Populate DT_ACTION_LEVEL_LOOKUP with the following new record:
▪EUID: populate with a distinct integer (e.g. '1'). The EUID column will be auto-corrected when the table is saved.
▪lookup_source (25 char): UsedinAR_II
▪lookup_code: REPORT_RESULT_TEXT
▪lookup_method: Thousands Separator
▪param_code (20 char): NONE
▪comparison_operator (10 char): NONE
▪comparison_value (19 char): NONE
5.The VB code (ExampleOfCodingTransformationOfARII.vb) of EarthSoft.Reports.Library.184911.dll is provided as a text file here to help advanced users or software developers to create their own custom transformation reports.