Product: PowerShell Universal
Version: 2.4.0
I’m new to PowerShell Universal, so although I did see there was a similar question to this asked, I didn’t understand the reply, and what I’m trying to achieve is slightly different anyway.
I’ve created a dashboard that contains a chart and a table side by side, and I’d like to make it so that when one of the values in the chart is clicked, the table is refreshed, with a filter set to show only entries that have that value.
I saw in another thread the response was “wrap it in a UDDynamic” but I don’t fully understand how to do that.
Here’s my code:
New-UDDashboard -Title "Students" -Content {
if ($Cache:StudentData -eq $null) {
# Define clear text string for username and password
[string]$userName = REDACTED
[string]$userPassword = REDACTED
# Convert to SecureString
[securestring]$secStringPassword = ConvertTo-SecureString $userPassword -AsPlainText -Force
[pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($userName, $secStringPassword)
$Cache:StudentData = Invoke-DbaQuery -SqlInstance REDACTED -SqlCredential $credObject -Database REDACTED -Query REDACTED | ForEach-Object {
@{
"Student ID" = $_."Student number"
"Preferred First Name" = $_."First Name"
Surname = $_.Surname
"Date of Birth" = (Get-Date $_."Date of Birth" -uformat "%d/%m/%Y")
"Enrolment Status" = if ($_."Student Status" -eq "E") {"Enrolled"} elseif ($_."Student Status" -eq "A") {"Applicant"} else {"Unknown"}
}
}
}
New-UDRow -Columns {
New-UDColumn -SmallSize 6 -MediumSize 6 -LargeSize 6 {
New-UDCard -Title "Chart" -Content {
$ChartData = [PSCustomObject]@{ Name = "Enrolled"; value = (($Cache:StudentData) | Where-Object {$_."Enrolment Status" -eq "Enrolled"}).Count }, [PSCustomObject]@{ Name = "Applicant"; value = (($Cache:StudentData) | Where-Object {$_."Enrolment Status" -eq "Applicant"}).Count }
New-UDChartJS -Type 'doughnut' -Data $ChartData -DataProperty 'Value' -LabelProperty 'Name' -OnClick { $ClickHandlerData = ConvertFrom-Json $Body; ** HERE is where I want to refresh the table ** } -BackgroundColor ("darkblue", "blue") -HoverBackgroundColor "darkmagenta"
}
}
New-UDColumn -SmallSize 6 -MediumSize 6 -LargeSize 6 {
New-UDTable -Title "Students" -LoadData {
$TableData = ConvertFrom-Json $Body
<# $Body will contain
filters: []
orderBy: undefined
orderDirection: ""
page: 0
pageSize: 5
properties: (5) ["Student ID", "Preferred First Name", "Surname", "Date of Birth", "Enrolment Status"]
search: ""
totalCount: 0
#>
if ($Cache:StudentData -eq $null) {
# Define clear text string for username and password
[string]$userName = REDACTED
[string]$userPassword = REDACTED
# Convert to SecureString
[securestring]$secStringPassword = ConvertTo-SecureString $userPassword -AsPlainText -Force
[pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($userName, $secStringPassword)
$Cache:StudentData = Invoke-DbaQuery -SqlInstance REDACTED -SqlCredential $credObject -Database REDACTED -Query REDACTED | ForEach-Object {
@{
"Student ID" = $_."Student number"
"Preferred First Name" = $_."First Name"
Surname = $_.Surname
"Date of Birth" = (Get-Date $_."Date of Birth" -uformat "%d/%m/%Y")
"Enrolment Status" = if ($_."Student Status" -eq "E") {"Enrolled"} elseif ($_."Student Status" -eq "A") {"Applicant"} else {"Unknown"}
}
}
}
$PageSize = $TableData.PageSize
# Calculate the number of rows to skip
$Offset = $TableData.Page * $PageSize
$PageEnd = $Offset + $PageSize - 1
if ($TableData.Filters) {
$whereFilters = foreach ($filter in $TableData.Filters) {
'$_."{0}" -like "*{1}*"' -f $filter.id, $filter.value
}
$whereScriptBlock = [scriptblock]::Create($whereFilters -join ' -and ')
$SessionData = $Cache:StudentData | Where-Object -FilterScript $whereScriptBlock
$Count = $SessionData.Count
$PageData = $SessionData[$Offset..$PageEnd]
} else {
$Count = $Cache:StudentData.Count
$PageData = $Cache:StudentData[$Offset..$PageEnd]
}
$PageData | Out-UDTableData -Page $TableData.page -TotalCount $Count -Properties $TableData.properties
} -Columns @(
New-UDTableColumn -Property 'Student ID' -ShowFilter -FilterType Text
New-UDTableColumn -Property 'Preferred First Name' -ShowFilter -FilterType Text
New-UDTableColumn -Property 'Surname' -ShowFilter -FilterType Text
New-UDTableColumn -Property 'Date of Birth'
New-UDTableColumn -Property 'Enrolment Status' -ShowFilter -FilterType Select
) -PageSize 10 -ShowPagination
}
}
} -DefaultTheme Dark
I also don’t really get how the $Cache scope works, e.g. does it invalidate itself after some period of time? if so how do I detect that? or do I need to do some sort of revalidation myself? maybe the UDDynamic can help with this?
Finally how do I change the text colour on the chart labels? Currently it’s a gray colour which doesn’t show up well in the dark theme. I tried Color or LabelColor but neither of those seem to work.