Summary and Aggregation

GraphQL Summary Feature

Overview

The Finale GraphQL API provides a powerful summary feature that enables you to calculate metrics and perform dimensional analysis across your data. The summary feature is an aggregation system with grouping capabilities similar to SQL's GROUP BY with aggregate functions like SUM, AVG (called 'mean' in Finale), MIN, MAX, and COUNT. Think of it like creating pivot tables in Excel or Google Sheets, where you can group data by dimensions and calculate metrics across those groups.

Key Capabilities

The summary feature is available on all 24 ViewConnection types in the API and enables you to:

Calculate Metrics: Aggregate data using operators like sum, mean (average), min, and max
Group by Dimensions: Break down metrics by categories, time periods, suppliers, customers, and other dimensions
Build Dashboards: Create analytics dashboards and reports with KPIs
Analyze Trends: Track performance over time with date-based grouping
Generate Reports: Create summary reports without fetching individual records

How It Works

The summary feature adds a summary field to any ViewConnection query. This field returns calculated metrics and dimensional groupings separately from the main edges data, allowing you to:

Get aggregated results without fetching individual items
Apply filters at the query level to narrow down what's aggregated
Request multiple metrics and groupings in a single query
Handle aggregation errors independently from the main query

Important Concept: Parallel Arrays

Summary results use aligned arrays where each index position corresponds across all metrics and groupBy dimensions:

{
  groupBy: {
    category: ["Electronics", "Clothing", "Home & Garden"]
  },
  metrics: {
    totalStock: [15230, 28450, 12890],
    totalSales: [8934, 15678, 7234]
  }
}

In this example, groupBy.category[0] ("Electronics") corresponds to metrics.totalStock[0] (15230) and metrics.totalSales[0] (8934).

Supported Collections

All 24 ViewConnection types in the API support summary metrics. Nine collections also support GroupBy for dimensional analysis: invoice, invoiceItem, journalEntry, journalEntryItem, order, orderItem, payment, product, and returnItem.

Collections without GroupBy support can still calculate summary metrics but cannot break down results by dimensions. For details on available metrics and groupBy dimensions for each collection, see the individual collection reference pages.

Basic Usage

Query Structure

To use the summary feature, add the summary field to any ViewConnection query:

{collectionName}ViewConnection(filters...) {
  summary {
    errorCode
    errorMessage
    metrics { ... }
    groupBy { ... }  # Only available on 9 collections
  }
}

Field Descriptions:

errorCode: Error code if aggregation fails (null on success)
errorMessage: Human-readable error message (null on success)
metrics: The aggregated values you requested
groupBy: The dimensional groupings (only on supported collections)

Important Notes:

Summary calculations can fail independently of the main query - always check errorCode before using results
Summary calculations are performed on the entire filtered dataset, regardless of pagination parameters like first or last. The pagination parameters only affect which edges are returned, not what data is summarized.
Filters applied to the ViewConnection affect what data is summarized. For general information about filtering, see the Filtering Guide.

Simple Example: Counting Products

query {
  productViewConnection(first: 1, category: ["Electronics"]) {
    summary {
      errorCode
      errorMessage
      metrics {
        totalProducts: count
      }
    }
  }
}

Result:

{
  "data": {
    "productViewConnection": {
      "summary": {
        "errorCode": null,
        "errorMessage": null,
        "metrics": {
          "totalProducts": [1523]
        }
      }
    }
  }
}

Note that count returns an array with a single element [1523] even though there's no grouping. All metrics return arrays.

Metrics: Calculating Summary Data

Metrics are calculated values derived from your data using summary operators (sum, mean, min, max).

Universal Metric: Count

Every collection supports the count metric, which returns the number of items matching your filters:

metrics {
  itemCount: count
}

Return Type

All metrics return [Float] (array of floats). When there's no grouping, the array contains one element. With grouping, there's one element per group.

Summary Operators

Most metrics accept an operator parameter to specify how values should be calculated:

Operator	Description	Example Use Case
`sum`	Sum all values	Total sales revenue, total quantity
`mean`	Calculate average	Average order value, average price
`min`	Find minimum value	Earliest order date, lowest price
`max`	Find maximum value	Latest shipment date, highest quantity

