Public API v1 → Query Endpoint

About the /query endpoint

The /query endpoint queries a single business view into a listing table, aggregated table, or Pivot table, enabling other systems to query and consume Incorta business views.

Any tenant user with a valid access token can use this endpoint to query a business view.

For information about how to get started with Public API v1, refer to References → Public API v1.

Note

You must use the HTTPS scheme to be able to successfully run the API endpoints.

Important

This endpoint simulates creating a table insight referencing a business schema view. The response of this endpoint is either a CSV or JSON file.

Request URL

https://<cluster_URL>/incorta/api/v1/query

HTTP method

POST

Request headers

{
"Content-Type": "application/json",
"Accept": "application/json",
"Authorization": "Bearer {access_token}"
}

You must use a valid access token of a tenant user.

For information about how to create an access token, refer to Public API v1 → Create Token Endpoint.

Request body

{
"stringify": boolean,
"username": "string",
"query": {
"aggregate": boolean,
"ensureParentSubtotal": boolean,
"skipSelf": boolean,
"sortWithinGroups": boolean,
"sampled": boolean,
"formatted": boolean,
"startRow": number,
"pageSize": number,
"format": "string",
"auditingInfo": {
"dashboardGuid": "string",
"insightGuid": "string",
"dashboardLayoutGuid": "string",
"dashboardName": "string",
"insightName": "string",
"dashboardLayoutName": "string"
},
"filters": [
{"type": "string",
"label": "string",
"fieldKey": "string",
"formulaKey": "string",
"value": {
"values": [
"string"],
"op": "string",
"options": {
"caseSensitive": boolean,
"level": number,
"depth": number}},
"prompt": boolean}
],
"aggregateFilters": [
{"field": "string",
"formula": "string",
"op": "string",
"values": [
"string"],
"aggregation": "string"}
],
"distinctFilter": {
"fields": [
{"label": "string",
"name": "string"}]
},
"sorting": [
{"field": "string",
"label": "string",
"formula": "string",
"dir": "string"}
],
"measures": [
{"field": "string",
"formula": "string",
"label": "string",
"formats": [
{"format": "string",
"type": "string"}],
"sourceField": "string",
"aggregation": "string",
"scale": "string",
"filters": [
{"type": "string",
"label": "string",
"fieldKey": "string",
"formulaKey": "string",
"value": {
"values": [
"string"],
"op": "string",
"options": {
"caseSensitive": boolean,
"level": number,
"depth": number}},
"prompt": boolean}]}
],
"rows": [
{"field": "string",
"formula": "string",
"label": "string",
"formats": [
{"format": "string",
"type": "string"}],
"sorting": [
{"field": "string",
"label": "string",
"formula": "string",
"dir": "string"}],
"showEmptyGroups": boolean,
"datePart": boolean,
"subTotal": boolean,
"hierarchy": {
"level": number,
"excludeParent": boolean,
"attributes": [
{"label": "string",
"name": "string",
"formats": [
{"format": "string",
"type": "string"}]}]}}
],
"columns": [
{"field": "string",
"formula": "string",
"label": "string",
"formats": [
{"format": "string",
"type": "string"}],
"sorting": [
{"field": "string",
"label": "string",
"formula": "string",
"dir": "string"}],
"datePart": boolean}
]}}
Note
  • In 2023.4.0, you must add a value or an empty string to the label parameter when defining a formula as a measure, row, column, measure filter, or individual filter. However, starting with 2023.4.1, you don’t need to add a label as the endpoint will use "New Formula" as a default header.

  • The the formatted and formats properties are available starting with 2023.4.1 to specify format options for dates and numbers in measures, rows, columns, and hierarchy attributes.

Request body parameters

The following table describes the high-level parameters of the request body.

ParameterTypeDescriptionRequired
queryObjectThe parameters of the query object.
For details, see Query object.
Required
stringifyBoolean
  ●  Set to true, which is the default value, to return the response as a string following the JSON notation.
  ●  Set to false to return the response as a JSON object or CSV according to the format specified in the Query object.
Optional
usernameStringSpecify the login name of the user account that you want to use (impersonate) to run the query.
Use the following format: "base64.encode(loginName)"
Optional

