Overview	Top

Definitions

You can perform two mathematical operations on entered numeric data:

Operation	Use
Transformations	Modify data, such as round or truncate the entered value.
Calculations	Perform arithmetic calculations, such as add to or subtract from the entered value.

After creating an expression to define one of these operations, you must associate the operation with a Parameter in a Parameter List.

How Transformations and Calculations are Processed

This is a high-level overview of how Transformations and Calculations are processed during numeric data entry:

1.	A User enters a data value: SDIDataItem.EnteredText = String entered during Data Entry   = 10.2676700 The system drops trailing zero significant digits, operators, and text: SDIDataItem.EnteredValue = 10.26767

 

2.

If the Parameter List defines a Transformation Rule, the system applies it: SDIDataItem.TransformRule = Transformation Rule defined by Parameter List   = round( [this], 3 ) SDIDataItem.TransformValue = Result of the Transformation   = 10.268 If the Parameter List does not define a Transformation Rule, SDIDataItem.EnteredValue = SDIDataItem.TransformValue. If the Parameter List defines a Display Format, it is applied to SDIDataItem.TransformValue to arrive at the Display Value.

 

3.	SDIDataItem.TransformValue is used to evaluate Calculation Rules and Parameter Limits. SDIDataItem.TransformValue is used to evaluate Specification Limits only if the Specification does not define a Transformation Rule.

 

4.	If the data are checked against a Specification, what happens next depends on whether or not the Parameter List defines a Transformation Rule:

5.

a.

If both the Specification and the Parameter List define a Transformation Rule, the Specification's Transform Rule is applied to the result of the Parameter List's Transformation (value in SDIDataItem.TransformValue). SpecParamItems.TransformRule = Transformation Rule defined by Specification     round( [this], 2 ) SDIDataItem.TransformValue = Result of the Transformation defined by the Parameter List   = 10.268 SDIDataItemSpec.TransformValue = 10.27 SDIDataItemSpec.CheckedValue = 10.27   = "Checked Value" reported in Data Entry pages SDIDataItemSpec.TransformValue is used to evaluate Specification Limits.

5.

b.

If the Specification defines a Transformation Rule but the Parameter List does not, the Specification's Transformation Rule is applied to SDIDataItem.EnteredValue: SpecParamItems.TransformRule = Transformation Rule defined by Specification     round( [this], 1 ) SDIDataItem.EnteredValue = 10.26767 SDIDataItemSpec.TransformValue = 10.3 SDIDataItemSpec.CheckedValue = 10.3   = "Checked Value" reported in Data Entry pages SDIDataItemSpec.TransformValue is used to evaluate Specification Limits.

Numeric Data Types in Calculations

LabVantage has two numeric Data Types that are used in Calculations:

•	"Numeric with Calculation Expression" (N) refers to entered numeric data or calculated data that can be manually changed in Data Entry (editable).
•	"Numeric Calculation" (NC) refers to calculated data that cannot be manually changed in Data Entry (readonly).

Maximum Numeric Data Values

If using an Oracle DBMS, LabVantage supports numeric input up to a maximum value of 12 digits and does not return more than 13 significant digits. Attempting to return more will return zeros as placeholders beyond the 13 digits.

If using a SQL Server DBMS, LabVantage supports numeric input up to a maximum value of 10 digits.

When executing Calculations, the LabVantage calculation engine does not preserve and honor rules of significance and precision across mathematical operations. The calculation engine simply processes the numeric value of each input.

Calculated values are stored both as text (in SDIDataItem.EnteredText) and as a number (in SDIDataItem.EnteredValue). SDIDataItem.EnteredValue holds the calculated value to the maximum precision supported by LabVantage. The Display Value is applied to the calculated value to alter presentation of the result. For example, if a Calculation is evaluated at 0.3333333333, the Display Value could be set to show this as 0.33.

About Parsing and Locale