Transform Parameter

Some metrics also accept a transform parameter:

Transform	Description	Use Case
`abs`	Apply absolute value	Calculate magnitudes ignoring positive/negative

Using GraphQL Aliases

GraphQL aliases let you request the same metric multiple times with different operators:

metrics {
  totalStock: stock(operator: sum)
  avgStock: stock(operator: mean)
  maxStock: stock(operator: max)
  minStock: stock(operator: min)
  productCount: count
}

Field-Specific Parameters

Many metrics accept additional parameters to filter or configure what's aggregated. Common parameters include:

dateRange: { start: "2024-01-01", end: "2024-12-31" } - Filter metrics by date range
facilityUrlList: ["facility1", "facility2"] - Filter by specific facilities
productUrlList: [...] - Filter by specific products
Various enum parameters for aggregation methods and options

The available parameters vary by collection and metric. See the Discovering Available Metrics section to explore what's available.

Example: Multiple Metrics Without Grouping

query {
  productViewConnection(
    first: 1
    category: ["Electronics"]
  ) {
    summary {
      errorCode
      errorMessage
      metrics {
        totalProducts: count
        totalStock: stock(operator: sum, facilityUrlList: ["warehouse1"])
        avgPrice: averageGrossSalesPerUnit(
          operator: mean
          dateRange: { start: "2024-01-01", end: "2024-12-31" }
        )
        minPrice: averageGrossSalesPerUnit(operator: min)
        maxPrice: averageGrossSalesPerUnit(operator: max)
      }
    }
  }
}

Result:

{
  "data": {
    "productViewConnection": {
      "summary": {
        "errorCode": null,
        "errorMessage": null,
        "metrics": {
          "totalProducts": [1523],
          "totalStock": [45230.5],
          "avgPrice": [29.99],
          "minPrice": [5.99],
          "maxPrice": [299.99]
        }
      }
    }
  }
}

Example: Order Metrics

query {
  orderViewConnection(
    first: 1
    type: ["SALES_ORDER"]
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
  ) {
    summary {
      errorCode
      errorMessage
      metrics {
        orderCount: count
        totalRevenue: total(operator: sum)
        avgOrderValue: total(operator: mean)
        totalUnits: totalUnits(operator: sum)
        earliestOrder: orderDate(operator: min)
        latestOrder: orderDate(operator: max)
      }
    }
  }
}

Result:

{
  "data": {
    "orderViewConnection": {
      "summary": {
        "errorCode": null,
        "errorMessage": null,
        "metrics": {
          "orderCount": [2847],
          "totalRevenue": [482950.75],
          "avgOrderValue": [169.68],
          "totalUnits": [8934],
          "earliestOrder": ["2024-01-02T08:15:00Z"],
          "latestOrder": ["2024-12-30T16:45:00Z"]
        }
      }
    }
  }
}

Collection-Specific Metrics

Each collection has its own set of available metrics. The specific metrics available vary by collection - for example, Product has metrics related to stock levels and sales, Order has metrics for revenue and units, and Invoice has metrics for totals and balances.

For a complete list of metrics available for each collection, see the collection reference pages listed in the Supported Collections section above.

GroupBy: Dimensional Analysis

GroupBy enables you to break down metrics by dimensions (categories, time periods, suppliers, etc.), similar to SQL's GROUP BY clause.

Availability

Only 9 of the 24 collections support GroupBy (see the Supported Collections section for the complete list). Other collections support metrics-only summaries without grouping.

Return Type

All groupBy fields return [String] (array of strings), with one element per unique group value.

Array Alignment

The most critical concept: groupBy arrays and metrics arrays align by index position:

{
  groupBy: {
    category: ["Electronics", "Clothing", "Home & Garden"]
  },
  metrics: {
    productCount: [523, 892, 654],
    totalStock: [15230, 28450, 12890]
  }
}

This means:

"Electronics" has 523 products with 15,230 total stock
"Clothing" has 892 products with 28,450 total stock
"Home & Garden" has 654 products with 12,890 total stock