Query object

The following table describes the sub-objects and arrays in the query object in the request body.

ParameterTypeDescriptionRequired
aggregateBoolean
  ●  Set to true to create a query for an aggregated table or a Pivot table.
  ●  Set to false to create a query for a listing table.
The default is false.
Optional
ensureParentSubtotalBooleanSet to true to create an aggregated table or a hierarchy table.
This simulates the Merge Columns option in the insight settings.
Optional
skipSelfBooleanIn hierarchy queries, set to true to skip counting the self-node to be accounted for in pagination.Optional
sortWithinGroupsBooleanSpecify if you want to sort the subcategories within groups.
Applicable to aggregate tables only.
Optional
sampledBooleanSpecify if you want to use sampled data to create the response file or to use the whole data setOptional
formattedBooleanSpecify if you want to apply the format options specified for measures, rows, columns, and hierarchy attributes. If you set this parameter to false, the endpoint will not apply any formats.Optional
filtersArrayDefine criteria to filter out rows returned by the query. You can use a business schema view column as a filter column and define the filter operator and filter values or specify a filter expression in a formula.
For details, see Query filters.
Optional
aggregate filtersArrayAdd aggregate filters using columns or formulas. Applicable to aggregated tables only.
You must define the aggregation function for each filter column. In the case of formulas, the formula expression must have an aggregation function as well.
For details, see Query aggregate filters.
Optional
distinct filtersObjectAdd criteria to show distinct values. Applicable to listing and aggregated table queries only. You can reference only business schema view columns.
For details, see Query distinct filters.
Optional
sortingArrayDefine sorting options for the query result.
For details, see Query sorting.
Optional
startRowNumberSpecify the index of the first row you want to return in the response file.Optional
pageSizeNumberSpecify the total number of rows you want to return in the response fileOptional
formatStringThe format of the response file.
The valid values are:
  ●  "json" (default)
  ●  "csv"
Optional
auditingInfoObjectAudit information for tracking the query transactions.
For details, see Query auditing info.
Optional
measuresArray of objectsThe list of columns and formulas to include as the query measures.
You must add at least one measure. The maximum is 1000 measures.
For details, see Query measures.
Required
rowsArray of objectsRows in the Pivot table or the grouping dimensions in listing and aggregated tables.
Accepts business schema view columns and formulas along with their properties.
For details, see Query rows: Grouping Dimensions.

  ●  Required in Pivot and Aggregated tables.
  ●  Optional in listing tables.
columnsArray of objectsColumns in the Pivot table. You can reference business schema columns or formulas. Applicable to Pivot tables only.
For details, see Query columns: Pivot table columns.

  ●  Required in Pivot tables only.
  ●  Not valid for listing or aggregated tables.

Query measures

Use this array to add the formulas and columns you want to reference in the query and define the properties of each measure. You must define the query measures for all tables.

ParameterTypeDescriptionRequired
fieldStringThe fully qualified name of the business schema view column businessSchemaName.viewName.columnNameRequired if no formula is defined
formulaStringThe formula expressionRequired if no field is defined
labelStringThe label of the column or formula that appears as the column header in the output file.
If you don’t set a label, the response shows the column name or "New Formula" respectively.
Optional
formatsArray of objectsSpecify the formats you want to apply to dates and numbers.Optional
formats > formatStringSpecify the format you want. For numbers, you can use formats such as "###,##0.00" while for dates, you can use formats such as "SHORT","LONG" and "M/d/yy".Optional
formats > typeStringSpecify the format type. The valid values are:
  ●  "num"
  ●  "date".
Optional
sourceFieldStringThe fully qualified name of the source physical schema column that represents the level column.
Applicable only when defining a hierarchy table.
Required when creating a hierarchy table
aggregationStringThe aggregation function to apply to the column or formula.
Applicable to Pivot and aggregated tables only.
The valid values are:
  ●  "sum"
  ●  "count"
  ●  "distinct"
  ●  "median"
  ●  "average"
  ●  "min"
  ●  "max"
Required in the case of Pivot and aggregated tables only.
scaleStringThe scale of the aggregated values.
Applicable to Pivot and aggregated tables only.
The valid values are:
  ●  "k"
  ●  "m"
  ●  "percent"
