---
title: "Building a Reporting API · RUAL Documentation"
description: "Aggregations over storage, answered as a CSV or XLSX download or a scheduled email."
canonical: https://docs.rual.nl/tutorials/reporting-api
language: en
---

# Building a Reporting API

Reports from storage data: aggregate with date histograms and terms, answer with a CSV or XLSX download, and deliver scheduled reports by email through the queue.

This tutorial builds a report endpoint: call it with a date range and get back a file, not a page of JSON. It covers the three shapes reports take in practice (a download now, an email later, a scheduled run), all fed by the same aggregation query. It assumes [Storages](https://docs.rual.nl/blueprints/storage) and a registered endpoint; see [Build Your First API](https://docs.rual.nl/cluster/api-quickstart) for the latter.

### 1. The Aggregation

Reports group documents instead of listing them. The grouping block picks the dimension:

- [`aggregation_type_date_histogram_bp_field`](https://docs.rual.nl/block-types/aggregation/aggregation_type_date_histogram_bp_field) (date histogram) buckets a date field per `interval` (`day`, `week`, `month`): signups per day, revenue per week.

- [`aggregation_type_terms_bp_field`](https://docs.rual.nl/block-types/aggregation/aggregation_type_terms_bp_field) (terms) counts documents per distinct value of a field: orders per status, tickets per assignee.

- [`aggregation_type_cardinality_bp_field`](https://docs.rual.nl/block-types/aggregation/aggregation_type_cardinality_bp_field) (cardinality) counts distinct values, and [`aggregation_type_value_count_bp_field`](https://docs.rual.nl/block-types/aggregation/aggregation_type_value_count_bp_field) (value count) counts entries.

[`query_aggregation`](https://docs.rual.nl/block-types/query/query_aggregation) (aggregation) wraps the grouping block under a `name` and attaches it to the search. Feed it into the `query` pin of [`function_search`](https://docs.rual.nl/block-types/storage/function_search) together with a filter query that limits the range (see the query blocks in [Storages](https://docs.rual.nl/blueprints/storage#building-search-queries)). Wire several aggregations and the studio adds one extra query pin per aggregation on the search block, so one pass over the data can fill a whole dashboard.

### 2. The Download

For a file the caller saves directly, build it and reply with it in one flow:

A [function trigger](https://docs.rual.nl/block-types/globals/trigger_custom_function) runs the report: a [date histogram](https://docs.rual.nl/block-types/aggregation/aggregation_type_date_histogram_bp_field) groups documents per day, [aggregation](https://docs.rual.nl/block-types/query/query_aggregation) names it and feeds [search](https://docs.rual.nl/block-types/storage/function_search), and [build CSV](https://docs.rual.nl/block-types/files/function_build_csv) turns the result rows into a file that [reply with file](https://docs.rual.nl/block-types/http%20connection/httpconnection_set_file) downloads to the caller.

![Studio canvas example for the build CSV block: an aggregation straight into a CSV download.](https://docs.rual.nl/canvas-examples/function_build_csv.png)

On the canvas: [`function_build_csv`](https://docs.rual.nl/block-types/files/function_build_csv) (build CSV) takes the search result rows as `values` plus a `filename`, and [`httpconnection_set_file`](https://docs.rual.nl/block-types/http%20connection/httpconnection_set_file) (reply with file) streams the file to the caller through the [`httpconnection_current_request`](https://docs.rual.nl/block-types/http%20connection/httpconnection_current_request) connection. For spreadsheets with real types and multiple sheets use [`file_to_xlsx`](https://docs.rual.nl/block-types/files/file_to_xlsx) (build XLS/X) instead; the wiring is identical.

### 3. Email and Scheduled Reports

A heavy report does not belong inside the request that asked for it. The pattern that scales:

- The endpoint validates the request and hands the work to [`function_custom_execute_from_queue`](https://docs.rual.nl/block-types/function%20execution/function_custom_execute_from_queue) (execute in queue), then replies immediately with 202. See [Queue](https://docs.rual.nl/blueprints/queue).

- The queued function runs the same aggregation, builds the file, and mails it as an attachment with [`postmarkconnection_send_email_template`](https://docs.rual.nl/block-types/postmark/postmarkconnection_send_email_template) (send email with template), which takes the CSV or XLSX straight on its `file` pin. See [Email System Setup](https://docs.rual.nl/tutorials/email-system) for the connection.

- A report that runs on its own schedule (the Monday morning export) starts from [`schedule_repeating_event`](https://docs.rual.nl/block-types/events/schedule_repeating_event) (repeating event) instead of an endpoint. See [Repeating Events](https://docs.rual.nl/blueprints/repeating-events).

### Notes From Practice

- Reports over large storages are read-heavy by definition; give the search a `cache_key` so a dashboard refreshed by ten people runs the aggregation once. See [Caching Strategies](https://docs.rual.nl/blueprints/common-patterns#caching-strategies).

- Branch on the search block's `success` pin and reply with an error body on failure, per [Error Handling](https://docs.rual.nl/blueprints/common-patterns#error-handling). A report that silently returns an empty file is worse than none.

- Scope report endpoints tightly: they aggregate exactly the data most callers should not bulk-download. `*loggedin` at minimum, a custom scope for anything financial. See [Remote Access Control](https://docs.rual.nl/blueprints/remote-access-control).

- Date histograms group on the stored date field; `_meta.created` is the one every document already has.

### Frequently asked

**How do I build a CSV report from storage data in RUAL?**

Group the documents with an aggregation block such as aggregation_type_date_histogram_bp_field, attach it to a search with query_aggregation, feed the result rows to function_build_csv, and reply with the file through httpconnection_set_file.

**How do I email a report on a schedule in RUAL?**

Start the flow from schedule_repeating_event instead of an endpoint, run the same aggregation, and mail the built file with postmarkconnection_send_email_template, which accepts the CSV or XLSX directly on its file pin. Reports triggered by a request go through the queue so the API can reply immediately.