Ignition Perspective XY Chart Grouping Belongs in the Query

The Perspective XY Chart has no group-by stage. It draws one point or column per row in the bound data source. To show target and actual per date, line, and shift, do two things before the data reaches the chart. First, aggregate in the named query with GROUP BY on those three fields. Second, give each resulting row a single category value the X axis can plot. Axis and series settings only control how those prepared rows are drawn.

Why the XY Chart Needs Pre-Grouped Rows

The Perspective XY Chart is built on a category/value charting engine. A category X axis reads one field from each row and places one slot per distinct value. Each series reads one numeric field from the same rows.

Three consequences follow for a date/line/shift layout:

• Raw rows are not summed. If the named query returns raw production records, the chart does not total them. Several records with the same category either overlap or produce repeated slots.
• Only one field defines the category. The axis cannot nest date → line → shift on its own. Combine the three values into one key, or move one dimension into series or separate charts.
• Order comes from the rows. Categories appear in the order the rows arrive, so the query or transform controls sort order.

Three Layouts for Date, Line, and Shift

Three layouts work with a single named query. They differ in where each grouping dimension ends up.

Recommendation: use Layout A.

• The series definitions never change, so the chart configuration stays static.
• Adding a line or a shift only adds rows.
• Target and actual sit side by side for each shift, which matches the stated goal.

Move to Layout C only if the category labels become unreadable. For C, drive it with a line selector parameter or a Flex Repeater of charts. Avoid Layout B unless you are prepared to rebuild the series array by script every time the line list changes.

Named Query Aggregation

Prerequisite: know which column holds the production timestamp, and whether any shift crosses midnight. Column names below are placeholders; substitute your own.

1. Add a parameterized date range to the named query. Use a start and end date so the chart never pulls the full history.
   Confirm: the query runs in the Designer's named query test pane with fixed dates.
2. Aggregate on the three grouping fields and order explicitly.

   SELECT
       CAST(prod_ts AS DATE) AS prod_day,
       line,
       shift,
       SUM(target) AS target,
       SUM(actual) AS actual
   FROM production_log
   WHERE prod_ts >= :startDate AND prod_ts < :endDate
   GROUP BY CAST(prod_ts AS DATE), line, shift
   ORDER BY prod_day, line, shift

   • Date truncation syntax varies by database, so adapt the CAST to your SQL dialect.
   • If target is a per-shift setpoint rather than an additive quantity, use MAX(target) instead of SUM(target).
   Confirm: the result has exactly one row per date/line/shift, with no duplicates.
3. Handle shifts that span midnight. Group on a production date, not the calendar date. For example, if a night shift starts one evening and ends the next morning, compute the production day in SQL with a CASE on shift and hour. Otherwise one shift splits across two dates.
   Confirm: each production day shows each shift once.

Binding, Transform, and Axis Configuration

Prerequisite: the named query returns clean grouped rows.

1. Bind the query to the chart. Create a Named Query binding on a key under the chart's dataSources property. Pass the start and end dates from view parameters or date pickers.
   Confirm: the binding preview shows the grouped dataset.
2. Add a script transform that builds the composite category.

   def transform(self, value, quality, timestamp):
       rows = []
       for r in system.dataset.toPyDataSet(value):
           day = system.date.format(r["prod_day"], "yyyy-MM-dd")
           rows.append({
               "category": "%s | %s | %s" % (day, r["line"], r["shift"]),
               "target": r["target"],
               "actual": r["actual"]
           })
       return rows

   • Format the date as a string. A raw date object on a category axis renders as a long timestamp or epoch value.
   • Building the key here, rather than in SQL, keeps the query portable across databases.
   Confirm: the transform output shows a list of objects, each with a readable category string.
3. Configure the X axis. In xAxes, set the axis render type to category and point its data field at category.
   Confirm: one slot appears per grouped row.
4. Configure the Y axis. In yAxes, use a single value axis for both measures.
   Confirm: target and actual share one scale.
5. Configure the two series. In series, define two entries on the same data source key and the same X field. Set Y to actual and target respectively.
   • Render actual as columns.
   • Render target either as a second column series, which clusters beside actual, or as a line with bullets.
   Confirm: each category shows both values, and tooltips match the query row.

Recurring Faults on Grouped XY Charts

Checks Against the Named Query Totals

1. Row count. Run the named query in the test pane for a known date range and note the row count. The chart must show the same number of X categories.
2. Distinct keys. Compare distinct category strings in the transform output with the row count. Equal counts mean no duplicate keys.
3. Values against source. Hover over three categories: the first, one night shift, and the last. Compare tooltip target and actual values with the query rows.
4. Raw-data cross-check. Change the date range parameters and confirm the chart refreshes. Then sum actual for one line and shift directly from the raw table for one date. The result must match the plotted column exactly.

FAQ