Single Dimension Grouping

Group by one dimension to see metrics broken down by that dimension:

query {
  productViewConnection(first: 1) {
    summary {
      errorCode
      errorMessage
      groupBy {
        category: category
      }
      metrics {
        productCount: count
        totalStock: stock(operator: sum)
        totalSales: unitSales(
          operator: sum
          dateRange: { start: "2024-01-01", end: "2024-12-31" }
        )
      }
    }
  }
}

Result:

{
  "data": {
    "productViewConnection": {
      "summary": {
        "errorCode": null,
        "errorMessage": null,
        "groupBy": {
          "category": ["Electronics", "Clothing", "Home & Garden"]
        },
        "metrics": {
          "productCount": [523, 892, 654],
          "totalStock": [15230, 28450, 12890],
          "totalSales": [8934, 15678, 7234]
        }
      }
    }
  }
}

Multiple Dimension Grouping

Request multiple dimensions to create more detailed breakdowns:

query {
  productViewConnection(first: 1, category: ["Electronics"]) {
    summary {
      groupBy {
        category: category
        supplier: supplier1
      }
      metrics {
        productCount: count
        totalStock: stock(operator: sum)
      }
    }
  }
}

Result:

{
  "data": {
    "productViewConnection": {
      "summary": {
        "groupBy": {
          "category": ["Electronics", "Electronics", "Electronics"],
          "supplier": ["Supplier A", "Supplier B", "Supplier C"]
        },
        "metrics": {
          "productCount": [150, 250, 123],
          "totalStock": [5200, 7800, 2230]
        }
      }
    }
  }
}

This shows three groups: Electronics from Supplier A (150 products), Electronics from Supplier B (250 products), and Electronics from Supplier C (123 products).

Hierarchical Grouping

For hierarchical analysis (like category → subcategory), you can use aliases like group and subgroup:

groupBy {
  group: category
  subgroup: supplier1
}

This is a naming convention used in Finale's dashboard UI for two-level hierarchical grouping, but you can use any alias names you prefer.

Date GroupBy Operators

Date fields support special operators that group values by time periods. These operators are available on date fields like orderDate, shipDate, receiveDate, invoiceDate, etc.

Available Operators:

Operator	Description	Example Output
`to_date`	Groups by calendar day	2024-01-15
`to_week`	Groups by week (Sunday-Saturday)	2024-W03 (week starting Sunday)
`to_iso_week`	Groups by ISO week (Monday-Sunday)	2024-W03 (week starting Monday)
`to_month`	Groups by calendar month	2024-01
`to_year`	Groups by calendar year	2024

Usage Example:

groupBy {
  day: orderDate(operator: to_date)
  week: orderDate(operator: to_week)
  month: orderDate(operator: to_month)
  year: orderDate(operator: to_year)
}

Important Notes:

Date groupBy operators are available on date fields across all collections that support groupBy
The operator transforms the date value before grouping, so all dates within the same period are grouped together
Results are returned as formatted strings representing the time period
ISO week (Monday-Sunday) vs regular week (Sunday-Saturday) can produce different groupings
Check collection reference pages to see which date fields are available for each collection

Example: Orders by Date and Customer

query {
  orderViewConnection(
    first: 1
    type: ["SALES_ORDER"]
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
  ) {
    summary {
      groupBy {
        orderMonth: orderDate(operator: to_month)
        customer: partyUrl
      }
      metrics {
        orderCount: count
        revenue: total(operator: sum)
        avgOrderValue: total(operator: mean)
        units: totalUnits(operator: sum)
      }
    }
  }
}

Collection-Specific GroupBy Dimensions

Each collection that supports GroupBy has its own set of available dimensions. For example, Product supports grouping by category and suppliers, Order supports grouping by date and customer, and Invoice supports grouping by date and customer.

For a complete list of groupBy dimensions available for each collection, see the collection reference pages listed in the Supported Collections section above.

Advanced Patterns

Pattern 1: Combining Filters with Summaries

Filters applied to the ViewConnection narrow down what's aggregated:

