HTML Reporting – Grouping Rows
Continuing my HTML reporting series, I have a new twist on coloring columns. There are times when you want to group a number of rows together, so I created this function to do just that. It’s essentially a riff on the Set-AlternatingRows with a little bit of Set-CellColor thrown into the mix.
Background
Lately I’ve been neck deep completely revamping our reporting on our faxing environments. We now have eight different environments with 3 more already planned for this year (retiring 2 others so a net gain of 1). This means a lot of changes will be happening and our current reporting capability is completely hard coded. That means all the queries where we gather data from the fax environments to our data warehouse all have to be updated (it’s about 8 queries altogether) and then the PowerShell reports creators have to be updated (there are three of those). It’s a mess and really painful.
My goal was to completely eliminate that pain and make a dynamic reporting structure. First step was to figure out how to make our queries run in a loop so I could go through a list of servers and execute the same query. Once that was done I had to revamp all of those queries to use this new looping structure and verify that the data was all getting pulled correctly. The final result with all of this was I wanted the data to reach the report database and look exactly like it did before. Once that was done it was time to rewrite the PowerShell reports–and since athena health had just gone through a color rebranding I had to “purplize” the reports too. The somewhat frustrating problem with this approach is the new reports look exactly like the old ones, and I’m pretty sure no one really appreciates how much change has happened in the background!
Getting back to the script, we have a lot of fax environments per location, plus multiple servers per environment so some reports needed to group the columns by environment and some by location.
Setting the Stage
First step is to get the parameters set. I’ll need the $InputObject which is the HTML from ConvertTo-Html, of course, and I’ll need to know the name of the column I’m keying off of. I’ll also need the CSS classes for the odd and even groupings. I set a default of TReven and TRodd so you don’t have to specify these but obviously your CSS will need to support those classes.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [CmdletBinding()] | |
| Param ( | |
| [Parameter(Mandatory,ValueFromPipeline)] | |
| [string[]]$InputObject, | |
| [Parameter(Mandatory)] | |
| [string]$ColumnName, | |
| [string]$CSSEvenClass = "TREven", | |
| [string]$CSSOddClass = "TROdd" | |
| ) |
Nothing too exciting here. I use the PowerShell 3.0 notation for setting Pipeline, so no need for “=$true” in there. $InputObject is mandatory here, as well as the ColumnName, and $InputObject can be read from pipeline. This will require a Begin/Process/End block set, though my Begin block just has a Write-Verbose in there that the script has begun!
At this point the script just reads through every line from $InputObject (in the case of the pipeline this will only be one line, but if you want to provide the script with the entire HTML it can handle that too) and check for the table header tag. If it finds the <th> tag, it’ll then search for $ColumnName and error out if it can’t find it. The next bit of code is kind of interesting:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $Search = $Line | Select-String –Pattern "<th>.*?</th>" –AllMatches | |
| $Index = 0 | |
| ForEach ($Column in $Search.Matches) | |
| { | |
| If (($Column.Groups.Value -replace "<th>|</th>","") -eq $ColumnName) | |
| { | |
| Write-Verbose "$(Get-Date): $ColumnName located at column $($Index + 1)" | |
| Break | |
| } | |
| $Index ++ | |
| } |
One problem I ran into with Set-CellColor was if a value in a column was duplicated it might not color the proper cell, and I wanted to avoid any chance of that here. So the idea with the above code was to determine exactly which column my $ColumnName was in. As usual, offset by zero because that’s what computers do. So I used Select-String to find all the column names (between the <th> and </th> tags) and then compare that to $ColumnName. If it doesn’t match then increment my $Index number and search the next column. When I have a match $Index will be the proper column number. I can then use a similar technique later on the <td> and </td> tags to make sure I’m looking at the proper column for a change.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| If ($Line -like "*<td>*") | |
| { | |
| #Check the column for a value change, and if so swap the designated class | |
| $Search = $Line | Select-String –Pattern "<td>.*?</td>" –AllMatches | |
| If ($LastColumn -ne $Search.Matches[$Index].Value) | |
| { | |
| If ($Class -eq $CSSEvenClass) | |
| { | |
| $Class = $CSSOddClass | |
| } | |
| Else | |
| { | |
| $Class = $CSSEvenClass | |
| } | |
| } | |
| $LastColumn = $Search.Matches[$Index].Value | |
| $Line = $Line.Replace("<tr>","<tr class=""$Class"">") | |
| } |
Speaking of, here’s that part. Use the same Select-String technique, but this time I just go directly to the proper column using $Index and if I don’t find a match then swap out the color designation for the column–which is really just me swapping back and forth between the CSS class of TRodd and TReven. The last part is simply using Write-Output of the modified (or not modified) $Line. Since this is a pipeline enabled script I don’t have to do anything more, it’s up to the script coder to deal with the output (pipe it into Out-File or assign it to a variable). The beauty of using the pipeline is often the intervening functions don’t have to worry about upstream or downstream processes, they just do their thing and move on.
Here’s an example report:
Notice how the colors in this report are grouped by server name (even those I had to redact most of that, it really is grouped that way!) If you’re interested in this particular report, which is great for monitoring your SQL jobs you can find it here and here. One thing to keep in mind is your sorting. Make sure to use Sort on your $ColumnName so everything is grouped together, otherwise the script will color a column differently every time it sees a change in the column value!
Last, here’s a sample CSS you can use:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <style> | |
| TABLE {border–width: 1px;border–style: solid;border–color: black;border–collapse: collapse;} | |
| TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;} | |
| TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;} | |
| .TRodd { background-color:#ffffff; } | |
| .TReven { background-color:#dddddd; } | |
| </style> |
Hope you enjoy!
Set-ColorHTMLRowsByColumn.ps1
1 Comment »
Leave a Reply
Me
LinkedIn
The Surly Admin 
[…] go into the dirty play by play that I typically go, but I would like to mention that I also use the Set-ColorHTMLRowsByColumn function to logically group the servers together in the […]