Show Calculated Columns in Tables: /Documentation

Show Calculated Columns in Tables

In most cases, we recommend implementing calculated columns/fields using a Calculation field. This topic shows an alternative implementation that can be used when the desired calculation is especially complex, or if Calculation fields are not available for some other reason.

Calculated columns can be useful when you want to extend tables like datasets and sample grids in order to:

Display a calculated column directly alongside the source data.
Surface a calculated column in LabKey Biologics or Sample Manager.

To expose the calculation, you'll need to have a method of alignment, i.e. a field in the table must be able to 'lookup' the calculation in the query.

Create a Query and Calculated Column
Include Alignment Key Field in Query B

Add a Key Field If Needed

Join the Query to the Original Table with an Aliased Field
Surface the Calculated Column in a Grid

Create a Query and Calculated Column

First, create a query based on the table (dataset, list, sample type, etc.) where you want to display the calculation. Include the calculated column in your query. We will refer to these as "table A" and "query B".

The specific table/query pair used in this topic are a dataset and BMI query introduced in this topic:

Calculated Columns

Include Alignment Key Field in Query B

To align the table and dataset, you need a common key, which will allow the query to become the target of a lookup from the table. You may have to modify query B to accomplish this. Consult the Query Browser to discover the available keys.

In the case where your table is a study dataset (with a compound key combining participant and visit details) you can include the "lsid" column.
For a sample type, you can use the sample "Name" column.

If your query already selects a primary key field of your table, you can skip this section and proceed to creating the lookup from the table to the query.

In our example, our BMI query B calculates the Body Mass Index (BMI) from data in the Physical Exam dataset (PK is "lsid") using an intermediate "Height and Weight" query. Both queries can be modified to include the "lsid" column to use for alignment.

Height and Weight query:

SELECT PhysicalExam.ParticipantId,
PhysicalExam.lsid,
PhysicalExam.date,
PhysicalExam.weight_kg,
TRUNCATE(CAST(Datasets.Demographics.height AS DECIMAL)/100, 2) as height_m,
FROM PhysicalExam

BMI query:

SELECT "Height and Weight".ParticipantId,
"Height and Weight".lsid,
"Height and Weight".Date,
"Height and Weight".weight_kg,
"Height and Weight".height_m,
ROUND(weight_kg / (height_m * height_m), 2) AS BMI 
FROM "Height and Weight"

Keep in mind row uniqueness when identifying your key. For example, using the ParticipantID to align a study dataset with a query will only work for a demographic dataset (and query based on it) where there is only a single row per participant. ParticipantVisit can be used to align a query with a study dataset, and is similar to the lsid. Remember that you can include a primary key like "lsid" in a query without exposing it to the user by using <isHidden>true</isHidden> in the XML metadata.

BMI query's XML Metadata:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="BMI" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="lsid">
        <isHidden>true</isHidden>
      </column>
    </columns>
  </table>
</tables>

Add a Key Field If Needed

If your query does not incorporate the primary key field of your table, you can annotate the query XML to identify the field you want to use as an additional key for alignment. Keep in mind row uniqueness when choosing a key.

Learn more in this topic:

Create a Lookup to a Non-Primary-Key Field

Edit the metadata of query B to annotate this column as a key field. To do this:

Go to the Query Browser and select query B.
Click Edit Metadata.
Scroll to the bottom of the page, and click Edit Source.
Edit the XML to add an <isKeyField> element to the key field, for example:

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="My Query" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="myColumn">
        <isKeyField>true</isKeyField>
      </column>
    </columns>
  </table>
</tables>

Click Save and Finish.

Join the Query to the Original Table with an Aliased Field

Edit the metadata of table A to "look up" into query B where your calculated column is found. You do this by creating a field alias, using a "copy" of the table's key in a new field which looks up into your query:

Go to the Query Browser and select table A.
Click Edit Metadata.
Scroll to the bottom of the page, and click Alias Field.
In the popup dialog, select the key field for table A, and click OK.
This will add a field called "Wrappedlsid", "Wrappedname", etc., depending on the name of your key field.

You can change the name if desired.
You can also annotate it with <isHidden>false</isHidden> in the XML metadata to avoid having to "show hidden fields" to see it later.

Set the Data Type for the wrapped field to Lookup. Point the lookup at query B. If you don't see it listed here, you may need to check your query's alignment key.

Click Save and then View Data.

Surface the Calculated Column in a Grid

Now modify the grid for table A to display the calculated column:

On the grid for A, select Grid Views > Customize Grid.
Under Available Fields select Show Hidden Fields (unless you annotated it as not hidden).
Open the node Wrappedlsid (or however your aliased field is named).
Select the calculated field of interest and click Save. Save the grid as the default grid, or a named grid, as desired.

LabKey Support

LabKey Support