Issue: UDR Summary Statistics Do Not Work On Columns With Expressions

Summary

The information in this article applies to LIMS 1.x/2.0/2.1

Prior to LIMS version 2.2, a user-defined report (UDR) column with an expression could not include summary statistics. Beginning with version 2.2, all summary statistics except geometric mean can now be used in UDR columns with VBA expressions as long as the expression does not reference another calculated control (e.g. txtValueX). That is, the expression can not reference another column whose value is the result of a VBA expression. The information below applies to the older LIMS versions 1.x, 2.0, and 2.1.

More Information

The UDR generator is a custom-written application that extends the capabilities of the MS Access report generator allowing users to create custom tabular-style reports. Since the UDR generator is built on top of the Access report generator, limitations in the Access report generator prevent summary statistics on columns with results calculated by an expression.

An Access report can calculate totals (or statistics) on fields in the report’s underlying data but it can not calculate totals on a calculated control. The UDR summary statistics controls use ControlSource expressions of the form “=Function([Col])”, where Function is the summary function (e.g. Sum, Min, Max, Avg, etc.) and Col is the column letter. Since the column letter identifies data in the report’s underlying data, summary statistics display properly for columns with values obtained from an analysis result or formula. See knowledge base article UDR Formula and Expression Dependency Rules for more information on the UDR generator’s steps used to construct a report.

The value of a UDR column with an expression is obtained by assigning the expression to the ControlSource property of an Access text control — a calculated control. In this case, the Access report generator calculates the value of the control so summary statistics are calculated for the columns underlying data and not the calculated value of the text control. If the expression column also includes an analysis result or formula, the statistics would be calculated for these values, otherwise the underlying value is uninitialized or zero.

One workaround to this situation is to try to eliminate the expression and perform the expression’s calculations using a formula. Another workaround to this problem is to use the Excel interface to export the data and calculate the statistics in an Excel workbook.