NIVO Charts From SQL Query

PowerShell Universal
1

I’m attempting to create a basic Nivo chart using data obtained from a SQL query. I tested with the sample code provided in the documentation and it shows the chart as it should but once I enter my script the chart is blank. The Data has been tested with a UDChartJS and it works.

I do have a license but I’m not sure if it was properly activated; I copied the contents of the .txt file to “Enter License” in the “License Settings” within localhost:5000.

PS: How do I find out what version of PSU I have and how do I update to the latest or a specific version?

        $Cache:ByZipData = Invoke-SqlCmd2 -ServerInstance $SQLServer -Database $SQLDB -Query $ZipQuery -As PSObject

        $Cache:ByZipChart = $Cache:ByZipData | ForEach-Object { 
            [PSCustomObject]@{ 
                Number = $_.Number;
                ZIP = $_.ZIP; 
            }
        }
        
        New-UDNivoChart -Id 'FirstNivoChart' -Bar -Keys "Number " -IndexBy 'ZIP' -Data $Cache:ByZipChart -Height 500 -Width 1000

I also noticed that the sample code declares the variables as “Name” and “Value”, but when using the New-UDNivoChart command it uses ‘name’ and “value.” Is the New-UDNivoChart command not case sensitive? If so, why changing it ‘Name’ and “Value” produces a blank chart?

 $Data = 1..10 | ForEach-Object { 
     $item = Get-Random -Max 1000 
     [PSCustomObject]@{
         Name = "Test$item"
         Value = $item
     }
 }
 New-UDNivoChart -Id 'autoRefreshingNivoBar' -Bar -Keys "value" -IndexBy 'name' -Data $Data -Height 500 -Width 1000

Product: PowerShell Universal
Version: 1.4.6
2

I managed to update to 1.5.3 but once I launched my dashboard all tables and charts were empty, they worked perfectly in 1.4.6. I have all my queries in a separate file, PASSQueries, that I call in my main Dahsboard file by using:

$Cache:FData = Invoke-SqlCmd2 -ServerInstance $SQLServer -Database $SQLDB -Query $FQuery -As PSObject

        $Cache:FTData = $Cache:FData | ForEach-Object {
            [PSCustomObject]@{
                FID     = $_.F_ID
                TotalUsed    = $_.TOTAL_USED
                Remainder    = $_.REMAINDER
            } 
        }

        $FTColumns = @(
            New-UDTableColumn -Property FID -Title "FID" -render {
                    $Item = $Body | ConvertFrom-Json
                    New-UDButton -id "btn$($Item.FID)" -text $Item.FID -onClick{
                        $Cache:TransLookup = Invoke-WebRequest http://localhost:10001/api/APP/$($Item.FID) | ConvertFrom-Json
                        Sync-UDElement -Id 'APPElement'
                        Show-UDToast -message $Item 
                        }
            }
            New-UDTableColumn -Property TotalUsed -Title "TotalUsed"
            New-UDTableColumn -Property Remainder -Title "Remainder"
        )
        New-UDTable -Title "Totals"  -Columns $FTColumns -Data $Cache:FTData -Sort -PageSize 10 -PageSizeOptions @(10, 25) -Search -export

If I want to build a Nivo chart using the data from the table above, what would the script look like? I feel like there isn’t much documentation on how to handle data from SQL or other kinds or relational databases.

EDIT: Nevermind, it had nothing to do with the cache. For some reason it was trying to log me in using my computer number instead of my username. All I had to do was kill a task called Universal.Server. My other questions still remain though,

1. How do you make a Nivo chart from the data above
2. How to properly activate a license

3

The version number for PSU is listed the footer of the admin console. If you are using the latest version of the UD framework, we can look that up based on the PSU version. All the UD version are listed here and within the frameworks page: Changelog - PowerShell Universal (ironmansoftware.com)

  1. I can put together some SQL examples for the docs site. I’ll make sure to include one for the nivo chart.
  2. On the license page, you should be able to just copy the contents of the file into the text box. If the license is then listed on that page, it has worked. We did just uncover an issue today where if you license the system with a Universal Dashboard license, the authentication switch will be disabled in the dashboards table. We’ll have that resolved in 1.5.4.
4

Thank you so much! I really appreciate your response, the SQL examples will be VERY helpful!

I downloaded the latest version of PSU from here. Before going to lunch I did notice the footer said 1.5.3 and I had additional fields in the Dashboard page. When I came back from lunch I opened the admin console and now it says 1.4.3 and the “extra” settings are gone. Could it be because I did a MSI upgrade when my initial install was from VS Code?

5

Seems like a PSU server is starting somewhere you aren’t expecting. If I had to guess, the VS Code extension is starting the one it downloaded and is now an older version of the server.

You can check where it’s running from with this.

(get-Process universal.server).Path
6

It says the server is located in C:\Users\TestAcc\AppData\Roaming\PowerShellUniversal\Universal.Server.exe

I think I found the 1.5.3 server version in
C:\Program Files (x86)\Universal

I’m assuming I would need VS Code to point to the new version of the server? How would I accomplish that?

7

Figured out how to change the path to the right server.

View > Extensions > PowerShell Universal Extension Settings > Enter new path in PowerShell Universal: Server Path

8

@adam

When trying to create a Nivo chart using the following script I get this error. Is this a bug or is there something wrong with my script?

image
image813×283 3.68 KB

Script:

$SQLServer = “Server”
$SQLDB = “DB”
$UserName = “UserName”
$Password = “Pass”

$SQLQuery = @"
Select * from Table
"@

Set-PSUCache -Key “TestKey” -Value (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDB -Query $SQLQuery -Username $UserName -Password $Password ) -AbsoluteExpirationFromNow ([TimeSpan]::FromHours(24))

Dashboard:

New-UDColumn -LargeSize 3 -Content {
     [array]$Data = [PSCustomObject]@{
          id = "DataSet"
          data = ((Get-PSUCache -Key "TestKey") | ForEach-Object {
               [PSCustomObject]@{
                    x  = $_.Column1
                    y  = $_.Column2
               }
          })
     }

     New-UDNivoChart -Line -Data $Data -Height 500 -Width 1000 -LineWidth 1

}