<< Click to Display Table of Contents >> Navigation: Database > Unit Conversion and Reporting Formula 
•Available Functions and Fields
•Example 2: Volumetric Calculations
•Example 5: Conversion Factor + Reporting Formula
•EUID generation for RT_MTH_ANL_GROUP_MEMBER reporting formula
Basic unit conversions are handled in EQuIS using the database function EQUIS.UNIT_CONVERSION. This function is frequently built into a report or other output requiring the units to be converted. The conversion is controlled by values entered into the RT_UNIT_CONVERSION_FACTOR table as follows:
•REPORTED_UNIT: The original unit in which the value is reported.
•TARGET_UNIT: The unit to which the results will be converted.
•CONVERSION_FACTOR: The value the reported unit results are multiplied by in order to convert to the target unit results.
•DELTA: The additional value to add to (or subtract from) the target unit results (e.g., temperature conversions generally require this).
By default, the formula for basic unit conversion is:
{original_value} * {conversion_factor} + {delta} 
A formula other than the standard unit conversion can also be used to perform a unit conversion. Formulas are built using certain fields, standard math operators, fixed numeric values, and various math functions.
The formula is defined in the RT_UNIT_CONVERSION_FACTOR table as follows:
•FORMULA: The formula used to perform the computation.
•CONVERSION_FACTOR, DELTA, A, B, C, D, E: Constants that can be referenced in the formula.
The formula should contain the {original_value}, which is the value to be converted. It may also include constants, e.g. {conversion_factor}, {delta}, {a}, {b}, {c}.
Basic unit conversion functionality is explained further in the help article RT_UNIT_CONVERSION_FACTOR.
The following functions and fields can be used in unit conversions formulas:
Standard mathematical operators – “+”, “”, “*”, “/”
Parentheses – to control the order of operations, which follow the standard mathematical order of operation based on operators (i.e., multiplication and division are performed prior to addition and subtraction)
Database functions – the following SQL Server database functions (largely Mathematical Functions)
ABS 
COS 
LOG10 
SIN 
ACOS 
COT 
PI 
SQRT 
ASIN 
DEGREES 
POWER 
SQUARE 
ATAN 
EXP 
RADIANS 
TAN 
ATN2 
FLOOR 
ROUND 

CEILING 
LOG 
SIGN 
* not actually mathematical functions but return the first nonnull value supplied as parameters
Database fields – must be contained in curly brackets and can also begin with a $, e.g.:
{original_value}, {conversion_factor}, ${delta}
Along with fields in RT_UNIT_CONVERSION_FACTOR, certain fields from other tables (listed below) can be used. For Unit conversion functionality to reference these supported fields, the indicated lookup values need to be included. For example, the EQUIS.UNIT_CONVERSION_RESULT function requires additional parameters (Primary Keys) needed to lookup these values (i.e., facility_id, sample_id, test_id, cas_rn, and RT_MTH_ANL_GROUP_MEMBER.EUID).
Values from the following fields of the EQuIS Database can be referenced in the formula when EQUIS.UNIT_CONVERSION_RESULT is used.
Table 
Column (Field) 
RT_ANALYTE_DETAIL(1) 
SPECIFIC_GRAVITY 
FORMULA_WEIGHT 

EQUIVALENT_WEIGHT 

HALF_LIFE_YEARS 

CUSTOM_VALUE_1 

CUSTOM_VALUE_2 

CUSTOM_VALUE_3 

CUSTOM_VALUE_4 

CUSTOM_VALUE_5 

DT_SAMPLE(2) 
START_DEPTH 
END_DEPTH 

DT_FIELD_SAMPLE(2) 
AIR_VOLUME 
DT_TEST(3) 
DILUTION_FACTOR 
PERCENT_MOISTURE (if text, no conversion will be returned) 

