LIMS.eu

In report tables, you can embed SQL queries and bar charts based on SQL queries. To do this, specify the Table Type as either SQL Table or Bar Chart for SQL Table when creating the report table.

In the yellow SQL field, you can enter your SQL query. There are a few specifics to be aware of, which are explained below:
Column names for the SQL table must be defined with AS, and the column headers in the created report table must have the same names.

You can use the following variables:
OrderID_
ProbeIDS_
_SELECTED_PARAMETERTYPE_IDS_

These can be incorporated into the SQL query using IN as follows:

SELECT 
samples.name AS 'Sample Number', 
parameters.value0 AS 'Result'
FROM parameters
JOIN samples ON parameters.sample_id = samples.id
WHERE parameters.sample_id IN (ProbeIDS_)
AND parameters.parametertype_id IN (_SELECTED_PARAMETERTYPE_IDS_);

The last line of this example restricts the parameters to those selected on the right side of the screen.

For the bar chart, you only need 2 columns in the SELECT statement. The first one, in this case 'Sample Number,' is displayed on the x-axis, and the second one, in this case 'Result Value 0' (parameters.value0 or parameters.calc_value0), is represented as the bar height on the y-axis.

These two example charts were generated using the following command:

SELECT samples.name, parameters.calc_value0
FROM parameters
JOIN samples ON parameters.sample_id = samples.id
WHERE parameters.sample_id IN (ProbeIDS_)
AND parameters.parametertype_id = 299;