Dashboards

Dashboard DSL — filters, widget types, ClickHouse queries, and interactive configuration.

Dashboard Definition

P9 dashboards provide real-time analytics powered by ClickHouse. Define filters, widgets, and queries declaratively in .dashboard files.

Structure

@dashboard DashboardName
@application ApplicationName
@description "Optional description"
@time-period-filter true

filter {
	FieldName = "@filter-variable"
}

widgettype WidgetName {
	label: "Display Label"
	query: "SQL query"
}

Dashboard Decorators

Decorator	Description	Example
`@dashboard`	Dashboard name (PascalCase)	`@dashboard FacilityDashboard`
`@application`	Application context	`@application FacilityManagement`
`@description`	Optional description	`@description "Dashboard for supervisors"`
`@profile`	Profile-specific dashboard	`@profile Supervisor`
`@time-period-filter`	Enable time period filter	`@time-period-filter true`
`@company-filter`	Enable/disable company filtering	`@company-filter false`

Time Period Filter

When @time-period-filter true is set, the UI displays a time period dropdown:

Year to Date (YTD) — January 1st to today
Month to Date (MTD) — 1st of current month to today
Specific Year — Filter by selected year
Specific Month — Filter by selected month and year

Filter Variables

Filter variables use the @ prefix and kebab-case naming:

filter {
	Year = "@year"
	Company = @company
	Technician = "@technician" optional: true
	Date > @start-date
	Date < @end-date
}

Widget Types

Scorecard

Single metric display:

scorecard TotalExpense {
	label: "Total Expense"
	value-key: "Total"
	unit: "@company-currency"
	query: "SELECT SUM(Amount) as Total FROM wh_Finance_Expense WHERE IsDeleted = false"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}
}

KPI Card

KPI with primary and secondary values:

kpicard RevenueMetrics {
	label: "Revenue Metrics"
	primary-value-key: "TotalRevenue"
	primary-value-format: "currency"
	secondary-value-key: "TotalContracts"
	query: "SELECT SUM(TotalContractAmount) as TotalRevenue, COUNT(Key) as TotalContracts FROM wh_Leasing_Contract WHERE IsDeleted = false AND Status = 'Active'"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}
}

Format types:

text (default) — Display as-is
currency — Format with currency code (e.g., "98.5M AED")
percentage — Format as percentage (e.g., "45.5%")
number — Compact notation (e.g., "1.5K", "2.3M")

Pie Chart

pie WorkOrdersByStatus {
	label: "Work Orders by Status"
	label-key: "Status"
	value-key: "Total"
	show-title: false
	show-legend: false
	query: "SELECT Status, COUNT(Key) as Total FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false GROUP BY Status"

	widget-filter {
		"toYear(CreatedAt)" = "@year"
	}

	group-by {
		key: "Status"
	}
}

Bar Chart

bar WorkOrdersTrend {
	label: "Work Orders Trend"
	x-axis-label: "Months"
	y-axis-label: "Total Work Orders"
	x-axis-key: "CreationMonth"
	y-axis-key: "RecordCount"
	query: "SELECT formatDateTime(CreatedAt, '%M') as CreationMonth, COUNT(Key) as RecordCount FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false GROUP BY CreationMonth"

	widget-filter {
		"toYear(CreatedAt)" = "@year"
	}

	group-by {
		key: "formatDateTime(CreatedAt, '%M')"
	}
}

Trendline

Trend line chart with series support:

trendline WorkOrdersTrendLine {
	label: "Work Orders Trend"
	label-key: "CreationMonth"
	value-key: "RecordCount"
	series: "CreationYear"
	query: "SELECT toYear(CreatedAt) as CreationYear, formatDateTime(CreatedAt, '%M') as CreationMonth, COUNT(Key) as RecordCount FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false GROUP BY CreationYear, CreationMonth"

	widget-filter {
		"toYear(CreatedAt)" = "@year"
	}

	group-by {
		key: "toYear(CreatedAt), formatDateTime(CreatedAt, '%M')"
	}
}

Aging

Aging distribution bars:

aging CaseAging {
	label: "Case Aging"
	tint: "negative"
	query: "SELECT CASE WHEN dateDiff('day', FilingDate, today()) <= 30 THEN '0-30 days' WHEN dateDiff('day', FilingDate, today()) <= 90 THEN '31-90 days' WHEN dateDiff('day', FilingDate, today()) <= 180 THEN '91-180 days' ELSE '180+ days' END AS label, COUNT(*) AS value FROM wh_Leasing_LegalCase WHERE FilingDate IS NOT NULL AND Status NOT IN ('Closed', 'Cancelled') AND IsDeleted = false GROUP BY label"
}

