Report Transformations in Analytical Results II

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

 

30374_rpt_trans_analytical_rslts_2

 

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, adds a row to the Analytical Results II output. 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.

 

Populate DT_ACTION_LEVEL_LOOKUP. The columns below are required unless otherwise noted.

 

lookup_source (25 char): EQuIS_AR_II

lookup_code (40 char): Optional column used to create different categories for each source.

lookup_method (255 char): MAG_SUM

param_code (20 char): the method analyte group code for the sum calculation

comparison_operator (10 char): This must be ND_X for the Non-Detect Multiplier to be activated.

oIf not using the Non-Detect Multiplier, enter NONE or NA or any other value since this column may not be NULL.

comparison_value (19 char): Optional value to multiply by the Non-Detect analytical result detection limits, before adding to the MAG_SUM.

oDefaults to 1 if no value entered.  

oIf the value entered is < 0, then 0 is used.  

oIf the value entered is > 1, then 1 is used.

oRequires comparison_operator = ND_X.

action_level_unit (15 char): The output unit for the sum calculation.

 

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 multiplier applied and product summed.

2.REPORT_RESULT_VALUE is replaced with result value after multiplied by TEF.  

 

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.

 

Populate DT_ACTION_LEVEL_LOOKUP. The columns below are required unless otherwise noted.

 

lookup_source (25 char): EQuIS_AR_II

lookup_code (40 char): Optional column used to create different categories for each source.

lookup_method (255 char): TEQ_SUM

param_code (20 char): The method analyte group code for the sum calculation.

comparison_id (20 char): The CAS_RN of the analyte to multiply by the TEF.

comparison_operator (10 char): TEF

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

action_level_unit (15 char): The output unit for the sum calculation.

 

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.For builds prior to 7.20.3, download this zip file, unblocking it and then extracting it to the EQuIS installation folder (typically C:\Program Files\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:

 

module_type:  ARII Transformation

Object_name:
  EarthSoft.Reports.Library.184911, EarthSoft.Reports.Library_184911.AddThousandsSeparatorToREPORT_RESULT_TEXT

name:  Thousands Separator

 

3.Populate DT_ACTION_LEVEL_LOOKUP with the following new record:

 

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

 

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