Report formulas — calculations, aggregates, and cross-field references

Do it

1. The simplest formula isn't a formula — just change the Data Type

   Before reaching for any SQL function, know that the most common Composer "formula" is no formula at all. Open the action menu on a date column → Add formula. Leave the ? in the Formula box exactly as it is. Just change the Data Type dropdown:

   • dat → doz — keep the timestamp, but skip the browser time-zone conversion so every user sees the same wall-clock time.
   • dat → dpo — date-part only, browser-local. "What calendar day was this, in my time zone?"
   • dat → dtz — date-part only, no conversion. Everyone sees the same calendar day.

   Click Save & Apply. The same date now renders the way you want, no SQL written.

   The same shortcut works for any numeric column:

   • Money column → set Data Type to cur. Dollar sign renders automatically.
   • Percentage column (already expressed as a percent, e.g. 75.5 for 75.5%) → per. Percent symbol renders automatically.
   • Numeric field that's stored as text and won't aggregate → num. Sum and Average reappear in the menu.

   This is the tier-3 / Composer-only version of the data-type override pattern. The change only affects this report. If every Composer is doing this same override across multiple reports, that's the signal to ask your App Builder to bake it in at the source — covered in Module 3 — formula fields. One change in App Builder eliminates the override in every report forever.

   The full Data Type cheat sheet with time-zone behavior for each code lives in the Module 3 lesson.

2. Open Add Formula and meet the function-type menu

   On any field card, open the action menu (three dots) and click Add formula. A sidebar opens with two controls:

   • Data Type — defaults to the card's existing type. Change it to match whatever your formula will return.
   • Formula — a textarea. The ? token is pre-loaded as a starting point because most formulas operate on "this card's field."

   Type @ in the Formula box (or click the function icon next to it) and the Select Function Type dropdown opens — Aggregate, String, DateTime, Math, Other, Insert Report Field. Pick a category to see the available functions for your database; pick a function to insert its template into the formula. Functions arrive with placeholders you replace (? for the current field, descriptive snake_case names like decimal_places, number_of_chars, or [insert_field] for additional arguments — each name describes what to put there).

   When the formula reads the way you want, click Save & Apply. The sidebar closes, the preview updates, and the card now shows the formula's result instead of the raw field.

3. Aggregates with ? — count, sum, average

   The most common Composer formula by a wide margin is an aggregate. Three you'll use constantly:

   • COUNT(?) — counts non-null rows. Works on any field type. Set Data Type to num.
   • SUM(?) — totals a numeric column. Data Type num.
   • AVG(?) — averages a numeric column, ignoring nulls. Data Type num.

   Quick-look-at-row-count trick: add any field (a ticket ID, a primary key, whatever), apply COUNT(?), and the preview tells you immediately how many rows your report has — 72,000? 495? — before you commit to running View Full Document.

   The "count by" pattern is where aggregates earn their keep. Drop a categorical field (status, customer, channel) into Selected Data and a counted field with COUNT(?). The preview rebuilds as a grouped result: counts per category. Want to see "tickets per customer per status"? Add both as categorical columns plus a counted one — the grouping cascades.

   One important gotcha lives here, covered in step 3.

4. The non-visible-column pattern

   Sometimes you want to filter on a column without showing it. Classic case: "tickets per customer, but only open ones" — you need the status field for the criterion, but you don't want a Status column in the output because every row would just say "Open."

   Open the action menu on the column you want to hide. The Visible checkbox at the top is on by default — uncheck it. The column disappears from the preview but stays in the Selected Data pane (it gets a small "non-visible" marker so you can find it). You can add criteria to it normally; the filtering takes effect, the column just doesn't render.

   This pattern is what makes count-by reports work cleanly. Without it, you'd have a redundant "Open" column appearing next to every customer's count. With it, the grid reads naturally.

   The non-visible field stays in your data flow, so anything else you do — criteria, sorts, joins — works as normal. The only thing that changes is the rendering.

5. Date extraction with the DateTime menu

   Open Add Formula on a date column. Type @ → DateTime. The submenu lists what's available for your DB — month name, month number, day of week (name and number), hour, quarter, year, day of month, and the current-date functions. Pick "month name" (or your DB's equivalent — MSSQL labels it Datetime to month name) and Composer inserts the template. Set Data Type to cha (it returns text), Save & Apply.

   The preview now shows month names (May, June, July) instead of the original timestamp.

   Sort behavior gotcha. Sorting by a month-name column sorts alphabetically — Apr, Aug, Dec, Feb… — not chronologically. The fix is to add the month-number formula (returns num) on a second card, sort by that one instead, and make it non-visible (step 3 pattern) so the output still shows just month names. You get chronological order with readable labels.

   The same trick generalizes: any time you need to display a derived label but sort by a different derived value, build both and use non-visible to hide the sort key.

6. Cross-field references with @ — Insert Report Field

   Up to this point every formula has been about one field. Real reports need to combine fields: subtract two dates, divide one number by another, concatenate first + last name. That's where the @ token earns its keep.

   In the Formula box, type @. The dropdown opens. Scroll to Insert Report Field at the bottom, or click it directly. A field picker appears listing every field in your report. Pick the one you want to reference. Composer inserts the alias-qualified reference — [svc_8066].[ticket_open_time] on MSSQL, "svc_8066"."ticket_open_time" on Postgres — exactly as your DB expects it.

   Two common patterns:

   • Arithmetic between two numbers. ? - [svc_8066].[discount] on a price card subtracts the discount from the price. The same shape works for +, *, /.
   • String combine. CONCAT(?, ' ', [svc_8066].[last_name]) on a first-name card produces "Jane Doe" as a single column.

   For divisions specifically, plan for nulls and zeros — see Block 4 and the SQL formula reference. Integer division also catches new users: 5 / 2 on integer columns returns 2 in most databases, not 2.5. The cast-to-decimal recipe is in the reference page.

7. CASE for bucketing values

   When you need to group raw values into broader categories — three grocery-store customers all rolling up to "Grocery", everyone else to "Other" — that's a CASE WHEN expression.

   Open Add Formula on the field you want to bucket (the customer column, in this example). The template looks like:

   CASE WHEN ? IN ('Shoplite Grocery', 'Genesis Groceries') THEN 'Grocery' ELSE 'Other' END

   Set Data Type to cha (it returns text). Save & Apply. Every row that matched the IN list now shows "Grocery"; everything else shows "Other." Pair with COUNT(?) on a different card and you have a two-row report: Grocery 495, Other 8200.

   CASE generalizes to any bucketing. Score ranges → letter grades. Amounts → "low/medium/high." Statuses → "open/closed/other." The pattern stays the same; only the WHEN conditions change.

   If you don't know CASE syntax for your DB offhand, ask an AI tool — see Block 4. The function-type menu's Other submenu also has a CASE template you can use as a starting point.

8. When to push a formula up to the App Builder

   Composer formulas live on one report — written here, used here, invisible to anyone else. That's the right home for one-off calculations and exploratory work. But if you find yourself writing the same formula across three reports, or you know other Composers will need it, the formula belongs in the app instead.

   Talk to your App Builder. The same SQL works in App Builder's SQL Name field — with one adjustment: replace any ? tokens with explicit [alias].[field] references (use Insert Report Field to discover the exact references first, then copy them into the App Builder side). App Builder's formula field has no card context, so ? doesn't resolve there. The Module 3 lesson Formula fields covers the full App Builder workflow.

   Rule of thumb: one or two reports → Composer formula. Three or more, or a formula your team will keep needing → push it to the app. Promotion costs five minutes; recurring duplication costs hours.