Skip to main content

Query report data

POST https://api.rollstack.com/public/v1/reports/{id}/data/query

Runs one read-only SQL query over the tables of a report's datasheets, to check a figure before it goes on a slide or to explore data before shaping it. It reads the copies stored at the sources' last reads, never a source, and saves nothing: a datasheet's table is the one getReportDatasheetData answers. Only the report's editors may. At most 200 rows and 30 seconds a query. Your queries run one at a time, together with your previewReportDatasheetChanges and saveReportDatasheetChanges calls and the renderReportSlides calls that carry datasheetChanges: send the next only once this one answers, never several at once, and check several figures in one query, as columns of one row or rows of a UNION ALL. At most 30 queries and previewReportDatasheetChanges calls a minute per user, counted together.

Requires the slide_decks:write scope.

Operation ID: queryReportData.

Path parameters

  • id (string, required): The report to act on, by its id from listReports or createReport. Only reports whose type is SlideDeck work here: another type, such as a Google Slides presentation, returns 400. You can also copy it from the report's link in Rollstack: it's the part right after /slides/.

Headers

  • Idempotency-Key (string): A unique value, such as a UUID, that makes retrying this call safe. A retry with the same key within 24 hours answers the first call's result instead of running it again. Use a new key for each new call. From 1 to 255 printable ASCII characters.

Request body, required

application/json

  • sql (string, required): One read-only DuckDB query (SELECT or WITH). Its tables are the report's datasheets, named by their id from listReportDatasheets in double quotes, as in SELECT region, SUM(revenue) AS revenue FROM "<id>" GROUP BY region. A column is a number when every value is one, else text, dates included: CAST an ISO date (YYYY-MM-DD) to DATE before year(), date_trunc() or date arithmetic. Give every computed column a name with AS.

Response 200

Success

application/json

  • columns (array of object, required): The columns of the table, in the same order as the cells of each row in rows.
    • name (string, required): The column's name in the datasheet's table, which the report's charts and tables use to refer to it. Unless the datasheet renames it, a column read from an uploaded file or a spreadsheet range is named after its header; a blank header becomes Column N, after the column's position, and a repeated one gets a _2, _3 suffix.
    • label (string): The column's display name, when its source gives one, such as a field's label in Looker or Metabase. Charts and tables drawn from the datasheet show it instead of name, unless a table renames the column. Omitted when the column has none.
    • type (string, required): What the column's cells contain: number for numbers, date for dates and times, string for text, and boolean for true or false. Any cell can also be null when it is empty. One of number, date, string, boolean.
  • rows (array of array of string or number or boolean or null, required): The rows of the table, starting from the first, at most limit of them. Each row is an array of cells in the order of columns. An empty cell is null, and a date cell is an ISO 8601 string in UTC, at midnight when the source gives a day without a time.
  • totalRow (true)
  • subtotals (object): When the datasheet's query adds subtotals: rowFields are the columns its rows are grouped by, outermost first, and levels has one number per row in rows, how many of those columns the row is grouped by. A row grouped by fewer than all of them is a subtotal above its group's rows, and 0 is the total row. Charts leave those rows out. Omitted without subtotals.
    • rowFields (array of string, required)
    • levels (array of integer, required)
  • truncated (boolean, required): Whether the table has more rows than limit, so rows doesn't contain all of them. Raise limit to get more, up to the first 1000 rows.