Go to ZyloLegacy API Documentation
API Reference

Report Builder

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 and filtered.

Reports return the full result set — pagination (limit / skip) is not supported. If you need to bound the number of rows, apply filters to narrow the result.

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. For example, Reporting supports the with keyword to join multiple resources together, and the primary resource can use op fields to aggregate its own data while also joining to related 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"
}

Example of a CSV response from the above report:

"label","total_amount"
"OpenAI",20020
"Salesforce",1072
"Facebook",501

Let'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"
}
  • builder - This field is used to tell Zylo what resources you want to combine to make your report. Each report needs to have the builder field to work.
  • sort - Used to sort the report. In the above example, it will sort the report by the "total_amount" field.

limit and skip are not supported on report jobs — they are silently ignored. The report returns every row that matches the query. Use filters to bound the result.

Builder Syntax

The builder object defines your primary resource and any related resource.

Primary Resource

{
    "builder": {
        "applications": {
            ...
        }
    },
    "sort": "+total_amount"
}
  • 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 Resource Relationships) for all possible resources.

Resource Fields

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "app_label",
                    "as": "label"
                }
            ],
            ...
        }
    },
    "sort": "+total_amount"
}
  • 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 the applications resource with the name parameter. The as parameter is an alias, which means the app_label will come back as label in the report.

Combining Resources

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "app_label",
                    "as": "label"
                }
            ]
            "with": {
                ...
            }
        }
    },
    "sort": "+total_amount"
}
  • with - This field is used to combine other resources to your primary resource. Any resource under the with field 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

Related Resources

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "app_label",
                    "as": "label"
                }
            ]
            "with": {
                "payments": {
                    ...
                }
            }
        }
    },
    "sort": "+total_amount"
}
  • payments - This is a resource that will be combined with the primary resource applications. On the relationships page, there is a relationship between applications and payments.

Relationship Aliases

Some resources have multiple relationships to the same target resource. In these cases, each relationship has a unique alias shown in parentheses on the relationships page. Use the alias name in your with block instead of the resource name.

For example, applications has three separate relationships to users (for application owner, business owner, and IT owner). To include one of these in your report, use the alias:

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "app_label",
                    "as": "label"
                }
            ],
            "with": {
                "application_owner": {
                    "fields": [
                        {
                            "name": "full_name",
                            "as": "app_owner"
                        }
                    ]
                }
            }
        }
    },
    "sort": "+label"
}

In the above example, application_owner is used instead of users because there are multiple users relationships on applications. The alias tells the system which specific relationship to use for the join.

{
    "builder": {
        "applications": {
            "fields": [
                {
                    "name": "app_label",
                    "as": "label"
                }
            ]
            "with": {
                "payments": {
                    "fields": [
                        {
                            "name": "amount",
                            "op": "sum",
                            "as": "total_amount"
                        }
                    ],
                }
            }
        }
    },
    "sort": "+total_amount"
}
  • 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 the payments resource with the name parameter. The as parameter is an alias, which means the amount will come back as total_amount in the report. The op parameter indicates how the field should be aggregated to primary resource. Since the relationship between applications and payments is "One-to-"Many", we must aggregate payments.

The list of available op operations are:

  • sum - Sum up the values. Numeric fields only.
  • max - Retrieve the max value. Numeric or date fields only.
  • min - Retrieve the min value. Numeric or date fields only.
  • avg - Calculate the average value. Numeric fields only.
  • count - Count the non-null occurrences of the value.
  • count_distinct - Count the distinct non-null values.

Time-Series Grouping (trunc)

For date or dateTime fields, add a trunc property to bucket values into a calendar period. Typically paired with op fields on the same resource to produce a single-query time series — a trunc field on its own is also accepted, in which case every row's date is truncated and aggregation is left to the caller.

{
    "builder": {
        "payments": {
            "fields": [
                { "name": "payment_date", "trunc": "month", "as": "month" },
                { "name": "amount", "op": "sum", "as": "total_spend" }
            ],
            "filters": { "payment_date": "2025-01-01,gte,2025-12-31,lte" }
        }
    },
    "sort": "+month"
}
  • Valid units: week, month, quarter, year. week truncates to Monday (ISO 8601).
  • Only date / dateTime fields support trunc.
  • trunc and op cannot be combined on the same field.
  • Truncation runs in UTC. The output is an ISO 8601 timestamp; format client-side if you want display strings like "2025-01" or "2025-Q1".

How op Works with Different Relationships

If 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.

Using op on the Primary Resource with Joins

The primary resource can use op fields when it has with relationships. When you do this:

  • Non-op fields on the primary become GROUP BY columns
  • At least one non-op field is required — a primary with only op fields and a with clause is rejected. Add a grouping field, or remove with if you only need a single aggregate.
  • The id column is not automatically included (it would make every row unique)
  • Results are aggregated first, then joined to related resources

When to use this: Use primary resource aggregation when the grouping dimension lives on the primary resource and the related resource just provides a label or detail you can't get otherwise.

For example, to get total spend per cost center with the application name:

{
    "builder": {
        "payments": {
            "fields": [
                {
                    "name": "cost_center",
                    "as": "cost_center"
                },
                {
                    "name": "amount",
                    "op": "sum",
                    "as": "total_spend"
                }
            ],
            "with": {
                "applications": {
                    "fields": [
                        {
                            "name": "app_label",
                            "as": "application_name"
                        }
                    ]
                }
            }
        }
    },
    "sort": "-total_spend"
}

This groups payments by cost_center (and the relation key to applications), then joins to applications for the app_label. The result shows total spend per (cost center, application) combination.

When NOT to use this: If you want one row per related resource (e.g., one row per application with aggregated payment data), use the standard one-to-many pattern instead — make the related resource the primary and put op on the joined resource.

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.

  • 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"
}

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

Resource-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"
}

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.

Important — placement changes meaning

A filter on the primary resource determines which rows the report returns. A filter on a resource inside a with block only determines what data gets joined to those rows; it does not drop parent rows.

In the example above, the filter on payments (inside with) does not remove any application from the result — it just restricts which payments get summed into total_amount for each application. Applications with no qualifying payments still appear, with total_amount of 0 (or null).

If you want to exclude applications without payments in that date range, move the equivalent filter to the primary resource, or use a report-level filter on the aggregated value (e.g. "total_amount": "0,gt").

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"
}

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"
}

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.

Updated about 1 month ago

Did this page help you?

Updated about 1 month ago

Did this page help you?