Pivot reports¶
New-UDPivotReport puts a pivot report builder into PSU apps, built on
pivotkit: readers drag fields from a searchable list into Rows,
Columns, Values and Filters and get a table with totals or a chart. Install the module,
pass the rows to -Data, and the report is there. Everything runs in the browser; the server is only
asked for what the event parameters do.
Install it from the PowerShell Gallery on the PSU server:
Install-PSResource -Name PowerShellUniversal.Component.Pivot
To see it with data first, the showcase app is a module of its own
(New-UDPivotShowcase): these examples, weather data, your own CSV and JSON files and a ticket report.
Pivot report from a web API¶
One line: the release cycles of Python from endoflife.date, one row each. Click a field to put it into Rows or Values, or drag it into a zone.
New-UDPivotReport -Id 'python' -Data (Invoke-RestMethod 'https://endoflife.date/api/python.json')
The same as a whole app script:
New-UDApp -Title 'Python releases' -Content {
New-UDPivotReport -Id 'python' -Data (Invoke-RestMethod 'https://endoflife.date/api/python.json')
}
Release data by endoflife.date, MIT licence, Copyright 2020 endoflife.date contributors; on this page a snapshot of 2026-11-08 (sources and licence).
Some products on endoflife.date mix dates and true/false in eol; the next example keeps the dates
only.
Then, depending on what the report needs:
| You want | Where |
|---|---|
| A start view, field names, a language, views in the Views menu | the next example, Views, Field names and descriptions |
| The report while the server is still loading, readers' own views, roles | the app example, Templates, view files and roles |
| Data from SQL Server | With your own data: SQL Server |
| Your corporate colours | Themes and corporate design |
| Run code when readers click or change the view | Events, security and limits |
| Every parameter | Commands and parameters |
Start view, field names and templates¶
Five products from the same API. -View opens the report with end of life per year and product, -FieldDefaults gives the fields names, descriptions and types, -Locale en-GB formats dates and numbers, and -Templates puts two views into the Views menu.
$cycles = foreach ($product in 'python', 'nodejs', 'postgresql', 'ubuntu', 'powershell') {
# eol and lts are a date or $true/$false: keep eol's dates, lts as $true/$false
(Invoke-RestMethod "https://endoflife.date/api/$product.json") | Select-Object @{ n = 'product'; e = { $product } }, cycle, releaseDate,
@{ n = 'eol'; e = { if ($_.eol -is [string]) { $_.eol } } }, @{ n = 'lts'; e = { [bool]$_.lts } }, latest
}
$eolPerYear = @{
derived = @(@{ name = 'End of life (year)'; field = 'eol'; by = 'year' })
rows = @('End of life (year)'); cols = @('product'); values = @(@{ field = 'cycle'; agg = 'count' })
}
New-UDPivotReport -Id 'endoflife' -Data $cycles -Locale en-GB -View $eolPerYear -FieldDefaults @{
product = @{ Label = 'Product' }
cycle = @{ Label = 'Release cycle' }
releaseDate = @{ Label = 'Released'; Type = 'date' }
eol = @{ Label = 'End of life'; Type = 'date'; Description = 'The day support ends; empty while endoflife.date knows no date.'; Href = 'https://endoflife.date/' }
lts = @{ Label = 'LTS'; Description = 'A long-term support release.' }
latest = @{ Label = 'Latest version' }
} -Templates @(
@{ Name = 'End of life per year and product'; View = $eolPerYear }
@{ Name = 'Releases per year'; View = @{
derived = @(@{ name = 'Released (year)'; field = 'releaseDate'; by = 'year' })
rows = @('Released (year)'); cols = @('product'); values = @(@{ field = 'cycle'; agg = 'count' })
display = 'chart'; chart = @{ type = 'bar'; stacked = $true }
}
}
)
The same as a whole app script:
New-UDApp -Title 'End of life' -Content {
$cycles = foreach ($product in 'python', 'nodejs', 'postgresql', 'ubuntu', 'powershell') {
# eol and lts are a date or $true/$false: keep eol's dates, lts as $true/$false
(Invoke-RestMethod "https://endoflife.date/api/$product.json") | Select-Object @{ n = 'product'; e = { $product } }, cycle, releaseDate,
@{ n = 'eol'; e = { if ($_.eol -is [string]) { $_.eol } } }, @{ n = 'lts'; e = { [bool]$_.lts } }, latest
}
$eolPerYear = @{
derived = @(@{ name = 'End of life (year)'; field = 'eol'; by = 'year' })
rows = @('End of life (year)'); cols = @('product'); values = @(@{ field = 'cycle'; agg = 'count' })
}
New-UDPivotReport -Id 'endoflife' -Data $cycles -Locale en-GB -View $eolPerYear -FieldDefaults @{
product = @{ Label = 'Product' }
cycle = @{ Label = 'Release cycle' }
releaseDate = @{ Label = 'Released'; Type = 'date' }
eol = @{ Label = 'End of life'; Type = 'date'; Description = 'The day support ends; empty while endoflife.date knows no date.'; Href = 'https://endoflife.date/' }
lts = @{ Label = 'LTS'; Description = 'A long-term support release.' }
latest = @{ Label = 'Latest version' }
} -Templates @(
@{ Name = 'End of life per year and product'; View = $eolPerYear }
@{ Name = 'Releases per year'; View = @{
derived = @(@{ name = 'Released (year)'; field = 'releaseDate'; by = 'year' })
rows = @('Released (year)'); cols = @('product'); values = @(@{ field = 'cycle'; agg = 'count' })
display = 'chart'; chart = @{ type = 'bar'; stacked = $true }
}
}
)
}
Release data by endoflife.date, MIT licence, Copyright 2020 endoflife.date contributors; on this page a snapshot of 2026-11-08 (sources and licence).
A report app with loading, views per reader and roles¶
The report in New-UDDynamic with New-UDPivotReportPlaceholder while the server asks the API; readers keep their own views on the server (New-UDPivotFileTemplateStore), readers with the role of -AdminRole publish shared views and see the admin panel, and -OnCellClick runs on the server. The app needs authentication and the role -AdminRole names: without a signed-in $User the readers' own views stay in the browser, and without that role in $Roles there is no Publish and no admin panel. On this page the report appears at once, the reader is signed in with the role Administrator, and the page shows the $EventData the server would get.
$products = 'python', 'nodejs', 'postgresql', 'ubuntu', 'debian', 'powershell', 'dotnet', 'mssqlserver', 'windows-server'
# view files next to the PSU repository: users/<hash>/ for each reader, shared/ for everyone
$store = New-UDPivotFileTemplateStore -MaxUserViews 20
New-UDDynamic -Id 'release-cycles-report' -LoadingComponent {
New-UDPivotReportPlaceholder -Height 640px -Locale en-GB -Label 'Release cycles'
} -Content {
$cycles = foreach ($product in $products) {
foreach ($cycle in (Invoke-RestMethod "https://endoflife.date/api/$product.json")) {
[pscustomobject]@{
Product = $product
Cycle = $cycle.cycle
Released = $cycle.releaseDate
# a date, or $true/$false when endoflife.date knows none
'End of life' = if ($cycle.eol -is [string]) { $cycle.eol }
LTS = [bool]$cycle.lts
'Latest version' = $cycle.latest
}
}
}
$perProduct = @{ rows = @('Product'); cols = @('LTS'); values = @(@{ field = 'Cycle'; agg = 'count' }) }
New-UDPivotReport -Id 'release-cycles' -Data $cycles -Label 'Release cycles' -SourceName 'Release cycles' -Height 640px -Locale en-GB `
-View $perProduct -Templates @(
@{ Name = 'Release cycles per product'; View = $perProduct }
@{ Name = 'End of life per year'; View = @{
derived = @(@{ name = 'End of life (year)'; field = 'End of life'; by = 'year' })
rows = @('End of life (year)'); values = @(@{ field = 'Cycle'; agg = 'count' }); display = 'chart'; chart = @{ type = 'bar' }
}
}
) -FieldDefaults @{
Released = @{ Type = 'date' }
'End of life' = @{ Type = 'date'; Description = 'The day support ends; empty while endoflife.date knows no date.' }
} -TemplateStore $store -AdminRole 'Administrator' -OnCellClick {
Show-UDToast -Message ('{0} release cycles' -f $EventData.count)
}
}
The same as a whole app script:
New-UDApp -Title 'Release cycles' -Content {
$products = 'python', 'nodejs', 'postgresql', 'ubuntu', 'debian', 'powershell', 'dotnet', 'mssqlserver', 'windows-server'
# view files next to the PSU repository: users/<hash>/ for each reader, shared/ for everyone
$store = New-UDPivotFileTemplateStore -MaxUserViews 20
New-UDDynamic -Id 'release-cycles-report' -LoadingComponent {
New-UDPivotReportPlaceholder -Height 640px -Locale en-GB -Label 'Release cycles'
} -Content {
$cycles = foreach ($product in $products) {
foreach ($cycle in (Invoke-RestMethod "https://endoflife.date/api/$product.json")) {
[pscustomobject]@{
Product = $product
Cycle = $cycle.cycle
Released = $cycle.releaseDate
# a date, or $true/$false when endoflife.date knows none
'End of life' = if ($cycle.eol -is [string]) { $cycle.eol }
LTS = [bool]$cycle.lts
'Latest version' = $cycle.latest
}
}
}
$perProduct = @{ rows = @('Product'); cols = @('LTS'); values = @(@{ field = 'Cycle'; agg = 'count' }) }
New-UDPivotReport -Id 'release-cycles' -Data $cycles -Label 'Release cycles' -SourceName 'Release cycles' -Height 640px -Locale en-GB `
-View $perProduct -Templates @(
@{ Name = 'Release cycles per product'; View = $perProduct }
@{ Name = 'End of life per year'; View = @{
derived = @(@{ name = 'End of life (year)'; field = 'End of life'; by = 'year' })
rows = @('End of life (year)'); values = @(@{ field = 'Cycle'; agg = 'count' }); display = 'chart'; chart = @{ type = 'bar' }
}
}
) -FieldDefaults @{
Released = @{ Type = 'date' }
'End of life' = @{ Type = 'date'; Description = 'The day support ends; empty while endoflife.date knows no date.' }
} -TemplateStore $store -AdminRole 'Administrator' -OnCellClick {
Show-UDToast -Message ('{0} release cycles' -f $EventData.count)
}
}
}
Release data by endoflife.date, MIT licence, Copyright 2020 endoflife.date contributors; on this page a snapshot of 2026-11-08 (sources and licence).
With your own data: SQL Server¶
The same call takes what Invoke-Sqlcmd returns:
New-UDPivotReport -Id 'tickets' -Data (Invoke-Sqlcmd -Query 'SELECT * FROM Tickets') -Locale de-DE
With thousands of rows, put the report into New-UDDynamic and give it New-UDPivotReportPlaceholder as
-LoadingComponent. The page then shows the report's frame at once, and the browser loads the module's
code while the server still builds the rows (Loading and performance):
New-UDDynamic -Id 'tickets-report' -LoadingComponent {
New-UDPivotReportPlaceholder -Locale de-DE
} -Content {
New-UDPivotReport -Id 'tickets' -Data (Invoke-Sqlcmd -Query 'SELECT * FROM Tickets') -Locale de-DE
}
Pivot report from a DataTable¶
Drag fields from the list into Rows, Columns and Values, switch to Chart, click a number for its rows. Saving a template and clicking a value show the $EventData the server would get.
# what a SQL query returns: a DataTable with dates, numbers, text and DBNull
$rng = [System.Random]::new(5)
$tickets = [System.Data.DataTable]::new()
foreach ($c in @(@('Ticket', [int]), @('Created', [datetime]), @('Team', [string]), @('Priority', [string]), @('Lead hours', [double]))) { [void]$tickets.Columns.Add($c[0], $c[1]) }
foreach ($i in 1..1200) {
$open = $rng.NextDouble() -lt 0.2
[void]$tickets.Rows.Add(10000 + $i, ([datetime]'2026-10-31').AddDays(-$rng.Next(0, 300)).AddHours($rng.Next(7, 19)),
('Service Desk', 'Network', 'Server', 'Applications')[$rng.Next(0, 4)], 'P{0}' -f $rng.Next(1, 5),
$(if ($open) { [DBNull]::Value } else { [math]::Round(2 + 40 * $rng.NextDouble(), 1) }))
}
$view = @{ rows = @('Team'); cols = @('Priority'); values = @(@{ field = 'Ticket'; agg = 'count' }) }
New-UDPivotReport -Id 'pivot-datatable' -Data $tickets -View $view -Height 620px -TimeZone UTC -Templates @(
@{ Name = 'Tickets per team and priority'; View = $view }
@{ Name = 'Lead time per month'; View = @{ rows = @('Created'); values = @(@{ field = 'Lead hours'; agg = 'average'; format = 'number:1' }); display = 'chart'; chart = @{ type = 'line' } } }
) -OnCellClick {
Show-UDToast -Message ('{0} tickets' -f $EventData.count)
} -OnSaveTemplate {
# checked before it is stored anywhere (see "Templates, view files and roles")
$json = ConvertFrom-UDPivotView -InputObject $EventData.view -AsJson
Show-UDToast -Message ('{0} saved ({1} characters)' -f $EventData.name, $json.Length)
}
What readers can do¶
| Part | What readers do |
|---|---|
| Fields | Search the list (by name, display name and description), drag a field into a zone, or click it (numbers go to Values, the rest to Rows); every field has a menu with the same moves, for the keyboard too. An "i" next to a field explains it when the app describes its fields (Field names and descriptions); an "i" next to each zone explains the zone. |
| Values | Sum, count, average, minimum, maximum and more statistics; "show as" a share of the total, of the row or column, a running total, a rank or the difference to the previous one; a number format; colour scales and data bars. |
| Rows and columns | Sort by label, by a value or in the field's own order, top or bottom N by any value, value conditions ("Sum of Revenue > 1,000"), number ranges. A date is grouped as soon as it is placed (by day, month, quarter or year, from the span of its values), and its menu groups along the time line or across years (month of the year, weekday, hour). |
| Filters | The values of a field with their row counts, search, "only this value", text conditions. |
| Table or chart | Bars, stacked or horizontal bars, lines, area, pie, donut, heatmap, or Automatic (lines over dates, bars otherwise); value labels, legend, title, smooth lines, axis and group labels, and for a pie or donut labels inside or outside, in the chart type's Display settings. |
| Enlarge, Full screen | Enlarge lets the table or chart fill the report (the field list and the zones step aside; Escape brings them back), Full screen shows the report on the whole screen where the browser allows it. Neither is part of the view. |
| Drill-down | A click on a value or a chart point opens the source rows behind it; the panel has its own Enlarge and an Export menu (Copy for a spreadsheet, Copy as JSON, Download CSV and Excel). |
| Undo, Reset, Views, Export | Undo and redo (Ctrl/Cmd+Z), Reset to the start view, Views with shared views and the reader's own, Save, Save as and New, empty report (Templates, view files and roles), "Copy view as JSON", "Download view" (a view file) and "Load view…", and Export to Excel (XLSX with number formats and merged headers), CSV and PNG, or Copy table and Copy data as JSON. |
| Tips | Above the result, for a layout that could be clearer: one at a time, each dismissible and remembered per report in the browser. -Tips Off or a list of tip ids limits them. Options, Help has "Show tips" and "Show explanations" (every "i" next to fields, zones and settings); -FieldInfo, -ZoneInfo and -SettingInfo decide one kind for the reader. |
| Field list | « collapses it to a narrow rail (the reader's choice is remembered); -FieldList Collapsed or Open starts every reader so. |
The report adapts to its own width:
| Where | What changes |
|---|---|
| A narrow column or a phone | The field list opens as a drawer and the zones stack above the table. |
| Below 600 pixels of height (820 in the narrow layout) | The zones start folded into one line; the toolbar's Layout button opens them. |
| Several reports on one page | Each needs its own -Label, the name screen readers hear for the report. |
Data¶
-Data takes a DataTable (Invoke-Sqlcmd, SqlClient, ODBC), DataRows, PSCustomObjects, hashtables or
a script block, like the other modules. The column names are the field names the reader sees, with
their PowerShell spelling. Every column gets a type from its values:
| Values | Type in the report |
|---|---|
| Numbers | number |
DateTime, DateTimeOffset |
date (see Dates and time zones) |
$true, $false |
boolean |
| Everything else | text |
DBNull, $null |
empty: they count as rows but not in sums and averages, and show as "(blank)" in rows and filters |
-FieldDefaults with Type reads a column as another type, for example dates that arrive as text
(Field names and descriptions).
Dates and time zones¶
| Value | Shown as |
|---|---|
A DateTime from SQL (no time zone) |
as it is, on every reader's screen the same |
A DateTimeOffset, or a DateTime of kind Local or Utc (Get-Date, [datetime]::UtcNow) |
an instant, at the wall clock of -TimeZone: the server's time zone by default (as an IANA name such as Europe/Berlin) |
Pass -TimeZone UTC, or the zone your readers are in, to fix it.
Language and formats¶
| Parameter | What |
|---|---|
-Locale |
The language of the report (German for de-*, English for everything else) and how numbers, dates and sorting look. The default is en-US for every reader: the report never takes the browser's or the server's language on its own, so a report and its exports look the same for everyone. |
-Labels |
Replaces single texts: the keys of pivotkit-react's LABELS, e.g. @{ zones = @{ values = 'Kennzahlen' }; tips = @{ values = '...' }; sharedViews = 'Team-Ansichten' }. pivotkit-react's pivotkit-react/labels entry lists every key, the texts of the menus included. |
Field names and descriptions¶
-FieldDefaults, -FieldDescriptions and -Schema give fields display names, descriptions with a
link, their own order of items, aggregators and formats - from your script, from SQL Server column
descriptions (Get-UDPivotSqlColumnDescription) or from a JSON Schema or an OpenAPI document:
Field names and descriptions.
Chart options¶
-ChartOptions is plain JSON merged over the chart's Apache ECharts option, for what the reader
cannot set in the chart's Display settings: -ChartOptions @{ legend = @{ top = 0 }; grid = @{ left = 80 } }.
It wins over the view's chart settings. pivotkit leaves out options that are markup or addresses
(formatter strings, rich, link, image) with a warning in the browser's console; at most 64 KB.
Pivot report in German¶
-Locale de-DE: German labels, numbers and dates; -FieldDefaults gives Lead hours an average with one decimal when it is put into Values.
$rng = [System.Random]::new(9)
$rows = foreach ($i in 1..600) {
[pscustomobject]@{ Ticket = $i; Team = ('Service Desk', 'Netzwerk', 'Server')[$rng.Next(0, 3)]; Kunde = 'Kunde {0}' -f [char](65 + $rng.Next(0, 4)); 'Lead hours' = [math]::Round(2 + 30 * $rng.NextDouble(), 1) }
}
New-UDPivotReport -Id 'pivot-german' -Data $rows -Locale de-DE -Height 560px -TimeZone UTC -FieldDefaults @{ 'Lead hours' = @{ Agg = 'average'; Format = 'number:1' } } -View @{
rows = @('Kunde'); cols = @('Team'); values = @(@{ field = 'Lead hours'; agg = 'average'; format = 'number:1' })
}
Theme and layout¶
| What | How |
|---|---|
| Light and dark | The report follows the app's light and dark switch and takes PSU's colours: the neutral greys of PSU's pages and MUI's primary blue. -Theme Light or -Theme Dark pins it. |
| Corporate design | From the app's stylesheet, the same one that themes the ECharts charts: Themes and corporate design. |
| Height | One screen: the window's height minus 180 pixels for PSU's app bar and a page title, between 600 and 1400 pixels (almost the whole window on a phone), so the whole report is on screen when the page loads and the table shows rows enough below the zones. -Height sets another one; give it at least 600 pixels where readers build reports: the zones take about 300 of them. |
| Inside a fixed size | The report stays inside its height - in a card of a fixed size too: the table scrolls inside it, however small it gets (measured in a 560-pixel New-UDCard on PSU 2026.3.4). |
| Growing | -Grow On lets it grow past the height instead when the table would get too small, and the page scrolls. |
| Containers | Inside New-UDTabs, New-UDCard, New-UDStack and expansion panels it fits its container and grows when a hidden tab is shown. |
Loading and performance¶
PSU draws a component once its properties have arrived, and the report's properties carry all of its
rows. Until then the page has nothing to show for it, and the browser does not even know that it will
need the module's code. Three parts take the time: the server converts the rows (-Data), PSU sends
them as JSON, and the browser builds the report.
New-UDPivotReportPlaceholder in New-UDDynamic -LoadingComponent (see
With your own data: SQL Server and the
app example) shows pivotkit's own skeleton of
the report while the server works:
| Placeholder | |
|---|---|
| What it shows | The field list, the toolbar, the zones and table rows as grey bars, in the report's colours, at the height you give it. |
| Parameters | The same -Height, -Theme, -Locale and -Label as the report, so nothing moves when the report replaces it. |
| Motion and screen readers | The bars move unless the reader asked the system for reduced motion; screen readers hear "Loading the report". |
| Why it is faster | The placeholder comes from the module, so the browser loads the module's code at once instead of after the data. |
| More than 10,000 rows | The report itself shows the same skeleton for a moment while the browser reads the data. |
What New-UDPivotReportPlaceholder shows in New-UDDynamic -LoadingComponent until the report arrives: pivotkit's own skeleton of the report in its colours, moving unless the reader asked for reduced motion.
New-UDPivotReportPlaceholder -Height 420px
The rows are converted without a call per cell. A DataTable of 1000 rows or more whose columns hold
text, numbers, booleans, GUIDs, dates or TimeSpans goes through a small C# class that the module
compiles with Add-Type the first time it needs it: about 2 seconds, once per PSU process (PSU runs
its apps in its own process, so once after PSU starts, not per app start). Where Add-Type is not
available, for example in constrained language mode, PowerShell does the same conversion more slowly.
The ECharts and Perspective modules convert their data the same way.
Measured on the test instance (PSU 2026.3.4 in a Linux container on a small NAS, 2026-11-07, Chromium; approximate, your server and network differ), with the showcase's DataTable of 5000 rows and 12 columns:
| Step | Time |
|---|---|
| Converting the rows, compiled class | about 40 ms (PSU 2026.2.5: about 36 ms) |
| Converting the rows, PowerShell only | about 150 to 250 ms |
| The report's properties: 1.24 MB of JSON, built, sent and received | about 0.25 s |
| Placeholder on screen, after the page request started | about 0.22 s |
| The table on screen, after the page request started | about 0.46 s |
| The browser, from the data to the table (the module's code already loaded) | about 40 ms |
What makes it slower is the size of the data: every row and every column travels to the browser.
Select the columns readers need (SELECT Team, Priority, Created ... instead of SELECT *), and
aggregate in SQL where readers never look at single rows.
Next: Field names and descriptions, Themes and corporate design, Views, Templates, view files and roles, Events, security and limits, Reference.