﻿ Unit Conversion and Reporting Formula

# Unit Conversion and Reporting Formula

## Introduction

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.

## Available Functions and Fields

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 non-null 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 look-up 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}.

## Reporting Formula

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

## Conversion Examples

Below are conversion examples involving temperature, volume, conversion factors, reporting formula, and a combination of unit conversion and reporting formula.

### Example 1: Temperature

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.

### Example 2: Volumetric Calculations

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.

### Example 3: Conversion Factor

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: 7440-61-1 (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 non-null 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

### Example 4: Reporting Formula

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: 14797-55-8

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: 14797-55-8

CHEMICAL_NAME: Nitrate as N

REPORTING_FORMULA: {original_value}*0.2259
(where 0.2259 is the conversion value for reporting purposes)

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.

### Example 5: Conversion Factor + Reporting Formula

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.

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: 7440-61-1

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: 7440-61-1

CHEMICAL_NAME: Uranium-234+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

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.

## EUID generation for RT_MTH_ANL_GROUP_MEMBER reporting formula

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.