Reference¶

From the comment-based help of the module (Get-Help New-UDPivotReport -Full shows the same).

New-UDPivotReport¶

A pivot report builder for PowerShell Universal apps: readers drag fields into rows, columns, values and filters and get a table or a chart, with drill-down, templates and Excel export.

Pass the data as it comes - a DataTable from a SQL query, objects from Invoke-RestMethod or any PowerShell command. Readers build their own report: a field list with search, four zones (Rows, Columns, Values, Filters) with drag and drop and a menu on every field, sums, counts, averages and "show values as", date grouping by year, quarter, month or day, filters with value counts, table or chart (bars, lines, area, pie, heatmap), a click on a value opens the rows behind it, undo and redo, templates, and Excel (XLSX), CSV and PNG export. All of it runs in the browser (pivotkit); the server is only asked for what the event parameters do.

A view - what the report shows - is plain JSON that pivotkit checks against the data; it never runs as code. -View sets the start view, -Templates the views offered in the Templates menu, -TemplatePath reads view files from folders, and -TemplateStore keeps readers' own views and the shared ones (New-UDPivotFileTemplateStore, New-UDPivotTemplateStore) with -AdminRole for who may change shared views; -OnSaveTemplate and -OnDeleteTemplate stay for full control. Field names are matched without regard to case.

New-UDPivotReportPlaceholder¶

A placeholder in the pivot report's own look, for New-UDDynamic -LoadingComponent: it shows the report's frame while the server builds the data.

PSU renders a component only once its properties have arrived, and the report's properties hold all of its rows: with thousands of rows the page stays empty until the server has converted and sent them. Put the report into New-UDDynamic and this placeholder into its -LoadingComponent:

New-UDDynamic `-Id` 'tickets-report' `-LoadingComponent` {
    New-UDPivotReportPlaceholder `-Height` 640px
} `-Content` {
    New-UDPivotReport `-Id` 'tickets' `-Data` $tickets `-Height` 640px
}

