Why Does a FactoryPMI Pie Chart Fail on Empty Data?

FactoryPMI can throw a null pointer error during changeover when the pie chart receives an empty dataset. Return a fixed two-row dataset for every order: row 0 represents scrap (n) and row 1 represents shippable weight (y), with zero substituted for a missing category.

Reject the quick fixes and define the result

Do not insert dummy records into CurrentIndexDetail. The table is read-only for this application, and its data is replaced by an Access-to-SQL transfer. A temporary insert also mixes display behavior with production data and can corrupt weight totals if cleanup fails.

Do not branch on COUNT(*). That count measures detail records, not the number of status categories. Ten records can all have Ship='n', while two records can contain both n and y. Neither count tells the chart which category is missing.

COUNT(DISTINCT Ship) is closer, but it ignores SQL NULL values and does not impose the required chart order. A pie chart whose colors follow dataset position needs an explicit output contract:

Check before continuing: write down the two-row contract and confirm that red is assigned to dataset row 0 and green to row 1.

Bind the current order and line

Read the current identifiers from the existing FactoryPMI bindings. Apply both values to every lookup; otherwise, records from another line or order can enter the totals during a changeover.

DECLARE @order AS int, @line AS int;
SET @order = {Root Container.cntStatus.Container.L1_OrderNum};
SET @line = {Root Container.cntStatus.Container.L1_OrderLineNum};

A changeover can briefly expose an order and line combination that has no matching detail rows. Treat that as a valid process state, not as a query failure. The result still needs two rows with zero weights.

Check before continuing: run the filter for the active @order and @line. Confirm that it selects only the intended order, including when it selects no rows.

Inspect and classify the status values

Check the source values before changing the chart. The recorded status can be lowercase, uppercase, blank, NULL, or another value. Normalize case and surrounding spaces for aggregation, but keep undefined statuses visible in a diagnostic query.

SELECT
    CASE
        WHEN Ship IS NULL THEN 'NULL'
        WHEN LTRIM(RTRIM(Ship)) = '' THEN 'BLANK'
        WHEN LOWER(LTRIM(RTRIM(Ship))) = 'n' THEN 'SCRAP'
        WHEN LOWER(LTRIM(RTRIM(Ship))) = 'y' THEN 'SHIPPABLE'
        ELSE 'OTHER'
    END AS status_class,
    COUNT(*) AS row_count,
    SUM(EstimatedWeight) AS total_weight
FROM CurrentIndexDetail
WHERE OrderNumber = @order
  AND OrderLineNumber = @line
GROUP BY
    CASE
        WHEN Ship IS NULL THEN 'NULL'
        WHEN LTRIM(RTRIM(Ship)) = '' THEN 'BLANK'
        WHEN LOWER(LTRIM(RTRIM(Ship))) = 'n' THEN 'SCRAP'
        WHEN LOWER(LTRIM(RTRIM(Ship))) = 'y' THEN 'SHIPPABLE'
        ELSE 'OTHER'
    END;

The production query below excludes blank, NULL, and other statuses from the two defined slices. That preserves the meaning of scrap versus shippable weight. If the plant requires undefined weight to count as scrap, obtain that rule from the process owner before changing the classification.

Check before continuing: account for every returned status class and resolve any unexplained BLANK, NULL, or OTHER weight.

Build two rows before joining the totals

Create the required status rows inside the query, aggregate the real records, and left-join the totals onto the required rows. This works for empty orders, one-category orders, and normal two-category orders without writing to the source table.

DECLARE @order AS int, @line AS int;
SET @order = {Root Container.cntStatus.Container.L1_OrderNum};
SET @line = {Root Container.cntStatus.Container.L1_OrderLineNum};

;WITH RequiredStatus AS
(
    SELECT sort_order, ship
    FROM (VALUES (0, 'n'), (1, 'y')) AS v(sort_order, ship)
),
Totals AS
(
    SELECT
        LOWER(LTRIM(RTRIM(Ship))) AS ship,
        SUM(EstimatedWeight) AS val
    FROM CurrentIndexDetail
    WHERE OrderNumber = @order
      AND OrderLineNumber = @line
      AND LOWER(LTRIM(RTRIM(Ship))) IN ('n', 'y')
    GROUP BY LOWER(LTRIM(RTRIM(Ship)))
)
SELECT
    r.ship,
    COALESCE(t.val, 0) AS val
FROM RequiredStatus AS r
LEFT JOIN Totals AS t
    ON t.ship = r.ship
ORDER BY r.sort_order;

The VALUES constructor supplies exactly one n row and one y row. COALESCE converts a missing aggregate, or an aggregate containing only null weights, to zero. The numeric sort_order controls position directly; alphabetical sorting is not a safe substitute. In particular, ORDER BY Ship DESC places y before n, which reverses the stated red/green row contract.

A plain UNION can add a missing row, but unconditional dummy rows can collide with real categories and require extra grouping. The fixed-domain left join expresses the requirement without count branches.

Check before continuing: confirm that the query always returns exactly two rows, with n first and y second.

Bind the chart without changing the data

1. Use the query result as the pie chart dataset.
2. Bind the category field to ship and the numeric field to val.
3. Assign red to the first slice and green to the second slice.
4. If scripting controls the palette, supply the sequence of Color objects in that same order.

Keep the status key in the dataset even if the displayed legend uses friendlier text such as Scrap and Shippable. The key gives maintenance a direct way to verify which source category produced each slice.

Two zero-valued rows prevent an empty dataset, but an all-zero pie has no meaningful angular extent. If the component still cannot draw that state, display a separate no-production indication or use the chart's supported empty-state behavior. Do not add nonzero fake weight merely to force a visible slice; that changes the displayed percentage.

Check before continuing: inspect the bound dataset in FactoryPMI and verify its field names, numeric value type, row count, and row order.

Prove the result through a full changeover

1. Record the grouped SQL totals for the current order and line.
2. Compare them with the two dataset values.
3. Start a changeover and watch the dataset as the old order disappears and the new order begins.
4. Confirm that row order and colors remain fixed throughout the transition.
5. Refresh after the Access transfer and confirm that the query has made no source-table changes.

Get production running with the fixed dataset, then trace recurring undefined statuses back to the system that writes Ship. The chart query can expose or exclude bad states, but it should not hide a data-quality fault.

FAQ