query {
  orderViewConnection(
    first: 1
    type: ["SALES_ORDER"]
    status: ["COMPLETED"]
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
  ) {
    summary {
      metrics {
        count: count
        revenue: total(operator: sum)
      }
    }
  }
}

This pattern is essential for focused analytics: apply broad filters at the query level, then use groupBy to break down the filtered dataset.

Pattern 2: Summary Data Without Fetching Items

When you only need summary data, you don't need to fetch any edges:

query {
  productViewConnection {
    summary {
      groupBy {
        category: category
      }
      metrics {
        productCount: count
        totalStock: stock(operator: sum)
        avgPrice: averageGrossSalesPerUnit(operator: mean)
      }
    }
  }
}

This pattern is useful for dashboards and reports where you don't need individual item details.

Pattern 3: Date-Based Grouping

Date dimensions can be grouped by different time periods using operators:

query {
  orderViewConnection(
    first: 1
    type: ["SALES_ORDER"]
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
  ) {
    summary {
      groupBy {
        week: orderDate(operator: to_week)  # Available: to_date, to_week, to_month, to_year
      }
      metrics {
        orders: count
        revenue: total(operator: sum)
        units: totalUnits(operator: sum)
      }
    }
  }
}

The specific date operators available depend on the field. Check the collection reference pages or use the dataSetMeta query to discover what's available.

Pattern 4: Handling Null and Empty Results

Always check for errors and handle empty arrays:

const summary = data?.productViewConnection?.summary

if (summary?.errorCode) {
  console.error('Aggregation failed:', summary.errorMessage)
  return
}

const productCount = summary?.metrics?.totalProducts?.[0] ?? 0
const categories = summary?.groupBy?.category ?? []

if (categories.length === 0) {
  console.log('No categories found')
}

Pattern 5: Multiple Metrics with Different Parameters

Use aliases to request the same metric with different filters:

query {
  productViewConnection(first: 1) {
    summary {
      metrics {
        totalStock: stock(operator: sum)
        warehouse1Stock: stock(operator: sum, facilityUrlList: ["warehouse1"])
        warehouse2Stock: stock(operator: sum, facilityUrlList: ["warehouse2"])
        q1Sales: unitSales(
          operator: sum
          dateRange: { start: "2024-01-01", end: "2024-03-31" }
        )
        q2Sales: unitSales(
          operator: sum
          dateRange: { start: "2024-04-01", end: "2024-06-30" }
        )
      }
    }
  }
}

Complete Real-World Examples

Example 1: Product Analytics Dashboard

This example shows how to build a comprehensive product analytics view with multiple perspectives:

query ProductAnalytics {
  # Analysis by Category
  byCategory: productViewConnection(first: 1) {
    summary {
      errorCode
      errorMessage
      groupBy {
        category: category
      }
      metrics {
        products: count
        totalStock: stock(operator: sum)
        avgStock: stock(operator: mean)
        lowStock: stock(operator: min)
        highStock: stock(operator: max)
        totalValue: neqPerUnit(operator: sum)
      }
    }
  }

# Analysis by Supplier
  bySupplier: productViewConnection(first: 1) {
    summary {
      errorCode
      errorMessage
      groupBy {
        supplier: supplier1
      }
      metrics {
        products: count
        reorderNeeded: reorderQuantityToOrder(operator: sum)
        totalCost: lastPurchaseLandedCostPerUnit(operator: sum)
        avgLeadTime: stock(operator: mean)
      }
    }
  }

# Overall Totals
  totals: productViewConnection(first: 1) {
    summary {
      errorCode
      errorMessage
      metrics {
        totalProducts: count
        totalStock: stock(operator: sum)
        totalValue: neqPerUnit(operator: sum)
        avgValue: neqPerUnit(operator: mean)
      }
    }
  }

# Low Stock Alert
  lowStock: productViewConnection(
    first: 10
    stockStatus: ["LOW"]
  ) {
    summary {
      errorCode
      errorMessage
      metrics {
        lowStockCount: count
        reorderNeeded: reorderQuantityToOrder(operator: sum)
      }
    }
  }
}

