BaasixBaasix

Reports Routes

← Back to Documentation Home

Table of Contents

  1. Overview
  2. Generate Report
  3. Generate Statistics
  4. Date Functions
  5. Advanced Features
  6. Examples

Overview

The Reports API provides powerful analytics and reporting capabilities for your BAASIX application. It offers two main functionalities:

  1. Report Generation - Create detailed reports with grouping, aggregation, and relational data
  2. Statistics Generation - Generate multiple statistics from different collections in a single request

Both endpoints leverage the same query structure as the Items API, ensuring consistency and familiarity.

Generate Report

Generate detailed reports from a specific collection with support for aggregation, grouping, and relational fields.

  • URL: /reports/:collection
  • Method: POST
  • Auth required: Yes

URL Parameters

ParameterTypeRequiredDescription
collectionstringYesName of the collection

Request Body

The request body supports all standard ItemsService query parameters:

ParameterTypeRequiredDescription
fieldsarrayNoFields to include (supports dot notation)
filterobjectNoFilter conditions
sortobjectNoSort criteria
limitnumberNoMaximum number of results
pagenumberNoPage number for pagination
aggregateobjectNoAggregation functions
groupByarrayNoFields to group by (supports date functions)

Optimization Features

The Reports API automatically optimizes queries based on the request:

  1. Simple Reports - No groupBy: Direct pass-through to ItemsService
  2. Grouped Reports - With groupBy but no relational fields: Single optimized query
  3. Complex Reports - With groupBy and relational fields: Two-step optimization approach

Success Response

  • Code: 200 OK
  • Content:
{
  "data": [
    {
      "categoryId": 1,
      "avg_price": 849.99,
      "product_count": "2",
      "category": {
        "id": 1,
        "name": "Electronics",
        "description": "Electronic devices"
      }
    }
  ],
  "totalCount": 3
}

Error Responses

  • Code: 400 Bad Request

    • Content: { "error": { "message": "Invalid query parameters" } }

  • Code: 401 Unauthorized

    • Content: { "error": { "message": "Authentication required" } }

  • Code: 403 Forbidden

    • Content: { "error": { "message": "Insufficient permissions" } }

  • Code: 404 Not Found

    • Content: { "error": { "message": "Model not found" } }

  • Code: 500 Internal Server Error

    • Content: { "error": { "message": "Error generating report" } }

Generate Statistics

Generate multiple statistics from different collections in a single request.

  • URL: /reports/stats
  • Method: POST
  • Auth required: Yes

Request Body

ParameterTypeRequiredDescription
statsarrayYesArray of statistics queries

Each stats object should contain:

ParameterTypeRequiredDescription
namestringYesUnique name for this statistic
collectionstringYesTarget collection name
queryobjectYesQuery parameters for the report

Success Response

  • Code: 200 OK
  • Content:
{
  "data": {
    "total_products": {
      "data": [
        {
          "count": "6"
        }
      ],
      "totalCount": 1
    },
    "products_by_category": {
      "data": [
        {
          "count": "2",
          "avg_price": 849.99,
          "categoryId": 1,
          "category": {
            "id": 1,
            "name": "Electronics"
          }
        }
      ],
      "totalCount": 3
    }
  },
  "totalStats": 2,
  "successfulStats": 2
}

Error Responses

  • Code: 400 Bad Request

    • Content: { "error": { "message": "stats array is required and must not be empty" } }
    • Content: { "error": { "message": "Each stats query must have a 'collection' property" } }

  • Code: 404 Not Found

    • Content: { "error": { "message": "Model {collection} not found" } }

Date Functions

The Reports API supports advanced date-based grouping using PostgreSQL's EXTRACT function:

Supported Date Functions

FunctionDescriptionRange
yearYear (4 digits)-
monthMonth number1-12
dayDay of month1-31
hourHour of day0-23
minuteMinute of hour0-59
weekWeek number of year1-53
dowDay of week (Sunday=0)0-6
isodowISO day of week (Monday=1)1-7

Date Function Syntax

Use the format: date:{function}:{field}

Examples:

  • date:year:createdAt - Group by year
  • date:month:orderDate - Group by month
  • date:dow:createdAt - Group by day of week (0=Sunday)
  • date:isodow:createdAt - Group by ISO day of week (1=Monday)

Advanced Features

Relational Field Support

The Reports API supports dot notation for accessing related data:

{
  "fields": ["categoryId", "category.name", "category.description"],
  "groupBy": ["categoryId"],
  "aggregate": {
    "avg_price": { "function": "avg", "field": "price" },
    "product_count": { "function": "count", "field": "id" }
  }
}

Two-Step Optimization

When using relational fields with groupBy, the API automatically uses a two-step approach:

  1. Step 1: Execute aggregation query with grouping
  2. Step 2: Fetch relational data using efficient filtering
  3. Merge: Combine results for optimal performance

Aggregation Functions

All standard aggregation functions are supported:

FunctionDescription
countCount of records
sumSum of numeric values
avgAverage of numeric values
minMinimum value
maxMaximum value
distinctCount of distinct values
array_aggArray of values

Examples

Basic Report

Generate a simple product report:

POST /reports/products
Authorization: Bearer <token>
Content-Type: application/json

{
  "fields": ["name", "price"],
  "filter": { "categoryId": 1 },
  "limit": 10
}

Grouped Report with Aggregation

Group products by category with average price:

