CLcloudbeds
Reports

Query report data

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

TypeHeadersIndexRecordsGroup RowsGroup ColumnsTotalsPeriodsTranspose
ListYesNoYesNoNoNoNoNo
List (Totals)YesNoYesNoNoYesNoNo
PeriodListYesYesYesNoNoNoYesYes
SummaryYesYesYesYesNoNoNoYes
Summary (Details)YesYesYesYesNoYesNoNo
Summary (Totals)YesYesYesYesNoYesNoYes
PeriodSummaryYesYesYesYesNoNoYesYes
PivotYesYesYesYesYesNoNoYes
Pivot (Totals)YesYesYesYesYesYesNoYes
post/datainsights/v1.1/reports/query/data

Request

  • The document declares no server URL.
  • Auth
    HTTP bearer

Query parameters

format'raw' | 'formatted'
Example:formatted

Select the format of the numbers in the records. The "raw" values are sent unmodified from the database. The "formatted" records as are cast to a string. Currency type CDFs are defined in the property settings and other numbers are formatted with commas to separate every third digit and a decimal to separate the fractional portion.

mode'Preview' | 'Run' required
Example:Preview

Select the mode to limit the amount of records. Preview has a limit of 100 records, and run has a limit 12,000 records

Headers

X-PROPERTY-IDinteger required

Property id of the client

Request body

property_idsinteger[] required

Report property ids

dataset_id1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 required

Dataset id

Example request

{
  "dataset_id": 1,
  "columns": [
    {
      "cdf": {
        "type": "default",
        "column": "credit_amount"
      },
      "metrics": [
        "sum"
      ]
    }
  ],
  "group_rows": [
    {
      "cdf": {
        "type": "default",
        "column": "credit_amount"
      }
    }
  ],
  "group_columns": [
    {
      "cdf": {
        "type": "default",
        "column": "credit_amount"
      }
    }
  ],
  "custom_cdfs": [
    {
      "column": "full_address",
      "name": "Full address",
      "description": "CDF with the full address information",
      "kind": "String",
      "format": "currency"
    }
  ],
  "filters": {
    "and": [
      {
        "cdf": {
          "type": "default",
          "column": "credit_amount"
        }
      }
    ],
    "or": [
      {
        "cdf": {
          "type": "default",
          "column": "credit_amount"
        }
      }
    ]
  },
  "sort": [
    {
      "cdf": {
        "type": "default",
        "column": "credit_amount"
      },
      "direction": "asc"
    }
  ],
  "periods": [
    {
      "cdf": {
        "type": "default",
        "column": "credit_amount"
      },
      "name": "Month-To-Date",
      "id": "start_of_last_year_to_today",
      "start": "'2014-12-22T03:12:58'|'start_current_year'",
      "end": "'2014-12-22T03:12:58'|'today'"
    }
  ],
  "formats": {
    "date": "YYYY-MM-DD"
  },
  "comparisons": [
    {
      "name": "Range 1",
      "id": "range_1",
      "filters": {
        "and": [
          {
            "cdf": {
              "type": "default",
              "column": "credit_amount"
            }
          }
        ]
      }
    }
  ]
}

Response

OK

group_rowsstring[] required

List of CDF names of group rows

group_columnsstring[] required

List of CDF names of group columns

periodsstring[]

List of names of the periods being compared

recordsobject required

Dictionary of records of column value combinations and metric values in the report

subtotalsobject required

Dictionary of subtotals in the report

totalsobject required

Dictionary of totals in the report

typestring required

Type of the report generated

comparisonsstring[]

List of names of the comparisons being made

aggregated_countinteger

Number of rows included in aggregations in the report

Example response

{
  "type": "List"
}

Changes

No recorded changes to this operation across all 1 revision of this API.