The placeholder comes with the page and shows pivotkit's own skeleton of the report: the field list, the toolbar, the zones and table rows as grey bars, in the report's colours (light, dark, a corporate design from the app's stylesheet); the bars pulse unless the reader asked for reduced motion. Screen readers hear "Loading the report". It also makes the browser load the module's code at once, while the server is still working on the data.

Give it the -Height, -Theme, -Locale and -Label of the report, so nothing moves when the report replaces it.

ConvertFrom-UDPivotView¶

Checks a pivot view (a report layout) and returns it as a clean object or as compact JSON.

A view is the report as JSON: the fields on Rows, Columns, Values and Filters, sorting, totals, the chart. Views come from the browser ($EventData of -OnViewChange and -OnSaveTemplate), from files or from a database, so treat them as untrusted input: this function is the check before a view is stored or used.

It accepts a JSON string, a hashtable or a PSCustomObject. The view goes through one round trip ConvertTo-Json/ConvertFrom-Json (nothing is evaluated) and must be a JSON object of at most -MaxLength characters and at most eight levels deep. Only the keys of a pivotkit view are kept (version, rows, cols, values, filters, derived, sort, totals, collapsed, display, chart, formatting); rows and cols must be lists of at most 64 field names, values, filters, derived and sort lists of at most 64 objects, display "table" or "chart". Anything else throws an error that says what is wrong. The report itself checks every field name and setting against the data again (pivotkit-core's normalize) and drops what does not fit.

ConvertFrom-UDPivotViewFile¶

Reads a pivotkit view file (or a saved view { name, view }) and returns its name, description, source and checked view.

A view file is the JSON pivotkit's Views menu downloads (Download view, Copy view as JSON) and a template store keeps: format "pivotkit-view", formatVersion 1, name, description, source and the view. This function reads one as untrusted input: at most 1,000,000 characters and 16 levels, parsed without evaluating anything; a newer formatVersion or view version is refused (saving it again would lose what the newer version added); the name becomes plain text of 1 to 120 characters; the view goes through ConvertFrom-UDPivotView. An object { name, view } (a saved view as an app keeps it) is read as well; a bare view needs -Name.

ConvertTo-UDPivotViewFile¶

A view as a pivotkit view file: the JSON pivotkit's Views menu loads and a template store keeps.

Checks the view with ConvertFrom-UDPivotView and writes the envelope in pivotkit's key order ($schema, format, formatVersion, name, description, createdAt, generator, source, extensions, view) with a two-space indent and a trailing newline.

New-UDPivotFileTemplateStore¶

A template store that keeps the readers' views as view files in a folder on the server.

For New-UDPivotReport -TemplateStore. Every signed-in reader saves views under "My views" and sees them on every device; views in the shared folder are listed for everyone under "Shared views", and only readers with a role of New-UDPivotReport -AdminRole publish or delete them.

Layout below -Path: users// holds a reader's own views, shared/ the shared ones, each view a pivotkit view file -.pivotkit.json (the files Download view in the Views menu writes; shared/ also works as a -TemplatePath). Writes go to a new file that then replaces the old one, under a lock per folder, so two saves at the same moment never leave half a file or lose the count. -MaxUserViews and -MaxSharedViews cap the number of views per folder.

Without -Path the store lies next to the PSU repository ($Repository), never inside it: a repository synced with git would pick the files up. On several PSU nodes (high availability) a local folder is per node: use a shared folder, or a store on a database (New-UDPivotTemplateStore).

New-UDPivotTemplateStore¶

A template store on your own storage (a database, Valkey or Redis, S3): you write how a view is stored, the module does the rest.

For New-UDPivotReport -TemplateStore. The module decides who may do what (the signed-in user, -AdminRole for shared views, checked on the server), checks every name and view, applies the limits and writes the view file; the script blocks only store and load. Each gets one argument, $Request:

• Get: Scope ('user' or 'shared'), User (the user name; $null for shared), UserKey (a hash of the user name, safe in a key or file name). Return the stored views: view file texts (the Json of a save), objects with Json and FileName (where it is stored), or objects with Name and View. The module checks each one again.
• Save: Scope, User, UserKey, Name (checked), FileName, Key ('users//' or 'shared/'), Json (the view file to store) and View (the checked view). Store it under FileName, replacing what is there: for a name the scope has (the same characters in the same case, as pivotkit compares names) the module passes the FileName of that view, so a save replaces it; for a new name -.pivotkit.json that no stored view has.
• Delete: Scope, User, UserKey, Name, FileName, Key.
• Publish (optional): as Save with Scope 'shared' and User the publisher. Without it, publishing calls Save with Scope 'shared'.

Throw to refuse: the reader sees the message. The script blocks run in the runspace of the app script that defined them and do not see $User or $Roles: use $Request. PSU runs event handlers in runspaces of their own, so a script block needs every module and variable it uses (Import-Module inside it).

Invoke-UDPivotTemplateRequest¶

Saves, publishes or deletes a view for the report's event handlers; decides on the server who may do it.

New-UDPivotReport -TemplateStore registers event handlers that call this function with the $EventData of the report and the $User and $Roles PSU sets for the event. A reader saves and deletes their own views; publishing and deleting shared views needs a role of -AdminRole, checked here with the event's $Roles - never with anything the browser sends. Returns nothing on success, @{ error = '...' } when it refuses (the report shows the text).

Get-UDPivotSqlColumnDescription¶

The column descriptions of a SQL Server table or view (extended property MS_Description), as the hashtable -FieldDescriptions of New-UDPivotReport takes.

Reads sys.extended_properties for the columns of one table or view (class 1, major_id the object, minor_id the column) with the property name MS_Description - what SQL Server Management Studio writes as a column's Description - and returns column name to text, in column order. Columns without a description are left out.

Pass an open connection (-Connection: System.Data.SqlClient, Microsoft.Data.SqlClient or any ADO.NET connection to SQL Server; it stays open) or a connection string (-ConnectionString: the function opens and closes a connection with Microsoft.Data.SqlClient when that is loaded, for example by the SqlServer module, else with System.Data.SqlClient, which PowerShell 7 ships). The table name goes to SQL Server as a parameter of OBJECT_ID(), never into the query text. The account sees only the descriptions of objects it has permissions on; an unknown table gives an empty result.