Query Builder guide

Query Builder lets you ask your own questions of the published business model: pick fields, filter, group, calculate, chart, export, save, share and schedule, without writing SQL.

Who: Explorers, Builders, Modelers and Admins, in workspaces where Business Model is turned on. Open it from the Portal: Build → Query Builder (it opens in a new tab). Viewers can't build queries, but they can run saved queries in the Portal.

Query Builder reads the views and cubes your Modelers published in Business Model. It doesn't create dashboards (that's Dashboard builder). The only model change it makes is adding your own file to a view (Admins and Modelers).

Start a query

A new query starts empty, named "Untitled query". Rename it in the box at the top. Use Query from to pick the view to work from.

Ask in plain English

Type a question in the box at the top, for example top 10 stores by net sales last quarter, and select Build query. The AI picks the fields, filters, sort and top N for you, using only fields that exist in the model. A Built from your question line shows what it chose; Undo puts the query back. You can keep editing by hand. (Available when AI assist is on for the workspace.)

Build it by hand

Crosstab

Choose Crosstab to lay results out like a pivot table.

• Place fields in three zones above the grid: Rows (down the side, nested, for example Region › Rep), Columns (across the top, for example Year › Quarter) and Values (as many measures as you like). Drag chips between zones, or use a chip's menu.
• Totals are exact at every level. Each subtotal and the grand total is worked out on its own, so margin %, averages and distinct counts are correct in every subtotal, not summed or averaged from the rows.
• Expand and collapse row and column groups with ▾ / ▸, or use Show rows down to…, Expand all and Collapse all.
• Sort by any column with ⇅ in its header; sorting happens inside each group. Top N per group keeps the first rows of each group and rolls the rest into "Other".
• Show as: % of row, % of column, % of grand total, % of parent row, difference or % change from the previous column, running total across columns. Heatmap colors a value from low to high.
• Values across or down, Row / Column subtotals and Hide empty are under the zones.
• Click any number, totals included, to see the records behind it. Compare adds ▲/▼ badges to every cell.
• Click Run to update a crosstab. Auto-run is off in Crosstab so you can place several fields first; sorting, expanding and show as apply straight away.
• Excel export keeps the shape: merged headers, grouped subtotal rows you can expand and collapse in Excel, and frozen headers.

Column menu

Click a column heading for:

Calculations and formulas

Calculate in the toolbar adds a new column:

• Simple: arithmetic between two measures, or a measure and a number.
• Formula: an expression with measures in square brackets, for example [Net sales] / [Orders]. Click a measure or function to insert it. The line under the box tells you whether the formula is valid.

Formulas are kept in saved queries and scheduled reports.

Save as shared metric (Admins and Modelers): open a calculation and choose Save as shared metric to add it to the Business Model as a measure everyone can use. It goes into the draft; publish in Business Model to share it. Formulas that use measures from more than one table, or the down-the-rows functions, stay in Query Builder.

Whether a measure is a sum or an average is set in Business Model. If you need a different aggregation, ask a Modeler to add that measure.

See the records behind a number

Click a number in the results (List, a crosstab cell, or a bar in a single-series bar chart) to see the rows that make it up, with that row's groups and period and the query's filters applied. Up to 500 rows, largest first.

Explain the result

Select Explain under the results for a short plain-English reading: the total and how it changed (with Compare on), which groups lead, what rose and fell the most, and the trend over time.

• The numbers come straight from your result. The AI only writes the sentences, and if it mentions a number that isn't in the result, you get a plain summary instead. A badge shows which one you're reading, and Based on lists the facts used.
• It explains the rows on screen. Run with All rows first if you're on Preview.
• Healthcare workspaces always get the plain summary; no data is sent to the AI.

Drill and quick filters

Click a value in the List:

• Dates: Drill down to a finer period, Drill up, or Only this period.
• Other fields: Drill down / up along the field's folder (for example Division → Region → District → Store), Filter to this value or Exclude this value.