Example 2: Sales Analysis by Time Period

Track sales performance over time with multiple metrics:

query SalesAnalysis {
  orderViewConnection(
    first: 1
    type: ["SALES_ORDER"]
    status: ["COMPLETED"]
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
  ) {
    summary {
      errorCode
      errorMessage
      groupBy {
        month: orderDate(operator: to_month)
      }
      metrics {
        orders: count
        revenue: total(operator: sum)
        avgOrderValue: total(operator: mean)
        minOrderValue: total(operator: min)
        maxOrderValue: total(operator: max)
        units: totalUnits(operator: sum)
        avgUnitsPerOrder: totalUnits(operator: mean)
        grossMargin: grossMargin(operator: sum)
        avgMargin: grossMargin(operator: mean)
        netSales: netSales(operator: sum)
      }
    }
  }
}

Processing the results:

const { summary } = data.orderViewConnection

if (summary.errorCode) {
  console.error('Failed to load sales data:', summary.errorMessage)
  return
}

// Transform into chart-friendly format
const chartData = summary.groupBy.month.map((month, i) => ({
  month,
  orders: summary.metrics.orders[i],
  revenue: summary.metrics.revenue[i],
  avgOrderValue: summary.metrics.avgOrderValue[i],
  units: summary.metrics.units[i],
  margin: summary.metrics.grossMargin[i]
}))

// Calculate totals
const totals = {
  orders: summary.metrics.orders.reduce((a, b) => a + b, 0),
  revenue: summary.metrics.revenue.reduce((a, b) => a + b, 0),
  units: summary.metrics.units.reduce((a, b) => a + b, 0)
}

// Calculate year-over-year growth
const revenueByMonth = summary.metrics.revenue
const avgMonthlyRevenue = revenueByMonth.reduce((a, b) => a + b, 0) / revenueByMonth.length

Example 3: Product Sales Performance

Analyze which products are performing best:

query ProductSalesPerformance {
  orderItemViewConnection(
    first: 1
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
    orderType: ["SALES_ORDER"]
  ) {
    summary {
      errorCode
      errorMessage
      groupBy {
        product: productUrl
      }
      metrics {
        quantitySold: quantity(operator: sum)
        revenue: total(operator: sum)
        avgPrice: price(operator: mean)
        orderCount: count
        avgQuantityPerOrder: quantity(operator: mean)
      }
    }
  }
}

Example 4: Customer Analysis

Segment customers by purchase behavior:

query CustomerAnalysis {
  orderViewConnection(
    first: 1
    type: ["SALES_ORDER"]
    status: ["COMPLETED"]
    orderDate: { start: "2024-01-01", end: "2024-12-31" }
  ) {
    summary {
      errorCode
      errorMessage
      groupBy {
        customer: partyUrl
      }
      metrics {
        orderCount: count
        totalSpent: total(operator: sum)
        avgOrderValue: total(operator: mean)
        totalUnits: totalUnits(operator: sum)
        firstOrder: orderDate(operator: min)
        lastOrder: orderDate(operator: max)
      }
    }
  }
}

Processing for customer segmentation:

const { summary } = data.orderViewConnection

if (!summary.errorCode) {
  const customers = summary.groupBy.customer.map((customer, i) => ({
    customer,
    orderCount: summary.metrics.orderCount[i],
    totalSpent: summary.metrics.totalSpent[i],
    avgOrderValue: summary.metrics.avgOrderValue[i],
    totalUnits: summary.metrics.totalUnits[i],
    firstOrder: summary.metrics.firstOrder[i],
    lastOrder: summary.metrics.lastOrder[i]
  }))

// Segment customers
  const vipCustomers = customers.filter(c => c.totalSpent > 10000)
  const regularCustomers = customers.filter(c => c.totalSpent >= 1000 && c.totalSpent <= 10000)
  const newCustomers = customers.filter(c => c.orderCount === 1)

console.log(`VIP Customers: ${vipCustomers.length}`)
  console.log(`Regular Customers: ${regularCustomers.length}`)
  console.log(`New Customers: ${newCustomers.length}`)
}

Example 5: Inventory Reorder Analysis