Optional
filtersArray of objectsFilters to apply to each aggregated measure.
Applicable to Pivot and aggregated tables only.
Optional

Measure filters

This array is optional and is applicable to Pivot and aggregated tables only. You can add multiple filters using either business schema view columns or formulas. The following table shows the parameters for each filter.

ParameterTypeDescriptionRequired
typeStringThe filter type: column or formula
The valid values are:
  ●  "fieldKey"
  ●  "formulaKey"
Required
labelStringThe label of the column or formula that appears as the column header in the output file.
If you don’t set a label, the response shows the column name or "New Formula" respectively.
Optional
fieldKeyStringThe fully qualified name of the column if the type is set to fieldKey.Required if the type is set to fieldKey
formulaKeyStringThe formula if the type is set to formualKey.
Example:
"Online_Store.Customers.Total_Sales * 0.25"
Required if the type is set to formulaKey
valueObject with arraysThe filter values, operator, and optionsOptional
value > valuesArray of string valuesThe filter valuesOptional
value > opStringThe filter operator.
The valid values are:
  ●  "="
  ●  "!=" or <>
  ●  ">"
  ●  ">="
  ●  "<"
  ●  "<="
  ●  "IN"
  ●  "BETWEEN"
  ●  "IN_LIST"
  ●  "NOT_IN"
  ●  "IS_NULL"
  ●  "IS_NOT_NULL"
  ●  "STARTS_WITH"
  ●  "NOT_STARTS_WITH"
  ●  "CONTAINS"
  ●  "NOT_CONTAINS"
  ●  "ENDS_WITH"
  ●  "FIRST_VERSION"
  ●  "LAST_VERSION"
  ●  "IS_DESCENDANT"
  ●  "IN_QUERY"
  ●  "IS_TRUE"
  ●  "IS_FALSE"
Optional
value > optionsObject with multiple elementsThe options of the filter valuesOptional
options > caseSensitiveBooleanSpecify if the string provided as a value is case-sensitive or not.
This option applies to the following operators only: CONTAINS, NOT_CONTAINS, STARTS_WITH, NOT_STARTS_WITH, and ENDS_WITH. For other operators, the value is always case-sensitive.
Optional
options > levelNumberSpecify the level of data in the case of the "IS_DESCENDANT" operatorOptional
options > depthNumberSpecify the depth of data in the case of the "IS_DESCENDANT" operatorOptional
promptBooleanSpecify if this filter is used in prompts or not.Optional

Query rows: Grouping Dimension

Use this array to define rows in the Pivot table or the grouping dimensions in listing and aggregated tables. You can use business schema view columns and formulas. You must define the query rows in the case of a Pivot or aggregated table only; however, it is optional in the case of a listing table.

ParameterTypeDescriptionRequired
fieldStringThe fully qualified name of the business schema view columnRequired if no formula is defined
formulaStringThe formula expressionRequired if no field is defined
labelStringThe label of the column or formula that appears as the column header in the output file.
If you don’t set a label, the response shows the column name or "New Formula" respectively.
Optional
formatsArray of objectsSpecify the formats you want to apply to dates and numbers.Optional
formats > formatStringSpecify the format you want. For numbers, you can use formats such as "###,##0.00" while for dates, you can use formats such as "SHORT","LONG" and "M/d/yy".Optional
formats > typeStringSpecify the format type. The valid values are:
  ●  "num"
  ●  "date".
Optional
sortingArrayDefine sorting options for each columnOptional
sorting > fieldStringThe business schema view column to sort the values byRequired if no filter formula is defined
sorting > formulaStringThe formula to sort the values byRequired if no filter field is defined
sorting > labelStringThe label of the sorting field or formulaOptional
sorting > dirStringThe direction of the sorting order: ascending or descending.
The valid values are:
  ●  "asc"
  ●  "desc"
