Skip to main content

REPORT

Runs aggregations across records in AnyDB using filters and date logic.

Description​

REPORT allows you to compute metrics (sum, count, max, etc.) across records of a given object type. It supports flexible filtering, including text, numeric, and date conditions, and can return grouped results for charts.

This function is backed by OpenSearch, enabling efficient queries across large datasets without loading all records.

Syntax​

REPORT(aggregationType, templateName, field, [filters])

Arguments​

  • aggregationType: The type of aggregation to perform. Supported values:

    • 'sum', 'avg', 'min', 'max', 'count'
    • 'terms' (grouped aggregation)

  • templateName: The name of the object type (e.g., "Credit Memo", "Software Ticket")

  • field: The field name to aggregate (e.g., "Subtotal", "Priority")

  • filters (optional): Array of conditions:

[[field, operator, value], ...]

Supported Operators​

OperatorDescription
=Equals
!=Not equals
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
INMatches any value in array

Examples​

Basic Aggregations​

REPORT('sum', 'Credit Memo', 'Subtotal') → Returns total subtotal across all credit memos

REPORT('max', 'Credit Memo', 'Subtotal') → Returns highest subtotal

REPORT('count', 'Credit Memo', 'Subtotal') → Returns number of records

Filtering by Text​

REPORT( 'sum', 'Credit Memo', 'Subtotal', [['Customer Name', '=', 'John Smith']] )

REPORT( 'count', 'Credit Memo', 'Subtotal', [['Customer Name', '!=', 'Jane Doe']] )

REPORT( 'sum', 'Credit Memo', 'Subtotal', [['Customer Name', 'IN', ['John Smith', 'Jane Doe']]] )

Filtering by Numbers​

REPORT(
'sum',
'Credit Memo',
'Subtotal',
[['Subtotal', '>', 500]]
)
REPORT(
'count',
'Credit Memo',
'Subtotal',
[['Subtotal', '>=', 200], ['Subtotal', '<', 1000]]
)

Date Filtering​

Date fields support both absolute dates and relative date math.

Absolute Date Ranges​

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '>=', '2026-03-01'], ['Fix Date', '<', '2026-04-01']]
)

→ Tickets in March 2026

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '=', '2026-03-27']]
)

→ Tickets on March 27, 2026

Relative Date Filters (Date Math)​

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '>=', 'now-7d']]
)

→ Last 7 days

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '<=', 'now+2M']]
)

→ Next 2 months

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '>=', 'now']]
)

→ Future records

Month-Based Queries​

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '>=', 'now/M'], ['Fix Date', '<', 'now+1M/M']]
)

→ This month

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '>=', 'now-1M/M'], ['Fix Date', '<', 'now/M']]
)

→ Last month

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Fix Date', '>=', 'now+1M/M'], ['Fix Date', '<', 'now+2M/M']]
)

→ Next month

Grouping (Terms Aggregation)​

REPORT('terms', 'Software Ticket', 'Priority')

Example output:

[
{ key: "High", count: 5 },
{ key: "Medium", count: 3 },
{ key: "Low", count: 2 }
]

Terms with Filters​

REPORT(
'terms',
'Software Ticket',
'Priority',
[['Fix Date', '>=', 'now-30d']]
)

Using Terms in Charts​

You can use the MAP function to convert a array with key, values returned by terms into 2 separate arrays.

X Axis = MAP(A1, "key")
Y Axis = MAP(A1, "count")

Multiple Conditions​

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[
['Priority', '=', 'High'],
['Status', '!=', 'Closed']
]
)

All conditions are combined using AND logic.

IN Operator with Multiple Values​

REPORT(
'count',
'Software Ticket',
'Ticket ID',
[['Priority', 'IN', ['High', 'Critical']]]
)

Advanced Examples​

Revenue in Last 30 Days​

REPORT(
'sum',
'Credit Memo',
'Subtotal',
[['Issue Date', '>=', 'now-30d']]
)

High Value Transactions This Month​

REPORT(
'count',
'Credit Memo',
'Subtotal',
[
['Issue Date', '>=', 'now/M'],
['Issue Date', '<', 'now+1M/M'],
['Subtotal', '>', 1000]
]
)

Average Value for Specific Customers​

REPORT(
'avg',
'Credit Memo',
'Subtotal',
[['Customer Name', 'IN', ['John Smith', 'Jane Doe']]]
)

Usage Notes​

  • Filters are evaluated using AND logic
  • Date values can be:
    • absolute (YYYY-MM-DD)
    • datetime (YYYY-MM-DD HH:mm:ss)
    • relative (now-7d, now+2M, now/M)
  • terms returns an array of { key, count }
  • Use MAP to extract values for charts
  • Results may require manual refresh depending on data updates

Limitations​

  • terms results are capped (default size limit)
  • Date math depends on system time (now)

Summary​

REPORT turns AnyDB into a real-time analytics layer, enabling:

  • KPI tracking
  • dashboards
  • charts
  • operational insights

directly inside your data model without external tools.