Building a query with the builder

A query tag reads the records that have been captured and turns them into a value — a key figure, a total, a count. There are two ways to say how that value comes about: in the Builder you describe what the figure should say and the query is written for you. In SQL mode you write the query yourself.

You switch between the two at the top of a query tag. A tag built with the builder opens in the builder; a hand-written one opens in SQL.

The five questions

Source. Which records the figure is computed over — one record type, for example ProductionReport. Master data is not offered here: it supplies the values a filter picks from, not the thing being measured.

Result. Either a key figure of the plant (availability, performance, quality, OEE) or a plain calculation over one field: number of records, total, average, smallest or largest value. A key figure is only offered when the chosen record type really has every field its formula reads. That way the list never contains a figure whose only possible outcome is an error.

Period. A field carrying a point in time, such as ProductionReport.Timestamp. The field is shown qualified, because it is a field of the record type one control to the left. The period produces the two parameters from and to — what fills them is described under Parameters and bindings. With no period, the figure covers every record.

Filter. Which of those records count. Conditions can be nested into groups with All of or Any of. Where a field has a closed value list you pick the value from a list instead of typing it — mistyping it is the commonest reason a filter silently matches nothing. A condition with no value is not written out at all: an unfinished condition cannot change the answer.

Broken down by. One field or several. One result row per combination — that is the shape a group table or a Pareto widget needs. Nothing chosen means one row in total.

What comes out

At the bottom right stands The query this produces. It follows the description on every change; there is no button that generates it first. If you do not read SQL, ignore it.

For “number of records in ProductionReport over Timestamp” it looks like this:

WITH src AS (
  SELECT 1 AS row_present
  FROM "ProductionReport"
  WHERE active
    AND ($1::timestamptz IS NULL OR "ProductionReport.Timestamp" >= $1::timestamptz)
    AND ($2::timestamptz IS NULL OR "ProductionReport.Timestamp" < $2::timestamptz)
)
SELECT COUNT(*)::numeric AS "Value"
FROM src

With a total and a breakdown by Line:

WITH src AS (
  SELECT "ProductionReport.Line" AS line, "ProductionReport.GoodParts" AS good_parts
  FROM "ProductionReport"
  WHERE active
)
SELECT line AS "Line",
       SUM(good_parts::numeric) AS "Value"
FROM src
GROUP BY line
ORDER BY line

Two properties of this shape are deliberate:

• WHERE active is always there. A record somebody took back counts in no figure.
• The result column is called Value. That is the name a widget looks for.

Beside the generated query stands Try it out. The dry run shows the answer as a table — see The dry run.

The one-way door

The moment you change the query by hand in SQL mode, the builder’s description is dropped. That is intended: a description left standing would later offer to replace a hand-tuned query with a rough approximation of it.

It holds the other way round too. Switch a hand-written query into the builder and the builder opens empty. It does not guess what the query says. Nothing is stored until you have actually built something.