--- title: "Query report data" method: POST path: "/datainsights/v1.1/reports/query/data" tags: ["Reports"] --- # Query report data `POST /datainsights/v1.1/reports/query/data` ## Request - The document declares no server URL. - Auth: HTTP bearer Query report data from a report that is not created Mode: - Preview: 100 Records - Run: 12000 Records Based on the report `type` sent in the request, the data structure returned may have multiple formats. ## Types #### List ``` { ... "columns": ... "settings": { ... "totals": false, "transpose": false, } } ``` #### List (Totals) ``` { ... "columns": ... "settings": { ... "totals": true, "transpose": false, } } ``` #### PeriodList ``` { ... "columns": ... "periods": ... "settings": { ... "totals": false, "transpose": true|false, } } ``` #### Summary ``` { ... "columns": ... "group_rows": ... "settings": { ... "totals": false, "transpose": true|false, } } ``` #### Summary (Totals) ``` { ... "columns": ... "group_rows": ... "settings": { ... "totals": true "transpose: true|false, } } ``` #### PeriodSummary ``` { ... "columns": ... "group_rows": ... "periods": ... "settings": { ... "totals": false, "transpose": true|false, } } ``` #### Pivot ``` { ... "columns": ... "group_rows": ... "group_columns": ... "settings": { ... "totals": false, "transpose": true|false } } ``` #### Pivot (Totals) ``` { ... "columns": ... "group_rows": ... "group_columns": ... "settings": { ... "totals": true, "transpose": true|false, } } ``` ## Data | Type | Headers | Index | Records | Group Rows | Group Columns | Totals | Periods | Transpose | | -------------------- | ------- | ------ | ------- | ---------- | ------------- | ------ | ------- | --------- | | **List** | Yes | No | Yes | No | No | No | No | No | | **List (Totals)** | Yes | No | Yes | No | No | Yes | No | No | | **PeriodList** | Yes | Yes | Yes | No | No | No | Yes | Yes | | **Summary** | Yes | Yes | Yes | Yes | No | No | No | Yes | | **Summary (Details)** | Yes | Yes | Yes | Yes | No | Yes | No | No | | **Summary (Totals)** | Yes | Yes | Yes | Yes | No | Yes | No | Yes | | **PeriodSummary** | Yes | Yes | Yes | Yes | No | No | Yes | Yes | | **Pivot** | Yes | Yes | Yes | Yes | Yes | No | No | Yes | | **Pivot (Totals)** | Yes | Yes | Yes | Yes | Yes | Yes | No | Yes | ## Query parameters - `format` 'raw' | 'formatted' - `mode` 'Preview' | 'Run', required ## Headers - `X-PROPERTY-ID` integer, required ## Request body - ReportQuery - `property_ids` integer[], required: Report property ids - `dataset_id` 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9, required: Dataset id - `columns` ReportColumns[], required: Report columns - `cdf` ReportCDF, required - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `metrics` string[]: Report column metrics - `group_rows` ReportGroup[], nullable: Report group rows - `cdf` ReportCDF, required - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `modifier` 'second' | 'minute' | 'hour' | 'day' | 'month' | 'year' | 'week' | 'weekday' | 'd' | 'm' | 'time_hour' | 'time_minute' | 'time_second': Report group modifier - `group_columns` ReportGroup[], nullable: Report group columns - `cdf` ReportCDF, required - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `modifier` 'second' | 'minute' | 'hour' | 'day' | 'month' | 'year' | 'week' | 'weekday' | 'd' | 'm' | 'time_hour' | 'time_minute' | 'time_second': Report group modifier - `custom_cdfs` ReportCustomCdf[], nullable: Report custom cdfs - `id` integer: Report custom cdf id - `column` string: Report custom cdf column - `name` string, required: Report custom cdf name - `description` string, nullable: Report custom cdf description - `formula` CustomCdfFormula[], required - `kind` 'cdf' | 'separator' | 'operator' | 'operand' | 'parenthesis', required - `value` string, required: - If the kind is *cdf* the value must be a valid cdf. - If the kind is *separator* the supported values are any. - If the kind is *operator* the supported values are: ['+', '-', '*', '/']. - If the kind is *operand* the supported values are numbers. - If the kind is *parenthesis* the supported values are: ( or ). - `metric` 'sum' | 'mean' | 'max' | 'min' | 'count' | 'std' | 'var' | 'null', nullable - `kind` 'String' | 'Number' | 'Dynamic', required: Report custom cdf kind - `created_at` string: Report custom cdf created at - `updated_at` string: Report custom cdf updated at - `format` 'number' | 'currency' | 'percentage' | 'null', nullable: Custom Cdf Numeric format (valid for Number and Dynamic only) - `filters` ReportFilters - `and` ReportFilter[] - `and` ReportFilter[] - `or` ReportFilter[] - `cdf` ReportCDF - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `operator` 'begins' | 'not_begins' | 'ends' | 'not_ends' | 'contains' | 'not_contains' | 'list_contains' | 'not_list_contains' | 'is_null' | 'is_not_null' | 'is_empty' | 'is_not_empty' | 'equals' | 'not_equals' | 'greater_than' | 'less_than' | 'less_than_or_equal' | 'greater_than_or_equal': Filter operator value - `value` unknown - `or` ReportFilter[] - `and` ReportFilter[] - `or` ReportFilter[] - `cdf` ReportCDF - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `operator` 'begins' | 'not_begins' | 'ends' | 'not_ends' | 'contains' | 'not_contains' | 'list_contains' | 'not_list_contains' | 'is_null' | 'is_not_null' | 'is_empty' | 'is_not_empty' | 'equals' | 'not_equals' | 'greater_than' | 'less_than' | 'less_than_or_equal' | 'greater_than_or_equal': Filter operator value - `value` unknown - `sort` ReportSort[], nullable: Report sort - `cdf` ReportCDF, required - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `direction` 'asc' | 'desc', required: Report sort direction - `settings` ReportSettings - `details` boolean, required: Report details - `totals` boolean, required: Report totals - `transpose` boolean: Transpose Report - `periods` ReportPeriod[], nullable: Periods to compare - `cdf` ReportCDF, required - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `name` string, required: Label for a period - `id` string: machine name of period (auto-generated) - `start` string, required: The beginning date or relative date of a period. When both start and end dates are relative dates, the difference between today and the end date is subtracted from the start date. - `end` string, required: The beginning date or relative date of a period - `start_relative_to_end` boolean: If true, relative start date will be relative to the end date instead of today,e.g. if end date is 2023-01-01, a start date of yesterday would be 2022-12-31 - `formats` ReportFormats - `date` 'YYYY-MM-DD' | 'YYYY.MM.DD' | 'YYYY/MM/DD' | 'YYYY-DD-MM' | 'YYYY.DD.MM' | 'YYYY/DD/MM' | 'MM-YYYY-DD' | 'MM.YYYY.DD' | 'MM/YYYY/DD' | 'MM-DD-YYYY' | 'MM.DD.YYYY' | 'MM/DD/YYYY' | 'DD-YYYY-MM' | 'DD.YYYY.MM' | 'DD/YYYY/MM' | 'DD-MM-YYYY' | 'DD.MM.YYYY' | 'DD/MM/YYYY' | 'null', nullable: Date Format that will be applied in the export. The default is YYYY-MM-DD - `comparisons` ReportComparison[], nullable: A list of up to 5 Comparisons, each containing 'name' and 'filters' properties. The 'filters' are the same structure as those in report.filters. - `name` string, required: Label for a Comparison - `id` string: machine name of comparison (auto-generated) - `filters` ReportComparisonFilters - `and` ReportFilter[] - `and` ReportFilter[] - `or` ReportFilter[] - `cdf` ReportCDF - `type` 'default' | 'custom': Report cdf type - `column` string, required: Report cdf name - `multi_level_id` 1 | 2 | 3 | 4: Multi-level where CDF is located - `operator` 'begins' | 'not_begins' | 'ends' | 'not_ends' | 'contains' | 'not_contains' | 'list_contains' | 'not_list_contains' | 'is_null' | 'is_not_null' | 'is_empty' | 'is_not_empty' | 'equals' | 'not_equals' | 'greater_than' | 'less_than' | 'less_than_or_equal' | 'greater_than_or_equal': Filter operator value - `value` unknown ## Response `200` OK - ReportData - `headers` unknown[], required: Column headers that list columns names. The order of headers is the same as those specified in the request through the columns property. The number of headers is the number of columns. - unknown - `index` array[], required: List of index distinct names - string[] - `group_rows` string[], required: List of CDF names of group rows - `group_columns` string[], required: List of CDF names of group columns - `periods` string[]: List of names of the periods being compared - `records` object, required: Dictionary of records of column value combinations and metric values in the report - `subtotals` object, required: Dictionary of subtotals in the report - `totals` object, required: Dictionary of totals in the report - `type` string, required: Type of the report generated - `comparisons` string[]: List of names of the comparisons being made - `aggregated_count` integer: Number of rows included in aggregations in the report ## Other responses - `400`: Bad Request - `401`: Unauthorized - `500`: Internal Server Error --- [API](https://skmtc.dev/cloudbeds/apis/data-insights-api.md) - [All operations](https://skmtc.dev/cloudbeds/apis/data-insights-api/llms.txt) - [OpenAPI document](https://skmtc.dev/cloudbeds/apis/data-insights-api/revisions/0a16807149c2?raw)