Optional
showEmptyGroupsBooleanSpecify if you want to return rows for which the grouping dimension contains a null valueOptional
datePartBooleanSpecify if the field is part of time series (Year, Quarter, Month, and Day).Optional
subTotalBooleanSpecify if you want to return the subtotals or not.Optional
hierarchyObject with elementsApplicable only to columns from objects with self-joinOptional
hierarchy > levelNumberSpecify the level of data in the hierarchy that you want to include in the query for the respective column.Optional
hierarchy > excludeParentBooleanSpecify if you want to exclude the parent row or notOptional
hierarchy > attributesArray of objectsSpecify the dimension attributes to be added next to each dimension value. This applies only to hierarchical queries with a single dimension.Optional
attributes > labelStringSpecify the attribute labelOptional
attributes > nameStringSpecify the attribute column, for example, "SALES.PRODUCT.PROD_NAME"Optional
attributes > formatsArray of objectsSpecify the formats you want to apply to dates and numbers.Optional
formats > formatStringSpecify the format you want. For numbers, you can use formats such as "###,##0.00" while for dates, you can use formats such as "SHORT","LONG" and "M/d/yy".Optional
formats > typeStringSpecify the format type. The valid values are:
  ●  "num"
  ●  "date".
Optional

Query columns: Pivot table columns

Use this array to define columns in the Pivot table. You can add business schema columns and formulas.

ParameterTypeDescriptionRequired
fieldStringThe fully qualified name of the business schema view columnRequired if no formula is defined
formulaStringThe formula expressionRequired if no field is defined
labelStringThe label of the column or formula that appears as the column header in the output file.
If you don’t set a label, the response shows the column name or "New Formula" respectively.
Optional
formatsArray of objectsSpecify the formats you want to apply to dates and numbers.Optional
formats > formatStringSpecify the format you want. For numbers, you can use formats such as "###,##0.00" while for dates, you can use formats such as "SHORT","LONG" and "M/d/yy".Optional
formats > typeStringSpecify the format type. The valid values are:
  ●  "num"
  ●  "date".
Optional
sortingArrayDefine sorting options for each columnOptional
sorting > fieldStringThe business schema column to sort the values of the Pivot table column byRequired if no sorting formula is defined
sorting > formulaStringThe formula to sort the values of the Pivot table column byRequired if no sorting field is defined
sorting > labelStringThe label of the sorting field or formulaOptional
sorting > dirStringThe direction of the sorting order: ascending or descending.
The valid values are:
  ●  "asc"
  ●  "desc"
Optional
datePartBooleanSpecify if the field is part of time series (Year, Quarter, Month, and Day).Optional

Query filters: Individual filter

Use this array to define criteria to filter out rows returned by the query. You can use a business schema view column as a filter column and define the filter operator and filter values or specify a filter expression in a formula. This part of the query is optional.

ParameterTypeDescriptionRequired
typeStringSpecify the type of the individual filter: column or formula
The valid values are:
  ●  "fieldKey"
  ●  "formulaKey"
Required
labelStringThe label of the column or formula that appears as the column header in the output file.
If you don’t set a label, the response shows the column name or "New Formula" respectively.
Optional
fieldKeyStringThe fully qualified name of the business schema view columnRequired if the type is set to fieldKey
formulaKeyStringThe formula expressionRequired if the type is set to formulaKey
valueObjectwith an array of values The filter values, operator, and optionsRequired
valuesArray of string valuesThe filter valuesRequired
value > opStringThe filter operator
The valid values are:
  ●  "="
  ●  "!=" or <>
  ●  ">"
  ●  ">="
  ●  "<"
  ●  "<="
  ●  "IN"
  ●  "BETWEEN"
  ●  "IN_LIST"
  ●  "NOT_IN"
  ●  "IS_NULL"
  ●  "IS_NOT_NULL"
  ●  "STARTS_WITH"
  ●  "NOT_STARTS_WITH"
  ●  "CONTAINS"
  ●  "NOT_CONTAINS"
  ●  "ENDS_WITH"
  ●  "FIRST_VERSION"
  ●  "LAST_VERSION"
  ●  "IS_DESCENDANT"
  ●  "IN_QUERY"
  ●  "IS_TRUE"
  ●  "IS_FALSE"
