Custom Reports Using Sitecore PowerShell

One of the Sitecore PowerShell Extensions (SPE) module’s greatest strengths is handling data collections in Sitecore. Among the many things you can do with a collection of items extracted with SPE scripts, one is presenting this data as a visual report that can be exported to multiple file formats. This is possible using the Show-ListView function included with SPE.

Basics

The Show-ListView function displays a list of Sitecore items in a window that includes functionality to paginate, filter, sort, and export to files. The following example extracts a list of items from the /sitecore/layout section and displays some columns in the view:

$itemList = Get-ChildItem "master:\layout"

$reportProps = @{
    Title = "Report Title"
    InfoTitle = "This is a heading of the report."
    InfoDescription = "Total entries found: $($itemList.length)"
    PageSize = 25
}

$itemList | Show-ListView @reportProps -Property `
    @{ Label = "Item Name"; Expression = { $_.DisplayName } },
    @{ Label = "Path"; Expression = { $_.FullPath} }

Close-Window

If you run the code snippet in PowerShell ISE, you will get the following result:

The following is a short explanation/description of what this script does:

• The $itemList is the collection of items extracted from Sitecore. In this example, it is just a very simple Get-ChildItem from a section of the Sitecore tree. But it can be the result of any other complex filtering/querying.

• The $reportProps object contains the basic properties of the report window, such as the title (that appears on the window bar), the information heading bar (blue bar that sits atop the item grid) and the maximum number of items per page.

• The $itemList is pipelined with the Show-ListView function to generate the report. The “Property” switch is an array of objects that define the columns you will see in the report.

• Each column definition has a Label that will appear as the heading of the column, and an Expression that in its simplest form is what property has to be extracted for that column. You can use more complex expressions to display in the column.

Filtering the list is simple. Just enter a value in the “Filter” box in the toolbar, and it will match against any of the columns to find results.

You can export the list to CSV, Excel, HTML, JSON and XML formats. Just click on the corresponding button, and when it finishes processing, it will show a “Save As” dialog. Enter the file name and location and save the file.

Pagination controls are in the toolbar, next to the Filter field. If “PageSize” is reduced, for example, to 3, you will get something like this:

In the lower bar, you will see the total number of Results, and the current Page / total Pages.

Finally, if you select any item in the list, you can navigate to it by clicking on the “Open” button. It will open the Content Editor and open the item in the tree.

Using Custom Objects

The report can be also generated with custom objects. You will lose the ability to navigate directly to an item with the “Open” button, and no icon will be displayed for each row in the report. But all other functionality will remain. Take the following example (not optimized to depict the creation of the custom list):

$itemList = Get-ChildItem “master:\templates” -Recurse | Where-Object { $_.TemplateID -eq "{0437FEE2-44C9-46A6-ABE9-28858D9FEE8C}" }
$customList = New-Object System.Collections.Generic.List[PSObject]
 
ForEach ($item in $itemList) {
    $newObject = New-Object PSObject @{ ItemPath = $item.FullPath; ItemId = $item.Id; ItemName = $item.DisplayName }
    $customList.Add($newObject)
}
 
$reportProps = @{
    Title = “Report Title”
    InfoTitle = “This is a heading of the report.”
    InfoDescription = “Total entries found: $($itemList.length)”
    PageSize = 10
}
 
$customList | Show-ListView @reportProps -Property `
    @{ Name = “Name”; Expression = { $_.ItemName } },
    @{ Name = “Path”; Expression = { $_.ItemPath} },
    @{ Name = “ID”; Expression = { $_.ItemId} }
 
Close-Window

If you run this snippet in PowerShell ISE, you will get the following report:

As you can notice, you do not have the "Open" button in the toolbar, and the Icon is a small circle indicating "empty". But you can sort, filter and export as any other report.

Reference

Show-ListView command documentation