Identify products that need reordering:

query ReorderAnalysis {
  productViewConnection(first: 1) {
    summary {
      errorCode
      errorMessage
      groupBy {
        category: category
        supplier: supplier1
      }
      metrics {
        productsNeedingReorder: count
        totalReorderQuantity: reorderQuantityToOrder(operator: sum)
        estimatedCost: lastPurchaseLandedCostPerUnit(operator: sum)
        currentStock: stock(operator: sum)
        avgReorderPoint: reorderQuantityToOrder(operator: mean)
      }
    }
  }
}

Performance Considerations

Depending on the size of your account, summary requests may take a long time to execute. Performance varies significantly based on your specific queries and the amount of data in your account.

Testing and Optimization

Start small and expand: Test your queries using smaller filters before running them on larger data sets. Start with a small amount of data (using filters like date ranges or specific categories) and expand once you verify performance is acceptable.

For example, when testing a new summary query:

query {
  orderViewConnection(
    first: 10
    # Use filters to start with a small dataset
    orderDate: { start: "2024-11-01", end: "2024-11-07" }  # One week
    type: ["SALES_ORDER"]
  ) {
    summary {
      metrics {
        revenue: total(operator: sum)
        orderCount: count
      }
    }
  }
}

Once you confirm the query performs well on a small dataset, you can gradually expand the date range or remove filters to query larger amounts of data.

Error Handling

Summary calculations can fail independently of the main query. Always check errorCode and errorMessage in the summary response before using the results:

const summary = data?.productViewConnection?.summary

if (summary?.errorCode) {
  console.error('Summary error:', summary.errorMessage)
  return
}

// Safe to use metrics and groupBy
const productCount = summary?.metrics?.totalProducts?.[0] ?? 0

Invalid queries (such as requesting metrics or groupBy dimensions that don't exist for a collection) will result in standard GraphQL validation errors.

Discovering Available Metrics and Dimensions

Different collections support different metrics and groupBy dimensions. Here's how to discover what's available.

Collection-Specific Documentation

The specific characteristics of each collection are documented in the reference section. Each collection type has its own set of available metrics and dimensions. For complete lists, refer to the collection reference pages listed in the Supported Collections section above.

Using dataSetMeta Query

The Finale API provides a dataSetMeta query to discover available fields, filters, and summary capabilities for each collection. This query provides more comprehensive information than GraphQL introspection, including business context and field relationships.

query {
  dataSetMeta(dataSet: "product") {
    fields {
      key
      label
      isSortable
      # ... other field metadata
    }
    # Additional metadata available in the full query
  }
}

The dataSetMeta query is the recommended way to discover:

Available collections and their capabilities
Fields available for each collection
Which fields support summary operations
Available operators for date grouping
Field descriptions and business context

For complete details on using dataSetMeta, see the collection reference pages.

Summary

The Finale GraphQL API's summary feature provides powerful data analysis capabilities:

Key Takeaways

Universal Availability: All 24 collections support summary metrics
Selective GroupBy: 9 collections support dimensional grouping
Parallel Arrays: GroupBy and metrics arrays align by index position
Flexible Operators: Use sum, mean, min, max to calculate summary data
Filter First: Apply ViewConnection filters to reduce the data being summarized
Error Handling: Always check errorCode before using results
Performance Matters: Use filters, caching, and minimal pagination
Discoverable: Use dataSetMeta query to explore available metrics and dimensions

Common Use Cases

Dashboards: Real-time KPIs and performance metrics
Reports: Summary reports with dimensional breakdowns
Analytics: Trend analysis and pattern discovery
Inventory: Stock levels and reorder analysis
Sales: Revenue tracking and customer analysis
Financial: Invoice and payment summaries

Next Steps

Review collection-specific documentation for complete metric and dimension lists
Experiment with simple queries (count without grouping)
Add groupBy for dimensional analysis
Build complex analytics with multiple metrics
Implement error handling and caching
Optimize performance with filters and minimal pagination

The summary feature is one of the most powerful features of the Finale GraphQL API, enabling sophisticated data analysis without complex client-side processing.