DT_RESULT(4) 
RESULT_NUMERIC 
The following are required for the related database lookups through EQUIS.UNIT_CONVERSION_RESULT:
(1) CAS_RN
(2) FACILITY_ID and SAMPLE_ID
(3) FACILITY_ID and TEST_ID
(4) FACILITY_ID, TEST_ID, and CAS_RN
Note: Some fields require both table and column names since the column name exists in multiple tables; e.g. {RT_ANALYTE_DETAIL.CUSTOM_VALUE_1} and {RT_ANALYTE_DETAIL.CUSTOM_VALUE_5}. 
In addition to the unit conversion formula stored in the RT_UNIT_CONVERSION_FACTOR table, the RT_MTH_ANL_GROUP_MEMBER table contains a REPORTING_FORMULA that the unit conversion functionality (EQUIS.UNIT_CONVERSION_RESULT db function and EQuIS.Common logic) also uses. To use a REPORTING_FORMULA requires the following:
•EUID value for the record in RT_MTH_ANL_GROUP_MEMBER
•any supported fields used for looking up values in the formula (see previous section)
As this is a reporting formula and is separate from the unit conversion, both may be applied; the reporting formula will be performed first, and then the unit conversion. Reporting formulas entered in RT_MTH_ANL_GROUP_MEMBER.REPORTING_FORMULA follow the same rules as described above (i.e. the same fields, mathematical operators, order of operations, functions, and syntax).
Below are conversion examples involving temperature, volume, conversion factors, reporting formula, and a combination of unit conversion and reporting formula.
To convert temperature from degrees Celsius to Fahrenheit, enter the following values in the RT_UNIT_CONVERSION_FACTOR table.
•REPORTED_UNIT: deg C
•TARGET_UNIT: deg F
•CONVERSION_FACTOR: 1.8
•DELTA: 32
•FORMULA: ({original_value} * {conversion_factor}) + COALESCE({delta},0)
Note: For the above example, the FORMULA is actually not necessary since it is the standard formula used by the EQUIS.UNIT_CONVERSION function when a formula is not provided. However, it is provided here as a simple example of how the formula is constructed. 
To convert a value listed in mg/m³ to ppmv, enter the following values in the RT_UNIT_CONVERSION_FACTOR table.
•REPORTED_UNIT: mg/m3
•TARGET_UNIT: ppmv
•FORMULA: ({original_value} * (273.15 + {a})) / (12.187 * {formula_weight})
•A: 25
Note: The above formula assumes that RT_ANALYTE_DETAIL.FORMULA_WEIGHT contains the molecular weight, and that a constant of 25° Centigrade is used. In addition, the EQUIS.UNIT_CONVERSION_RESULT function is required, and the CAS_RN is passed to that function so that the molecular weight can be retrieved from RT_ANALYTE_DETAIL for each analyte. 
If a particular analyte needs a different conversion factor than the standard one, it could be done by entering that conversion factor in the RT_ANALYTE_DETAIL table for the specific analyte(s), and modifying the standard conversion formula in RT_UNIT_CONVERSION_FACTOR as follows.
In RT_ANALYTE_DETAIL, enter the following for an mg/L to pCi/L conversion for radiological data.
•CAS_RN: 7440611 (i.e. the CAS_RN(s) needing the different conversion  must match a record in RT_ANALYTE)
•STATUS_FLAG: A
•CUSTOM_VALUE_1: 687
In RT_UNIT, ensure that there are entries for mg/L and pCi/L, and set UNIT_TYPE = cpv for the latter to make it available for reporting.
In RT_UNIT_CONVERSION_FACTOR, enter the following.
•REPORTED_UNIT: mg/L  i.e. any/all units this conversion may need to be made FROM
•TARGET_UNIT: pCi/L  i.e. any/all units this conversion may need to be made TO
•CONVERSION_FACTOR: 675.7 or the standard conversion factor used for all other conversion made for the respective units
•DELTA: 0 or NULL  i.e. standard value to add/subtract for the conversion in this scenario nothing is added
•FORMULA: {original_value}*COALESCE({RT_ANALYTE_DETAIL.CUSTOM_VALUE_1},{conversion_factor},1)) + COALESCE({delta},0)
This is a slight modification to the standard unit conversion formula where if a value is found in RT_ANALYTE_DETAIL.CUSTOM_VALUE_1 for a specified analyte, it will be used rather than the standard CONVERSION_FACTOR found in RT_UNIT_CONVERSION_FACTOR; the COALESCE() function returns the first nonnull value  in other words, for analytes where the different conversion factor is to be used, that value will be used, but for everything else, the standard conversion factor will be used. An RT_ANALYTE_DETAIL entry is expected for the referenced CAS_RN, even if the CUSTOM_VALUE_1 field is blank.
•STATUS_FLAG: A
This example shows how a REPORTING_FORMULA from RT_MTH_ANL_GROUP_MEMBER can be used. To report via formula as well in a converted state using a conversion factor (i.e. Ammonium (NH4) Ion as N reported as Ammonium Ion as NH4), enter a conversion factor REPORTING_FORMULA into RT_MTH_ANL_GROUP_MEMBER as detailed below. This example will produce two rows of output for a single results row, accomplished by entering two rows in the RT_MTH_ANL_GROUP_MEMBER table for the same CAS_RN, one that includes the REPORTING_FORMULA, and one that does not. When reporting these results, the specified METHOD_ANALYTE_GROUP must be selected.
1.Create or copy a METHOD_ANALYTE_GROUP_CODE from RT_MTH_ANL_GROUP.
2.Ensure that these fields are set as follows:
a.METHOD_YN: N
b.STATUS_FLAG: A
3.In RT_MTH_ANL_GROUP_MEMBER, enter two records, as shown below.
Record 1•METHOD_ANALYTE_GROUP_CODE: from Step 1 •DISPLAY_ORDER: 1 •CAS_RN: 14797558 •CHEMICAL_NAME: Nitrate •REPORTING_FORMULA: <NULL/BLANK> •STATUS_FLAG: A •EUID: populated per below 
Record 2•METHOD_ANALYTE_GROUP_CODE: from Step 1 •DISPLAY_ORDER: 2 •CAS_RN: 14797558 •CHEMICAL_NAME: Nitrate as N •REPORTING_FORMULA: {original_value}*0.2259 •STATUS_FLAG: A •EUID: populated per below 
4.If the analyte conversion also requires a unit conversion, use the instructions in Example 3 above.
Note: To work properly, the calculation requires the same CAS_RN for both RT_MTH_ANL_GROUP_MEMBER records. 
When running a report to view these, add that METHOD_ANALYTE_GROUP_CODE to the Results > Analyte > Group(s) (or similar) report parameter. Output will include two rows, one for each of the rows in RT_MTH_ANALYTE_GROUP_MEMBER; the first does not apply the reporting formula, while the second does.
This example shows how a REPORTING_FORMULA from RT_MTH_ANL_GROUP_MEMBER can be used in combination with unit conversion. To report via formula as well in a converted state using a conversion factor (i.e. Ammonium (NH4) Ion as N reported as Ammonium Ion as NH4), enter a conversion factor REPORTING_FORMULA into RT_MTH_ANL_GROUP_MEMBER as detailed below. This example will produce two rows of output for a single results row, accomplished by entering two rows in the RT_MTH_ANL_GROUP_MEMBER table for the same CAS_RN, one that includes the REPORTING_FORMULA, and one that does not. When reporting these results, the specified METHOD_ANALYTE_GROUP must be selected.
1.Follow Example 3 above.
2.Create or copy a METHOD_ANALYTE_GROUP_CODE from RT_MTH_ANL_GROUP.
3.Ensure that these fields are set as follows:
a.METHOD_YN: N
b.STATUS_FLAG: A
4.In RT_MTH_ANL_GROUP_MEMBER, enter two records, as shown below.
Record 1•METHOD_ANALYTE_GROUP_CODE: from Step 1 •DISPLAY_ORDER: 1 •CAS_RN: 7440611 •CHEMICAL_NAME: Uranium •REPORTING_FORMULA: <NULL/BLANK> •STATUS_FLAG: A •EUID: populated per below 
Record 2•METHOD_ANALYTE_GROUP_CODE: from Step 1 •DISPLAY_ORDER: 2 •CAS_RN: 7440611 •CHEMICAL_NAME: Uranium234+238 •REPORTING_FORMULA: {original_value} * [list a conversion factor here, e.g., 0.9767] •STATUS_FLAG: A •EUID: populated per below 
Note: To work properly, the calculation requires the same CAS_RN for both RT_MTH_ANL_GROUP_MEMBER records. 
5.When running a report (such as Analytical Results II) to view these, include the following selections:
a.Results > Analyte > Group(s) – include the METHOD_ANALYTE_GROUP_CODE from Step 1
b.Result > Unit
c.Additional Fields > RT_MTH_ANL_GROUP_MEMBER.EUID
Output will include two rows, one for each of the rows in RT_MTH_ANALYTE_GROUP_MEMBER; the first does not apply the reporting formula, while the second does. The unit conversion is applied after the reporting formula has been applied.
•When manually populating a Reporting Formula in EQuIS Professional, note that the Reporting Formula will not work unless the EUID field in the RT_MTH_ANL_GROUP_MEMBER table for the row containing your reporting formula is populated. The RT_MTH_ANL_GROUP_MEMBER table does not allow editing of the EUID field, and does not automatically assign a EUID when you add records to that table, but one of the following methods can be used instead:
•If you have access to your database in SQL Server Management Studio, you can run the following stored procedure for the RT_MTH_ANL_GROUP_MEMBER table to automatically populate the EUIDs:
EXEC equis.populate_euid 'dbo', 'rt_mth_anl_group_member';
•Populate the EUID field using the Groups Interface using the steps below:
a.Click Groups in the Edit section of the top toolbar in EQuIS Professional to open the Group Form.
b.Click the Analyte group type in the left pane of the Group Maintenance window to see the available Analyte groups.
c.Select the Analyte group you wish to edit.
d.Click on the Group Members tab in the right window. This will display records from RT_MTH_ANL_GROUP_MEMBER. You may need to scroll all the way to the right to see the EUID field (typically it is the second to last field).
e.Run the Get New EUID Values Report to generate as many new EUID values as you need.
f.Copy and paste those EUID values into the EUID field in the table within the groups interface.
g.Click Save.
•If your permissions allow you access to this feature, open the table RT_MTH_ANL_GROUP_MEMBER in EQuIS Professional, then click the Note button to open the notes form. It will automatically generate a EUID for the record; you can close the form without adding a note.
Copyright © 2024 EarthSoft, Inc. • Modified: 24 Jun 2024