Chart data shaping — getting your report into chart shape

Do it

1. Use a numeric field as the argument

   By default the builder offers only text fields for the argument, because the category is almost always a label. But sometimes it isn't — you want to chart by day-of-week number, or a month number, so the order is numeric and correct.

   Try it and you'll get a message: there's no character field, so add one or enable numeric arguments. Do the latter — open Chart Options and check Allow Numeric Argument Fields. The argument dropdown now includes your numeric fields, and you can pick the day-of-week number. (If your data doesn't have a numeric day-of-week column, that's a data-transformation your App Builder can add — covered in Module 3.)

   Why the opt-in instead of just showing everything? On a normal report ninety-five percent of arguments are labels, and listing every numeric column would bury the one field you actually want. The checkbox keeps the common case clean and lets you reach for numbers when you genuinely need them.

2. Add multiple value series — and meet the problem

   Real charts usually plot more than one number. Take a tickets report with status as the argument, and bring in two measures — response time and time open. Now you have three columns: the argument and two values.

   But look at the rows: Open and Closed each appear many times, once per underlying ticket. That's the duplicate-argument tell — the data isn't chart-shaped. A chart built on this would try to draw a bar for every ticket, not one per status.

3. Collapse to one row per argument

   The fix is aggregation. Just as you counted tickets in the basics lesson, here you put an average on each value field — average response time, average time open. The report collapses to one row per status with two aggregated numbers beside it.

   That's chartable data: one row per argument, each value series in its own column. Open the builder, add both value series, and the chart draws cleanly. Which aggregate you choose is a judgment call — count for "how many," sum for totals, average for rates and durations — but the goal is always the same: one number per category.

4. Keep filter fields, but make them non-visible

   Here's the trap that breaks more charts than anything else. You want a field available so users can filter or be prompted on it — say channel or region — but you don't want it on the chart. If you leave it as a visible column, it becomes part of every row's identity, and your one-row-per-status report fractures into one row per status-and-channel. The chart turns into a tangle.

   The answer is to keep the field but set it non-visible. A non-visible field still participates in the report — it can carry a criterion or a runtime prompt, so users can still filter by it — but it's not an output column, so it doesn't split your rows. The filter then applies by the chart's argument: pick a channel in the prompt, and every bar re-aggregates to just that channel's data.

   The rule of thumb: a field is either something you're charting (visible, aggregated) or something you're filtering by (non-visible). Anything visible that's neither the argument nor a value series is almost certainly fragmenting your rows. Runtime prompts on non-visible fields are covered in Criteria and prompts.

5. Add the series — one at a time, all at once, in order

   In the builder's value-series picker you have a choice. Add series one at a time when you have a fixed, known set — response time, then time open — setting each one's color and label as you go. Or pick Select All when your columns are dynamic: if the report grows new columns over time — an August column, then an October column as months roll in — Select All keeps adding them automatically, so the chart grows with the data instead of needing a manual edit each month.

   Order matters for stacked columns. The sequence of your value columns sets the stacking order. If a series is stacking in the wrong place, reset to the grid, drag the columns into the order you want, and rebuild the series — the stack follows the column order.

6. Reshape tall data with Simple Pivot

   Sometimes the values you want to plot aren't in separate columns — they're stacked in one. A finance report often arrives this way: a category, a value type (Actual, Budget, Forecast), and a single amount. That's tall: several rows per category, which won't chart.

   Pivot it. From the Data Prep dropdown, Simple Pivot turns the distinct values of one column into column headers. Pivot value type into columns, with category as the row and amount as the data, and you get the wide shape from Block 1 — one row per category, with Actual / Budget / Forecast as their own columns. Now it's a multi-series chart. Best practice: pivot the field with the fewest distinct values into columns, so you don't end up with a hundred columns. The pivot itself is covered in Data Prep tools; this is the moment it pays off.

7. Flip the orientation — series in columns vs rows

   Once your data is wide, you still get to choose which dimension is the argument and which is the legend, without re-pivoting. In Chart Options, Select Series Type offers Series in columns (the default) and Series in rows.

   With the finance pivot, "series in columns" plots category along the x-axis with Actual / Budget / Forecast in the legend. Switch to "series in rows" and it flips: Actual / Budget / Forecast become the x-axis and the categories become the legend. Same data, two readings — pick whichever answers the question you're asking.