Optional
value > optionsObjectThe filter value optionsOptional
options > caseSensitiveBooleanSpecify if the string provided as a value is case-sensitive or not.
This option applies to the following operators only: CONTAINS, NOT_CONTAINS, STARTS_WITH, NOT_STARTS_WITH, and ENDS_WITH. For other operators, the value is always case-sensitive.
Optional
options > levelNumberSpecify the level of data in the case of the "IS_DESCENDANT" operatorOptional
options > depthNumberSpecify the depth of data in the case of the "IS_DESCENDANT" operatorOptional
promptBooleanSpecify if this filter is used in prompts or notOptional

Query aggregate filters

You can add aggregate filters to aggregated and Pivot tables only. You can create multiple aggregate filters using columns or formulas. You must define the aggregation function for each filter column you add. In the case of formulas, the formula expression must have an aggregation function as well. This part is optional.

ParameterTypeDescriptionRequired
fieldStringThe fully qualified name of the business schema view column you want to use in the aggregate filterRequired if no formula is defined
formulaStringThe formula you want to use as the aggregate filter.
The formula must contain the whole filter expression, including a filter operator and filter values.
Example: "SUM(SALES.PRODUCTS.PROD_COST) > 100000"
Required if no field is defined
opStringThe filter operator in the case of using a filter column.
The valid values are:
  ●  "="
  ●  "!=" or <>
  ●  ">"
  ●  ">="
  ●  "<"
  ●  "<="
  ●  "IN"
  ●  "BETWEEN"
  ●  "IN_LIST"
  ●  "NOT_IN"
  ●  "IS_NULL"
  ●  "IS_NOT_NULL"
  ●  "STARTS_WITH"
  ●  "NOT_STARTS_WITH"
  ●  "CONTAINS"
  ●  "NOT_CONTAINS"
  ●  "ENDS_WITH"
  ●  "FIRST_VERSION"
  ●  "LAST_VERSION"
  ●  "IS_DESCENDANT"
  ●  "IN_QUERY"
  ●  "IS_TRUE"
  ●  "IS_FALSE"
Optional
valuesArray of stringsThe filter valuesOptional
aggregationStringThe aggregation function to apply to the filter column.
The valid values are:
  ●  "sum"
  ●  "count"
  ●  "distinct"
  ●  "median"
  ●  "average"
  ●  "min"
  ●  "max"
Optional

Query distinct filters

You can specify distinct filters in listing and aggregated table queries only. You can reference only business schema view columns. For each filter, you must define the column to use. You can also define a label for this filter. This part is optional.

ParameterTypeDescriptionRequired
labelStringThe label of the distinct filter column.
If you don’t specify a label, the column name is used.
Optional
nameStringThe fully qualified name of the business schema view column you want to filter the result according to its distinct valuesRequired

Query sorting

Use this array to define sorting options for the query result. This part is optional.

ParameterTypeDescriptionRequired
fieldStringThe business schema view column to sort the query result byOptional
labelStringThe label of the sorting field or formulaOptional
formulaStringThe formula to sort the query result byOptional
dirStringThe direction of the sorting order: ascending or descending.
The valid values are:
  ●  "asc"
  ●  "desc"
Optional

Query auditing info

Use this object to specify the audit information for tracking the query transactions.

ParameterTypeDescriptionRequired
dashboardGuidStringThe GUID of the dashboardOptional
insightGuidStringThe GUID of the insightOptional
dashboardLayoutGuidStringThe GUID of the dashboard layoutOptional
dashboardNameStringThe dashboard nameOptional
insightNameStringThe insight nameOptional
dashboardLayoutNameStringThe dashboard layout nameOptional

Request body examples

The following are examples that reference the Online_Store business schema.

Listing table example

{
"stringify": "false",
"query": {
"aggregate": "false",
"format": "json",
"measures": [
{"field": "Online_Store.Customers.AccountNumber",
"label": "Account Number"},
{"field": "Online_Store.Customers.CustomerID",
"label": "Customer ID"},
{"field": "Online_Store.Customers.Sales_Order_Count",
"label": "Sales Order Count"},
{"field": "Online_Store.Customers.Average_Sales_Order",
"label": "Average Sales Order"},
{"field": "Online_Store.Customers.Total_Sales",
"label": "Total Sales"},
{"field": "Online_Store.Customers.Rank",
"label": "Rank"},
{"field": "Online_Store.Customers.StateProvinceName",
"label": "State"},
{"field": "Online_Store.Customers.City",
"label": "City"}
]}}

