Writing Sitecore PowerShell Reports for Content Authors

Sitecore PowerShell Extensions (SPE) is such a great tool that I now consider it a fundamental part of any Sitecore solution. Among other uses, it excels at reporting and task automation. For example, when a Sitecore content tree balloons to 100,000+ items, writing efficient reports and automating tasks become critical to managing the madness.
With that being said, SPE is definitely a developer-focused tool, largely thanks to the learning curve of PowerShell syntax and conventions (sprinkle in the Sitecore API and we’re definitely in developer territory). SPE does come with some great tools, however, that allow PowerShell scripts to be utilized by non-developers, namely Sitecore content authors, editors, and administrators.
I’ve written extensively about creating a quality authoring experience for content authors. Extending these concepts to PowerShell, content authors shouldn’t have to edit a complicated script (with all those weird symbols and names and indentations!) just to run a custom report. The ideal content authoring experience ensures that authors go no deeper into Sitecore than the Client Desktop.
The SPE ISE (“integrated scripting environment”) looks like this:

SPE provides tools that allow developers to write custom scripts for reporting and automation, then dress them up with clean forms for input and table-based output with file export options. In SPE these are provided as cmdlets:

• Read-Variable – display a form for user input and create script variables from the input (documentation)
• Show-ListView – format and display data in an interactive table; also provides export options to various file formats (CSV, HTML, Excel, etc.) (documentation)

Let’s walk through the steps to transform a dirty, dev-centric script into a hands-off report that is readily available for any Sitecore user.

A Sample Script

Good UX Means Good Business

In a world where technology is rapidly advancing and user expectations are rising, it’s no longer enough to have an average user experience; to delight your users and surpass your competition you must strive for the exceptional.

Get the Guide

I commonly use the following script for reporting purposes. Given a folder of renderings, it finds all website pages that utilize said renderings.

This is a pretty basic script (and without any optimizations). It demonstrates a few things: input is needed from the user (the “renderingRoot” “templateName” variables), a bunch of processing occurs, and some output is written directly to the console. Most Sitecore users – including content authors – should never have to see this script (or any of the PowerShell ISE interface). Even with clean, well-written comments, non-technical should not have to manually assign variables or copy/paste results from the console window.

Adding an Input Form

All necessary inputs should be collected from the user, and this is where the Read-Variable cmdlet comes into play. Read-Variable accepts several parameters that determine what text, input, and variables get displayed on the form. It then assigns the provided values to script variables.

In the example above, I’m building up all my properties before I use the cmdlet. This is primarily for readability purposes. Most of the properties are window dressing (“Title,” “Description,” etc.), but “Parameters” is crucial. The most important takeaway from these examples are the variables names and editor types.

The ‘Name’ property (“renderingRoot”) will become a local variable, and can be subsequently used as needed:

The ‘Editor’ property defines what kind of control is rendered on the form (textbox, dropdowns, radios, etc.). The Read-Variable documentation has a section that provides usage examples of each type of editor, and I recommend careful study of that code.
Here is what the user would see with the above Read-Variable cmdlet:

Using Show-ListView

With a simple, clean input mechanism, it’s only fair that report results are displayed in an equally nice interface. The standout features of the Show-ListView cmdlet include: 1) tabular results that can be paged, sorted and filtered, 2) the ability to export to various file formats, and 3) lots of customization options.
It is best to pass a data structure into the cmdlet, so if a script was previous outputting data to the ISE console, a simple array-type data structure handily replaces that behavior. For a simple example, I’m appending new PSObjects to an array:

Using Show-ListView is then a matter of passing your data to the cmdlet and defining some basic structure:

The resulting window:

And finally, the full script:

Integrating with the Start Menu

We now have a script that accepts inputs via a form and outputs results into an interactive table. How do we go about allowing content authors to run the script? Even without any script editing to be done, authors shouldn’t have to open the ISE, load a script, and click “Run.” Instead, they can use what they know: the Sitecore Client Desktop. There are other integration points that scripts can utilize – such as context menus or the content editor ribbon.

The simplest way to let authors run scripts is to create a new SPE module and set the integration point to “Start Menu Reports”:

Under the “Report” item of the new module, add a new “PowerShell Script Library,” then a “PowerShell Script” item. Copy/paste your script into the Script body and save. And voila: