<< Click to Display Table of Contents >> Navigation:  Professional > Database > Unit Conversion Formulas

•Function Examples

•Reporting Formula

•Conversion Examples

•Troubleshooting

 

Basic unit conversions are handled in EQuIS using the database function EQUIS.UNIT_CONVERSION. This function is frequent 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 unit the value is stored in the database.

•TARGET_UNIT: The unit to which the results will be converted.

•CONVERSION_FACTOR: The value multiplied by the reported unit results to give the target unit results.

•DELTA: Additional value to add (or subtract) to give the target unit (e.g. temperature conversions generally require this).

 

By default, the formula for basic unit conversion is: (original_value × conversion_factor) + delta. Basic unit conversion functionality is explained further in the help article Understanding RT_UNIT_CONVERSION_FACTOR.

 

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 using the following.

•FORMULA: 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 the {conversion_factor}, {delta}, {a}, {b}, {c}, etc. For example, a very simple formula could be the following.

 

 

Function Examples

 

Note that the fields where the values come from in the database are contained in squiggly brackets (e.g. {original_value}, {conversion_factor}, {delta}, etc.). These can also begin with a $ (i.e. ${conversion_factor}) is interpreted the same as {conversion_factor} in the formula.

 

The FORMULA uses standard mathematical operators (“+”, “-”, “*”, “/”). Mathematical order of operation based on operators is applied (i.e. multiplication and division are performed prior to addition and subtraction) however, parentheses can also be used to control the order of operations.

 

Finally, several standard database functions can be used in the formulas.

 

* These functions are not actually mathematical functions, however they return the first non-null value supplied as parameters.

(1) Microsoft SQL Server Only

 

The RT_UNIT_CONVERSION_FACTOR table, several other fields can be looked up from other tables. For these the EQUIS.UNIT_CONVERSION_RESULT function is required, since it includes additional parameters (Primary Keys) needed to look-up these values (i.e. facility_id, sample_id, test_id, cas_rn, and RT_MTH_ANAL_GROUP_MEMBER.EUID).

 

The values stored in the following fields of the EQuIS Database can be used in the formula when the 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 EQUIS.UNIT_CONVERSION_RESULT also uses. For a REPORTING_FORMULA to be used, the EUID value from the RT_MTH_ANL_GROUP_MEMBER table must be passed to the function as well as any Primary Key fields used for looking up values in the formula. 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

 

Example 1

 

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

 

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

 

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.

•CAS_RN: 07440-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_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.

•STATUS_FLAG: A

 

Example 4

 

This example shows how a REPORTING_FORMULA from RT_MTH_ANL_GROUP_MEMBER can be used. If a particular analyte needs to be reported as analyzed as well as reported in a converted state using a conversion factor (i.e. Ammonium (NH4) Ion as N reported as Ammonium Ion as NH4), it could be done by entering a conversion factor REPORTING_FORMULA in the RT_MTH_ANL_GROUP_MEMBER table as detailed below. This example will produce two rows of output for a single results row, which is 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.

 

In RT_MTH_ANL_GROUP, enter the following.

•METHOD_ANALYTE_GROUP_CODE: EXAMPLE4

•METHOD_YN: N

•STATUS_FLAG: A

 

In RT_MTH_ANL_MEMBER, enter two records, as shown below.

 

Record 1

 

•METHOD_ANALYTE_GROUP_CODE: EXAMPLE4

•DISPLAY_ORDER: 1

•CAS_RN: 07440-61-1

•STATUS_FLAG: A

•CHEMICAL_NAME: Standard Results (chemical name that will appear on the report for the unadjusted result)

•REPORTING_FORMULA: <NULL/BLANK>

•STATUS_FLAG: A

 

Record 2

 

•METHOD_ANALYTE_GROUP_CODE: EXAMPLE4

•DISPLAY_ORDER: 2

•CAS_RN: 07440-61-1

•STATUS_FLAG: A

•CHEMICAL_NAME: Results with Reporting Formula Adjustment (chemical name that will appear on the report for the adjusted result)

•REPORTING_FORMULA: {original_value} * 4.427 (conversion value for reporting purposes)

•STATUS_FLAG: A

 

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

 

 

When running a report to view these, select the METHOD_ANALYTE_GROUP_CODE EXAMPLE4. 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, the second does. In the case where a unit conversion is also necessary, it will be applied after the reporting formula has been applied.

 

Troubleshooting

 

If you are manually populating a Reporting Formula in EQuIS Professional, it is important to 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 you to edit the EUID field, and does not automatically assign a EUID when you add records to that table. There are two potential workarounds:

 

1.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';

 

2.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 the EUID values generated by the Get New EUID Values Report into the EUID field in the table within the groups interface.

g.Click Save.
 

3.Open table RT_MTH_ANL_GROUP_MEMBER in EQuIS Professional, then click the Note button, if your permissions allow you access to this feature; you can close immediately after without adding a note.