POST /reports/products
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["categoryId"],
  "aggregate": {
    "avg_price": { "function": "avg", "field": "price" },
    "count": { "function": "count", "field": "*" }
  },
  "fields": ["categoryId"]
}

Report with Relational Data

Group products by category and include category information:

POST /reports/products
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["categoryId"],
  "aggregate": {
    "avg_price": { "function": "avg", "field": "price" },
    "product_count": { "function": "count", "field": "id" }
  },
  "fields": ["categoryId", "category.name", "category.description"]
}

Date-Based Reporting

Group orders by year and month:

POST /reports/orders
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["date:year:createdAt", "date:month:createdAt"],
  "aggregate": {
    "total_amount": { "function": "sum", "field": "amount" },
    "order_count": { "function": "count", "field": "id" }
  }
}

Day of Week Analysis

Analyze orders by day of week:

POST /reports/orders
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["date:dow:createdAt"],
  "aggregate": {
    "daily_total": { "function": "sum", "field": "amount" },
    "order_count": { "function": "count", "field": "id" }
  }
}

ISO Day of Week Analysis

Using ISO standard (Monday=1):

POST /reports/orders
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["date:isodow:createdAt"],
  "aggregate": {
    "weekly_total": { "function": "sum", "field": "amount" },
    "order_count": { "function": "count", "field": "id" }
  }
}

Complex Report with Relations and Date

Group by category and year with related data:

POST /reports/orders
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["categoryId", "date:year:createdAt"],
  "aggregate": {
    "yearly_total": { "function": "sum", "field": "amount" },
    "order_count": { "function": "count", "field": "id" }
  },
  "fields": ["categoryId", "date:year:createdAt", "category.name", "category.description"]
}

Multiple Statistics Request

Generate multiple statistics in one request:

POST /reports/stats
Authorization: Bearer <token>
Content-Type: application/json

{
  "stats": [
    {
      "name": "total_products",
      "collection": "products",
      "query": {
        "aggregate": {
          "count": { "function": "count", "field": "*" }
        }
      }
    },
    {
      "name": "orders_by_month",
      "collection": "orders",
      "query": {
        "groupBy": ["date:month:createdAt"],
        "aggregate": {
          "monthly_total": { "function": "sum", "field": "amount" },
          "order_count": { "function": "count", "field": "id" }
        }
      }
    },
    {
      "name": "products_by_category",
      "collection": "products",
      "query": {
        "groupBy": ["categoryId"],
        "aggregate": {
          "count": { "function": "count", "field": "id" },
          "avg_price": { "function": "avg", "field": "price" }
        },
        "fields": ["categoryId", "category.name"]
      }
    }
  ]
}

Date Range with Complex Filtering

Analyze sales data with complex conditions:

POST /reports/orders
Authorization: Bearer <token>
Content-Type: application/json

{
  "groupBy": ["date:month:createdAt", "categoryId"],
  "aggregate": {
    "monthly_revenue": { "function": "sum", "field": "amount" },
    "order_count": { "function": "count", "field": "id" },
    "avg_order_value": { "function": "avg", "field": "amount" }
  },
  "filter": {
    "AND": [
      { "status": { "eq": "completed" } },
      { "createdAt": { "between": ["2024-01-01", "2024-12-31"] } }
    ]
  },
  "fields": ["categoryId", "date:month:createdAt", "category.name"],
  "sort": { "monthly_revenue": "desc" }
}

Performance Considerations

Query Optimization

  1. Use specific fields - Only request fields you need
  2. Leverage indexing - Ensure groupBy fields are indexed
  3. Limit results - Use appropriate limits for large datasets
  4. Filter early - Apply filters to reduce dataset size

Two-Step Approach Benefits

  • Reduced memory usage - Processes data in optimized chunks
  • Better performance - Avoids complex JOINs in aggregation queries
  • Scalability - Handles large datasets efficiently

Date Function Performance

  • Date functions are computed at the database level for optimal performance
  • Consider creating functional indexes for frequently used date extractions: 
    CREATE INDEX idx_orders_year ON orders (EXTRACT(year FROM created_at));
CREATE INDEX idx_orders_dow ON orders (EXTRACT(dow FROM created_at));

Error Handling

Common Error Scenarios

  1. Invalid Collection - Returns 404 when collection doesn't exist
  2. Invalid Query Structure - Returns 400 for malformed queries
  3. Permission Denied - Returns 403 for insufficient permissions
  4. Database Errors - Returns 500 for database-related issues

Error Response Format

All errors follow the standard BAASIX error format:

{
  "error": {
    "message": "Descriptive error message",
    "code": "ERROR_CODE",
    "details": {}
  }
}

← Back to Documentation Home

Notification Routes

Notification Routes

Settings Routes

Settings Routes

On this page

Reports RoutesTable of ContentsOverviewGenerate ReportURL ParametersRequest BodyOptimization FeaturesSuccess ResponseError ResponsesGenerate StatisticsRequest BodySuccess ResponseError ResponsesDate FunctionsSupported Date FunctionsDate Function SyntaxAdvanced FeaturesRelational Field SupportTwo-Step OptimizationAggregation FunctionsExamplesBasic ReportGrouped Report with AggregationReport with Relational DataDate-Based ReportingDay of Week AnalysisISO Day of Week AnalysisComplex Report with Relations and DateMultiple Statistics RequestDate Range with Complex FilteringPerformance ConsiderationsQuery OptimizationTwo-Step Approach BenefitsDate Function PerformanceError HandlingCommon Error ScenariosError Response FormatRelated Documentation