<< Click to Display Table of Contents >> Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports > Analytical Results > Analytical Results II > Report Transformations in Analytical Results II

The Analytical Results II Report features an API for applying standard or custom logic (transformations) to report results:
 

1.Standard Transformations

a.MAG Sum Transformation

b.TEQ Sum Transformation

2.Custom Transformations

 

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. Note the following:

 

•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. Only the value for the SUM row is used.

 

 

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 .dll:

 

1.Configure its info in DT_ACTION_LEVEL_LOOKUP and ST_MODULE (see details in the Example, below).
 

2.Do one of the following to the .dll itself:

a.Upload it into ST_MODULE, or

b.Place it in the EQuIS Professional installation folder (typically C:\ProgramFiles\EarthSoft\EQuIS) for use in EQuIS Professional and in the Enterprise bin folder for use in Enterprise.

 

Example

 

The following steps will create a transformation for Analytical Results II that adds a thousands separator to REPORT_RESULT_TEXT.

 

Download this .zip file, unblock it, and then extract the .zip file to get the transformation file (EarthSoft.Reports.Library.184911.dll).

 

1.Configure DT_ACTION_LEVEL_LOOKUP.
 

Add a new row to DT_ACTION_LEVEL_LOOKUP with the following info:

•EUID: Populate with a distinct integer (e.g. '1') which will be auto-corrected when the table is saved.

•lookup_source (25 char): E.G., 'UsedinAR_II' or your preferred text

•lookup_code: REPORT_RESULT_TEXT

•lookup_method: Thousands Separator

•param_code (20 char): NONE

•comparison_operator (10 char): NONE

•comparison_value (19 char): NONE

 

 

2.Configure ST_MODULE with the following info:

•name:  Thousands Separator

•module_type:  ARII Transformation

•Object_name: 

EarthSoft.Reports.Library.184911, EarthSoft.Reports.Library._184911.AddThousandsSeparatorToREPORT_RESULT_TEXT

 

3.To use EarthSoft.Reports.Library.184911.dll in both EQuIS Professional and Enterprise, you can either:
 

a.Upload it to ST_MODULE:

•Start Professional and open ST_MODULE.

•Copy the MODULE_ID of the row for name = Thousands Separator.

•Click the Forms tool and open Blobber (Note: Blobber or EarthSoft.Forms.Library.78163.dll can be obtained from EarthSoft).

•Change ST_MODULE to Blob Table.

•Paste the MODULE_ID IN Key Value.

•Click Update; the green-colored message ‘BLOB successfully updated’ should be displayed to indicate the .dll is populated into ST_MODULE.ASSEMBLY (you can refresh ST_MODULE to confirm it).

•Close Blobber.

 

b.Place it in the EQuIS installation folder – typically C:\Program Files\EarthSoft\EQuIS or (for per-user installation) %localappdata%\Programs\EarthSoft\EQuIS\ 
 

c.Place it in the Enterprise bin folder if using it in Enterprise