Aging Tint Colors:

negative (default) — Green → Yellow → Orange → Red (older is bad)
positive — Red → Orange → Yellow → Green (older is good)
neutral — Light blue → Dark blue (informational)

Tabular

Table/grid display with pagination:

tabular WorkOrders {
	label: "Work Orders"
	query: "SELECT Key as Id, Number as Title, Status FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false"

	widget-filter {
		"Team" % "@technician"
		"TakePage" = "@take-page"
		"LimitPage" = "@limit-page"
	}

	paginate {
		take-page: "@take-page"
		limit-page: "@limit-page"
	}

	widget-order-by {
		sort-by: CreatedAt
		sort-type: desc
	}
}

Common Widget Attributes

Attribute	Description	Example
`label`	Display label	`label: "Work Orders"`
`query`	SQL query (ClickHouse)	`query: "SELECT ..."`
`value-key`	Field for main value	`value-key: "Total"`
`label-key`	Field for labels	`label-key: "Status"`
`unit`	Currency/unit indicator	`unit: "@company-currency"`
`show-title`	Show/hide title	`show-title: false`
`show-legend`	Show/hide legend	`show-legend: false`

Widget Blocks

widget-filter

Filter specific to a widget:

widget-filter {
	Status = "Active"
	"toYear(CreatedAt)" = @year
	"Team" % "@technician"
}

Operators: =, !=, >, <, >=, <=, % (LIKE/contains)

group-by

Group results by field(s):

group-by {
	key: "Status"
}

group-by {
	key: "toYear(CreatedAt), formatDateTime(CreatedAt, '%M')"
}

widget-order-by

Sort results:

widget-order-by {
	sort-by: CreatedAt
	sort-type: desc
}

paginate

Enable pagination:

paginate {
	take-page: "@take-page"
	limit-page: "@limit-page"
}

ClickHouse Query Functions

All widget queries run against ClickHouse for sub-second performance.

Date Functions:

toYear(date), toMonth(date), toDay(date)
toStartOfMonth(date), toStartOfYear(date)
formatDateTime(date, '%M') — Month name
dateDiff('day', date1, date2) — Difference in days
now(), today()

Aggregate Functions:

COUNT(*), SUM(field), AVG(field)
COUNTIf(condition), SUMIf(field, condition)

Complete Dashboard Example

@dashboard FacilityDashboard
@application FacilityManagement
@description "Facility management metrics"
@time-period-filter true

scorecard TotalWorkOrders {
	label: "Total Work Orders"
	value-key: "Total"
	query: "SELECT COUNT(*) AS Total FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}
}

scorecard OpenWorkOrders {
	label: "Open Work Orders"
	value-key: "Total"
	query: "SELECT COUNT(*) AS Total FROM wh_FacilityManagement_WorkOrder WHERE Status = 'Open' AND IsDeleted = false"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}
}

kpicard AvgCompletionTime {
	label: "Avg Completion Time"
	primary-value-key: "AvgDays"
	secondary-value-key: "TotalCompleted"
	query: "SELECT AVG(dateDiff('day', CreatedAt, CompletedAt)) as AvgDays, COUNT(*) as TotalCompleted FROM wh_FacilityManagement_WorkOrder WHERE Status = 'Completed' AND IsDeleted = false"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}
}

pie WorkOrdersByStatus {
	label: "By Status"
	label-key: "Status"
	value-key: "Total"
	query: "SELECT Status, COUNT(Key) as Total FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false GROUP BY Status"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}

	group-by {
		key: "Status"
	}
}

bar MonthlyTrend {
	label: "Monthly Trend"
	x-axis-key: "Month"
	y-axis-key: "Count"
	query: "SELECT formatDateTime(CreatedAt, '%b') as Month, COUNT(*) as Count FROM wh_FacilityManagement_WorkOrder WHERE IsDeleted = false GROUP BY Month ORDER BY toMonth(CreatedAt)"

	widget-filter {
		"toYear(CreatedAt)" = @year
	}

	group-by {
		key: "formatDateTime(CreatedAt, '%b')"
	}
}

aging WorkOrderAging {
	label: "Work Order Aging"
	tint: "negative"
	query: "SELECT CASE WHEN dateDiff('day', CreatedAt, today()) <= 7 THEN '0-7 days' WHEN dateDiff('day', CreatedAt, today()) <= 30 THEN '8-30 days' WHEN dateDiff('day', CreatedAt, today()) <= 60 THEN '31-60 days' ELSE '60+ days' END AS label, COUNT(*) AS value FROM wh_FacilityManagement_WorkOrder WHERE Status NOT IN ('Completed', 'Cancelled') AND IsDeleted = false GROUP BY label"
}