Aggregates Queries

Overview

The aggregate endpoints allow for fast dimensional queries into the aggregated sales data. There are dedicated endpoints and query/filter schemas for each aggregate type but they all function identically.

To make an aggregate query you will need the following:

  1. Aggregate endpoint uri
  2. An access token with the Reporting Data (View) permission
  3. A valid query/filter specific to the aggregate being requested which will be combined in the body of the POST request in the form of a ReportQueryRequest
  4. The response type expected for that aggregate.

An example query:

HTTP POST <POSCore URI (from access token)>/<Aggregate endpoint uri>
Authorization: Bearer <AccessToken>

{
    "query": {}, /* Values according to query type for this aggregate */
    "filter": {}, /* Values according to filter type for this aggregate */
}

The service will respond with a HTTP 200 and a JSON encoded array of the response type. Any null properties will be omitted to save on transmission size.

Core Concepts

Aggregation Some form of numerical value that can be summed. Eg - Amount of Sales, Amount of Tax.

Dimension: A way of distinguishing between two aggregated sales. Eg - Who sold the item? When was it sold? What was the item sold? Any given aggregate will have multiple dimensions but a query may request 0-X dimensions.

Aggregate The actual type/schema being requested. It will contain the values for requested aggregations/dimensions

Dimensioning

When making a query into the sales data (for a specific aggregate) you will by default be requesting the summed Aggregations for sales passing your filter. This is a perfectly valid query but it's also common to want say the sales total aggregation broken down by Employee.

To enable these queries you will need to specify one or more dimensions and then the responses will contain multiple aggregate objects, each containing the employee name and associated amount.

A hypothetical query with no dimensions:

{
    "query": {
        "aggregation_Total_Amount": {
            "include": true
        }
    }
}

Could return the following JSON:

[{
    "Total_Amount": 100
}]

But then adding a couple of dimensions to same query:

{
    "query": {
        "aggregation_Total_Amount": {
            "include": true
        },
        "dimension_OwnedBy_EmployeeStringID": {
            "dimensionedStrings": {
                "reporting_UserName": true  /*Our first dimension*/
            }
        },
        "dimension_Created_TerminalStringID": {
            "dimensionedStrings": {
                "reporting_OutletCommonName": true /*Our second dimension*/
            }
        }
    }
}

Could then return the following JSON (notice that the Total_Amount is now broken down by Employeee and Outlet name):

[{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Alice",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 1",
    "Total_Amount": 10
},{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Alice",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 2",
    "Total_Amount": 40
},{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Bob",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 1",
    "Total_Amount": 20
},{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Carla",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 3",
    "Total_Amount": 30
}]

Filtering

Unless a filter is specified all returned data will span ALL sales on record. The aggregate data can be filtered by any of the exposed dimensions, simply specify one or more dimensions and they will be combined to form an overall filter for the query.

For example the following query+filter will only request aggregates for Alice/Bob in a specific period of time

{
    "query": {
        "aggregation_Total_Amount": {
            "include": true
        },
        "dimension_OwnedBy_EmployeeStringID": {
            "dimensionedStrings": {
                "reporting_UserName": true  /*Our first dimension*/
            }
        },
        "dimension_Created_TerminalStringID": {
            "dimensionedStrings": {
                "reporting_OutletCommonName": true /*Our second dimension*/
            }
        }
    },
    "filter": {
        "ownedBy_EmployeeStringIDs": {
            "reportingStringLookups": {
                "reporting_UserName": {
                    "listCondition": ["Alice", "Bob"]
                }
            }
            "timePeriodType": 30, /* Set to 'Day' See below section on dates for more info */
            "dateStart": {
                "range": {
                    "from": "2020-01-25T00:00:00.00"
                }
            }
        }
    }
}

Could then return the following JSON:

[{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Alice",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 1",
    "Total_Amount": 4.55
},{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Alice",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 2",
    "Total_Amount": 25.5
},{
    "CreatedBy_EmployeeStringID_Reporting_UserName": "Bob",
    "Created_TerminalStringID_Reporting_OutletCommonName": "Outlet 1",
    "Total_Amount": 11.99
}]

Notes on Dates

All dates are in time local to the outlet. There is no timezone information encoded. That means 9AM in the UK will be treated as the same as 9AM in Australia and will dimension accordingly.

If Dates are being either filtered or dimensioned you should be aware that you will need to specify a TimePeriodType as Dates are only aggregated as Hourly, Daily or Monthly blocks.

If you are aggregating using:

  • Hourly - All dates will be aggregated on the hour so only specify and expect dates with minutes/seconds/milliseconds set to 0. Eg "2020-01-25T13:00:00.00"
  • Daily - All dates will aggregated to 00:00 so only specify and expect dates with hours/minutes/seconds/milliseconds set to 0. Eg "2020-01-25T00:00:00.00"
  • Monthly - All dates will be aggregate to 00:00 on the first day of the month with hours/minutes/seconds/milliseconds set to 0. Eg "2020-03-01T00:00:00.00"

Specifying the wrong TimePeriodType will result in overly large queries or filters that don't correctly filter for the range you specify.

Please note that there are two potential dates to query, they are often the same but can vary in circumstances. The TradingDate (default) and ActualDate which is toggled using BaseFilter.UseActualDateTimeForFilter

  • Trading Date: The "real" time of day but adjusted to the trading date that the sale/payment/etc was registered against.
    • This can vary from Actual Date for venues that trade past midnight and wish to record the sales occuring from say 06:00 to 03:00 (the following day) as a single trading day.
  • Actual Date: The "real" calendar date and time that the sale/payment/etc occured.

Available Aggregates