The Data panel organises the available data and shows how it is structured across sources.
AG Studio supports multiple data sources. This page explains how data is presented in the Data panel and how it is structured across sources. For how fields behave when used in widgets, see Using Data.
Data Panel Copy Link
The Data Panel lists the data available to the report, grouped by source. For an overview of how the Data Panel fits within the Studio interface, see User Interface.
The Data panel can be used to search for items by name. When a data pill is dragged, valid drop areas are highlighted in blue. Data pills can be dropped into widget configuration slots and Filter Conditions.
Select any data item to switch the Edit Panel to its data-field view, which displays metadata about that item.
Data Items Copy Link
Data items are fields made available in the Data Panel. A field is a data element that can be used in widgets and is one of three types: a column, a calculated column, or a measure.
- Columns are fields that come directly from the underlying data source and are typically used for categories (grouping) and, where appropriate, as values.
- Calculated Columns are fields derived from other data that return a value for each row.
- Measures are pre-aggregated calculations intended for Metrics and KPIs.
Each data item has the following properties:
| Property | Description |
|---|---|
| Id | The unique identifier for the data item. |
| Name | The display name shown in the interface. |
| Description | Additional supporting information about the data item. |
| Data Type | The type of data the item contains, such as text, number, date, or boolean. |
| Calculation Type | Whether the field is calculated: No Calculation for a column from the source, Calculation for a Calculated Column, or Measure for a Measure. |
| Format | How the value is displayed in the interface, for example as text, currency, percentage, or a date format. |
Select any field in the Data Panel to open its data-field view, which shows the metadata for that field, including its name, description, data type, calculation type, and format.
Id, Name, and Data Type are required and configured by your developer. Description and Format are optional and also configured by your developer.
Calculation Type reports which of the three kinds of field it is, and that is chosen when the field is created rather than worked out from the data. A field that comes from a data source is always a column, so it shows No Calculation. A calculation is either a Calculated Column or a Measure, and whoever creates it chooses which: your developer for the calculations you are given, you for the ones you add yourself (see Calculations).
The choice decides how the field can be used. A column or a Calculated Column holds a value for each row, so when you use one as a value in a widget you choose how to aggregate it. A Measure is already aggregated, so it brings its own aggregation and no other can be applied to it. See Using Data.
import {
AgFieldDefinition,
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const salesFields: AgFieldDefinition[] = [
{
id: "sale_id",
name: "Sale ID",
format: "textFormat",
description: "Unique identifier for each sale transaction",
},
{
id: "date",
name: "Date",
format: "dateFormat",
description: "Date when the sale occurred",
},
{
id: "store_id",
name: "Store ID",
format: "textFormat",
description: "Identifier for the store where the sale took place",
},
{
id: "product_id",
name: "Product ID",
format: "textFormat",
description: "Identifier for the product sold",
},
{
id: "customer_id",
name: "Customer ID",
format: "textFormat",
description: "Identifier for the customer making the purchase",
},
{
id: "employee_id",
name: "Employee ID",
format: "textFormat",
description: "Identifier for the employee who processed the sale",
},
{
id: "quantity",
name: "Quantity",
format: "integerFormat",
description: "Number of units sold",
},
{
id: "unit_price",
name: "Unit Price",
format: "currencyFormat",
description: "Price per unit of the product",
formatOptions: {
format: "$#,##0.00",
},
},
{
id: "discount_pct",
name: "Discount Percentage",
format: "percentageFormat",
description: "Discount applied to the sale as a percentage",
formatOptions: {
format: "0.00%",
},
},
{
id: "amount",
name: "Amount",
format: "currencyFormat",
description: "Total sale amount after discounts",
formatOptions: {
format: "$#,##0.00",
},
},
{
id: "payment_method",
name: "Payment Method",
format: "textFormat",
description: "Method used for payment",
},
{
id: "status",
name: "Status",
format: "textFormat",
description: "Current status of the sale transaction",
},
{
id: "promo_id",
name: "Promo ID",
format: "textFormat",
description: "Identifier for any promotional offer applied",
},
];
const initialState: AgReportState = {
pages: [
{
id: "a",
},
],
selectedPageId: "a",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["edit", "data"],
},
},
data: {
sources: [
{
id: "sales",
name: "Sales",
fields: salesFields,
data: getData(),
},
],
},
};
let studioApi: AgStudioApi;
// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
export function getData() {
return [
{
sale_id: 601,
date: '2022-01-01',
store_id: 48,
product_id: 139,
customer_id: 3496,
employee_id: 477,
promo_id: null,
quantity: 3,
unit_price: 798.79,
discount_pct: 0,
amount: 2396.37,
payment_method: 'Debit Card',
status: 'Cancelled',
},
{
sale_id: 757,
date: '2022-01-01',
store_id: 43,
product_id: 226,
customer_id: 1338,
employee_id: 422,
promo_id: null,
quantity: 6,
unit_price: 447.38,
discount_pct: 0,
amount: 2684.28,
payment_method: 'Mobile Payment',
status: 'Completed',
},
{
sale_id: 1230,
date: '2022-01-01',
store_id: 6,
product_id: 48,
customer_id: null,
employee_id: 52,
promo_id: null,
quantity: 4,
unit_price: 343.01,
discount_pct: 0,
amount: 1372.04,
payment_method: 'Credit Card',
status: 'Completed',
},
{
sale_id: 1801,
date: '2022-01-01',
store_id: 48,
product_id: 139,
customer_id: 3496,
employee_id: 477,
promo_id: null,
quantity: 3,
unit_price: 798.79,
discount_pct: 0,
amount: 2396.37,
payment_method: 'Debit Card',
status: 'Cancelled',
},
{
sale_id: 1957,
date: '2022-01-01',
store_id: 43,
product_id: 226,
customer_id: 1338,
employee_id: 422,
promo_id: null,
quantity: 6,
unit_price: 447.38,
discount_pct: 0,
amount: 2684.28,
payment_method: 'Mobile Payment',
status: 'Completed',
},
{
sale_id: 2430,
date: '2022-01-01',
store_id: 6,
product_id: 48,
customer_id: null,
employee_id: 52,
promo_id: null,
quantity: 4,
unit_price: 343.01,
discount_pct: 0,
amount: 1372.04,
payment_method: 'Credit Card',
status: 'Completed',
},
{
sale_id: 3001,
date: '2022-01-01',
store_id: 48,
product_id: 139,
customer_id: 3496,
employee_id: 477,
promo_id: null,
quantity: 3,
unit_price: 798.79,
discount_pct: 0,
amount: 2396.37,
payment_method: 'Debit Card',
status: 'Cancelled',
},
{
sale_id: 3157,
date: '2022-01-01',
store_id: 43,
product_id: 226,
customer_id: 1338,
employee_id: 422,
promo_id: null,
quantity: 6,
unit_price: 447.38,
discount_pct: 0,
amount: 2684.28,
payment_method: 'Mobile Payment',
status: 'Completed',
},
{
sale_id: 3630,
date: '2022-01-01',
store_id: 6,
product_id: 48,
customer_id: null,
employee_id: 52,
promo_id: null,
quantity: 4,
unit_price: 343.01,
discount_pct: 0,
amount: 1372.04,
payment_method: 'Credit Card',
status: 'Completed',
},
{
sale_id: 4201,
date: '2022-01-01',
store_id: 48,
product_id: 139,
customer_id: 3496,
employee_id: 477,
promo_id: null,
quantity: 3,
unit_price: 798.79,
discount_pct: 0,
amount: 2396.37,
payment_method: 'Debit Card',
status: 'Cancelled',
},
];
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>
Data Source Copy Link
Data items shown in the Data Panel are grouped by data source. Reports can be powered by one data source or multiple data sources.
When the report uses a single data source, all fields and calculations come from the same source. Widgets can be configured without thinking about relationships.
When multiple data sources are used, every data item is listed under the source it comes from. A calculation is listed under a single source as well, even when its expression reads from more than one. A calculation you create stays in the section you created it in, whatever its expression later references. A calculation your developer provides is listed under the first source its expression reaches, whether it names that source itself or reaches it through another calculation it references. One that reaches no source at all is listed in a Computed Columns section at the end of the panel.
If relationships exist between data sources, data items can be combined across sources when Configuring Widgets. If no relationship exists between selected data, the interface prevents invalid combinations.
When you use data from multiple sources, you can create widgets that combine data items from different sources. In the example below, the table widget displays product names from the Products table along with order metrics from the Order Items table, including quantity, unit price, and net sales.
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: "page1",
widgets: {
"1": {
type: "grid",
dataMapping: {
cols: [
{ id: "products.product_name" },
{ id: "order_items.quantity", aggregation: "sum" },
{ id: "order_items.unit_price", aggregation: "avg" },
{ id: "net_sales" },
],
},
format: {
title: {
enabled: true,
text: "Sales by Product",
},
},
},
},
widgetLayout: {
"1": {
xTrack: 0,
yTrack: 0,
xSpan: 24,
ySpan: 32,
},
},
},
],
selectedPageId: "page1",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["edit", "data"],
},
},
data: getMainDemoData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
// setup Studio after the page has finished loading
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>
Calculated Fields Copy Link
Some fields in the Data panel are calculations rather than raw source columns. Calculations are derived from other fields, and are either provided by your developer or created by you (see Calculations). They are computed at runtime rather than stored in the underlying data, so they update as the data changes. There are two types of calculations:
Calculated Columns - Non-aggregated calculations that return a value for each row. They are useful for transforming data (for example, cleaned labels), bucketing values (for example, a band), or computing flags based on logic.
Measures - Pre-aggregated calculations that compute values at the dataset level or per category. When a Measure is used without any grouping categories, it returns a single aggregated value for the entire dataset (for example, total revenue across all regions). When used with categories, it returns one value per category (for example, total revenue per region). Measures are intended for Metrics and KPIs and do not support changing aggregation at runtime.
Both are derived from other fields and computed at runtime. The difference is whether the value arrives already aggregated. A Calculated Column does not, so it behaves like any other column in a widget and you choose how to aggregate it. A Measure does, so it carries its own aggregation and none can be applied on top.
| Icon | Description |
|---|---|
 | Number data types |
 | Calculated number data type |
