Overview of the reporting builder syntax.
Zylo allows for users to build their own reports by combining multiple different resources such as Applications, Payments, etc.
Reports are built by creating JSON objects that specify:
- Which resources should be combined.
- How the fields should be displayed/aggregated
- How the results should be sorted, filtered, and limited.
Difference between Query and Reporting
The biggest difference between Query and Reporting builders is that Reporting can join multiple resources together into a single report. Due to this, Reports must be submitted as a job and then downloaded, while Queries can be an HTTP request.
Another difference is minor syntax rules, such as that the primary resource in an Reporting Builder cannot have a op type field if it has joined resources.
Reporting Syntax
Below is an example of a report JSON object to get the "Top 10 Most Expensive Applications". Details on the report syntax are beneath the example.
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
],
"with": {
"payments": {
"fields": [
{
"name": "amount",
"op": "sum",
"as": "total_amount"
}
]
}
}
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}Example of a CSV response from the above report:
"label","total_amount"
"OpenAI",20020
"Salesforce",1072
"Facebook",501Let's break down what is happening in the report object above.
Top-Level Structure
Every report object follows the same structure:
{
"builder": {
...
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}builder- This field is used to tell Zylo what resources you want to combine to make your report. Each report needs to have thebuilderfield to work.sort- Used to sort the report. In the above example, it will sort the report by the "total_amount" field.limit- Used to set the limit of returned rows in the report. If not provided, it will default to the API limit (50 results).skip- Used for paginating the results. If not provided, it will default to the API limit (0).
Builder Syntax
The builder object defines your primary resource and any related resource.
Primary Resource
{
"builder": {
"applications": {
...
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}applications- This is the primary resource for your report. Any other resources in the builder will be combined to the primary resource. It can be any resource with a relationship (see @TODO link to relations) for all possible resources.
Resource Fields
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
],
...
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}fields- These are the fields from the resource that are brought into the report. In the above example, the report is retrieving the "app_label" field from theapplicationsresource with thenameparameter. Theasparameter is an alias, which means theapp_labelwill come back aslabelin the report.
Combining Resources
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
...
}
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}with- This field is used to combine other resources to your primary resource. Any resource under thewithfield will be joined to the primary resource. To see all the resources that can be joined to your primary resource, check out the relations page (@todo relation page)
Related Resources
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
"payments": {
...
}
}
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}payments- This is a resource that will be combined with the primary resourceapplications. On the relationships page (@todo relationship page), there is a relationship betweenapplicationsandpayments.
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
"payments": {
"fields": [
{
"name": "amount",
"op": "sum",
"as": "total_amount
}
],
}
}
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}fields- These are the fields from the resource that are brought into the report. In the above example, the report is retrieving the "amount" field from thepaymentsresource with thenameparameter. Theasparameter is an alias, which means theamountwill come back astotal_amountin the report. Theopparameter indicates how the field should be aggregated to primary resource. Since the relationship betweenapplicationsandpaymentsis "One-to-"Many", we must aggregatepayments.
The list of available op operations are:
sum- Sum up the values.max- Retrieve the max value.min- Retrieve the min value.avg- Calculate the average value.count- Count the occurrences of the value.
How op Works with Different Relationships
op Works with Different RelationshipsIf the relationship between the primary resource and the related resource is "One-to-Many", then all the fields within the related resource need to have an op field.
If the relationship between the primary resource and the related source is "Many-to-One", then none of the fields within the related resource can have an op field.
The op field is used to aggregate results, so the relationship between the resources needs to be able to be grouped and aggregated.
Filtering
One advantage to using the report syntax is that you can filter down the results on multiple levels.
The filter syntax for the report is similar to the API filter syntax (@todo filtering syntax).
- Filter down the entire report results set.
- Filter down on a resource level.
- Filter down on a field-level.
Below are use cases for each filtering type.
Report-Level Filtering
Report-level filtering are filters applied to the aggregated result of the report.
Using the above example report, a filter can be added to only retrieve labels that are equal to "Salesforce".
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
"payments": {
"fields": [
{
"name": "amount",
"op": "sum",
"as": "total_amount
}
],
}
}
}
},
"filters": {
"label": "Salesforce"
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}Take note where the filters value is positioned. It is on the top-level similar to the builder value. This indicates a report-level filter.
Resource-Level Filtering
Resouce-level filtering are filters that are applied to a single resource in the report.
Using the above example report, a filter can be added to only retrieve payments that are between 2025-01-01 and 2025-06-30.
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
"payments": {
"fields": [
{
"name": "amount",
"op": "sum",
"as": "total_amount
}
],
"filters": {
"payment_date": "2025-01-01,gte,2025-06-30,lte"
}
}
}
}
},
"sort": "+total_amount",
"limit": 10,
"skip": 0
}Take note where the filters value is positioned. It is on the same level as fields under the payments resource. This indicates a resource-level filter.
Field-Level Filtering
Field-level filtering are filters that are applied to a single field within a resource.
Using the above example report, a filter can be added to only retrieve amount on payment that are payment_type of "AP".
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
"payments": {
"fields": [
"name": "amount",
"filters": {
"payment_type": "AP"
},
"op": "sum",
"as": "ap_amount"
]
}
}
}
},
"sort": "+ap_amount",
"limit": 10,
"skip": 0
}The benefit to field-level filtering is that the filters can be applied on the same field to create multiple values.
For example, another field can be added for amount and another filter can be applied to only retrieve payment_type of "Expense".
{
"builder": {
"applications": {
"fields": [
{
"name": "app_label",
"as": "label"
}
]
"with": {
"payments": {
"fields": [
{
"name": "amount",
"filters": {
"payment_type": "AP"
},
"op": "sum",
"as": "ap_amount"
},
{
"name": "amount",
"filters": {
"payment_type": "Expense",
},
"op": "sum",
"as": "expense_amount"
}
]
}
}
}
},
"sort": "+ap_amount",
"limit": 10,
"skip": 0
}Take note where the filters value is positioned. It is on the same level as name for each of the fields. This indicates a field-level filter.