Aggregated table example

{
"stringify": "false",
"query": {
"aggregate": "true",
"format": "json",
"rows": [
{"field": "Online_Store.OrderDetails.OrderDateYear",
"label": "Year (Order Date)"},
{"field": "Online_Store.OrderDetails.OrderDateMonth",
"label": "Month (Order Date)",
"sorting" : [
{"field": "Online_Store.OrderDetails.OrderDateMonthNumber",
"label": "Month Order",
"dir": "asc"}]},
{"field": "Online_Store.OrderDetails.SubcategoryName",
"label": "Subcategory"},
{"field": "Online_Store.OrderDetails.ProductName",
"label": "Product"}
],
"measures": [
{"field": "Online_Store.OrderDetails.OrderQty",
"label": "Orderqty",
"aggregation" : "sum"},
{"field": "Online_Store.OrderDetails.UnitPrice",
"label": "Unitprice",
"aggregation" : "sum"},
{"field": "Online_Store.OrderDetails.UnitPriceDiscount",
"label": "Discount",
"aggregation" : "sum"}
],
"filters": [
{"type": "fieldKey",
"label": "Year(Order Date)",
"fieldKey": "Online_Store.OrderDetails.OrderDateYear",
"value": {
"values": ["2012-2014"],
"op": "BETWEEN"}}
]}}

Pivot table example

{
"stringify": "false",
"query": {
"aggregate": "true",
"format": "csv",
"ensureParentSubtotal":"true",
"rows": [
{"field": "Online_Store.Customers.StateProvinceName",
"label": "State",
"showEmptyGroups": "false"}
],
"columns": [
{"field": "Online_Store.Customers.City",
"label": "City"}
],
"measures": [
{"field": "Online_Store.Customers.Sales_Order_Count",
"label": "Sales Order Count",
"aggregation" : "sum"},
{"field": "Online_Store.Customers.Total_Sales",
"label": "Total Sales",
"aggregation" : "sum"}
],
"filters": [
{"type": "fieldKey",
"label": "Account Number",
"fieldKey": "Online_Store.Customers.AccountNumber",
"value": {
"values": ["AW00000001","AW00000002","AW00000003","AW00000004","AW00000005","AW00000006","AW00000007"],
"op": "IN_LIST"}
}]}}

Hierarchy table example

{
"stringify": "false",
"query": {
"aggregate": "true",
"format": "json",
"ensureParentSubtotal": "true",
"sortWithinGroups" : "true",
"rows": [
{"field": "Online_Store.RevenueDetail.CategoryName",
"label": "Category",
"subTotal": "true"},
{"field": "Online_Store.RevenueDetail.SubcategoryName",
"label": "Subcategory",
"subTotal": "true"}
],
"measures": [
{"field": "Online_Store.RevenueDetail.LineCost",
"label": "Cost",
"aggregation" : "sum"},
{"field": "Online_Store.RevenueDetail.LineTotal",
"label": "Revenue",
"aggregation" : "sum"}
]}}

Endpoint response

The response of the endpoint can be in a CSV or JSON format according to the format parameter.

The following table shows the HTTP response status codes for this endpoint.

CodeDescriptionPayload Response
200Success
The endpoint returns the query result in the specified format successfully.
The response of the endpoint can be in a CSV or JSON format according to the format parameter.
For details, see Response examples.
{"data": "string"}
400Bad Request
The endpoint couldn’t run the query due to incorrect or missing request parameters.
{"message": "string"}
Example: {can't parse JSON. Raw result: INC_05000301: Runtime exception occurred [null].}
403Forbidden
The endpoint couldn’t run the query due to authentication errors, such as an invalid or expired access token.
{"message": "string"}
Example: {"message": "INC_09030101: Invalid access token or access token may have expired."}
422Unprocessable Entity
The endpoint couldn’t run the query due to referencing objects other than one business schema view.
{"errorMessages": [{"message": "string"}]}
Example: {"message": "INC_04050771: Physical schema OnlineStore is used, only business schemas are allowed in public API."}
500Internal Server Error
The endpoint couldn’t run the query due to a server error.
{"message": "string"}

Response examples

The following are examples of the endpoint response when the endpoint returns the query result successfully (code 200).

Note
  • In 2023.4.0, the returned data will not be formatted when querying a business schema view or Incorta Analyzer view, and date values will be returned in the following format: yyyy-mm-dd 00:00:00.
  • When querying an Incorta SQL view while setting the query’s format option to json and regardless of the formatted parameter, the response will contain an additional attribute: formatted. This attribute shows the same data as the value attribute, except in the case of a date, it will shows the value in the following format: m/d/yy.

Example:

data": [
{"value": "AW00000676",
"formatted": "AW00000676"},
{"value": "676",
"formatted": "676"},
{"value": "2020-01-30 00:00:00",
"formatted": "1/30/20"}
]