In the example below, the table demonstrates a measure grouped by product. Line Net (sum) is an aggregated column calculated from order items. Gross Margin % is a measure that shows the aggregated margin percentage for each product.
import {
AgReportState,
AgStudioApi,
AgStudioProperties,
createStudio,
enableStudioDevValidations,
} from "ag-studio";
import { getCalculationsData } from "./data.ts";
if (process.env.NODE_ENV !== "production") {
// Enable extended validations only for development
enableStudioDevValidations();
}
const initialState: AgReportState = {
pages: [
{
id: "page1",
widgets: {
"1": {
type: "grid",
dataMapping: {
cols: [
{ id: "products.product_name" },
{ id: "line_net", aggregation: "sum" },
{ id: "gross_margin_pct" },
],
},
format: {
title: {
enabled: true,
text: "Calculated Column and Measure by Product",
},
},
},
},
widgetLayout: {
"1": {
xTrack: 0,
yTrack: 0,
xSpan: 24,
ySpan: 32,
},
},
},
],
selectedPageId: "page1",
};
const studioProperties: AgStudioProperties = {
mode: "edit",
initialState,
panels: {
edit: {
right: ["edit", "data"],
},
},
data: getCalculationsData("https://www.ag-grid.com/studio/archive/3.0.0/example-assets"),
};
let studioApi: AgStudioApi;
// setup Studio after the page has finished loading
const studioDiv = document.querySelector<HTMLElement>("#myStudio")!;
studioApi = createStudio(studioDiv, studioProperties);
import type { AgDataSourcesDefinition, AgExpressionFieldDefinition, AgFieldDefinition } from 'ag-studio';
const productsFields: AgFieldDefinition[] = [
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
hide: true,
},
{
id: 'product_name',
name: 'Product',
format: 'textFormat',
},
{ id: 'category', name: 'Category', format: 'textFormat' },
{
id: 'subcategory',
name: 'Subcategory',
format: 'textFormat',
hide: true,
},
{ id: 'brand', name: 'Brand', format: 'textFormat', hide: true },
{ id: 'launch_date', name: 'Launch Date', format: 'dateFormat', hide: true },
{
id: 'list_price',
name: 'List Price',
format: 'currencyFormat',
hide: true,
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'unit_cost',
name: 'Unit Cost',
format: 'currencyFormat',
hide: true,
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'is_discontinued',
name: 'Discontinued',
format: 'booleanFormat',
hide: true,
},
];
const orderItemsFields: AgFieldDefinition[] = [
{
id: 'order_item_id',
name: 'Order Item ID',
format: 'textFormat',
hide: true,
},
{
id: 'order_id',
name: 'Order ID',
format: 'textFormat',
hide: true,
},
{
id: 'product_id',
name: 'Product ID',
format: 'textFormat',
hide: true,
},
{
id: 'quantity',
name: 'Qty',
format: 'integerFormat',
hide: true,
},
{
id: 'unit_price',
name: 'Unit Price',
format: 'currencyFormat',
hide: true,
formatOptions: {
format: '£#,##0.00',
},
},
{
id: 'discount_pct',
name: 'Discount',
format: 'percentageFormat',
hide: true,
},
{
id: 'tax_rate',
name: 'Tax Rate',
format: 'percentageFormat',
hide: true,
},
{ id: 'returned', name: 'Returned', format: 'booleanFormat', hide: true },
{
id: 'return_reason',
name: 'Return Reason',
format: 'textFormat',
hide: true,
},
];
// Simplified expressions focused on key calculation types
const expressions: AgExpressionFieldDefinition[] = [
// Calculated Column: line_net
{
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' },
],
},
],
},
},
// Calculated Column: line_cogs
{
id: 'line_cogs',
isMeasure: false,
name: 'Line COGS',
hide: true,
format: 'currencyFormat',
formatOptions: {
format: '£#,##0.00',
},
expression: {
operator: 'multiply',
inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }],
},
},
// Calculated Column: line_margin
{
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' }] },
],
},
},
// Measure: gross_margin_percentage
{
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%' },
},
];
// Cached data loaders
let productsCache: Promise<any[]> | null = null;
const getProducts = (baseUrl: string) =>
(productsCache ??= fetch(`${baseUrl}/main-demo/products.json`)
.then((r) => r.json())
.then((rows) =>
rows.map((row: Record<string, any>) => ({
...row,
list_price: Number(row.list_price),
unit_cost: Number(row.unit_cost),
is_discontinued: row.is_discontinued === 'True' || row.is_discontinued === true,
}))
));
let orderItemsCache: Promise<any[]> | null = null;
const getOrderItems = (baseUrl: string) =>
(orderItemsCache ??= fetch(`${baseUrl}/main-demo/order_items.json`)
.then((r) => r.json())
.then((rows) =>
rows.map((row: Record<string, any>) => ({
...row,
quantity: Number(row.quantity),
unit_price: Number(row.unit_price),
discount_pct: Number(row.discount_pct),
tax_rate: Number(row.tax_rate),
returned: row.returned === 'True' || row.returned === true,
}))
));
export function getCalculationsData(baseUrl: string): AgDataSourcesDefinition {
const url = `${baseUrl}`;
return {
sources: [
{
id: 'products',
name: 'Products',
dataShape: 'row',
tables: [{ id: 'products', name: 'Products', fields: productsFields }],
getData: async () => ({ data: await getProducts(url) }),
},
{
id: 'order_items',
name: 'Order Items',
dataShape: 'row',
tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
getData: async () => ({ data: await getOrderItems(url) }),
},
],
relationships: [
{
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,
},
],
expressions,
};
}
<div style="display: flex; flex-direction: column; height: 100%">
<div id="myStudio" class="my-studio-container"></div>
</div>