Widgets are the visual building blocks of a report.
They are created on the layout, then configured in the Edit Panel where data and appearance can be adjusted.
Widget Creation Copy Link
Drag and drop is the main mechanism for Widget creation - this can be done by dragging data or Widgets into the page. Alternatively, clicking on the Widget icons in the Compose Panel will also create a Widget on the page.
When a Widget is dragged, it snaps to the Layout Grid and a preview shows the proposed placement. If the Widget would exceed the layout, placement is clamped to the nearest valid position and size. Where possible, the Widget is resized to fit the available space.
Once the Widget is created, it can be resized from its edges or corners (depending on the Widget). Resizing also snaps to the grid. Some Widgets enforce a minimum size. For example, the Table Widget has a minimum height of 220px (equivalent to 4 rows, including the header).
The example below shows an empty page in Edit Mode. Widgets can be created by dragging a Widget from the Edit Panel or dragging a field from the Data Panel onto the canvas.
"use client";
import React, {
useCallback,
useMemo,
useRef,
useState,
StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgStudio, AgStudioRef } from "ag-studio-react";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgPanelConfig,
AgReportState,
AgStudioApi,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.tsx";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const StudioExample = () => {
const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
const studioStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);
const [data, setData] = useState<AgDataSource>(
getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
);
const initialState = useMemo<AgReportState>(() => {
return {
pages: [
{
id: "a",
},
],
selectedPageId: "a",
};
}, []);
const panels = useMemo<AgPanelConfig>(() => {
return {
edit: {
right: ["edit", "data"],
},
};
}, []);
return (
<div style={containerStyle}>
<div style={{ display: "flex", flexDirection: "column", height: "100%" }}>
<AgStudio
style={studioStyle}
className="my-studio-container"
data={data}
initialState={initialState}
mode={"edit"}
panels={panels}
/>
</div>
</div>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<StudioExample />
</StrictMode>,
);
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' },
{ id: 'region', name: 'Region', format: 'textFormat' },
{ id: 'city', name: 'City', format: 'textFormat' },
{
id: 'opened_date',
name: 'Opened Date',
format: 'dateFormat',
},
{ id: 'store_type', name: 'Store Type', format: 'textFormat' },
];
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' },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
},
{ id: 'brand', name: 'Brand', format: 'textFormat' },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat' },
{
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' },
{ id: 'region', name: 'Region', format: 'textFormat' },
{ id: 'segment', name: 'Segment', format: 'textFormat' },
{ id: 'is_active', name: 'Active', format: 'booleanFormat' },
{
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' },
{ id: 'status', name: 'Status', format: 'textFormat' },
{
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' },
{
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' },
{ 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 Types Copy Link
AG Studio provides Widgets for tables, charts, headline values, on-canvas filters and static content. The Widget Catalogue lists every Widget in the Widget selector, with its variants, the data it needs and the options specific to it.
Once a Widget is on the layout, configure its data inputs and appearance in the Edit Panel. See Building Widgets for the Setup and Format tabs.
Widget Toolbar Copy Link
When a Widget is focused, a toolbar is displayed with actions for working with the Widget. Widget focus is indicated by a default blue border around the Widget.
The available actions depend on the current Mode and the Widget type. For example, some actions are only available in Edit mode, and different Widget types may show different menus.
| Name | Icon | Mode | Description |
|---|---|---|---|
| CSV |  | Both | Exports the Widget data as a CSV file. |
| Download |  | Both | Downloads the chart as an image. |
| Duplicate |  | Edit | Creates a copy of the Widget. |
| Delete |  | Edit | Removes the Widget from the layout. |
Table and Chart Widgets show a CSV button. Chart Widgets also show a Download button for exporting the chart as an image. The Table Widget does not have a Download button.
An exported CSV uses the Widget title as its filename, and its values are formatted the same way as in the Widget.
Empty State Copy Link
If a Widget runs successfully but returns no rows, AG Studio shows a consistent empty state. This could be due to empty underlying Fields or active Filters not returning any results. The empty state uses a single message ("No data to display") and is centred within the Widget.
The example below shows the same bar chart with a page-level filter applied that returns no matching data. The Filters Panel is open to show the active filter condition.
"use client";
import React, {
useCallback,
useMemo,
useRef,
useState,
StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgStudio, AgStudioRef } from "ag-studio-react";
import {
AgDataEngine,
AgDataSourcesDefinition,
AgPanelConfig,
AgReportState,
AgStudioApi,
AgStudioMode,
AgStudioProperties,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.tsx";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const StudioExample = () => {
const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
const studioStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);
const [data, setData] = useState<AgDataSource>(
getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
);
const initialState = useMemo<AgReportState>(() => {
return {
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,
},
},
filter: {
page: [
{
field: { id: "stores.region" },
model: {
operator: "equals",
value: "ZZZZZ",
},
},
],
},
},
],
selectedPageId: "a",
};
}, []);
const panels = useMemo<AgPanelConfig>(() => {
return {
edit: {
right: ["filters"],
},
};
}, []);
return (
<div style={containerStyle}>
<div style={{ display: "flex", flexDirection: "column", height: "100%" }}>
<AgStudio
style={studioStyle}
className="my-studio-container"
data={data}
initialState={initialState}
mode={"edit"}
panels={panels}
/>
</div>
</div>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<StudioExample />
</StrictMode>,
);
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' },
{ id: 'region', name: 'Region', format: 'textFormat' },
{ id: 'city', name: 'City', format: 'textFormat' },
{
id: 'opened_date',
name: 'Opened Date',
format: 'dateFormat',
},
{ id: 'store_type', name: 'Store Type', format: 'textFormat' },
];
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' },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
},
{ id: 'brand', name: 'Brand', format: 'textFormat' },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat' },
{
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' },
{ id: 'region', name: 'Region', format: 'textFormat' },
{ id: 'segment', name: 'Segment', format: 'textFormat' },
{ id: 'is_active', name: 'Active', format: 'booleanFormat' },
{
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' },
{ id: 'status', name: 'Status', format: 'textFormat' },
{
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' },
{
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' },
{ 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,
};
}