Prompts

In a filter, turn on Ask when the query runs to make it a prompt: a question, required or optional, one or many values. The date range can prompt too. Anyone opening the saved query answers the prompts first. Copy link shares the query with the answers filled in.

Analyze your own file (CSV or Excel)

Under the field list, Your files lets you upload a .csv or .xlsx file and query it on its own: list, crosstab, chart, filters, totals, calculations, export and schedules all work.

• The file is stand-alone. It doesn't join the business model, and a query uses either a file or a model view, never both.
• The field list shows exactly the file's columns, nothing added. To total or count something, use the menu beside a field to make it a measure: Sum, Average, Minimum or Maximum for number columns, Count or Count distinct for any column. Pick Column to switch it back. Date columns can be grouped by day, week, month, quarter or year. Whole-number date keys like 20231001 in a column named like a date are read as dates, and a Total row at the bottom of the file is skipped.
• Add to a view uses the same setup: measure columns become measures in the view, everything else becomes fields.
• Up to 10 MB and 100,000 rows per file. For bigger or shared data, add it in Connections and model it in Business Model.
• Only you can see your files. Teammates never see them in their file list or in Query from. To share results, save a query and share it (or put it in the Portal): people can open that query, but not browse or pick your file.
• Deleting a file stops every saved query that uses it, including ones you shared.
• View as doesn't apply to files, because they have no tenants.
• Add to a view (Admins and Modelers, the ⇪ button on a file): joins the file to a published view without leaving Query Builder. Pick the view, the field in the view to match on (for example Region or Customer ID), the matching column in your file, and a customer column (or None for data every customer shares, like targets). Click Add and publish: the file's columns are added to the view and published, and Query Builder opens the view with them in the field list. Charts and dashboards on that view can use them too. Works for views on PostgreSQL, Redshift or MySQL connections, up to about 500 KB of data. Adding the same file again replaces it. Your file in Query Builder stays private and unchanged.

Save and share

• Save keeps the query private, or select Share with workspace so others can open it.
• A new saved query also appears in your Portal My content. Move it to a Team content folder, or use Share…, to share it in the Portal.
• Only you can change or delete queries you created.
• Saved queries never keep a View as customer.

Export

Export to Excel (.xlsx, with an About sheet), Save as PDF…, CSV, or Copy as table. With Compare on, exports include the comparison columns.

Export → Add to Dashboard builder… (Builders, Modelers and Admins) turns the query into a chart in Dashboard builder, with its filters and date range. Find it under Charts in Dashboard builder and add it to any dashboard. The message tells you anything that couldn't be carried over, such as calculations or the comparison. The view must be published from Business Model first; files can't be used.

Schedule

Schedule… sends a saved query daily, on weekdays, weekly or monthly, at a time and time zone you choose: by Email (as Excel or CSV), or to a Slack or Microsoft Teams channel (a summary, the first rows and a link). An Admin adds Slack and Teams channels in Operator console → Deliveries.

• Email recipients must be members of the workspace.
• Send: Every time, Only when the results change, or Only when a value crosses a threshold (for example when any row's margin is below 0). Skipped runs show on the Deliveries page; Send now always sends.
• Send now sends it right away; you can turn a schedule off or delete it.
• Schedules send the last saved version. A schedule stops if its creator leaves the workspace, or a shared query stops being shared.
• If email isn't set up for your workspace, the dialog says so.
• Did it send? Reopen Schedule…: each schedule shows Last run with the time and result. A failed run shows the reason in red. Send now shows the reason too if it fails.
• Healthcare workspaces: the email has no file and no data, only a sign-in link that opens the query with the schedule's answers. A private query can only be scheduled to yourself; share it first to send it to teammates.

When something's off

Related

• Portal guide: opening and organizing dashboards and queries.
• Business Model guide: how the fields you query are defined (Modelers and Admins).
• Embedding Query Builder in your own product for your customers: Developers → Mint embed sessions.