Response example in CSV format:

"Account Number","Customer ID","Sales Order Count","Average Sales Order","Total Sales","Rank","State","City"
"AW00000170","170","1348","63726.64965107999","85903523.72965583","1","Tennessee","Memphis"
"AW00000166","166","1265","67820.8349202214","85793356.17408007","2","Vermont","Derby"
"AW00000024","24","1013","79331.27349487085","80362580.05030417","3","New York","Queens Village"
"AW00000254","254","1256","63738.705722391605","80055814.38732386","4","Washington","Bellevue"
"AW00000085","85","732","105860.65467478275","77489999.22194098","5","Washington","Easton"
"AW00000697","697","1017","75861.21537671397","77150856.03811811","6","Utah","Tooele"
...

Response example in JSON format:

{
"isSampled": false,
"subqueryComplete": true,
"rowHeaders": [
{"label": "Category",
"dataType": "string",
"index": 0},
{"label": "Subcategory",
"dataType": "string",
"index": 1}
],
"measureHeaders": [
{"label": "Cost",
"dataType": "double"},
{"label": "Revenue",
"dataType": "double"}
],
"data": [
["Accessories",
"Bike Racks",
"1.0547742480000407E7",
"1.7510340792000014E7"],
["Accessories",
"Bike Stands",
"1048147.7160000057",
"2802534.0"],
[...],
["Components",
null,
"8.670673554101496E8",
"9.067912530366442E8"]
],
"isAggregated": true,
"startRow": 0,
"endRow": 39,
"totalRows": 39,
"complete": true,
"raw": true
}

The JSON response contains additional information that the CSV file doesn’t show. The following table shows the additional parameters in the JSON response:

ParameterTypeDescription
isSampleBooleanSpecifies if data is sampled or not
rowHeadersArray of stringsThe grouping dimension headers
measureHeadersArray of stringsThe measure headers
colHeadersArray of stringsApplicable on pivot table only column headers
pivotColumnHeadersArray of stringsThe headers of the Pivot table columns
DataArray of arraysThe response data
isAggregatedBooleanSpecifies if the response is aggregated or not
startRowNumberThe request starting row
endRowNumberThe request end row
totalRowsNumberThe count of the source rows
CompleteBooleanSpecifies if the request is complete or not. This is when the number of the total rows equals the number of the source rows.
RawBooleanSpecifies if data is formatted or not

Error message examples

The following are examples of the error messages that you may receive when you use this endpoint.

Error MessageDescription
INC_04050772: only one Business Schema and Business view is allowed, provided schemas [OnlineStore, Online_Store] and views [address, Customers]You referenced multiple views or business schemas in the request.
INC_04050771: Physical schema OnlineStore is used, only business schemas are allowed in public API.You referenced a physical schema table instead of a single business schema view.
INC_04050707: Undefined column Online_Store.Customers.Hello, either the column or the table it belongs to has been deleted.A column you referenced in the request couldn’t be found.
"errorMessages" : [{"message" : "INC_01210000: Measure field or formula, one of them should have value"} ]You didn’t add a value for the field or formula properties of a measure object.
"message" : "Encoded value is a not a Base64 string: username"You didn’t use the correct format to encode the username for impersonation.
"message" : "INC_09040104: You cannot query data on behalf of another super user"You tried to impersonate another Super User, which you can’t.