Creating beautiful Sitecore reports easily with PowerShell Extensions

Creating reports is probably a task that every developer dread. I for once always felt like listening to Tennessee Ernie Ford?s ?16 Tons? every time when I was supposed to do it for yet another project audit ? especially this part resonated with me:

I was born one mornin’ when the sun didn’t shine
I picked up my shovel and I walked to the mine
I loaded sixteen tons of number nine coal
And the straw boss said “Well, a-bless my soul”

While the Advanced System Reporter gets you a long way, I’ve found that there were still many scenarios where I would have to write the reports by hand. Madness? Fast forward to Sitecore PowerShell Extensions (or SPE for short) it actually this doesn?t have to be like that and creating reports can be both quick and fun ? provided you?re going to use the module.

At the heart of Sitecore PowerShell Extensions (SPE) reports is the Show-ListView commandlet. The commandlet is somewhat based on how the Out-GridView commandlet in the Windows PowerShell. You can try it out by running the following command in your Windows PowerShell Console:

PS C:\> Get-ChildItem C:\ | Out-GridView

What you should see as a result of execution of the above will look somewhat like the following image:

Now using this commandlet is not possible in the remote/web scenario PowerShell Extensions.

Meet the Show-ListView commandlet

An analogous commandlet can be found in SPE. It?s called Show-ListView. The syntax for the command as generated by our built in help system looks like follows:

Show-ListView [-PageSize <Int32>] [-Icon <String>] [-InfoTitle <String>] [-InfoDescription <String>] [-Modal] [-ActionData <Object>] [-ViewName <String>] [-ActionsInSession] -Data <Object> [-Property <Object[]>] [-Title <String>] [-Width <Int32>] [-Height <Int32>]

The Show-ListView commandlet sends the output from a command to a grid view window where the output is displayed in an interactive table. Obviously because this commandlet requires a user interface, (similarly to Out-GridView) it does not work in a non-interactive scenarios like within web service calls. But you can use the following features of the table to examine your data:

Sort. To sort the data, click a column header. Click again to toggle from ascending to descending order.
Quick Filter. Use the “Filter” box at the top of the window to search the text in the table. You can search for text in a particular column, search for literals, and search for multiple words.
Execute actions on selected items. To execute action on the data from Show-ListView, Ctrl+click the items you want to be included in the action and press the desired action in the “Actions” chunk in the ribbon.
Export contents of the view in XML, CSV, JSON, HTML or Excel file and download that onto the user’s computer. The downloaded results will take into account current filter and order of the items.

So an effect of the following one liner:

Get-ChildItem master:\ | Show-ListView -Property DisplayName, __Updated , "__Updated by"

is…

That?s a lot of functionality for a single line of script, don?t you think? But I don?t particularly like how the columns are named. And I don?t have everything exposed as properties but I rather need some more functionality to get the data from my items. This is fully supported and achievable pretty much in the same vein as you would to it using the Format-Table commandlet. Let?s rename the columns to have some more user criendly names and add an information whether an item is Read Only (which is contained within the Appearance propery on an item.

Get-ChildItem master:\ | Show-ListView -Property `
    @{ Name="Display Name"; Expression={$_.DisplayName}}, # property renamed
    @{ Name="Updated"; Expression={$_."__Updated"}}, 
    @{ Name="Updated by"; Expression={$_."__Updated by"}}, 
    @{ Name="Read Only"; Expression={$_.Appearance.ReadOnly}} # We can access and manipulate object properties in the Expression

That?s far better! You can see that We?ve even detected that the property is a boolean value and turned result into a checkbox instead of showing a true/false text. What I still don?t like in this report is how the users are presented. I would much rather have a proper user name there. Which is easily achievable since we have full access to the Sitecore API from PowerShell in our script.

Get-ChildItem master:\ | Show-ListView -Property `
    @{ Name="Display Name"; Expression={$_.DisplayName}}, 
    @{ Name="Updated"; Expression={$_."__Updated"}}, 
    #I can use Sitecore API for that!
    @{ Name="Updated by"; Expression={[Sitecore.Security.Accounts.User]::FromName($_."__Updated by", $false).Profile.FullName}}, 
    @{ Name="Read Only"; Expression={$_.Appearance.ReadOnly}}

Obviously You?re not always going to be sending a straight output from a Get-Child item listing there. That wouldn?t be of much use. But since you can do any operations and pre-process your list you can do any rich operation with your data set as you need prior to sending it to Show-ListView. But that?s just straight PowerShell and is beyond of scope of this article.

How else can I influence my report?

You can specify how many items per page you?re going to show using the ?PageSize parameter
You can give the report window a meaningful title using the ?Title parameter
Provide a summary using the following 3 parameters:
- ?InfoTitle title for the summary
- ?InfoDescription Description shown in the summary
- You can specify an icon that will be shown in the report summary with the ?Icon parameter
Specify the report window size using the ?Width and ?Height parameters

Let?s try that on our script

Get-ChildItem master:\ | Show-ListView -Property `
    @{ Name="Display Name"; Expression={$_.DisplayName}}, 
    @{ Name="Updated"; Expression={$_."__Updated"}}, 
    @{ Name="Updated by"; Expression={[Sitecore.Security.Accounts.User]::FromName($_."__Updated by", $false).Profile.FullName}}, 
    @{ Name="Read Only"; Expression={$_.Appearance.ReadOnly}} `
    -Title "Children of Sitecore Item" `
    -InfoTitle "Report showing children of the Sitecore item" `
    #I can even do some additional logic inside my script - like count the children
    -InfoDescription "There are $((Get-ChildItem master:\).Count) immediate children to the /sitecore/ item." `
    -Icon "Software/32x32/branch.png"

I think you will find it pretty appealing considering especially a size of the script and the fact that you can also export it as both Excel:

or a beautiful HTML report:

Nowadays – when I?m doing my audits you will rather find me listening to Ella Fitzgerald?s ?Stairways to the Stars? instead of the sad Ernie?s song. Don?t be surprised to find me humming it when I?m writing my next report?

Keep building the stairway to the stars
A lovely stairway to the stars
It would be heaven
Heaven to climb with you.

Let me hear you humming!

PS. Funny thing – even though I’ve commented on Mike’s blog when he originally wrote about about it I’ve completely forgotten about it at the time of writing this blog. You can read more about the subject on Sitecore Junkie.