Query report data
- OpenAPI 3.1.0 document: https://api.rollstack.com/public/v1/openapi.json
- The whole reference in Markdown: https://docs.rollstack.com/llms-full.txt
- An index of the reference: https://docs.rollstack.com/llms.txt
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 itsidfromlistReportsorcreateReport. Only reports whosetypeisSlideDeckwork here: another type, such as a Google Slides presentation, returns400. 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 (SELECTorWITH). Its tables are the report's datasheets, named by theiridfromlistReportDatasheetsin double quotes, as inSELECT region, SUM(revenue) AS revenue FROM "<id>" GROUP BY region. A column is a number when every value is one, else text, dates included:CASTan ISO date (YYYY-MM-DD) toDATEbeforeyear(),date_trunc()or date arithmetic. Give every computed column a name withAS.
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 inrows.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 becomesColumn N, after the column's position, and a repeated one gets a_2,_3suffix.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 ofname, unless a table renames the column. Omitted when the column has none.type(string, required): What the column's cells contain:numberfor numbers,datefor dates and times,stringfor text, andbooleanfortrueorfalse. Any cell can also benullwhen it is empty. One ofnumber,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 mostlimitof them. Each row is an array of cells in the order ofcolumns. An empty cell isnull, and adatecell 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:rowFieldsare the columns its rows are grouped by, outermost first, andlevelshas one number per row inrows, 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, and0is 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 thanlimit, sorowsdoesn't contain all of them. Raiselimitto get more, up to the first1000rows.