Filters change the data shown on a Page or Widget.
Filters can be applied in a few different ways. Page and Widget Filters can be added in the Filters Panel, Filter Widgets can be used on the layout, or cross-filtering can be applied by interacting with a Widget. When Filter Widgets or cross-filtering apply a condition, it appears in the Filters Panel as a read-only card.
There are four types of filters in AG Studio:
- Page Filters - apply to all Widgets on the page and are managed in the Filters Panel,
- Widget Filters - apply only to a specific Widget and are managed in the Filters Panel,
- Cross-filtering - filters driven by interacting with a Widget,
- Filter Widgets - on-canvas filter controls that apply a page filter and appear as a read-only card in the Page Filters section.
Filters Panel Copy Link
The Filters Panel provides a centralised space for managing all filter types. Filters are presented as cards, organised by type.
In Edit Mode, filters can be added, removed, and reordered by dragging filter cards within a section. In View Mode, the configuration of Page Filters and Widget Filters can be changed, but by default filters cannot be added or removed - your developer can enable this. Read-only cards, such as those for Filter Widgets and cross-filtering conditions, can always be removed from the Filters Panel regardless of mode.
Filter Widgets and cross-filtering conditions appear as read-only, collapsed cards, so the report user can see at a glance what is affecting the data display. Clicking a Widget Filter or Cross Filter card puts the origin Widget into focus.
Applying Filter Changes Copy Link
By default, a change to a filter card takes effect straight away and the page updates as you type.
Your developer can instead configure the Filters Panel so that changes are held until you confirm them. In that case each expanded filter card shows Apply and Cancel buttons at the bottom. Nothing on the page changes until you press Apply, and Cancel returns the card to the values that were last applied. This suits reports whose data is fetched from a server, where every keystroke would otherwise start a new query.
A card holding changes you have not applied yet is marked with an edit indicator in its header, so you can still see which filters are pending while the cards are collapsed. A collapsed card always describes the filter that is actually applied, not the change you are part-way through making.
Two behaviours are worth knowing about. A filter that is only partly filled in - the start of a range with no end, for example - does not count as a filter at all, so Apply stays greyed out until the condition is complete. And clearing one end of a range that has already been applied removes the filter, because the remaining condition is incomplete.
The example below is in View Mode and opens with a region Page Filter and a date Page Filter already applied, both cards expanded so the button row is visible. The report is configured with applyOnChange: false, so changing a card's values holds the edit rather than applying it - add or remove a region and the Widgets stay as they are until Apply is pressed. Cancel restores the values that were last applied.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"kpi-net-sales": {
type: "value",
dataMapping: { value: [{ id: "net_sales" }] },
format: { caption: { enabled: true, text: "Net Sales" } },
},
"kpi-order-count": {
type: "value",
dataMapping: { value: [{ id: "order_count" }] },
format: { caption: { enabled: true, text: "Order Count" } },
},
"kpi-aov": {
type: "value",
dataMapping: { value: [{ id: "average_order_value" }] },
format: { caption: { enabled: true, text: "Avg Order Value" } },
},
"net-sales-by-region": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "stores.region" }],
valueKey: [{ id: "net_sales" }],
},
format: { title: { enabled: true, text: "Net Sales by Region" } },
},
"net-sales-by-subcategory": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "products.subcategory" }],
valueKey: [{ id: "net_sales" }],
},
format: {
title: { enabled: true, text: "Net Sales by Subcategory" },
},
},
},
widgetLayout: {
"kpi-net-sales": { xTrack: 0, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-order-count": { xTrack: 8, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-aov": { xTrack: 16, yTrack: 0, xSpan: 8, ySpan: 6 },
"net-sales-by-region": { xTrack: 0, yTrack: 6, xSpan: 24, ySpan: 18 },
"net-sales-by-subcategory": {
xTrack: 0,
yTrack: 24,
xSpan: 24,
ySpan: 18,
},
},
filter: {
// Both cards start expanded and already applied, so the Apply and Cancel buttons
// are visible on load without the reader having to add a filter first.
page: [
{
field: { id: "stores.region" },
view: { expanded: true, viewTypeId: "selection" },
model: {
operator: "isIn",
value: ["DACH", "UK"],
},
},
{
field: { id: "orders.order_datetime" },
view: { expanded: true },
model: {
operator: "greaterThan",
value: "2024-12-01",
},
},
],
},
},
],
selectedPageId: "a",
};
const studioProperties: AgStudioProperties = {
mode: "view",
initialState,
panels: {
// An options-only config leaves each mode's default panels in place, so view mode still
// shows the filters panel on the right.
options: {
filters: { applyOnChange: false },
},
},
data: getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
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,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>
Page Filters Copy Link
Page Filters apply to all Widgets on the current page. Page Filters should be used when a consistent slice of data is required across the whole page (for example, a single region or date range).
The example below shows a page with a date Page Filter applied. The filter affects all Widgets on the page - the KPIs and both bar charts (Net Sales by Region and Net Sales by Subcategory) all reflect the same filtered data.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"kpi-net-sales": {
type: "value",
dataMapping: { value: [{ id: "net_sales" }] },
format: { caption: { enabled: true, text: "Net Sales" } },
},
"kpi-order-count": {
type: "value",
dataMapping: { value: [{ id: "order_count" }] },
format: { caption: { enabled: true, text: "Order Count" } },
},
"kpi-aov": {
type: "value",
dataMapping: { value: [{ id: "average_order_value" }] },
format: { caption: { enabled: true, text: "Avg Order Value" } },
},
"net-sales-by-region": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "stores.region" }],
valueKey: [{ id: "net_sales" }],
},
format: { title: { enabled: true, text: "Net Sales by Region" } },
},
"net-sales-by-subcategory": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "products.subcategory" }],
valueKey: [{ id: "net_sales" }],
},
format: {
title: { enabled: true, text: "Net Sales by Subcategory" },
},
},
},
widgetLayout: {
"kpi-net-sales": { xTrack: 0, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-order-count": { xTrack: 8, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-aov": { xTrack: 16, yTrack: 0, xSpan: 8, ySpan: 6 },
"net-sales-by-region": { xTrack: 0, yTrack: 6, xSpan: 24, ySpan: 18 },
"net-sales-by-subcategory": {
xTrack: 0,
yTrack: 24,
xSpan: 24,
ySpan: 18,
},
},
filter: {
page: [
{
field: { id: "orders.order_datetime" },
view: { expanded: true },
model: {
operator: "greaterThan",
value: "2024-12-01",
},
},
],
},
},
],
selectedPageId: "a",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["filters"],
},
},
data: getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
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,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>
Widget Filters Copy Link
Widget Filters apply only to one Widget. Widget Filters should be used when a Widget needs a different scope from the rest of the page (for example, one chart showing "Top 10" while other Widgets show all results).
The example below shows a page where a Widget Filter is applied only to the Net Sales by Subcategory chart, limiting it to three subcategories. The KPIs and Net Sales by Region chart are unaffected and show all data. Clicking the subcategory chart shows the active Widget Filter card in the Filters Panel.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"kpi-net-sales": {
type: "value",
dataMapping: { value: [{ id: "net_sales" }] },
format: { caption: { enabled: true, text: "Net Sales" } },
},
"kpi-order-count": {
type: "value",
dataMapping: { value: [{ id: "order_count" }] },
format: { caption: { enabled: true, text: "Order Count" } },
},
"kpi-aov": {
type: "value",
dataMapping: { value: [{ id: "average_order_value" }] },
format: { caption: { enabled: true, text: "Avg Order Value" } },
},
"net-sales-by-region": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "stores.region" }],
valueKey: [{ id: "net_sales" }],
},
format: {
title: { enabled: true, text: "Net Sales by Region - All Data" },
},
},
"net-sales-by-subcategory": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "products.subcategory" }],
valueKey: [{ id: "net_sales" }],
},
format: {
title: {
enabled: true,
text: "Net Sales by Subcategory - Widget Filter Applied",
},
},
},
},
widgetLayout: {
"kpi-net-sales": { xTrack: 0, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-order-count": { xTrack: 8, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-aov": { xTrack: 16, yTrack: 0, xSpan: 8, ySpan: 6 },
"net-sales-by-region": { xTrack: 0, yTrack: 6, xSpan: 24, ySpan: 18 },
"net-sales-by-subcategory": {
xTrack: 0,
yTrack: 24,
xSpan: 24,
ySpan: 18,
},
},
filter: {
widget: {
"net-sales-by-subcategory": [
{
field: { id: "products.subcategory" },
view: { expanded: true },
model: {
operator: "isIn",
value: [
"Binders & Presentation",
"Desk Accessories",
"Paper & Notebooks",
],
},
},
],
},
},
},
],
selectedPageId: "a",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["filters"],
},
},
data: getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
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,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>
Page Filters and Widget Filters share the same filter types, described below. The available types depend on the field and Widget.
Selection Filter Copy Link
Selection Filter should be used when picking one or more values from a list, which is particularly useful for filtering categorical fields where a value list is the most direct way to specify the condition. Selection Filter is available for both Page Filters and Widget Filters.
Simple Filter Copy Link
Simple Filter should be used when expressing a condition using operators (for example, equals, does not equal, contains, or numeric comparisons). Some filter UIs also support combining conditions with AND/OR. Simple Filter is available for both Page Filters and Widget Filters.
Rank Filter Copy Link
Rank Filter should be used to restrict a Widget to the Top N and/or Bottom N categories based on a numeric result. Rank Filter is only available as a Widget Filter, and only when the Widget produces a numeric output per category, which includes any numeric field - whether used as a group-by or with a numeric Aggregation (for example, Count, Sum, Average, Minimum, Maximum). Only one Rank Filter can be active per Widget.
When Rank Filter is enabled, separate Top and Bottom values can be entered, where N is an integer.
The field used for ranking must be present in the Widget's Data Mapping. The ranking result is determined by how that field is used in the Widget - for example, ranking by Line Net with an Average aggregation ranks categories by their average Line Net value; ranking by a numeric group-by field ranks categories by the field's value directly.
The example below shows two bar charts using Rank Filters: the top chart shows the Top 5 subcategories by Average Line Net, sorted descending; the bottom chart shows the Bottom 5 subcategories by Net Sales, sorted ascending. The Filters Panel shows the active Rank Filter cards on each widget.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "a",
layout: {},
widgets: {
"top-5": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "products.subcategory" }],
valueKey: [{ id: "line_net", aggregation: "sum" }],
},
sort: [{ field: { id: "net_sales" }, direction: "desc" }],
format: {
title: {
enabled: true,
text: "Top 5 Subcategories by Line Net (avg)",
},
},
},
"bottom-5": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "products.subcategory" }],
valueKey: [{ id: "net_sales" }],
},
sort: [{ field: { id: "net_sales" }, direction: "asc" }],
format: {
title: {
enabled: true,
text: "Bottom 5 Subcategories by Net Sales",
},
},
},
},
widgetLayout: {
"top-5": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 20 },
"bottom-5": { xTrack: 0, yTrack: 20, xSpan: 24, ySpan: 20 },
},
filter: {
widget: {
"top-5": [
{
field: { id: "line_net" },
view: { expanded: true },
model: {
operator: "rank",
value: [5, null],
},
},
],
"bottom-5": [
{
field: { id: "net_sales" },
view: { expanded: true },
model: {
operator: "rank",
value: [null, 5],
},
},
],
},
},
},
],
selectedPageId: "a",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["filters"],
},
},
data: getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
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,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>
Rank Filter respects all other active Filters, and ranking is applied after existing Filters. If N exceeds the number of available categories, all available results are shown.
If the Widget changes so the category no longer produces a numeric output, the Rank Filter is cleared and a non-blocking message explains why it was removed.
Cross-Filtering Copy Link
Cross-filtering is driven by interacting with a Widget. When values are selected in a Widget, the selection becomes a filter condition that can affect other Widgets on the page. When cross-filtering is active, an additional Cross Filters section appears between Page Filters and Widget Filters.
Cross-filtering is configured per widget and is on by default. Settings can be changed in the Edit Panel. Cross-filtering has three modes:
- Cross Highlight - highlights proportional selection in the visualisation (pie, doughnut, bar, and column).
- Cross Filter - redraws data under the cross-filter condition.
- None - disables cross-filtering.
The example below shows a page with mixed cross-filter settings. The KPI Widgets have cross-filtering disabled - they display totals that reflect all data and do not respond to cross-filter changes. The bar, column, and pie charts use Cross Highlight mode, so a selection shows a proportional highlight without redrawing the data. The line chart uses Cross Filter mode, so it redraws under the cross-filter condition. UK is pre-selected in Net Sales by Region and Mid-Market in Net Sales by Segment to show the Filters Panel and highlight effects on load.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "a",
widgets: {
"kpi-net-sales": {
type: "value",
dataMapping: { value: [{ id: "net_sales" }] },
format: {
caption: { enabled: true, text: "Net Sales" },
crossFilter: "none",
},
},
"kpi-order-count": {
type: "value",
dataMapping: { value: [{ id: "order_count" }] },
format: {
caption: { enabled: true, text: "Order Count" },
crossFilter: "none",
},
},
"kpi-aov": {
type: "value",
dataMapping: { value: [{ id: "average_order_value" }] },
format: {
caption: { enabled: true, text: "Avg Order Value" },
crossFilter: "none",
},
},
"net-sales-by-region": {
type: "bar-chart-grouped",
dataMapping: {
categoryKey: [{ id: "stores.region" }],
valueKey: [{ id: "net_sales" }],
},
format: { title: { enabled: true, text: "Net Sales by Region" } },
},
"net-sales-by-subcategory": {
type: "column-chart-grouped",
dataMapping: {
categoryKey: [{ id: "products.subcategory" }],
valueKey: [{ id: "net_sales" }],
},
format: {
title: { enabled: true, text: "Net Sales by Subcategory" },
},
},
"net-sales-over-time": {
type: "line-chart",
dataMapping: {
categoryKey: [{ id: "orders.order_month" }],
valueKey: [{ id: "net_sales" }],
},
format: { title: { enabled: true, text: "Net Sales over Time" } },
},
"net-sales-by-category": {
type: "pie-chart",
dataMapping: {
categoryKey: [{ id: "customers.segment" }],
valueKey: [{ id: "net_sales" }],
},
format: { title: { enabled: true, text: "Net Sales by Segment" } },
},
},
widgetLayout: {
"kpi-net-sales": { xTrack: 0, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-order-count": { xTrack: 8, yTrack: 0, xSpan: 8, ySpan: 6 },
"kpi-aov": { xTrack: 16, yTrack: 0, xSpan: 8, ySpan: 6 },
"net-sales-over-time": { xTrack: 0, yTrack: 6, xSpan: 24, ySpan: 18 },
"net-sales-by-subcategory": {
xTrack: 0,
yTrack: 24,
xSpan: 24,
ySpan: 18,
},
"net-sales-by-category": {
xTrack: 0,
yTrack: 42,
xSpan: 12,
ySpan: 18,
},
"net-sales-by-region": { xTrack: 12, yTrack: 42, xSpan: 12, ySpan: 18 },
},
crossFilter: {
values: {
"net-sales-by-region": [
{
type: "value",
field: { id: "stores.region" },
values: ["UK"],
},
],
"net-sales-by-category": [
{
type: "value",
field: { id: "customers.segment" },
values: ["Mid-Market"],
},
],
},
},
},
],
selectedPageId: "a",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["filters"],
},
},
data: getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
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,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>
When cross-filtering applies a condition, a read-only, collapsed filter card is shown in the Cross Filters section of the Filters Panel indicating the applied condition. Cross Filters cards can be clicked to put the origin Widget into focus, and the condition can be removed using the 'x' action on the card.
Cross-filtering is designed to be quick and reversible. Clicking the same selection again clears the condition, including when using ^ Ctrl⌘ Command for multi-select. If no cross-filtering is applied, the Cross Filters section is hidden.
Filter Widgets Copy Link
Filter Widgets are on-canvas filter controls that allow report consumers to filter the page using visible controls, without opening the Filters Panel.
AG Studio supports three types of Filter Widgets:
| Type | Description | Input Requirements |
|---|---|---|
| List | A dropdown or list that allows selecting one or more values from available options. | Any categorical or value field, such as product names, regions, or status |
| Button | Button-based controls that display distinct values as individual buttons for quick selection. | Any categorical or value field, such as product names, regions, or status |
| Date | A date picker that allows selecting a date, date range, or predefined date ranges. | Date or DateTime fields |
Filter Widgets do not update in response to Page and Widget Filters or cross-filter conditions. In Edit Mode, clicking a Filter Widget puts it in focus and exposes its options in the Filters Panel, where the available options can be customised using a Widget Filter.
When a Filter Widget applies a condition, a read-only card appears in the Page Filters section of the Filters Panel, and the condition can be cleared using the 'x' action, which is equivalent to clearing the Filter Widget selection directly.
The example below uses List Filter Widgets to filter a nuclear dataset. Click values in any filter list to apply a condition and watch the corresponding card appear in the Filters Panel. Switch to Edit mode, click a Filter Widget to put it in focus, and update its options in the Filters Panel to see the available selections being customised.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getNuclearDataSource } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "segre-chart",
widgets: {
"report-title": {
type: "text",
dataMapping: {},
format: {
style: {
text: "Nuclear Wallet Cards - Segrè Chart Visualization",
typography: { fontWeight: "normal" },
},
},
},
"element-family-filter": {
type: "list-filter",
dataMapping: {
value: [{ id: "nuclear-data.Element Family" }],
},
},
"stability-filter": {
type: "list-filter",
dataMapping: {
value: [{ id: "nuclear-data.Stability" }],
},
},
"occurrence-filter": {
type: "list-filter",
dataMapping: {
value: [{ id: "nuclear-data.Occurrence" }],
},
},
"decay-mode-filter": {
type: "list-filter",
dataMapping: {
value: [{ id: "nuclear-data.Decay Mode" }],
},
},
"nuclides-chart": {
type: "scatter-chart",
dataMapping: {
categoryKey: [
{ id: "nuclear-data.Neutrons (N)", aggregation: "first" },
],
valueKey: [
{ id: "nuclear-data.Atomic Number (Z)", aggregation: "first" },
],
groupByKey: [{ id: "nuclear-data.Element + Atomic Mass (A)" }],
tooltipKey: [
{ id: "nuclear-data.Element", aggregation: "first" },
{ id: "nuclear-data.Atomic Mass (A)", aggregation: "first" },
],
},
format: {
title: {
enabled: true,
text: "Chart of Nuclides",
},
subtitle: {
enabled: true,
text: "A Segrè chart showing isotopes by neutron count and atomic number.",
},
},
},
"element-grid": {
type: "grid",
dataMapping: {
cols: [
{ id: "nuclear-data.Element + Atomic Mass (A)" },
{ id: "nuclear-data.Element" },
{ id: "nuclear-data.Atomic Number (Z)" },
{ id: "nuclear-data.Atomic Mass (A)" },
{ id: "nuclear-data.Neutrons (N)" },
{ id: "nuclear-data.Half-Life" },
{ id: "nuclear-data.Half-Life (Unit)" },
{ id: "nuclear-data.Spin-Parity" },
{ id: "nuclear-data.Decay Modes" },
],
},
format: {
title: {
enabled: true,
text: "Nuclear Properties",
},
style: {
theme: { rowHeight: 28 },
},
},
},
},
widgetLayout: {
"report-title": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 4 },
"element-family-filter": { xTrack: 0, yTrack: 4, xSpan: 6, ySpan: 12 },
"stability-filter": { xTrack: 0, yTrack: 16, xSpan: 6, ySpan: 9 },
"occurrence-filter": { xTrack: 0, yTrack: 25, xSpan: 6, ySpan: 6 },
"decay-mode-filter": { xTrack: 0, yTrack: 31, xSpan: 6, ySpan: 9 },
"nuclides-chart": { xTrack: 6, yTrack: 4, xSpan: 18, ySpan: 36 },
"element-grid": { xTrack: 0, yTrack: 40, xSpan: 24, ySpan: 14 },
},
filter: {
page: [],
},
},
],
selectedPageId: "segre-chart",
};
let studioApi: AgStudioApi;
async function getStudioProperties(): Promise<AgStudioProperties> {
const nuclearSource = await getNuclearDataSource();
return {
mode: "view",
initialState,
data: {
sources: [nuclearSource],
relationships: [],
},
};
}
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
const studioProperties = await getStudioProperties();
studioApi = createStudio(studioDiv, studioProperties);
import type { AgFieldDefinition, AgFormat } from 'ag-studio';
declare const Papa: any;
function getElementFamily(z: number): string {
if (z === 1) return 'Nonmetal';
if (z === 2) return 'Noble Gas';
if (z <= 4) return 'Alkali/Alkaline Earth';
if (z <= 10) return 'Nonmetal';
if (z <= 12) return 'Alkali/Alkaline Earth';
if (z <= 18) return 'Nonmetal';
if (z <= 20) return 'Alkali/Alkaline Earth';
if (z <= 30) return 'Transition Metal';
if (z <= 36) return 'Post-transition Metal';
if (z <= 54) return 'Transition Metal';
if (z <= 86) return 'Post-transition Metal';
return 'Superheavy Element';
}
function getStability(halfLife: any, unit: any): string {
const hl = String(halfLife || '')
.trim()
.toLowerCase();
const u = String(unit || '')
.trim()
.toLowerCase();
// Empty or STABLE means stable
if (!hl || hl === 'stable') return 'Stable';
// Check the unit to categorize
if (u.includes('y')) return 'Long Half-Life';
if (u.includes('d')) return 'Medium Half-Life';
if (u.includes('h') || u.includes('m') || u.includes('s') || u.includes('ms')) return 'Short Half-Life';
return 'Unknown';
}
function getOccurrence(abundance: any): string {
const ab = String(abundance || '').trim();
return ab ? 'Natural' : 'Synthetic';
}
function inferDecayMode(z: number, n: number, halfLife: any): string {
const hl = String(halfLife || '')
.trim()
.toLowerCase();
// Stable nuclei
if (hl === 'stable' || !hl) return 'Stable';
// Heavy nuclei (Z > 82): alpha decay is dominant
if (z > 82) return 'Alpha';
// Below stability valley (N < Z): beta+ decay or electron capture
if (n < z) return 'Beta+/EC';
// Above stability valley: beta- decay
if (n > z + 8) return 'Beta-';
// Light nuclei in valley: beta- or beta+
if (z < 20 && n > z) return 'Beta-';
if (z < 20 && n < z) return 'Beta+/EC';
// Default to beta decay family
return 'Beta-';
}
async function loadNuclearData(): Promise<any[] | null> {
try {
const response = await fetch('https://www.ag-grid.com/studio/archive/3.0.0/example-assets/nuclear_walletcards.csv');
if (!response.ok) {
console.error(`Failed to load CSV file: ${response.status} ${response.statusText}`);
return null;
}
const csvText = await response.text();
return new Promise((resolve, reject) => {
Papa.parse(csvText, {
header: true,
skipEmptyLines: true,
dynamicTyping: true,
complete: (results: any) => {
if (results.errors && results.errors.length > 0) {
console.error('CSV parsing errors:', results.errors);
reject(new Error('CSV parsing failed'));
} else {
// One row per isotope (ground state only) - the source data has a row per
// nuclear energy level, so excited states share the same element, mass
// and neutron count as their isotope's ground state row.
const groundStateRows = results.data.filter((row: any) => row['Level Index'] === 0);
// Calculate neutrons (A - Z), add element family, stability, occurrence, and inferred decay mode
const enrichedData = groundStateRows.map((row: any) => ({
...row,
'Element + Atomic Mass (A)': `${row['Element']} + ${row['Atomic Mass (A)']}`,
'Neutrons (N)': row['Atomic Mass (A)'] - row['Atomic Number (Z)'],
'Element Family': getElementFamily(row['Atomic Number (Z)']),
Stability: getStability(row['Half-Life'], row['Half-Life (Unit)']),
Occurrence: getOccurrence(row['Abundance']),
'Decay Mode': inferDecayMode(
row['Atomic Number (Z)'],
row['Atomic Mass (A)'] - row['Atomic Number (Z)'],
row['Half-Life']
),
}));
resolve(enrichedData);
}
},
error: (error: any) => {
console.error('Papaparse error:', error);
reject(error);
},
});
});
} catch (error) {
console.error('Error loading CSV data:', error);
return null;
}
}
const ISO_DATE_PATTERN = /^\d{4}-\d{2}-\d{2}([ T]\d{2}:\d{2}(:\d{2})?)?$/;
function isDateLikeString(candidate: string): boolean {
return ISO_DATE_PATTERN.test(candidate) && !Number.isNaN(Date.parse(candidate));
}
function inferFieldsFromCsv(data: any[]): AgFieldDefinition[] {
const fields: AgFieldDefinition[] = [];
if (data.length === 0) {
return fields;
}
const firstRecord = data[0];
for (const key in firstRecord) {
if (!key) continue;
const value = firstRecord[key];
let format: AgFormat = 'textFormat';
if (typeof value === 'number') {
format = 'integerFormat';
} else if (value instanceof Date || (typeof value === 'string' && isDateLikeString(value))) {
format = 'dateFormat';
}
fields.push({
id: key,
format,
});
}
return fields;
}
export async function getNuclearDataSource() {
const data = await loadNuclearData();
if (!data) {
console.warn('Nuclear data not available, returning empty source');
return {
id: 'nuclear-data',
data: [],
fields: [],
};
}
const fields = inferFieldsFromCsv(data);
return {
id: 'nuclear-data',
data,
fields,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>