A Widget on the layout has its data and appearance configured in the Edit Panel.
In Edit Mode, clicking a Widget on the layout puts it into focus. When a Widget is focused, the Edit Panel updates to show Widget configuration, split into two tabs:
- Setup - configures data inputs and interactivity.
- Format - controls how the Widget looks.
The example below shows a bar chart displaying Net Sales by Region. Clicking on the chart updates the Edit Panel to show the Setup and Format tabs for that Widget.
import { Component } from "@angular/core";
import { AgStudio } from "ag-studio-angular";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgPanelConfig,
AgReportState,
AgStudioApi,
AgStudioApiReadyEvent,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
@Component({
selector: "my-app",
standalone: true,
imports: [AgStudio],
template: `<div style="display: flex; flex-direction: column; height: 100%">
<ag-studio
style="width: 100%; height: 100%;"
class="my-studio-container"
[initialState]="initialState"
[mode]="mode"
[panels]="panels"
[data]="data"
/>
</div> `,
})
export class AppComponent {
initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"region-net-sales": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "stores.region" }],
valueKey: [{ id: "net_sales" }],
},
format: {
title: {
enabled: true,
text: "Net Sales by Region",
},
},
},
},
widgetLayout: {
"region-net-sales": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 32 },
},
},
],
selectedPageId: "a",
};
mode: AgStudioMode = "edit";
panels: AgPanelConfig = {
edit: {
right: ["edit"],
},
};
data: AgDataSourcesDefinition | AgDataEngine = getMainDemoData(
"https://www.ag-grid.com/studio/archive/3.0.0/example-assets",
);
}
import '@angular/compiler';
import { provideHttpClient } from '@angular/common/http';
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component.ts';
const app = bootstrapApplication(AppComponent, {
providers: [provideHttpClient()],
});
import type { AgDataSourcesDefinition, AgExpressionFieldDefinition, AgFieldDefinition } from 'ag-studio';
// =============================================================================
// Field Definitions
// =============================================================================
const storesFields: AgFieldDefinition[] = [
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
},
{ id: 'store_name', name: 'Store', format: 'textFormat', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', notBlank: true },
{ id: 'city', name: 'City', format: 'textFormat', notBlank: true },
{
id: 'opened_date',
name: 'Opened Date',
format: 'dateFormat',
},
{ id: 'store_type', name: 'Store Type', format: 'textFormat', notBlank: true },
];
const productsFields: AgFieldDefinition[] = [
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
hide: false,
},
{
id: 'product_name',
name: 'Product',
format: 'textFormat',
},
{ id: 'category', name: 'Category', format: 'textFormat', notBlank: true },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
},
{ id: 'brand', name: 'Brand', format: 'textFormat', notBlank: true },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat', notBlank: true },
{
id: 'list_price',
name: 'List Price',
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'unit_cost',
name: 'Unit Cost',
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'is_discontinued',
name: 'Discontinued',
format: 'booleanFormat',
},
];
const customersFields: AgFieldDefinition[] = [
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
hide: false,
},
{
id: 'customer_name',
name: 'Customer',
format: 'textFormat',
},
{ id: 'signup_date', name: 'Signup Date', format: 'dateFormat', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', notBlank: true },
{ id: 'segment', name: 'Segment', format: 'textFormat', notBlank: true },
{ id: 'is_active', name: 'Active', format: 'booleanFormat', notBlank: true },
{
id: 'marketing_opt_in',
name: 'Marketing Opt-in',
format: 'booleanFormat',
},
{
id: 'lifetime_orders',
name: 'Lifetime Orders',
format: 'integerFormat',
formatOptions: { format: '#,##0' },
},
];
const ordersFields: AgFieldDefinition[] = [
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
hide: false,
},
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
},
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
},
{
id: 'order_datetime',
name: 'Order Date/Time',
format: 'dateTimeFormat',
},
{ id: 'channel', name: 'Channel', format: 'textFormat', notBlank: true },
{ id: 'status', name: 'Status', format: 'textFormat', notBlank: true },
{
id: 'payment_method',
name: 'Payment Method',
format: 'textFormat',
},
{
id: 'currency',
name: 'Currency',
format: 'textFormat',
hide: false,
},
{
id: 'promo_code',
name: 'Promo Code',
format: 'textFormat',
hide: false,
},
{ id: 'notes', name: 'Notes', format: 'textFormat', hide: false },
{
id: 'order_month',
name: 'Order Month',
format: 'textFormat',
hide: false,
accessor: (row: any) => {
const d = new Date(row.order_datetime);
if (Number.isNaN(d.getTime())) return null;
return `${String(d.getUTCFullYear())}-${String(d.getUTCMonth() + 1).padStart(2, '0')}`;
},
},
];
const orderItemsFields: AgFieldDefinition[] = [
{
id: 'order_item_id',
name: 'Order Item ID',
format: 'textFormat',
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
},
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
},
{
id: 'quantity',
name: 'Qty',
format: 'integerFormat',
formatOptions: { format: '#,##0' },
},
{
id: 'unit_price',
name: 'Unit Price',
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'discount_pct',
name: 'Discount',
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'tax_rate',
name: 'Tax Rate',
format: 'percentageFormat',
formatOptions: { format: '#,##0%' },
},
{ id: 'returned', name: 'Returned', format: 'booleanFormat', notBlank: true },
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
},
];
const shipmentsFields: AgFieldDefinition[] = [
{
id: 'shipment_id',
name: 'Shipment ID',
format: 'textFormat',
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
},
{
id: 'ship_datetime',
name: 'Shipped Date/Time',
format: 'dateTimeFormat',
},
{
id: 'delivery_datetime',
name: 'Delivered Date/Time',
format: 'dateTimeFormat',
},
{ id: 'carrier', name: 'Carrier', format: 'textFormat', notBlank: true },
{ id: 'delayed', name: 'Delayed', format: 'booleanFormat' },
];
// =============================================================================
// JSON Loading & Per-Source Caches
// =============================================================================
async function loadJson(baseUrl: string, filename: string): Promise<any[]> {
const url = `${baseUrl}/${filename}`;
const response = await fetch(url);
if (!response.ok) {
console.error(`Failed to load ${filename}: ${response.status}`);
return [];
}
const data = await response.json();
return data;
}
// =============================================================================
// Data Parsing & Cached Loaders
// =============================================================================
const parseBool = (v: unknown): boolean | undefined =>
v === true || v === 'True' ? true : v === false || v === 'False' ? false : undefined;
// Each loader caches its promise so the file is fetched and parsed at most once.
let storesCache: Promise<any[]> | null = null;
const getStores = (baseUrl: string) => (storesCache ??= loadJson(baseUrl, 'stores.json'));
let productsCache: Promise<any[]> | null = null;
const getProducts = (baseUrl: string) =>
(productsCache ??= loadJson(baseUrl, 'products.json').then((rows) =>
rows.map((row) => ({
...row,
list_price: Number(row.list_price),
unit_cost: Number(row.unit_cost),
is_discontinued: parseBool(row.is_discontinued),
}))
));
let customersCache: Promise<any[]> | null = null;
const getCustomers = (baseUrl: string) =>
(customersCache ??= loadJson(baseUrl, 'customers.json').then((rows) =>
rows.map((row) => ({
...row,
lifetime_orders: row.lifetime_orders !== '' ? Number(row.lifetime_orders) : null,
is_active: parseBool(row.is_active),
marketing_opt_in: parseBool(row.marketing_opt_in),
}))
));
let ordersCache: Promise<any[]> | null = null;
const getOrders = (baseUrl: string) => (ordersCache ??= loadJson(baseUrl, 'orders.json'));
let orderItemsCache: Promise<any[]> | null = null;
const getOrderItems = (baseUrl: string) =>
(orderItemsCache ??= loadJson(baseUrl, 'order_items.json').then((rows) =>
rows.map((row) => ({
...row,
quantity: Number(row.quantity),
unit_price: Number(row.unit_price),
discount_pct: Number(row.discount_pct),
tax_rate: Number(row.tax_rate),
returned: parseBool(row.returned),
}))
));
let shipmentsCache: Promise<any[]> | null = null;
const getShipments = (baseUrl: string) =>
(shipmentsCache ??= loadJson(baseUrl, 'shipments.json').then((rows) =>
rows.map((row) => {
const ship_datetime = row.ship_datetime == null || row.ship_datetime === '' ? null : row.ship_datetime;
const delivery_datetime =
row.delivery_datetime == null || row.delivery_datetime === '' ? null : row.delivery_datetime;
// Always guarantee boolean: default to false if not true.
const delayed =
row.delayed === true || row.delayed === 'True'
? true
: row.delayed === false || row.delayed === 'False'
? false
: false;
return { ...row, ship_datetime, delivery_datetime, delayed };
})
));
// =============================================================================
// Expressions (Calculated Columns & Measures)
// =============================================================================
const expressions: AgExpressionFieldDefinition[] = [
// -------------------------------------------------------------------------
// Pre-aggregation expressions (row-level calculations on order_items)
// -------------------------------------------------------------------------
// line_gross = quantity * unit_price
{
id: 'line_gross',
isMeasure: false,
name: 'Line Gross',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
},
// line_discount_amount = line_gross * discount_pct
{
id: 'line_discount_amount',
isMeasure: false,
name: 'Discount Amount',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{ id: 'order_items.discount_pct' },
],
},
},
// line_net = line_gross - line_discount_amount
{
id: 'line_net',
isMeasure: false,
name: 'Line Net',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
},
// line_cogs = quantity * unit_cost (from products via join)
{
id: 'line_cogs',
isMeasure: false,
name: 'Line COGS',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }],
},
},
// line_margin = line_net - line_cogs
{
id: 'line_margin',
isMeasure: false,
name: 'Line Margin',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
// line_net
{
operator: 'subtract',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
// line_cogs
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }] },
],
},
},
// return_flag = returned IS TRUE (for filtering/counting)
{
id: 'return_flag',
isMeasure: false,
name: 'Return Flag',
hide: true,
expression: {
operator: 'isTrue',
inputs: [{ id: 'order_items.returned' }],
},
},
// returned_line_flag = IF(return_flag, 1, 0) - numeric flag for summing
{
id: 'returned_line_flag',
isMeasure: false,
name: 'Returned Line Flag',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// returned_line_net = IF(return_flag, line_net, 0) - line net only for returned items
{
id: 'returned_line_net',
isMeasure: false,
name: 'Returned Line Net',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_net' }, { type: 'number', value: 0 }],
},
},
// returned_line_margin = IF(return_flag, line_margin, 0) - margin only for returned items
{
id: 'returned_line_margin',
isMeasure: false,
name: 'Returned Line Margin',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_margin' }, { type: 'number', value: 0 }],
},
},
// -------------------------------------------------------------------------
// Post-aggregation expressions (measures for KPIs)
// For now, use pre-agg expressions with aggregation in widgets directly
// -------------------------------------------------------------------------
// Gross Margin % = (SUM(line_net) - SUM(line_cogs)) / SUM(line_net)
{
id: 'gross_margin_pct',
isMeasure: true,
name: 'Gross Margin %',
hide: false,
expression: {
operator: 'divide',
inputs: [
{
operator: 'subtract',
inputs: [
{ id: 'line_net', aggregation: 'sum' },
{ id: 'line_cogs', aggregation: 'sum' },
],
},
{ id: 'line_net', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// order_status_group = IF(status = "Processing", "Open", "Closed")
{
id: 'order_status_group',
isMeasure: false,
name: 'Status Group',
expression: {
operator: 'if',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Processing' }] },
{ type: 'string', value: 'Open' },
{ type: 'string', value: 'Closed' },
],
},
},
// is_closed = status IN ("Completed", "Returned", "Cancelled") via chained OR
{
id: 'is_closed',
isMeasure: false,
name: 'Is Closed',
expression: {
operator: 'or',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Completed' }] },
{
operator: 'or',
inputs: [
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Returned' }],
},
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Cancelled' }],
},
],
},
],
},
},
// ship_to_delivery_days = DATEDIFF(day, ship_datetime, delivery_datetime)
{
id: 'ship_to_delivery_days',
isMeasure: false,
name: 'Ship to Delivery Days',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'day' },
{ id: 'shipments.ship_datetime' },
{ id: 'shipments.delivery_datetime' },
],
},
},
// order_to_ship_hours = DATEDIFF(hour, order_datetime, ship_datetime)
{
id: 'order_to_ship_hours',
isMeasure: false,
name: 'Order to Ship Hours',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'hour' },
{ id: 'orders.order_datetime' },
{ id: 'shipments.ship_datetime' },
],
},
},
// is_shipped = ship_datetime IS NOT NULL
{
id: 'is_shipped',
isMeasure: false,
name: 'Is Shipped',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.ship_datetime' }],
},
},
// is_delivered = delivery_datetime IS NOT NULL
{
id: 'is_delivered',
isMeasure: false,
name: 'Is Delivered',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.delivery_datetime' }],
},
},
// is_on_time = IF(is_delivered, delayed = FALSE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_on_time',
isMeasure: false,
name: 'Is On Time',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: false }] },
{ type: 'boolean', value: false },
],
},
},
// is_delayed = IF(is_delivered, delayed = TRUE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_delayed',
isMeasure: false,
name: 'Is Delayed',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: true }] },
{ type: 'boolean', value: false },
],
},
},
// delayed_shipments = IF(is_delayed, 1, 0) - numeric flag for stacking
{
id: 'delayed_shipments',
isMeasure: false,
name: 'Delayed Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delayed' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// on_time_shipments = IF(is_on_time, 1, 0) - numeric flag for stacking
{
id: 'on_time_shipments',
isMeasure: false,
name: 'On-time Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delivered_shipments = IF(is_delivered, 1, 0) - numeric flag for rate denominators
{
id: 'delivered_shipments',
isMeasure: false,
name: 'Delivered Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delay_rate = SUM(delayed_shipments) / SUM(delivered_shipments)
// Use delivered shipments as the denominator so pending (not delivered) rows don't dilute the rate.
{
id: 'delay_rate',
isMeasure: true,
name: 'Delay Rate',
hide: false,
expression: {
operator: 'divide',
inputs: [
{ id: 'delayed_shipments', aggregation: 'sum' },
{ id: 'delivered_shipments', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// shipped_order_id = IF(is_shipped, order_id, NULL)
{
id: 'shipped_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_shipped' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// on_time_order_id = IF(is_on_time, order_id, NULL)
{
id: 'on_time_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivered_order_id = IF(is_delivered, order_id, NULL)
{
id: 'delivered_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivery_status = IF(NOT is_shipped, "Not shipped", IF(delayed = true, "Delayed", "On time"))
{
id: 'delivery_status',
isMeasure: false,
name: 'Delivery Status',
expression: {
operator: 'if',
inputs: [
{ operator: 'not', inputs: [{ id: 'is_shipped' }] },
{ type: 'string', value: 'Not shipped' },
{
operator: 'if',
inputs: [
{ id: 'shipments.delayed' },
{ type: 'string', value: 'Delayed' },
{ type: 'string', value: 'On time' },
],
},
],
},
},
// returned_order_id = IF(return_flag, order_id, NULL)
// Used for counting orders that have at least one returned line.
{
id: 'returned_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'order_items.order_id' }, { type: 'string', value: null }],
},
},
// Aggregated calculations for KPIs (using pre-aggregation expressions as inputs)
{
id: 'net_sales',
isMeasure: true,
name: 'Net Sales',
expression: {
id: 'line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_sales',
isMeasure: true,
name: 'Gross Sales',
expression: {
id: 'line_gross',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'discount_amount',
isMeasure: true,
name: 'Discount Amount',
expression: {
id: 'line_discount_amount',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
{
id: 'COGS',
isMeasure: true,
name: 'COGS',
expression: {
id: 'line_cogs',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_margin',
isMeasure: true,
name: 'Gross Margin',
expression: {
id: 'line_margin',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_margin_percentage',
isMeasure: true,
name: 'Gross Margin %',
expression: {
operator: 'divide',
inputs: [{ id: 'gross_margin' }, { id: 'net_sales' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// --- Orders ---
{
id: 'order_count',
isMeasure: true,
name: 'Order Count',
expression: {
id: 'orders.order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'average_order_value',
isMeasure: true,
name: 'Average Order Value',
expression: {
operator: 'divide',
inputs: [{ id: 'net_sales' }, { id: 'order_count' }],
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'active_customers',
isMeasure: true,
name: 'Active Customers',
expression: {
id: 'orders.customer_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
// --- Returns ---
{
id: 'returned_lines',
isMeasure: true,
name: 'Returned Lines',
expression: {
id: 'returned_line_flag',
aggregation: 'sum',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'returned_orders',
isMeasure: true,
name: 'Returned Orders',
expression: {
id: 'returned_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'return_rate',
isMeasure: true,
name: 'Return Rate (Lines)',
expression: {
operator: 'divide',
inputs: [{ id: 'returned_lines' }, { id: 'order_items.order_item_id', aggregation: 'count' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'return_value',
isMeasure: true,
name: 'Return Value',
expression: {
id: 'returned_line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
// return_margin_impact = -SUM(returned_line_margin)
// Display as a negative number to represent margin lost due to returns.
{
id: 'return_margin_impact',
isMeasure: true,
name: 'Return Margin Impact',
expression: {
operator: 'subtract',
inputs: [
{ type: 'number', value: 0 },
{ id: 'returned_line_margin', aggregation: 'sum' },
],
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
// --- Delivery ---
{
id: 'shipped_orders',
isMeasure: true,
name: 'Shipped Orders',
expression: {
id: 'shipped_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'delivered_orders',
isMeasure: true,
name: 'Delivered Orders',
expression: {
id: 'delivered_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'delivered_not_returned_orders',
isMeasure: true,
name: 'Delivered (Not Returned)',
expression: {
operator: 'subtract',
inputs: [{ id: 'delivered_orders' }, { id: 'returned_orders' }],
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_deliveries',
isMeasure: true,
name: 'On-time Deliveries',
expression: {
id: 'on_time_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_rate',
isMeasure: true,
name: 'On-time Rate',
expression: {
operator: 'divide',
inputs: [{ id: 'on_time_deliveries' }, { id: 'delivered_orders' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'avg_ship_to_delivery_days',
isMeasure: true,
name: 'Avg Ship to Delivery Days',
expression: {
id: 'ship_to_delivery_days',
aggregation: 'avg',
},
format: 'decimalFormat',
formatOptions: { format: '#,##0.0' },
},
];
// =============================================================================
// Data Source Definition
// =============================================================================
export function getMainDemoData(baseUrl: string): AgDataSourcesDefinition {
const url = `${baseUrl}/main-demo`;
return {
sources: [
{
id: 'stores',
name: 'Stores',
dataShape: 'row',
tables: [{ id: 'stores', name: 'Stores', fields: storesFields }],
getData: async () => ({ data: await getStores(url) }),
},
{
id: 'products',
name: 'Products',
dataShape: 'row',
tables: [{ id: 'products', name: 'Products', fields: productsFields }],
getData: async () => ({ data: await getProducts(url) }),
},
{
id: 'customers',
name: 'Customers',
dataShape: 'row',
tables: [{ id: 'customers', name: 'Customers', fields: customersFields }],
getData: async () => ({ data: await getCustomers(url) }),
},
{
id: 'orders',
name: 'Orders',
dataShape: 'row',
tables: [{ id: 'orders', name: 'Orders', fields: ordersFields }],
getData: async () => ({ data: await getOrders(url) }),
},
{
id: 'order_items',
name: 'Order Items',
dataShape: 'row',
tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
getData: async () => ({ data: await getOrderItems(url) }),
},
{
id: 'shipments',
name: 'Shipments',
dataShape: 'row',
tables: [{ id: 'shipments', name: 'Shipments', fields: shipmentsFields }],
getData: async () => ({ data: await getShipments(url) }),
},
],
relationships: [
{
id: 'orders-customers',
source: { tableId: 'orders', fieldId: 'customer_id' },
target: { tableId: 'customers', fieldId: 'customer_id' },
type: 'many-to-one',
},
{
id: 'orders-stores',
source: { tableId: 'orders', fieldId: 'store_id' },
target: { tableId: 'stores', fieldId: 'store_id' },
type: 'many-to-one',
},
{
id: 'order_items-orders',
source: { tableId: 'order_items', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
{
id: 'order_items-products',
source: { tableId: 'order_items', fieldId: 'product_id' },
target: { tableId: 'products', fieldId: 'product_id' },
type: 'many-to-one',
// line_cogs/line_margin multiply order_items.quantity (many-side, varies per row) by
// products.unit_cost (one-side) before summing - grain-invariant-safe, not a real fan-out.
acceptFanout: true,
},
{
id: 'shipments-orders',
source: { tableId: 'shipments', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
],
expressions,
};
}
Widget Setup Copy Link
Data can be dragged and dropped into an empty Widget or the Setup data section. The UI guides visually to the appropriate drop targets. If an item cannot be used in a slot, AG Studio prevents the drop and guides to a valid slot. This might be because no relationship between data is defined or Widget data requirements are not met.
Where possible, items can be reordered within a slot by dragging them into the required order, which determines the order of Fields being displayed. For detailed information on managing data fields, see Data Setup.
To remove an item, its remove action can be used from the data menu accessible in the data pill. Aggregation and sorting options are also in the same menu. Read more on Aggregation and Sorting.
Widgets can be changed to a different type via the Setup Panel dropdown. AG Studio will preserve field configurations during Widget type switching where possible. Where the new input requirement needs less data, additional fields will be dropped into Tooltip.
Widget Formatting Copy Link
Format settings vary by Widget type. Common settings include the Widget title and description, number formatting, labels and legends, and display options.
Additional customisation is supported on interactivity features such as crosshair, navigator, and zoom. The options available for customisation depend on the application preset and will vary depending on what is permitted.
Reset to Default Copy Link
When an Edit Panel setting differs from the default value, a reset to default button will appear next to the section name. Clicking this will reset all settings within that section to their default values.
Default values are different from saved state. If your application has loaded a saved state, the values will reset to the Studio default values rather than the saved state. The default values can be configured by your developer.
Working with Date & Time Copy Link
There are two ways to put dates on a Widget's axis, and they differ in the span of time the axis covers:
- Drag a date field - the axis covers the range of dates present in that field's data.
- Drag a calendar - the axis covers the date range defined for the calendar, regardless of which dates appear in the data.
In both cases the axis still narrows to match any filters applied to the Widget.
Using a Date Field Copy Link
Drop a date field onto a chart or table and the axis spans the dates found in that field. It groups by a default period - day for a date field, minute for a field that also carries a time of day.
To change the period, select the Widget to open the Setup panel on the right, then open the date field's menu from its pill in the data section and pick another period - year, quarter, month, week, or day. The periods on offer are configured by the developer. The pill shows the field name and the period it's using, for example Order Date · Month, so you can always see how dates are grouped. Changing the period re-groups the same field in place; you don't need to remove it and drag a different one.
In the example below, the Order Date field is on the axis and grouped by year. Select the chart, open the field's menu from its pill, and switch the period to see the chart re-group.
import { Component } from "@angular/core";
import { AgStudio } from "ag-studio-angular";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgReportState,
AgStudioApi,
AgStudioApiReadyEvent,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
@Component({
selector: "my-app",
standalone: true,
imports: [AgStudio],
template: `<div style="display: flex; flex-direction: column; height: 100%">
<ag-studio
style="width: 100%; height: 100%;"
class="my-studio-container"
[initialState]="initialState"
[data]="data"
[mode]="mode"
/>
</div> `,
})
export class AppComponent {
initialState: AgReportState = {
selectedPageId: "by-year",
pages: [
{
id: "by-year",
selection: { type: "widget", id: "year-chart" },
widgets: {
"year-chart": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "sales.order_date::year" }],
valueKey: [{ id: "sales.amount", aggregation: "sum" }],
tooltipKey: [],
},
format: { title: { enabled: true, text: "Revenue by Year" } },
},
"year-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "sales.order_date::year" },
{ id: "sales.amount", aggregation: "sum" },
{ id: "sales.amount", aggregation: "count" },
{ id: "sales.amount", aggregation: "avg" },
],
},
sort: [
{ field: { id: "sales.order_date::year" }, direction: "asc" },
],
},
},
widgetLayout: {
"year-chart": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 20 },
"year-grid": { xTrack: 0, yTrack: 20, xSpan: 24, ySpan: 10 },
},
},
],
panels: {
filters: {
collapsed: true,
},
data: {
collapsed: true,
},
},
};
data: AgDataSourcesDefinition | AgDataEngine = getData();
mode: AgStudioMode = "edit";
}
import '@angular/compiler';
import { provideHttpClient } from '@angular/common/http';
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component.ts';
const app = bootstrapApplication(AppComponent, {
providers: [provideHttpClient()],
});
import type { AgRelationDefinition } from 'ag-studio';
import type { AgDataSourcesDefinition, AgFieldDefinition } from 'ag-studio';
function buildSalesData() {
const rows: { order_date: string; amount: number; region: string }[] = [];
const regions = ['North', 'South', 'East', 'West'];
let rng = 42;
const rand = () => {
rng = (rng * 1664525 + 1013904223) & 0x7fffffff;
return rng / 0x7fffffff;
};
for (let year = 2022; year <= 2024; ++year) {
for (let month = 1; month <= 12; ++month) {
const daysInMonth = new Date(year, month, 0).getDate();
for (let day = 1; day <= daysInMonth; ++day) {
const mm = String(month).padStart(2, '0');
const dd = String(day).padStart(2, '0');
rows.push({
order_date: `${year}-${mm}-${dd}`,
amount: Math.round(200 + rand() * 800 * (1 + month * 0.05)),
region: regions[Math.floor(rand() * regions.length)],
});
}
}
}
return rows;
}
export function getData(): AgDataSourcesDefinition {
return {
sources: [
{
id: 'sales',
name: 'Sales',
data: buildSalesData(),
fields: [
{
id: 'order_date',
name: 'Order Date',
format: 'dateFormat',
} satisfies AgFieldDefinition,
{
id: 'amount',
name: 'Revenue',
format: 'currencyFormat',
} satisfies AgFieldDefinition,
{
id: 'region',
name: 'Region',
format: 'textFormat',
cardinality: 'low',
} satisfies AgFieldDefinition,
],
},
],
relationships: [
{
id: 'sales-calendar',
source: { tableId: 'sales', fieldId: 'order_date' },
target: { calendarId: 'calendar' },
},
] satisfies AgRelationDefinition[],
calendars: [
{
id: 'calendar',
label: 'Calendar',
range: { from: { type: 'date', value: '2022-01-01' }, to: { type: 'date', value: '2024-12-31' } },
fragments: ['year', 'quarter', 'month', 'monthOfYear', 'dayOfWeek'],
},
],
};
}
Using a Calendar Copy Link
A calendar is a named date range defined in the dashboard's data, with a fixed start and end. Its time periods (Year, Quarter, Month, and so on) appear in the Data Panel as their own group, named after the calendar.
Dragging a calendar period works just like dragging a date field - you place it on an axis and change its period from the pill the same way. Two things differ: the periods on offer come from the calendar, and the axis spans the calendar's whole range rather than the dates in your data.
Drag a calendar period such as Month onto a Widget and a chart's timeline stays continuous: periods with no data show as gaps instead of being skipped, which keeps trends honest. The axis still responds to filters, narrowing to the filtered range.
The example below produces the same chart as above, but the axis comes from dragging in a calendar period rather than the date field. Select the chart and open the field's menu to switch the period.
import { Component } from "@angular/core";
import { AgStudio } from "ag-studio-angular";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgReportState,
AgStudioApi,
AgStudioApiReadyEvent,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
@Component({
selector: "my-app",
standalone: true,
imports: [AgStudio],
template: `<div style="display: flex; flex-direction: column; height: 100%">
<ag-studio
style="width: 100%; height: 100%;"
class="my-studio-container"
[initialState]="initialState"
[data]="data"
[mode]="mode"
/>
</div> `,
})
export class AppComponent {
initialState: AgReportState = {
selectedPageId: "by-year",
pages: [
{
id: "by-year",
selection: { type: "widget", id: "year-chart" },
widgets: {
"year-chart": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "calendar::year" }],
valueKey: [{ id: "sales.amount", aggregation: "sum" }],
tooltipKey: [],
},
format: { title: { enabled: true, text: "Revenue by Year" } },
},
"year-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "calendar::year" },
{ id: "sales.amount", aggregation: "sum" },
{ id: "sales.amount", aggregation: "count" },
{ id: "sales.amount", aggregation: "avg" },
],
},
sort: [{ field: { id: "calendar::year" }, direction: "asc" }],
},
},
widgetLayout: {
"year-chart": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 20 },
"year-grid": { xTrack: 0, yTrack: 20, xSpan: 24, ySpan: 10 },
},
},
],
panels: {
filters: {
collapsed: true,
},
data: {
collapsed: true,
},
},
};
data: AgDataSourcesDefinition | AgDataEngine = getData();
mode: AgStudioMode = "edit";
}
import '@angular/compiler';
import { provideHttpClient } from '@angular/common/http';
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component.ts';
const app = bootstrapApplication(AppComponent, {
providers: [provideHttpClient()],
});
import type { AgRelationDefinition } from 'ag-studio';
import type { AgDataSourcesDefinition, AgFieldDefinition } from 'ag-studio';
function buildSalesData() {
const rows: { order_date: string; amount: number; region: string }[] = [];
const regions = ['North', 'South', 'East', 'West'];
let rng = 42;
const rand = () => {
rng = (rng * 1664525 + 1013904223) & 0x7fffffff;
return rng / 0x7fffffff;
};
for (let year = 2022; year <= 2024; ++year) {
for (let month = 1; month <= 12; ++month) {
const daysInMonth = new Date(year, month, 0).getDate();
for (let day = 1; day <= daysInMonth; ++day) {
const mm = String(month).padStart(2, '0');
const dd = String(day).padStart(2, '0');
rows.push({
order_date: `${year}-${mm}-${dd}`,
amount: Math.round(200 + rand() * 800 * (1 + month * 0.05)),
region: regions[Math.floor(rand() * regions.length)],
});
}
}
}
return rows;
}
export function getData(): AgDataSourcesDefinition {
return {
sources: [
{
id: 'sales',
name: 'Sales',
data: buildSalesData(),
fields: [
{
id: 'order_date',
name: 'Order Date',
format: 'dateFormat',
} satisfies AgFieldDefinition,
{
id: 'amount',
name: 'Revenue',
format: 'currencyFormat',
} satisfies AgFieldDefinition,
{
id: 'region',
name: 'Region',
format: 'textFormat',
cardinality: 'low',
} satisfies AgFieldDefinition,
],
},
],
relationships: [
{
id: 'sales-calendar',
source: { tableId: 'sales', fieldId: 'order_date' },
target: { calendarId: 'calendar' },
},
] satisfies AgRelationDefinition[],
calendars: [
{
id: 'calendar',
label: 'Calendar',
range: { from: { type: 'date', value: '2022-01-01' }, to: { type: 'date', value: '2024-12-31' } },
fragments: ['year', 'quarter', 'month', 'monthOfYear', 'dayOfWeek'],
},
],
};
}
Group By Legend Copy Link
The Legend field splits a single chart into multiple coloured series by a categorical dimension - for example, sales broken down by region. It is available on Bar, Column, Line, Area, Scatter, and Bubble charts.
Charts that support a legend expose a Legend slot in the Setup panel. Assign a categorical field to break the chart down by that field's distinct values: each value gets its own colour, and the legend updates automatically. Removing the field reverts to a single series.
Bar and Column Charts Copy Link
How a Legend field segments each bar or column depends on how many measures are present.
Single measure. The Legend field splits each bar or column by the field's distinct values, arranged according to the chart variant:
- Clustered - values appear as separate bars side by side within each category.
- Stacked - values appear as stacked segments within one bar.
- 100% Stacked - segments are proportional, each bar normalised to 100%.
Multiple measures, no Legend field. Each measure becomes its own series, defined by the measure name.
Multiple measures with a Legend field. Measures form the primary (outer) grouping and legend values the secondary (inner) grouping. Colours stay consistent per legend value across every measure group. For example, plot Net Sales and Gross Sales by region with Segment in the Legend slot - X-Axis: Region, Y-Axis: Net Sales and Gross Sales, Legend: Segment. Each region then carries six values - Net Sales and Gross Sales, each split across Enterprise, Mid-Market, and SMB - arranged according to the chart variant:
- Clustered - six bars per region, grouped by measure first (the three Net Sales bars, then the three Gross Sales bars).
- Stacked - two bars per region (Net Sales, Gross Sales), each split into Enterprise, Mid-Market, and SMB segments.
- 100% Stacked - as Stacked, normalised to 100%.
The example below plots Net Sales and Gross Sales by region with Segment assigned to the Legend slot, so each measure splits into one coloured series per segment - Enterprise, Mid-Market, and SMB - with colours consistent across both measures. Open the Setup panel to change or clear the Legend field, or change the chart type to another bar or column variant to see how the arrangement differs.
import { Component } from "@angular/core";
import { AgStudio } from "ag-studio-angular";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgPanelConfig,
AgReportState,
AgStudioApi,
AgStudioApiReadyEvent,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
@Component({
selector: "my-app",
standalone: true,
imports: [AgStudio],
template: `<div style="display: flex; flex-direction: column; height: 100%">
<ag-studio
style="width: 100%; height: 100%;"
class="my-studio-container"
[initialState]="initialState"
[mode]="mode"
[panels]="panels"
[data]="data"
/>
</div> `,
})
export class AppComponent {
initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"region-net-sales": {
type: "bar-chart-stacked",
dataMapping: {
categoryKey: [{ id: "stores.region" }],
valueKey: [{ id: "net_sales" }, { id: "gross_sales" }],
legendKey: [{ id: "customers.segment" }],
},
format: {
title: {
enabled: true,
text: "Net Sales and Gross Sales by Region and Segment",
},
style: {
theme: {
common: {
legend: {
enabled: true,
},
},
},
},
},
},
},
widgetLayout: {
"region-net-sales": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 32 },
},
selection: { type: "widget", id: "region-net-sales" },
},
],
selectedPageId: "a",
};
mode: AgStudioMode = "edit";
panels: AgPanelConfig = {
edit: {
right: ["edit"],
},
};
data: AgDataSourcesDefinition | AgDataEngine = getMainDemoData(
"https://www.ag-grid.com/studio/archive/3.0.0/example-assets",
);
}
import '@angular/compiler';
import { provideHttpClient } from '@angular/common/http';
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component.ts';
const app = bootstrapApplication(AppComponent, {
providers: [provideHttpClient()],
});
import type { AgDataSourcesDefinition, AgExpressionFieldDefinition, AgFieldDefinition } from 'ag-studio';
// =============================================================================
// Field Definitions
// =============================================================================
const storesFields: AgFieldDefinition[] = [
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
},
{ id: 'store_name', name: 'Store', format: 'textFormat', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', notBlank: true },
{ id: 'city', name: 'City', format: 'textFormat', notBlank: true },
{
id: 'opened_date',
name: 'Opened Date',
format: 'dateFormat',
},
{ id: 'store_type', name: 'Store Type', format: 'textFormat', notBlank: true },
];
const productsFields: AgFieldDefinition[] = [
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
hide: false,
},
{
id: 'product_name',
name: 'Product',
format: 'textFormat',
},
{ id: 'category', name: 'Category', format: 'textFormat', notBlank: true },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
},
{ id: 'brand', name: 'Brand', format: 'textFormat', notBlank: true },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat', notBlank: true },
{
id: 'list_price',
name: 'List Price',
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'unit_cost',
name: 'Unit Cost',
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'is_discontinued',
name: 'Discontinued',
format: 'booleanFormat',
},
];
const customersFields: AgFieldDefinition[] = [
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
hide: false,
},
{
id: 'customer_name',
name: 'Customer',
format: 'textFormat',
},
{ id: 'signup_date', name: 'Signup Date', format: 'dateFormat', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', notBlank: true },
{ id: 'segment', name: 'Segment', format: 'textFormat', notBlank: true },
{ id: 'is_active', name: 'Active', format: 'booleanFormat', notBlank: true },
{
id: 'marketing_opt_in',
name: 'Marketing Opt-in',
format: 'booleanFormat',
},
{
id: 'lifetime_orders',
name: 'Lifetime Orders',
format: 'integerFormat',
formatOptions: { format: '#,##0' },
},
];
const ordersFields: AgFieldDefinition[] = [
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
hide: false,
},
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
},
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
},
{
id: 'order_datetime',
name: 'Order Date/Time',
format: 'dateTimeFormat',
},
{ id: 'channel', name: 'Channel', format: 'textFormat', notBlank: true },
{ id: 'status', name: 'Status', format: 'textFormat', notBlank: true },
{
id: 'payment_method',
name: 'Payment Method',
format: 'textFormat',
},
{
id: 'currency',
name: 'Currency',
format: 'textFormat',
hide: false,
},
{
id: 'promo_code',
name: 'Promo Code',
format: 'textFormat',
hide: false,
},
{ id: 'notes', name: 'Notes', format: 'textFormat', hide: false },
{
id: 'order_month',
name: 'Order Month',
format: 'textFormat',
hide: false,
accessor: (row: any) => {
const d = new Date(row.order_datetime);
if (Number.isNaN(d.getTime())) return null;
return `${String(d.getUTCFullYear())}-${String(d.getUTCMonth() + 1).padStart(2, '0')}`;
},
},
];
const orderItemsFields: AgFieldDefinition[] = [
{
id: 'order_item_id',
name: 'Order Item ID',
format: 'textFormat',
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
},
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
},
{
id: 'quantity',
name: 'Qty',
format: 'integerFormat',
formatOptions: { format: '#,##0' },
},
{
id: 'unit_price',
name: 'Unit Price',
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'discount_pct',
name: 'Discount',
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'tax_rate',
name: 'Tax Rate',
format: 'percentageFormat',
formatOptions: { format: '#,##0%' },
},
{ id: 'returned', name: 'Returned', format: 'booleanFormat', notBlank: true },
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
},
];
const shipmentsFields: AgFieldDefinition[] = [
{
id: 'shipment_id',
name: 'Shipment ID',
format: 'textFormat',
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
},
{
id: 'ship_datetime',
name: 'Shipped Date/Time',
format: 'dateTimeFormat',
},
{
id: 'delivery_datetime',
name: 'Delivered Date/Time',
format: 'dateTimeFormat',
},
{ id: 'carrier', name: 'Carrier', format: 'textFormat', notBlank: true },
{ id: 'delayed', name: 'Delayed', format: 'booleanFormat' },
];
// =============================================================================
// JSON Loading & Per-Source Caches
// =============================================================================
async function loadJson(baseUrl: string, filename: string): Promise<any[]> {
const url = `${baseUrl}/${filename}`;
const response = await fetch(url);
if (!response.ok) {
console.error(`Failed to load ${filename}: ${response.status}`);
return [];
}
const data = await response.json();
return data;
}
// =============================================================================
// Data Parsing & Cached Loaders
// =============================================================================
const parseBool = (v: unknown): boolean | undefined =>
v === true || v === 'True' ? true : v === false || v === 'False' ? false : undefined;
// Each loader caches its promise so the file is fetched and parsed at most once.
let storesCache: Promise<any[]> | null = null;
const getStores = (baseUrl: string) => (storesCache ??= loadJson(baseUrl, 'stores.json'));
let productsCache: Promise<any[]> | null = null;
const getProducts = (baseUrl: string) =>
(productsCache ??= loadJson(baseUrl, 'products.json').then((rows) =>
rows.map((row) => ({
...row,
list_price: Number(row.list_price),
unit_cost: Number(row.unit_cost),
is_discontinued: parseBool(row.is_discontinued),
}))
));
let customersCache: Promise<any[]> | null = null;
const getCustomers = (baseUrl: string) =>
(customersCache ??= loadJson(baseUrl, 'customers.json').then((rows) =>
rows.map((row) => ({
...row,
lifetime_orders: row.lifetime_orders !== '' ? Number(row.lifetime_orders) : null,
is_active: parseBool(row.is_active),
marketing_opt_in: parseBool(row.marketing_opt_in),
}))
));
let ordersCache: Promise<any[]> | null = null;
const getOrders = (baseUrl: string) => (ordersCache ??= loadJson(baseUrl, 'orders.json'));
let orderItemsCache: Promise<any[]> | null = null;
const getOrderItems = (baseUrl: string) =>
(orderItemsCache ??= loadJson(baseUrl, 'order_items.json').then((rows) =>
rows.map((row) => ({
...row,
quantity: Number(row.quantity),
unit_price: Number(row.unit_price),
discount_pct: Number(row.discount_pct),
tax_rate: Number(row.tax_rate),
returned: parseBool(row.returned),
}))
));
let shipmentsCache: Promise<any[]> | null = null;
const getShipments = (baseUrl: string) =>
(shipmentsCache ??= loadJson(baseUrl, 'shipments.json').then((rows) =>
rows.map((row) => {
const ship_datetime = row.ship_datetime == null || row.ship_datetime === '' ? null : row.ship_datetime;
const delivery_datetime =
row.delivery_datetime == null || row.delivery_datetime === '' ? null : row.delivery_datetime;
// Always guarantee boolean: default to false if not true.
const delayed =
row.delayed === true || row.delayed === 'True'
? true
: row.delayed === false || row.delayed === 'False'
? false
: false;
return { ...row, ship_datetime, delivery_datetime, delayed };
})
));
// =============================================================================
// Expressions (Calculated Columns & Measures)
// =============================================================================
const expressions: AgExpressionFieldDefinition[] = [
// -------------------------------------------------------------------------
// Pre-aggregation expressions (row-level calculations on order_items)
// -------------------------------------------------------------------------
// line_gross = quantity * unit_price
{
id: 'line_gross',
isMeasure: false,
name: 'Line Gross',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
},
// line_discount_amount = line_gross * discount_pct
{
id: 'line_discount_amount',
isMeasure: false,
name: 'Discount Amount',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{ id: 'order_items.discount_pct' },
],
},
},
// line_net = line_gross - line_discount_amount
{
id: 'line_net',
isMeasure: false,
name: 'Line Net',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
},
// line_cogs = quantity * unit_cost (from products via join)
{
id: 'line_cogs',
isMeasure: false,
name: 'Line COGS',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }],
},
},
// line_margin = line_net - line_cogs
{
id: 'line_margin',
isMeasure: false,
name: 'Line Margin',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
// line_net
{
operator: 'subtract',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
// line_cogs
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }] },
],
},
},
// return_flag = returned IS TRUE (for filtering/counting)
{
id: 'return_flag',
isMeasure: false,
name: 'Return Flag',
hide: true,
expression: {
operator: 'isTrue',
inputs: [{ id: 'order_items.returned' }],
},
},
// returned_line_flag = IF(return_flag, 1, 0) - numeric flag for summing
{
id: 'returned_line_flag',
isMeasure: false,
name: 'Returned Line Flag',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// returned_line_net = IF(return_flag, line_net, 0) - line net only for returned items
{
id: 'returned_line_net',
isMeasure: false,
name: 'Returned Line Net',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_net' }, { type: 'number', value: 0 }],
},
},
// returned_line_margin = IF(return_flag, line_margin, 0) - margin only for returned items
{
id: 'returned_line_margin',
isMeasure: false,
name: 'Returned Line Margin',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_margin' }, { type: 'number', value: 0 }],
},
},
// -------------------------------------------------------------------------
// Post-aggregation expressions (measures for KPIs)
// For now, use pre-agg expressions with aggregation in widgets directly
// -------------------------------------------------------------------------
// Gross Margin % = (SUM(line_net) - SUM(line_cogs)) / SUM(line_net)
{
id: 'gross_margin_pct',
isMeasure: true,
name: 'Gross Margin %',
hide: false,
expression: {
operator: 'divide',
inputs: [
{
operator: 'subtract',
inputs: [
{ id: 'line_net', aggregation: 'sum' },
{ id: 'line_cogs', aggregation: 'sum' },
],
},
{ id: 'line_net', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// order_status_group = IF(status = "Processing", "Open", "Closed")
{
id: 'order_status_group',
isMeasure: false,
name: 'Status Group',
expression: {
operator: 'if',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Processing' }] },
{ type: 'string', value: 'Open' },
{ type: 'string', value: 'Closed' },
],
},
},
// is_closed = status IN ("Completed", "Returned", "Cancelled") via chained OR
{
id: 'is_closed',
isMeasure: false,
name: 'Is Closed',
expression: {
operator: 'or',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Completed' }] },
{
operator: 'or',
inputs: [
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Returned' }],
},
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Cancelled' }],
},
],
},
],
},
},
// ship_to_delivery_days = DATEDIFF(day, ship_datetime, delivery_datetime)
{
id: 'ship_to_delivery_days',
isMeasure: false,
name: 'Ship to Delivery Days',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'day' },
{ id: 'shipments.ship_datetime' },
{ id: 'shipments.delivery_datetime' },
],
},
},
// order_to_ship_hours = DATEDIFF(hour, order_datetime, ship_datetime)
{
id: 'order_to_ship_hours',
isMeasure: false,
name: 'Order to Ship Hours',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'hour' },
{ id: 'orders.order_datetime' },
{ id: 'shipments.ship_datetime' },
],
},
},
// is_shipped = ship_datetime IS NOT NULL
{
id: 'is_shipped',
isMeasure: false,
name: 'Is Shipped',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.ship_datetime' }],
},
},
// is_delivered = delivery_datetime IS NOT NULL
{
id: 'is_delivered',
isMeasure: false,
name: 'Is Delivered',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.delivery_datetime' }],
},
},
// is_on_time = IF(is_delivered, delayed = FALSE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_on_time',
isMeasure: false,
name: 'Is On Time',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: false }] },
{ type: 'boolean', value: false },
],
},
},
// is_delayed = IF(is_delivered, delayed = TRUE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_delayed',
isMeasure: false,
name: 'Is Delayed',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: true }] },
{ type: 'boolean', value: false },
],
},
},
// delayed_shipments = IF(is_delayed, 1, 0) - numeric flag for stacking
{
id: 'delayed_shipments',
isMeasure: false,
name: 'Delayed Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delayed' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// on_time_shipments = IF(is_on_time, 1, 0) - numeric flag for stacking
{
id: 'on_time_shipments',
isMeasure: false,
name: 'On-time Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delivered_shipments = IF(is_delivered, 1, 0) - numeric flag for rate denominators
{
id: 'delivered_shipments',
isMeasure: false,
name: 'Delivered Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delay_rate = SUM(delayed_shipments) / SUM(delivered_shipments)
// Use delivered shipments as the denominator so pending (not delivered) rows don't dilute the rate.
{
id: 'delay_rate',
isMeasure: true,
name: 'Delay Rate',
hide: false,
expression: {
operator: 'divide',
inputs: [
{ id: 'delayed_shipments', aggregation: 'sum' },
{ id: 'delivered_shipments', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// shipped_order_id = IF(is_shipped, order_id, NULL)
{
id: 'shipped_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_shipped' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// on_time_order_id = IF(is_on_time, order_id, NULL)
{
id: 'on_time_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivered_order_id = IF(is_delivered, order_id, NULL)
{
id: 'delivered_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivery_status = IF(NOT is_shipped, "Not shipped", IF(delayed = true, "Delayed", "On time"))
{
id: 'delivery_status',
isMeasure: false,
name: 'Delivery Status',
expression: {
operator: 'if',
inputs: [
{ operator: 'not', inputs: [{ id: 'is_shipped' }] },
{ type: 'string', value: 'Not shipped' },
{
operator: 'if',
inputs: [
{ id: 'shipments.delayed' },
{ type: 'string', value: 'Delayed' },
{ type: 'string', value: 'On time' },
],
},
],
},
},
// returned_order_id = IF(return_flag, order_id, NULL)
// Used for counting orders that have at least one returned line.
{
id: 'returned_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'order_items.order_id' }, { type: 'string', value: null }],
},
},
// Aggregated calculations for KPIs (using pre-aggregation expressions as inputs)
{
id: 'net_sales',
isMeasure: true,
name: 'Net Sales',
expression: {
id: 'line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_sales',
isMeasure: true,
name: 'Gross Sales',
expression: {
id: 'line_gross',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'discount_amount',
isMeasure: true,
name: 'Discount Amount',
expression: {
id: 'line_discount_amount',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
{
id: 'COGS',
isMeasure: true,
name: 'COGS',
expression: {
id: 'line_cogs',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_margin',
isMeasure: true,
name: 'Gross Margin',
expression: {
id: 'line_margin',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_margin_percentage',
isMeasure: true,
name: 'Gross Margin %',
expression: {
operator: 'divide',
inputs: [{ id: 'gross_margin' }, { id: 'net_sales' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// --- Orders ---
{
id: 'order_count',
isMeasure: true,
name: 'Order Count',
expression: {
id: 'orders.order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'average_order_value',
isMeasure: true,
name: 'Average Order Value',
expression: {
operator: 'divide',
inputs: [{ id: 'net_sales' }, { id: 'order_count' }],
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'active_customers',
isMeasure: true,
name: 'Active Customers',
expression: {
id: 'orders.customer_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
// --- Returns ---
{
id: 'returned_lines',
isMeasure: true,
name: 'Returned Lines',
expression: {
id: 'returned_line_flag',
aggregation: 'sum',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'returned_orders',
isMeasure: true,
name: 'Returned Orders',
expression: {
id: 'returned_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'return_rate',
isMeasure: true,
name: 'Return Rate (Lines)',
expression: {
operator: 'divide',
inputs: [{ id: 'returned_lines' }, { id: 'order_items.order_item_id', aggregation: 'count' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'return_value',
isMeasure: true,
name: 'Return Value',
expression: {
id: 'returned_line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
// return_margin_impact = -SUM(returned_line_margin)
// Display as a negative number to represent margin lost due to returns.
{
id: 'return_margin_impact',
isMeasure: true,
name: 'Return Margin Impact',
expression: {
operator: 'subtract',
inputs: [
{ type: 'number', value: 0 },
{ id: 'returned_line_margin', aggregation: 'sum' },
],
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
// --- Delivery ---
{
id: 'shipped_orders',
isMeasure: true,
name: 'Shipped Orders',
expression: {
id: 'shipped_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'delivered_orders',
isMeasure: true,
name: 'Delivered Orders',
expression: {
id: 'delivered_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'delivered_not_returned_orders',
isMeasure: true,
name: 'Delivered (Not Returned)',
expression: {
operator: 'subtract',
inputs: [{ id: 'delivered_orders' }, { id: 'returned_orders' }],
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_deliveries',
isMeasure: true,
name: 'On-time Deliveries',
expression: {
id: 'on_time_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_rate',
isMeasure: true,
name: 'On-time Rate',
expression: {
operator: 'divide',
inputs: [{ id: 'on_time_deliveries' }, { id: 'delivered_orders' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'avg_ship_to_delivery_days',
isMeasure: true,
name: 'Avg Ship to Delivery Days',
expression: {
id: 'ship_to_delivery_days',
aggregation: 'avg',
},
format: 'decimalFormat',
formatOptions: { format: '#,##0.0' },
},
];
// =============================================================================
// Data Source Definition
// =============================================================================
export function getMainDemoData(baseUrl: string): AgDataSourcesDefinition {
const url = `${baseUrl}/main-demo`;
return {
sources: [
{
id: 'stores',
name: 'Stores',
dataShape: 'row',
tables: [{ id: 'stores', name: 'Stores', fields: storesFields }],
getData: async () => ({ data: await getStores(url) }),
},
{
id: 'products',
name: 'Products',
dataShape: 'row',
tables: [{ id: 'products', name: 'Products', fields: productsFields }],
getData: async () => ({ data: await getProducts(url) }),
},
{
id: 'customers',
name: 'Customers',
dataShape: 'row',
tables: [{ id: 'customers', name: 'Customers', fields: customersFields }],
getData: async () => ({ data: await getCustomers(url) }),
},
{
id: 'orders',
name: 'Orders',
dataShape: 'row',
tables: [{ id: 'orders', name: 'Orders', fields: ordersFields }],
getData: async () => ({ data: await getOrders(url) }),
},
{
id: 'order_items',
name: 'Order Items',
dataShape: 'row',
tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
getData: async () => ({ data: await getOrderItems(url) }),
},
{
id: 'shipments',
name: 'Shipments',
dataShape: 'row',
tables: [{ id: 'shipments', name: 'Shipments', fields: shipmentsFields }],
getData: async () => ({ data: await getShipments(url) }),
},
],
relationships: [
{
id: 'orders-customers',
source: { tableId: 'orders', fieldId: 'customer_id' },
target: { tableId: 'customers', fieldId: 'customer_id' },
type: 'many-to-one',
},
{
id: 'orders-stores',
source: { tableId: 'orders', fieldId: 'store_id' },
target: { tableId: 'stores', fieldId: 'store_id' },
type: 'many-to-one',
},
{
id: 'order_items-orders',
source: { tableId: 'order_items', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
{
id: 'order_items-products',
source: { tableId: 'order_items', fieldId: 'product_id' },
target: { tableId: 'products', fieldId: 'product_id' },
type: 'many-to-one',
// line_cogs/line_margin multiply order_items.quantity (many-side, varies per row) by
// products.unit_cost (one-side) before summing - grain-invariant-safe, not a real fan-out.
acceptFanout: true,
},
{
id: 'shipments-orders',
source: { tableId: 'shipments', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
],
expressions,
};
}
Scatter and Bubble Charts Copy Link
The Legend field splits plotted points into one coloured series per distinct value, with consistent colours and tooltips reflecting the hovered point's legend value. Bubble charts behave identically: the Size measure still drives bubble area within each series.
The example below plots average quantity against average discount as a bubble chart, with bubble size driven by net sales and Category assigned to the Legend slot, so each point belongs to one coloured series per product category.
import { Component } from "@angular/core";
import { AgStudio } from "ag-studio-angular";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgPanelConfig,
AgReportState,
AgStudioApi,
AgStudioApiReadyEvent,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
@Component({
selector: "my-app",
standalone: true,
imports: [AgStudio],
template: `<div style="display: flex; flex-direction: column; height: 100%">
<ag-studio
style="width: 100%; height: 100%;"
class="my-studio-container"
[initialState]="initialState"
[mode]="mode"
[panels]="panels"
[data]="data"
/>
</div> `,
})
export class AppComponent {
initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"qty-vs-discount": {
type: "bubble-chart",
dataMapping: {
groupByKey: [{ id: "products.brand" }],
categoryKey: [{ id: "order_items.quantity", aggregation: "avg" }],
valueKey: [
{ id: "order_items.discount_pct", aggregation: "avg" },
],
sizeKey: [{ id: "line_net", aggregation: "sum" }],
legendKey: [{ id: "products.subcategory" }],
},
format: {
title: {
enabled: true,
text: "Quantity vs Discount (size by Net Sales)",
},
style: {
theme: {
common: { legend: { enabled: true, position: "bottom" } },
},
},
},
},
},
widgetLayout: {
"qty-vs-discount": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 38 },
},
selection: { type: "widget", id: "qty-vs-discount" },
},
],
selectedPageId: "a",
};
mode: AgStudioMode = "edit";
panels: AgPanelConfig = {
edit: {
right: ["edit"],
},
};
data: AgDataSourcesDefinition | AgDataEngine = getMainDemoData(
"https://www.ag-grid.com/studio/archive/3.0.0/example-assets",
);
}
import '@angular/compiler';
import { provideHttpClient } from '@angular/common/http';
import { bootstrapApplication } from '@angular/platform-browser';
import { AppComponent } from './app.component.ts';
const app = bootstrapApplication(AppComponent, {
providers: [provideHttpClient()],
});
import type {
AgCalendar,
AgDataSourcesDefinition,
AgExpressionFieldDefinition,
AgFieldDefinition,
AgRelationDefinition,
} from 'ag-studio';
// =============================================================================
// Field Definitions
// =============================================================================
export const storesFields: AgFieldDefinition[] = [
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{ id: 'store_name', name: 'Store', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'city', name: 'City', format: 'textFormat', cardinality: 'low', notBlank: true },
{
id: 'opened_date',
name: 'Opened Date',
format: 'dateFormat',
cardinality: 'medium',
notBlank: true,
},
{ id: 'store_type', name: 'Store Type', format: 'textFormat', cardinality: 'low', notBlank: true },
];
export const productsFields: AgFieldDefinition[] = [
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
hide: false,
},
{
id: 'product_name',
name: 'Product',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{ id: 'category', name: 'Category', format: 'textFormat', cardinality: 'low', notBlank: true },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
cardinality: 'medium',
notBlank: true,
},
{ id: 'brand', name: 'Brand', format: 'textFormat', cardinality: 'medium', notBlank: true },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat', cardinality: 'high', notBlank: true },
{
id: 'list_price',
name: 'List Price',
format: 'currencyFormat',
cardinality: 'high',
notBlank: true,
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'unit_cost',
name: 'Unit Cost',
format: 'currencyFormat',
cardinality: 'high',
notBlank: true,
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'is_discontinued',
name: 'Discontinued',
format: 'booleanFormat',
cardinality: 'low',
notBlank: true,
},
];
export const customersFields: AgFieldDefinition[] = [
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
hide: false,
},
{
id: 'customer_name',
name: 'Customer',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{ id: 'signup_date', name: 'Signup Date', format: 'dateFormat', cardinality: 'high', notBlank: true },
{ id: 'region', name: 'Region', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'segment', name: 'Segment', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'is_active', name: 'Active', format: 'booleanFormat', cardinality: 'low', notBlank: true },
{
id: 'marketing_opt_in',
name: 'Marketing Opt-in',
format: 'booleanFormat',
cardinality: 'low',
notBlank: true,
},
];
export const ordersFields: AgFieldDefinition[] = [
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
hide: false,
},
{
id: 'customer_id',
name: 'Customer ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'store_id',
name: 'Store ID',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'order_datetime',
name: 'Order Date/Time',
format: 'dateTimeFormat',
cardinality: 'high',
notBlank: true,
},
{ id: 'channel', name: 'Channel', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'status', name: 'Status', format: 'textFormat', cardinality: 'low', notBlank: true },
{
id: 'payment_method',
name: 'Payment Method',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'currency',
name: 'Currency',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
hide: false,
},
{
id: 'promo_code',
name: 'Promo Code',
format: 'textFormat',
cardinality: 'medium',
notBlank: false,
hide: false,
},
{ id: 'notes', name: 'Notes', format: 'textFormat', cardinality: 'high', notBlank: false, hide: false },
];
export const orderItemsFields: AgFieldDefinition[] = [
{
id: 'order_item_id',
name: 'Order Item ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'quantity',
name: 'Qty',
format: 'integerFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '#,##0' },
},
{
id: 'unit_price',
name: 'Unit Price',
format: 'currencyFormat',
cardinality: 'high',
notBlank: true,
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'discount_pct',
name: 'Discount',
format: 'percentageFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '#,##0.0%' },
},
{
id: 'tax_rate',
name: 'Tax Rate',
format: 'percentageFormat',
cardinality: 'low',
notBlank: true,
formatOptions: { format: '#,##0%' },
},
{ id: 'returned', name: 'Returned', format: 'booleanFormat', cardinality: 'low', notBlank: true },
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
cardinality: 'low',
notBlank: false,
},
];
export const shipmentsFields: AgFieldDefinition[] = [
{
id: 'shipment_id',
name: 'Shipment ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
cardinality: 'high',
notBlank: true,
},
{
id: 'ship_datetime',
name: 'Shipped Date/Time',
format: 'dateTimeFormat',
cardinality: 'high',
notBlank: false,
},
{
id: 'delivery_datetime',
name: 'Delivered Date/Time',
format: 'dateTimeFormat',
cardinality: 'high',
notBlank: false,
},
{ id: 'carrier', name: 'Carrier', format: 'textFormat', cardinality: 'low', notBlank: true },
{ id: 'delayed', name: 'Delayed', format: 'booleanFormat', cardinality: 'low', notBlank: false },
];
const returnCostsFields: AgFieldDefinition[] = [
{
id: 'period',
name: 'Period',
format: 'dateFormat',
cardinality: 'medium',
notBlank: true,
},
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
cardinality: 'low',
notBlank: true,
},
{
id: 'refunds',
name: 'Refunds',
format: 'currencyFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '£#,##0.0,K' },
},
{
id: 'shipping',
name: 'Shipping',
format: 'currencyFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '£#,##0.0,K' },
},
{
id: 'write_offs',
name: 'Write-offs',
format: 'currencyFormat',
cardinality: 'medium',
notBlank: true,
formatOptions: { format: '£#,##0.0,K' },
},
];
// =============================================================================
// JSON Loading & Per-Source Caches
// =============================================================================
async function loadJson(baseUrl: string, filename: string): Promise<any[]> {
const url = `${baseUrl}/${filename}`;
const response = await fetch(url);
if (!response.ok) {
console.error(`Failed to load ${filename}: ${response.status}`);
return [];
}
const data = await response.json();
return data;
}
// =============================================================================
// Cached Loaders
// =============================================================================
// Each loader caches its promise so the file is fetched and parsed at most once.
let storesCache: Promise<any[]> | null = null;
const getStores = (baseUrl: string) => (storesCache ??= loadJson(baseUrl, 'stores.json'));
let productsCache: Promise<any[]> | null = null;
const getProducts = (baseUrl: string) => (productsCache ??= loadJson(baseUrl, 'products.json'));
let customersCache: Promise<any[]> | null = null;
const getCustomers = (baseUrl: string) => (customersCache ??= loadJson(baseUrl, 'customers.json'));
let ordersCache: Promise<any[]> | null = null;
const getOrders = (baseUrl: string) => (ordersCache ??= loadJson(baseUrl, 'orders.json'));
let orderItemsCache: Promise<any[]> | null = null;
const getOrderItems = (baseUrl: string) => (orderItemsCache ??= loadJson(baseUrl, 'order_items.json'));
let shipmentsCache: Promise<any[]> | null = null;
const getShipments = (baseUrl: string) => (shipmentsCache ??= loadJson(baseUrl, 'shipments.json'));
// =============================================================================
// Expressions (Calculated Columns & Measures)
// =============================================================================
export const expressions: AgExpressionFieldDefinition[] = [
// -------------------------------------------------------------------------
// Pre-aggregation expressions (row-level calculations on order_items)
// -------------------------------------------------------------------------
// line_gross = quantity * unit_price
{
id: 'line_gross',
isMeasure: false,
name: 'Line Gross',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
},
// line_discount_amount = line_gross * discount_pct
{
id: 'line_discount_amount',
isMeasure: false,
name: 'Discount Amount',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{ id: 'order_items.discount_pct' },
],
},
},
// line_net = line_gross - line_discount_amount
{
id: 'line_net',
isMeasure: false,
name: 'Line Net',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
},
// line_cogs = quantity * unit_cost (from products via join)
{
id: 'line_cogs',
isMeasure: false,
name: 'Line COGS',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }],
},
},
// line_margin = line_net - line_cogs
{
id: 'line_margin',
isMeasure: false,
name: 'Line Margin',
hide: false,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'subtract',
inputs: [
// line_net
{
operator: 'subtract',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{
operator: 'multiply',
inputs: [
{
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
},
{ id: 'order_items.discount_pct' },
],
},
],
},
// line_cogs
{ operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }] },
],
},
},
// return_flag = returned IS TRUE (for filtering/counting)
{
id: 'return_flag',
isMeasure: false,
name: 'Return Flag',
hide: true,
expression: {
operator: 'isTrue',
inputs: [{ id: 'order_items.returned' }],
},
},
// returned_line_flag = IF(return_flag, 1, 0) - numeric flag for summing
{
id: 'returned_line_flag',
isMeasure: false,
name: 'Returned Line Flag',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// returned_line_net = IF(return_flag, line_net, 0) - line net only for returned items
{
id: 'returned_line_net',
isMeasure: false,
name: 'Returned Line Net',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_net' }, { type: 'number', value: 0 }],
},
},
// returned_line_margin = IF(return_flag, line_margin, 0) - margin only for returned items
{
id: 'returned_line_margin',
isMeasure: false,
name: 'Returned Line Margin',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_margin' }, { type: 'number', value: 0 }],
},
},
// returned_line_cogs = IF(return_flag, line_cogs, 0)
{
id: 'returned_line_cogs',
isMeasure: false,
name: 'Returned Line COGS',
hide: true,
format: 'currencyFormat',
formatOptions: { format: '£#,##0.00' },
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'line_cogs' }, { type: 'number', value: 0 }],
},
},
// return_refunds = returned_line_net * 0.45
{
id: 'return_refunds',
isMeasure: false,
name: 'Refunds',
hide: true,
format: 'currencyFormat',
formatOptions: { format: '£#,##0.0,K' },
expression: {
operator: 'multiply',
inputs: [{ id: 'returned_line_net' }, { type: 'number', value: 0.45 }],
},
},
// return_shipping = returned_line_margin * 0.65
{
id: 'return_shipping',
isMeasure: false,
name: 'Shipping',
hide: true,
format: 'currencyFormat',
formatOptions: { format: '£#,##0.0,K' },
expression: {
operator: 'multiply',
inputs: [{ id: 'returned_line_margin' }, { type: 'number', value: 0.65 }],
},
},
// return_write_offs = returned_line_cogs * 0.35
{
id: 'return_write_offs',
isMeasure: false,
name: 'Write-offs',
hide: true,
format: 'currencyFormat',
formatOptions: { format: '£#,##0.0,K' },
expression: {
operator: 'multiply',
inputs: [{ id: 'returned_line_cogs' }, { type: 'number', value: 0.35 }],
},
},
// -------------------------------------------------------------------------
// Post-aggregation expressions (measures for KPIs)
// For now, use pre-agg expressions with aggregation in widgets directly
// -------------------------------------------------------------------------
// Gross Margin % = (SUM(line_net) - SUM(line_cogs)) / SUM(line_net)
{
id: 'gross_margin_pct',
isMeasure: true,
name: 'Gross Margin %',
hide: false,
expression: {
operator: 'divide',
inputs: [
{
operator: 'subtract',
inputs: [
{ id: 'line_net', aggregation: 'sum' },
{ id: 'line_cogs', aggregation: 'sum' },
],
},
{ id: 'line_net', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// order_status_group = IF(status = "Processing", "Open", "Closed")
{
id: 'order_status_group',
isMeasure: false,
name: 'Status Group',
expression: {
operator: 'if',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Processing' }] },
{ type: 'string', value: 'Open' },
{ type: 'string', value: 'Closed' },
],
},
},
// is_closed = status IN ("Completed", "Returned", "Cancelled") via chained OR
{
id: 'is_closed',
isMeasure: false,
name: 'Is Closed',
expression: {
operator: 'or',
inputs: [
{ operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Completed' }] },
{
operator: 'or',
inputs: [
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Returned' }],
},
{
operator: 'equals',
inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Cancelled' }],
},
],
},
],
},
},
// ship_to_delivery_days = DATEDIFF(day, ship_datetime, delivery_datetime)
{
id: 'ship_to_delivery_days',
isMeasure: false,
name: 'Ship to Delivery Days',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'day' },
{ id: 'shipments.ship_datetime' },
{ id: 'shipments.delivery_datetime' },
],
},
},
// order_to_ship_hours = DATEDIFF(hour, order_datetime, ship_datetime)
{
id: 'order_to_ship_hours',
isMeasure: false,
name: 'Order to Ship Hours',
expression: {
operator: 'datediff',
inputs: [
{ type: 'string', value: 'hour' },
{ id: 'orders.order_datetime' },
{ id: 'shipments.ship_datetime' },
],
},
},
// is_shipped = ship_datetime IS NOT NULL
{
id: 'is_shipped',
isMeasure: false,
name: 'Is Shipped',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.ship_datetime' }],
},
},
// is_delivered = delivery_datetime IS NOT NULL
{
id: 'is_delivered',
isMeasure: false,
name: 'Is Delivered',
hide: true,
expression: {
operator: 'isNotNull',
inputs: [{ id: 'shipments.delivery_datetime' }],
},
},
// is_on_time = IF(is_delivered, delayed = FALSE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_on_time',
isMeasure: false,
name: 'Is On Time',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: false }] },
{ type: 'boolean', value: false },
],
},
},
// is_delayed = IF(is_delivered, delayed = TRUE, FALSE)
// Use IF+EQUALS to guarantee a boolean result.
{
id: 'is_delayed',
isMeasure: false,
name: 'Is Delayed',
hide: true,
expression: {
operator: 'if',
inputs: [
{ id: 'is_delivered' },
{ operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: true }] },
{ type: 'boolean', value: false },
],
},
},
// delayed_shipments = IF(is_delayed, 1, 0) - numeric flag for stacking
{
id: 'delayed_shipments',
isMeasure: false,
name: 'Delayed Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delayed' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// on_time_shipments = IF(is_on_time, 1, 0) - numeric flag for stacking
{
id: 'on_time_shipments',
isMeasure: false,
name: 'On-time Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delivered_shipments = IF(is_delivered, 1, 0) - numeric flag for rate denominators
{
id: 'delivered_shipments',
isMeasure: false,
name: 'Delivered Shipments',
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
},
},
// delay_rate = SUM(delayed_shipments) / SUM(delivered_shipments)
// Use delivered shipments as the denominator so pending (not delivered) rows don't dilute the rate.
{
id: 'delay_rate',
isMeasure: true,
name: 'Delay Rate',
hide: false,
expression: {
operator: 'divide',
inputs: [
{ id: 'delayed_shipments', aggregation: 'sum' },
{ id: 'delivered_shipments', aggregation: 'sum' },
],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// shipped_order_id = IF(is_shipped, order_id, NULL)
{
id: 'shipped_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_shipped' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// on_time_order_id = IF(is_on_time, order_id, NULL)
{
id: 'on_time_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_on_time' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivered_order_id = IF(is_delivered, order_id, NULL)
{
id: 'delivered_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'is_delivered' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
},
},
// delivery_status = IF(NOT is_shipped, "Not shipped", IF(delayed = true, "Delayed", "On time"))
{
id: 'delivery_status',
isMeasure: false,
name: 'Delivery Status',
expression: {
operator: 'if',
inputs: [
{ operator: 'not', inputs: [{ id: 'is_shipped' }] },
{ type: 'string', value: 'Not shipped' },
{
operator: 'if',
inputs: [
{ id: 'shipments.delayed' },
{ type: 'string', value: 'Delayed' },
{ type: 'string', value: 'On time' },
],
},
],
},
},
// returned_order_id = IF(return_flag, order_id, NULL)
// Used for counting orders that have at least one returned line.
{
id: 'returned_order_id',
isMeasure: false,
hide: true,
expression: {
operator: 'if',
inputs: [{ id: 'return_flag' }, { id: 'order_items.order_id' }, { type: 'string', value: null }],
},
},
// Aggregated calculations for KPIs (using pre-aggregation expressions as inputs)
{
id: 'net_sales',
isMeasure: true,
name: 'Net Sales',
expression: {
id: 'line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_sales',
isMeasure: true,
name: 'Gross Sales',
expression: {
id: 'line_gross',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'discount_amount',
isMeasure: true,
name: 'Discount Amount',
expression: {
id: 'line_discount_amount',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
{
id: 'COGS',
isMeasure: true,
name: 'COGS',
expression: {
id: 'line_cogs',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_margin',
isMeasure: true,
name: 'Gross Margin',
expression: {
id: 'line_margin',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,,\\M',
},
},
{
id: 'gross_margin_percentage',
isMeasure: true,
name: 'Gross Margin %',
expression: {
operator: 'divide',
inputs: [{ id: 'gross_margin' }, { id: 'net_sales' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
// --- Orders ---
{
id: 'order_count',
isMeasure: true,
name: 'Order Count',
expression: {
id: 'orders.order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'average_order_value',
isMeasure: true,
name: 'Average Order Value',
expression: {
operator: 'divide',
inputs: [{ id: 'net_sales' }, { id: 'order_count' }],
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'active_customers',
isMeasure: true,
name: 'Active Customers',
expression: {
id: 'orders.customer_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
// --- Returns ---
{
id: 'returned_lines',
isMeasure: true,
name: 'Returned Lines',
expression: {
id: 'returned_line_flag',
aggregation: 'sum',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'returned_orders',
isMeasure: true,
name: 'Returned Orders',
expression: {
id: 'returned_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0',
},
},
{
id: 'return_rate',
isMeasure: true,
name: 'Return Rate (Lines)',
expression: {
operator: 'divide',
inputs: [{ id: 'returned_lines' }, { id: 'order_items.order_item_id', aggregation: 'count' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'return_value',
isMeasure: true,
name: 'Return Value',
expression: {
id: 'returned_line_net',
aggregation: 'sum',
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
// return_margin_impact = -SUM(returned_line_margin)
// Display as a negative number to represent margin lost due to returns.
{
id: 'return_margin_impact',
isMeasure: true,
name: 'Return Margin Impact',
expression: {
operator: 'subtract',
inputs: [
{ type: 'number', value: 0 },
{ id: 'returned_line_margin', aggregation: 'sum' },
],
},
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.0,K',
},
},
// --- Delivery ---
{
id: 'shipped_orders',
isMeasure: true,
name: 'Shipped Orders',
expression: {
id: 'shipped_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
},
{
id: 'delivered_orders',
isMeasure: true,
name: 'Delivered Orders',
expression: {
id: 'delivered_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
},
{
id: 'delivered_not_returned_orders',
isMeasure: true,
name: 'Delivered (Not Returned)',
expression: {
operator: 'subtract',
inputs: [{ id: 'delivered_orders' }, { id: 'returned_orders' }],
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_deliveries',
isMeasure: true,
name: 'On-time Deliveries',
expression: {
id: 'on_time_order_id',
aggregation: 'countd',
},
format: 'integerFormat',
formatOptions: {
format: '#,##0,K',
},
},
{
id: 'on_time_rate',
isMeasure: true,
name: 'On-time Rate',
expression: {
operator: 'divide',
inputs: [{ id: 'on_time_deliveries' }, { id: 'delivered_orders' }],
},
format: 'percentageFormat',
formatOptions: { format: '#,##0.0%' },
},
{
id: 'avg_ship_to_delivery_days',
isMeasure: true,
name: 'Avg Ship to Delivery Days',
expression: {
id: 'ship_to_delivery_days',
aggregation: 'avg',
},
format: 'decimalFormat',
formatOptions: { format: '#,##0.0' },
},
];
// =============================================================================
// Computed Table Data (shared cache across all four pre-aggregated tables)
// =============================================================================
interface ReturnCostsRow {
period: string;
subcategory: string;
return_reason: string;
refunds: number;
shipping: number;
write_offs: number;
}
interface ComputedTableData {
returnCosts: any[];
}
let computedDataPromise: Promise<ComputedTableData> | null = null;
function getComputedData(baseUrl: string): Promise<ComputedTableData> {
return (computedDataPromise ??= buildComputedData(baseUrl));
}
async function buildComputedData(baseUrl: string): Promise<ComputedTableData> {
const [orders, orderItems, products] = await Promise.all([
getOrders(baseUrl),
getOrderItems(baseUrl),
getProducts(baseUrl),
]);
const toMonthIsoStr = (raw: any): string | null => {
const d = raw instanceof Date ? raw : raw != null && raw !== '' ? new Date(raw) : null;
if (!d || Number.isNaN(d.getTime())) return null;
const year = d.getUTCFullYear();
const month1 = d.getUTCMonth() + 1;
return `${year}-${String(month1).padStart(2, '0')}`;
};
// Order -> Month ISO string ("YYYY-MM")
const orderMonthById = new Map<string, string>();
for (const o of orders) {
const month = toMonthIsoStr((o as any).order_datetime);
if (month != null) {
orderMonthById.set(String((o as any).order_id), month);
}
}
// Helper to compute line-net consistent with existing expressions
const computeLineNet = (oi: any): number => {
const qty = Number(oi.quantity ?? 0);
const unitPrice = Number(oi.unit_price ?? 0);
const discountPct = Number(oi.discount_pct ?? 0);
const gross = qty * unitPrice;
return gross - gross * discountPct;
};
// Product -> unit_cost index (for margin)
const unitCostByProductId = new Map<string, number>();
for (const p of products) {
unitCostByProductId.set(String((p as any).product_id), Number((p as any).unit_cost ?? 0));
}
const computeLineCogs = (oi: any): number => {
const qty = Number(oi.quantity ?? 0);
const unitCost = unitCostByProductId.get(String(oi.product_id)) ?? 0;
return qty * unitCost;
};
// --- Return costs at month × subcategory granularity ---
const productSubcategory = new Map<string, string>();
for (const p of products) {
productSubcategory.set(String((p as any).product_id), String((p as any).subcategory));
}
// Accumulate net/cogs per month×subcategory×return_reason triple
const cellNet = new Map<string, number>();
const cellCogs = new Map<string, number>();
for (const oi of orderItems) {
if ((oi as any).returned !== true) continue;
const month = orderMonthById.get(String((oi as any).order_id));
if (month == null) continue;
const sub = productSubcategory.get(String((oi as any).product_id));
if (!sub) continue;
const reason = String((oi as any).return_reason ?? '');
if (!reason) continue;
const lineNet = computeLineNet(oi);
const lineCogs = computeLineCogs(oi);
const key = `${month}|${sub}|${reason}`;
cellNet.set(key, (cellNet.get(key) ?? 0) + lineNet);
cellCogs.set(key, (cellCogs.get(key) ?? 0) + lineCogs);
}
const allSubs = new Set<string>();
const allReasons = new Set<string>();
for (const key of cellNet.keys()) {
const parts = key.split('|');
allSubs.add(parts[1]);
allReasons.add(parts[2]);
}
// Build rows for real months
const realMonthStrs = Array.from(new Set(Array.from(cellNet.keys()).map((k) => k.split('|')[0]))).sort();
const realRows: ReturnCostsRow[] = [];
for (const monthStr of realMonthStrs) {
for (const sub of allSubs) {
for (const reason of allReasons) {
const key = `${monthStr}|${sub}|${reason}`;
const net = cellNet.get(key) ?? 0;
const cogs = cellCogs.get(key) ?? 0;
if (net === 0 && cogs === 0) continue;
const margin = net - cogs;
realRows.push({
period: `${monthStr}-01`,
subcategory: sub,
return_reason: reason,
refunds: Math.round(net * 0.45),
shipping: Math.round(margin * 0.65),
write_offs: Math.round(cogs * 0.35),
});
}
}
}
return { returnCosts: realRows };
}
// =============================================================================
// Relationships
// =============================================================================
export const mainDemoCalendar: AgCalendar = {
id: 'calendar',
label: 'Calendar',
range: {
from: { type: 'date', value: '2021-01-01' },
to: { operator: 'currentDate', inputs: [] },
},
fragments: ['year', 'quarter', { unit: 'month', format: 'MMM yyyy' }, 'week', 'day', 'monthOfYear', 'dayOfWeek'],
};
export const relationships: AgRelationDefinition[] = [
{
id: 'orders-customers',
source: { tableId: 'orders', fieldId: 'customer_id' },
target: { tableId: 'customers', fieldId: 'customer_id' },
type: 'many-to-one',
},
{
id: 'orders-stores',
source: { tableId: 'orders', fieldId: 'store_id' },
target: { tableId: 'stores', fieldId: 'store_id' },
type: 'many-to-one',
},
{
id: 'order_items-orders',
source: { tableId: 'order_items', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
{
id: 'order_items-products',
source: { tableId: 'order_items', fieldId: 'product_id' },
target: { tableId: 'products', fieldId: 'product_id' },
type: 'many-to-one',
// line_cogs/line_margin multiply order_items.quantity (many-side, varies per row) by
// products.unit_cost (one-side) before summing - grain-invariant-safe, not a real fan-out.
acceptFanout: true,
},
{
id: 'shipments-orders',
source: { tableId: 'shipments', fieldId: 'order_id' },
target: { tableId: 'orders', fieldId: 'order_id' },
type: 'many-to-one',
},
{
id: 'return_costs-products',
source: { tableId: 'return_costs', fieldId: 'subcategory' },
target: { tableId: 'products', fieldId: 'subcategory' },
type: 'many-to-many',
},
// Calendar bindings - fact date columns bound to the calendar
{
id: 'orders-calendar',
source: { tableId: 'orders', fieldId: 'order_datetime' },
target: { calendarId: 'calendar' },
truncate: 'day',
},
{
id: 'shipments-calendar-ship',
source: { tableId: 'shipments', fieldId: 'ship_datetime' },
target: { calendarId: 'calendar' },
truncate: 'day',
},
{
id: 'return_costs-calendar',
source: { tableId: 'return_costs', fieldId: 'period' },
target: { calendarId: 'calendar' },
},
];
// =============================================================================
// Data Source Definition
// =============================================================================
export function getMainDemoData(baseUrl: string): AgDataSourcesDefinition {
const url = `${baseUrl}/main-demo`;
return {
sources: [
{
id: 'stores',
name: 'Stores',
dataShape: 'row',
tables: [{ id: 'stores', name: 'Stores', fields: storesFields }],
getData: async () => ({ data: await getStores(url) }),
},
{
id: 'products',
name: 'Products',
dataShape: 'row',
tables: [{ id: 'products', name: 'Products', fields: productsFields }],
getData: async () => ({ data: await getProducts(url) }),
},
{
id: 'customers',
name: 'Customers',
dataShape: 'row',
tables: [{ id: 'customers', name: 'Customers', fields: customersFields }],
getData: async () => ({ data: await getCustomers(url) }),
},
{
id: 'orders',
name: 'Orders',
dataShape: 'row',
tables: [{ id: 'orders', name: 'Orders', fields: ordersFields }],
getData: async () => ({ data: await getOrders(url) }),
},
{
id: 'order_items',
name: 'Order Items',
dataShape: 'row',
tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
getData: async () => ({ data: await getOrderItems(url) }),
},
{
id: 'shipments',
name: 'Shipments',
dataShape: 'row',
tables: [{ id: 'shipments', name: 'Shipments', fields: shipmentsFields }],
getData: async () => ({ data: await getShipments(url) }),
},
{
id: 'return_costs',
name: 'Return Costs',
dataShape: 'row',
tables: [{ id: 'return_costs', name: 'Return Costs', fields: returnCostsFields }],
getData: async () => {
const computed = await getComputedData(url);
return { data: computed.returnCosts };
},
},
],
relationships,
expressions,
calendars: [mainDemoCalendar],
};
}
Line and Area Charts Copy Link
Single measure. The Legend field splits the measure into one line or area per distinct value, each with its own colour and legend entry. This is the way to plot one measure across several categories - for example, revenue over time with one line per country.
Multiple measures with a Legend field. Each measure is split by every legend value, and the series are labelled with both parts, such as Net Sales · Enterprise. Colours stay consistent per series, and tooltips show the hovered series' legend value.
The Area variants stack the split series: Stacked areas add up to the total, and 100% Stacked areas are normalised so each period fills the plot.
Limiting the Number of Series Copy Link
A Legend field with many distinct values would produce one series per value and a legend too large to read. Charts guard against this automatically, keeping only the most significant series. This engages only when the series count would exceed 50; below that, every series renders as normal.
Legend values are ranked by measure value, largest first, and the chart keeps the largest 50 series and leaves the rest out altogether. With several measures, every legend value produces one series per measure, so the measures share the limit: two measures keep the 25 largest legend values each, ranked by the first measure, and every measure shows the same values. Whenever series have been left out, the chart says so in the browser console while development validations are enabled.
Widget-Specific Options Copy Link
Some Widget families have setup slots and options of their own, such as the Rows, Columns and Values slots on a Pivot Table or the Hierarchy Levels on a Treemap. These are described with the Widget in the Widget Catalogue.