Reporting API - Ordering & Pagination

Note

Tip: The ordering and pagination patterns below work the same way with views (like Sales) and cubes (like Orders).

Link to section

Ordering results

Control the sort order of your results.

Link to section

Order Structure

{
  "order": {
    "Cube.measure_or_dimension": "asc"  // or "desc"
  }
}

Link to section

Order Examples

Link to section

Sort by Measure (Descending)

{
  "measures": ["Orders.net_sales"],
  "dimensions": ["Orders.location_id"],
  "order": {
    "Orders.net_sales": "desc"
  }
}

Returns locations sorted by net sales (highest first).

Link to section

Sort by Dimension (Ascending)

{
  "measures": ["Orders.net_sales"],
  "dimensions": ["Orders.location_id"],
  "order": {
    "Orders.location_id": "asc"
  }
}

Returns locations sorted alphabetically.

Link to section

Sort by Time (Ascending)

{
  "measures": ["Orders.net_sales"],
  "timeDimensions": [{
    "dimension": "Orders.sale_timestamp",
    "dateRange": ["2024-01-01", "2024-01-31"],
    "granularity": "day"
  }],
  "order": {
    "Orders.sale_timestamp": "asc"
  }
}

Returns days in chronological order.

Link to section

Multiple Sort Keys

{
  "measures": ["Orders.net_sales"],
  "dimensions": ["Orders.location_id", "Orders.sales_channel_id"],
  "order": [
    ["Orders.location_id", "asc"],
    ["Orders.net_sales", "desc"]
  ]
}

Sorts by location first, then by net sales within each location.

Link to section

Limiting and pagination

Control the number of results returned.

Link to section

Limit

{
  "measures": ["Orders.net_sales"],
  "dimensions": ["Orders.location_id"],
  "limit": 10
}

Returns top 10 results.

Link to section

Offset

{
  "measures": ["Orders.net_sales"],
  "dimensions": ["Orders.location_id"],
  "limit": 10,
  "offset": 10
}

Returns results 11-20 (skips first 10).

Link to section

Pagination Pattern

async function fetchAllResults(baseQuery, pageSize = 100) {
  let allResults = [];
  let offset = 0;
  let hasMore = true;

  while (hasMore) {
    const query = {
      ...baseQuery,
      limit: pageSize,
      offset: offset
    };

    const response = await executeQuery(query);
    allResults.push(...response.data);

    hasMore = response.data.length === pageSize;
    offset += pageSize;
  }

  return allResults;
}

Warning

Use a stable sort key when paginating. If results are not sorted by a deterministic key, rows can shift between pages — causing duplicates or missed rows. Always include an order clause with a stable key (such as a unique dimension or a combination like timestamp + ID) when using limit/offset pagination.

Warning

Performance Consideration: Large result sets (>10,000 rows) may be slow. Consider:

Adding filters to reduce scope
Using fewer dimensions
Increasing aggregation granularity (e.g., month instead of day)

Link to section

Best practices

Link to section

1. Order Time Series Chronologically

{
  "order": {
    "Orders.sale_timestamp": "asc"
  }
}

For time-series data, ascending order shows progression over time.

Link to section

2. Order Rankings by Measure

{
  "order": {
    "Orders.net_sales": "desc"
  }
}

For rankings (top locations, top customers), descending order shows highest first.

Link to section

3. Always Set Limits with Multiple Dimensions

{
  "dimensions": ["Orders.location_id", "Orders.sales_channel_id"],
  "limit": 1000
}

Multiple dimensions can create thousands of rows—always limit results.

Link to section

4. Use Reasonable Page Sizes

{
  "limit": 100  // Good for UI display
}

{
  "limit": 1000  // Good for exports
}

Avoid fetching unlimited results.

Link to section

5. Use Stable Sort Keys for Pagination

When paginating through results, always sort by a deterministic key:

{
  "measures": ["Orders.net_sales"],
  "dimensions": ["Orders.order_id"],
  "order": {
    "Orders.order_id": "asc"
  },
  "limit": 100,
  "offset": 0
}

Without a stable sort key, the database may return rows in a different order between pages, leading to duplicates or gaps.

Docs

Ordering & Pagination

Note

Ordering results

Order Structure

Order Examples

Sort by Measure (Descending)

Sort by Dimension (Ascending)

Sort by Time (Ascending)

Multiple Sort Keys

Limiting and pagination

Limit

Offset

Pagination Pattern

Warning

Warning

Best practices

1. Order Time Series Chronologically

2. Order Rankings by Measure

3. Always Set Limits with Multiple Dimensions

4. Use Reasonable Page Sizes

5. Use Stable Sort Keys for Pagination

On this page