LabVantage numeric parsing routines ignore the thousands separator on input. This can result in unexpected consequences when executing Actions through the ToDo List, such as when executing an Action as a Task (see Action Processing and ToDoList Maintenance).

Tasks are executed as the (system) user, which uses the same Locale as the Application Server. If you execute an EnterDataItem Task with an EnteredText value of 38.20 and the Application Server is running in Danish Locale, the Application Server will correctly interpret the 38.20 as Danish numerical format. However, since LabVantage ignores the thousands separator, the user will see 3820, regardless of the user's Locale.

 

Transformation Rules	Top

Calculation Rules	Top

In General...

Calculation Rules are expressions that perform basic arithmetic calculations, such as add to or subtract from an entered value.

LabVantage supports expressions written in these languages:

•	LabVantage (LabVantage's proprietary expression language).
•	Groovy (maintained by the Apache Software Foundation at http://www.groovy-lang.org/).

Parameter Syntax

When writing Calculation expressions, you must define all information necessary to uniquely identify each Parameter used in the Calculation.   For each group of terms required to identify the Parameter down to the Replicate level:

LabVantage Syntax	Groovy Syntax
Enclose the group within square brackets, e.g., [group]	Preface the group with a dollar sign, and enclose the group with curly braces, e.g., ${group}
Use a pipe (|) to separate SDI qualifiers, Parameter List qualifiers, and Parameter qualifiers.
Between pipes, use a semicolon to delimit each qualifier.

Using LabVantage syntax, you can build expressions that identify data from the SDI to Replicate level. In general, the complete form of such an expression is:

[SDI|PL;PLV;Var;DS|P;PT;R;DI]

where

SDI	=	SDI Reference
PL	=	Parameter List
PLV	=	Parameter List Version
Var	=	Parameter List Variant
DS	=	Data Set Number (see Note below)
P	=	Parameter
PT	=	Parameter Type
R	=	Replicate
DI	=	SDIDataItem Column or Data Item Attribute

NOTE:

Two highly specialized variations on Data Set syntax are described in Referencing Data Sets by an SDIData Column.

The equivalent Groovy syntax is:

${SDI|PL;PLV;Var;DS|P;PT;R;DI}

Internally, LabVantage identifies a Groovy expression by automatically prefacing the expression with $G{ and ending it with }.

NOTE:

Variants on Data Item syntax are described in Referencing Data Items by an SDIDataItem Column.

To avoid confusion, the bulk of examples that follow use LabVantage syntax. For examples of Groovy expressions, see Groovy Examples.

Internal and Reference Calculations

LabVantage supports two types of Calculations:

•	Internal Calculations involve only one SDI. Accordingly, you need to specify only the qualifiers you need to identify the Parameter List and Parameter, e.g., [PL;PLV;Var;DS|P;PT;R;DI]
•	Reference Calculations involve more than one SDI. This is where you must also specify the "SDI Reference" qualifiers, e.g., [SDI|PL;PLV;Var;DS|P;PT;R;DI]

As shown, the syntax for writing expressions that perform Reference Calculations is an extension to the syntax used for Internal Calculations. In other words, it is best to gain familiarity with Internal Calculations before attempting Reference Calculations.

Standard Operators

LabVantage uses the following standard mathematical operators for expressions created using the Expression Library and Expression Editor:

Operator	Operation		Operator	Operation
+	Addition		^	Exponentiation
-	Subtraction		<	Less Than
*	Multiplication		>	Greater Than
/	division		=	Equal To

Expression Length

Expressions are stored in the Expression.Expression column. The data type of this column is CLOB. Expressions longer than 2000 characters can therefore be stored.

 

Internal Calculations	Top

In General...

You do not need to specify all qualifiers... only what you need to identify the desired item. You can:

As a start, these are simple examples of valid variables using the Parameter List "PL_01" and Parameter "Cu". Note the use of the word "current"... this means the item being edited... think of it as "this". For example, if you are entering data for Sample "S-001" and the Calculation Rule specifies that Parameter "Cu" is in the "current" Parameter List, the Calculation Rule is done on S-001's Parameter List for which you are entering data... in other words, "this" Parameter List.

As an example of a simple Internal Calculation, consider summing the values of Parameters Fe and Ni to determine the value of Parameter Steel (both Fe and Ni are in the same Parameter List). For Steel, you would define the Calculation [Fe] + [Ni]. For Fe = 5 and Ni = 1, the value of Steel would be 6.

You can use these wildcards in place of any value:

Note that in the Simple Examples (where information was truncated or omitted), the missing values are treated as #. For example:

[Cu] is the same as [#;#;#|Cu;#;#]

As another example of using wildcards, you can create a single expression that calculates multiple values, with each calculation based on a set of Replicates for a unique Parameter. Consider a Parameter List that defines the floor space of a room using the Parameters Length, Width, and Area.  You want to calculate the area by taking three sets of measurements, each with a different measurement instrument.  You therefore specify three Replicates for each Parameter (the Replicate column is for the purpose of clarifying the example):

You can create a single expression to calculate three values for Area.  The expression for each Area Replicate number would use the Length and Width values of the corresponding Replicate number.  For example, the Area of Replicate number 1 would calculate using the Length and Width of Replicate number 1. To create this expression, use the following syntax for the Area calculation, which assumes that Length and Width are in the current Parameter List, and the Parameter Type is Standard:

Area = [Length;Standard;#]*[Width;Standard;#]

The # symbol tells the calculation engine to use the value of each Replicate in sequence.  Of course, the engine will not calculate Area until both Length and Width values are entered.  The results:

You can also use the asterisk (*) as a wildcard character to calculate a single value based on all Replicates for a Parameter.  This is useful when executing Aggregate functions such as averaging (see Expression Library and Expression Editor below).  For example, to calculate the average of the Lengths above, the expression

AverageLength = avg([Length;Standard;*])

takes the average of 20, 40, and 60 to give a value of 40.

Both the Replicate and Data Set Number accept an additional operator: "max". This uses the Parameter with the (arithmetically) highest Data Set Number or the highest Replicate number. Examples:

You can write an expression that returns text as a result of a calculation.  For example, consider a Parameter List with the Parameters Al, Fe, and Ni. The following calculation expression is applied to the Parameter Al:

if ( [Ni] < [Fe], [Ni], [Fe] )

This expression performs the calculation using numeric values of the Parameters. If the value of Ni is less than the value of Fe, the value of Al is the value of Ni; otherwise, the value of Al is the value of Fe:

Now. look at the following expression:

if ( [Ni] < [Fe], "<" + string([Fe]), string([Ni]) )

This time, if the numeric value of Ni is less than the numeric value of Fe, the value of Al is the < character followed by the text string representing the numeric value of Fe; otherwise, the value of Al is the text string representing the numeric value of Ni:

Use these guidelines when returning text from a calculation (we are using our example Parameter List and applying the calculations to Al):

If you write your expression in Groovy, you can pass these values into a calculation (see Groovy Examples: Other Data Types):

You can exclude certain Data Items from Calculations by either of these methods:

Calculations can reference columns in the SDIDataItem table. How this works depends on whether the Calculation expression is written in LabVantage syntax or Groovy syntax:

A Data Item's Parameter Limit can be referenced using the following variables:

Examples.

The expression below refers to the Calcium;Standard;1 Parameter with a "Value 1" having an "Absurd" Parameter Limit Type:

[Calcium;Standard;1;ParamLimit_1:Absurd]

The Calculation below sums the number of Replicates that have met the "Absurd" Parameter Limit Type for Calcium;Standard;*:

sum([Calcium;Standard;*;ParamLimit_Status:Absurd])

As shown in Column Name Reference above, when a single ColumnId is specified, the Calculation engine determines if an SDIDataItem column of the specified name exists. If so, the column value is used. If not, it looks for a Data Item Attribute having the specified name.

Multiple instances of a Data Item Attribute can be evaluated only when the Attribute's Data Type is "Numeric", and the expression returns a single Data Item.

Example. This expression can be evaluated ("oxidation" is a Data Item Attribute):

sum([Ag;Standard;1;oxidation])

This returns a single Data Item, so the expression can be evaluated.

Multiple instances of a Data Item Attribute cannot be evaluated if the expression returns multiple Data Items.

Example. This expression could be a problem:

sum([Ag;Standard;*;oxidation])

This expression specifies all Replicates of Ag;Standard. If multiple Replicates are found with multiple instances of a Data Item Attribute, an error is returned (and displayed in the Calculation Report).

Calculations can reference a Data Set with respect to any single column in the SDIData table, such as the SourceWorkitemId or InstrumentId columns. In a Calculation, you can filter by these (or any) SDIData column.

To provide such filtering using the form [PL;PLV;Var;DS|P;PT;R;DI], specify the SDIData column for DS. Note that you can specify only one SDIData column.

Calculations can also reference any single column in the SDIData table. Using the form [PL;PLV;Var;DS|DSCol], specify the SDIData column for DSCol. Note that you can specify only one SDIData column.

Multiple SDIData columns can be used in an expression with a logical AND operator. This offers extended filtering capability.

Example. The expression below filters all of the Data Sets with activeflag=Y and modifiableflag=N and s_cancellableflag=N.

As shown in Column Name Reference above, when a single ColumnId is specified after the pipe (|), the Calculation engine determines if an SDIData column of the specified name exists. If so, the column value is used. If not, it looks for a Data Set Attribute having the specified name.

Multiple instances of a Data Set Attribute can be evaluated only when the Attribute's Data Type is "Numeric", and the expression returns a single Data Set.

Example. This expression can be evaluated ("oxidation" is a Data Set Attribute):

sum([Metals;#;#;1|oxidation])

This returns a single Data Set, so the expression can be evaluated.

Multiple instances of a Data Set Attribute cannot be evaluated if the expression refers to multiple Data Sets, and one of the Data Sets has multiple instances of the specified Attribute.

Example. This expression could spell trouble:

sum([Metals;#;#;*|oxidation])

This expression specifies all Data Sets of Metals;#;#. If one of those Data Sets has multiple instances of the Attribute "oxidation", an error is returned (and displayed in the Calculation Report).

The Calculation is executed when a Data Item has been modified. For example, sum([Metals;#;#;*|oxidation])+ [Ag;Standard;max] executes when data are entered in the "max" Replicate of Ag;Standard in the current Data Set. However, the RedoCalculation Action executes calculations for all Data Items.

If you specify a Data Set Attribute and that Attribute is not defined for the Data Set, the Calculation engine searches for a Test Attribute of the same name in the Test (SDIWorkitem) related to Data Set. If found, the Test Attribute is used. In this case, the Calculation Report shows the WorkitemId and WorkitemInstance. 

Finally, you can reference a single column in the current SDI, such as when evaluating a Calculation on a Data Item in a Data Set for the current SDI. The current SDI is referred to as the "primary". This works for both SDI column values and SDC Attributes.

The syntax for specifying a primary SDI column value is:

[primary:columnid]

Consider that one of the the simplest expression is to refer to another Data Item in the same Data Set by simply specifying the ParamId, e.g., [Ag]. It then follows that [Ag] * [primary:samplecolumn] is a valid expression.

The syntax for specifying a primary SDI Attribute value is:

[primary:attributeid]

The Calculation engine determines if an SDI column of the specified name exists. If so, the column value is used. If not, it looks for an SDC Attribute (on the current SDI) with that name.

The Calculation is executed when a Data Item has been modified. For example, [Ag]+[primary:oxidation] executes when data are entered for the Data Item with ParamId of Ag in the current Data Set. However, the RedoCalculation Action executes calculations whether or not a Data Item has been modified.

Reference Calculations	Top

As stated earlier, Reference Calculations are an extension of Internal Calculations. Reference Calculations are performed on the current SDI (the one for which you are entering data), but reference Parameters in another SDI. As such, an additional qualifier (which we call the "SDI Reference") is added to the Parameter List and Parameter qualifiers used to write Internal Calculations:

[SDI|PL;PLV;Var;DS|P;PT;R;DI]

The "SDI Reference" is a reference to another SDI (called the "Reference SDI") that contains the value you want to use in a calculation for the current SDI (called the "Data SDI"). The terms "Reference SDI" and "Data SDI" are used only for documentation purposes and are not defined in LabVantage. They are used only to identify SDIs in the discussions that follow.

The scenarios below apply to Reference Calculations. Note that the "SDI Reference" qualifier can have several variations for some scenarios, as shown in the examples that follow.

The examples that follow demonstrate fundamental expressions for Reference Calculations. If you require assistance in building expressions using multiple variations on these syntaxes, try using the Expression Editor's Cross SDI Helper.

Starting Conditions:

This is a "forward reference"... you must go "to" another SDI to get the required value:

Calculation for Ni:

Note the SDI Reference qualifier is SDI:Product-X. This defines the Reference SDI... in other words, this is where the calculation goes to get the Factor.

Now suppose that someone changed the Factor, and you want to recalculate Ni... in other words, you want to do a "reverse reference". In this case, you must programmatically call the RedoCalculations Action (for example, by using the postData Entry SDC Rule). Pass in sdcid=Sample and keyid1=Sample-001:

Value of Ni recalculated:

Starting Conditions:

Calculation for WS:

Note the SDIReference qualifier is link:containerid. This defines where to get the value in the Reference SDI (the ContainerId column)... this is where the calculation goes to get the weight of the Container. To do this type of Calculation, the column you specfy (in this case, ContainerId) must be a foreign key linked column.

Alternatively, if the foreign key link is in the "LinkId" column of the "SDCLink" table, you could specify the "LinkId" value rather than the "ColumnId".

If the ColumnId or LinkId is not found for the primary SDC:

Now suppose that someone reweighed the Container, and you want to recalculate WS... in other words, you want to do a "reverse reference". In this case, you must programmatically call the RedoCalculations Action (for example, by using the postData Entry SDC Rule). Pass in the values shown below:

Calculation for WS:

RedoCalculations properties:

Starting Conditions:

As with the previous example, a foreign key link must exist from a ContainerId column in the Sample SDC to the Container SDC.

Both Samples are poured into the Container.

To determine the total weight of both Samples, plus the weight of the Container, use the link:Sample SDIReference. This syntax indicates a reverse reference back to both Samples. Then use the sum function as shown:

Calculation for WS:

This calculation says "Find all Samples for which you are entering data that contain a ContainerId column that is linked by foreign key to the Samples".
This returns both values, evaluating the expression for each.

As in the previous example, the column you specfy must be a foreign key linked column, and you can also specify a "LinkId".

As another example, if two child Samples are drawn from a parent Sample, calculate the total weight of the parent. This relationship (child-to-parent) is made by a foreign key link from the ParentSampleId column of the Sample SDC to the Sample SDC itself. Although this is a foreign key link within a single SDI, it is nevertheless
treated in the same manner as the previous example:

Calculation for W (weight of the parent only):

Rather than use foreign-key links, you could set up the "SDIRelation" table to make a many-to-many relationship.

Note that "holder" is the value of the "RelationType" column in the SDIRelation table.

This calculation says "When entering data for Samples, find Samples that have a relationship (in the SDIRelation table) to Containers of RelationType = Holder".

To find the relation from the Reference SDI (Container) back to the Data SDI (Sample), programmatically call the RedoCalculations Action (for example, by using the postData Entry SDC Rule).

RedoCalculations properties:

Note that multiple Relation Types can also be specified as shown in the example below:

[Absorbance]+sum([sdirelation:Reagent;PriorTimepoint|BacterialTest;1;1;1|EndotoxinLevel])

Multiple SDIRelation values can be used for both sdirelation and reversesdirelation as shown in the examples below:

[sdirelation:Reagent;PriorTimepoint|Alkali Metals;1;1;1|Astatine]

[reversesdirelation:Reagent;PriorTimepoint|Alkali Metals;1;1;1|Astatine]

If you want to do Calculations on different Data Sets associated with the same SDI, you could use the SDIDataRelation table to setup the relationship. This is very similar to using the SDIRelation table, except this is a many-to-many link from a Data Set in one SDI to another SDI.

Example: If you are testing a Sample using a Consumable, you can use the SDIRelation table because there is only one Consumable involved. However, if you need to retest, you must use another bottle of Consumable. Now, two Consumable SDIs are related to the Sample... and the Sample could possibly be linked to both. To help deal with this, the SDIDataRelation table lets you associate each Consumable with a different Data Set for the same SDI. In the diagram below, if DS1 and DS2 are different tests, each Data Set can point to a different Consumable (referred to as "Reagent" in the diagram).

Calculation for S-001:

Note that "Reagent" (Consumable) points to the "ToSDCId" column of the SDIDataRelation table, and "Salient" points to the "RelationType" column.

This expression finds Samples that have a relationship (in the SDIDataRelation table) to Consumables of RelationType = 'Salient'".

For an SDI Data Relation, you can omit the "sdidatarelation" operator and the qualifiers for the Data SDI. In the example above, you could begin the expression with the RelationType column, e.g.,

[Salient|ParamList|C]

If the ParamListId, ParamListVersionId, VariantId, and Data Set will be same as that of the target Data Set, you can omit the ParamList (Data Set information), e.g.,

[Salient||C]

In this case, if the Sample's Data Set is Fluid;1;1;1 the target SDI is determined based on ToSDCId, ToKeyId1 and Salient Relation Type from the SDIDataRelation table for the current Data Set. The target Data Item C also has the same Data Set (Fluid;1;1;1) as the source.

If the RelationType is not found in the SDIDataRelation table for the current Data Set:

Note that multiple Relation Types can also be specified. In the case of SDIDataRelation, this means multiple Consumable Types can be specified. For example:

[GC;H2SO4;NaOH|Alkali Metals;1;1;1|Astatine]

If using Array Management, you can perform Calculations using data from Array Items. See Master Data Setup → Array Methods → Adding a New Array Method → Parameters → Calculation Rule.

Calculations can be performed between Parent and Child Samples as follows:

Using Calculations with QC Samples (AQC)	Top

When dealing with QC Samples (AQC module), the following syntax can be used to perform Calculations on QC Samples in a QC Batch:

To perform inter-Sample Calculations on QC Samples in a QC Batch, the following syntax can be used:

AQC:<qcsampletype>;<mode>;<level>

where

Groovy Examples	Top

Groovy Examples: Numeric

Also see Java Objects Passed Into Groovy Calculation Expressions.

These are examples of passing other data types into a calculation (as described in Passing Other Data Types into a Calculation).

Enter a different string for Calcium:

The example above demonstrates a case for using "Calc" Data Types, which prevent editing a calculated field (as defined in the Data detail of the Parameter List). For example, defining the Data Types in the Parameter List as shown below would prevent the calculated fields from being edited:

The Java object passed into a Groovy expression depends on the Data Type of the Parameter, column, or Attribute being requested, as well as the number of Data Items found by the Calculation engine:

For example, if a single Data Item is passed into ${Ag;Standard;*}.size(), that single Data Item is passed in as a BigDecimal object (not an "array of one"). An array of objects is passed only if multiple Data Items are found. If a Groovy expression must handle both single and multiple Data Items, try something like this:

def p = ${Ag;Standard;*}

def x= p instanceof BigDecimal ? 1 : p.size();

where instanceof is a Groovy command that returns the type of data object.

Expression Library and Expression Editor	Top

To assist in generating expressions used in Calculations, LabVantage offers the Expression Library and Expression Editor.