<< Click to Display Table of Contents >> Navigation:  Database > Unit Conversion and Reporting Formula

•Introduction

•Available Functions and Fields

•Reporting Formula

•Conversion Examples

•Example 1: Temperature

•Example 2: Volumetric Calculations

•Example 3: Conversion Factor

•Example 4: Reporting Formula

•Example 5: Conversion Factor + Reporting Formula

•EUID generation for RT_MTH_ANL_GROUP_MEMBER 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:

 

 

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)

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

 

 

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

 

 

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)

 

 

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

 

 

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.

 

 

4.If the analyte conversion also requires a unit conversion, use the instructions in Example 3 above.

 

 

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.

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.

 

 

 

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.

 

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.