Dynamic Columns -OnRender

PowerShell Universal
1

I have a SQL stored procedure I call that can return the data with dynamic columns of either the fiscal year or fiscal quarters.

Now, I can get the data to show up when I do this:

foreach ($year in $Fiscalyears) {
      $fy = $year.FiscalYear
     New-UDTableColumn -Property $fy -Title $fy 
}

But, I would like to format the output to be currency:

foreach ($year in $Fiscalyears) {
          $fy = $year.FiscalYear
          New-UDTableColumn -Property $fy -Title $fy -OnRender {
                   "{0:C}" -f $EventData.$($fy)
           }
}

Unfortunately, that doesn’t work. (I have tried a number of different ways to get that to work. Any ideas on how I can have dynamic columns with an -OnRender?

2

I recently ran into a similar problem. The issue was that the variable was out of scope by the time the scriptblock was executed. I fixed this by first creating the scriptblock as a string and expanding the variable. After that, I passed the scriptblock to the -OnRender parameter like this:

$Fy = $year.FiscalYear
$ScriptBlockContent = @"
    `$Fy = `'$Fy`'
    "{0:C}" -f `$EventData.`$fy
"@
$scriptBlock = [ScriptBlock]::Create($ScriptBlockContent)
New-UDTableColumn -Property $Fy -Title $Fy -OnRender $Scriptblock

This way, the variable is properly expanded before being passed to the scriptblock.

Perhaps this could be solved by passing something to the scriptblock that contains the property name of the tablecolumn similar to $Eventdata.