Sitecore

Sitecore Powershell 7: Creating Reports

Istock 696570136

One scenario you may encounter is wanting to create a report containing information about our Sitecore instance and its content. In this blog, we will see a few methods of how we can create reports using Sitecore Powershell.

Exporting Data as Plain Text File Using Out-Download Command

The Out-Download command is used to prompt the Browser client to download the generated output stored in the file named using –Name parameter. We need to specify input data using –InputObject.

Following is the script that will export information about the items that refer to item with ID – {13AAD693-D140-4054-840D-4F9DABF681D3} and are created from Event Details template.

 

#Exporting information in plain text

[String[]] $data = Get-ItemReferrer -ID "{13AAD693-D140-4054-840D-4F9DABF681D3}" | Where-Object { $_.TemplateName -eq "Event Details" } | Select-Object -Property Name,Id, TemplateName

 Out-Download -Name ReferrerInfo.txt -InputObject $data

See that $data is of type String[], and the –Name parameter of Out-Download command has a file name with an extension .txt. After running the script, I was prompted to download a file named ReferrerInfo.txt and inside it, I found Item object information as Powershell Hash tables in plain text.

Following are the downloaded file’s content:

    Spereports1

Exporting Data as Excel File Using Out-Download Command

The exported file had raw objects in plain text which is readable only for a developer. We can instead use Out-Download command to export data as an Excel sheet in proper Excel format.

We will be using the ConvertTo-Xlsx function, which will apply the proper Excel native format. For using this function, we need to first Import it using the Import-Function command. After that, we just need to add ConvertTo-Xlsx command after a (|). Also, as this will not be a plain text, we will change the data type of $data to byte[].

#Export data as Excel sheet
Import-Function -Name ConvertTo-Xlsx

[byte[]] $data = Get-ItemReferrer -ID "{13AAD693-D140-4054-840D-4F9DABF681D3}" | Where-Object { $_.TemplateName -eq "Event Details" } | Select-Object -Property Name,Id, TemplateName | ConvertTo-Xlsx

 Out-Download -Name ReferrerInfo.xlsx -InputObject $data
Sitecore - Understanding Development Approaches: A Sitecore Outlook
Understanding Development Approaches: A Sitecore Outlook

Designing, building, and implementing top-notch experiences not only requires a great deal of planning, strategy, and time – it also requires the right digital experience platform (DXP) and the right development approach for your business needs.

Get the Guide

 

After running the script and downloading ReferrerInfo.xlsx file, it had the following content:

Spereports2

 

You can see how properly structured it is with respect to a plain text files.

Exporting Data as CSV File Using Export-CSV Command

This command will create a CSV Format file based on the input provided and dump the CSV information in the file specified by –Path location.

#Export data to CSV in file disk
$DataLoc = "C:\testCSV\ComponentsInUse.csv"

Get-ItemReferrer -ID "{13AAD693-D140-4054-840D-4F9DABF681D3}" | Select-Object -Property Name,Id, TemplateName | Export-Csv -Path $DataLoc

This script will create a CSV file ComponentsInUse.csv inside C:\testCSV folder.

Following are the content of this file:

Spereports3

 

When opened in Excel:

Spereports4

 

So far, we have seen how to export item information in a simple text file, Excel file using Out-Download command, and how to use Export-Csv command to export information in CSV. In the next post of this series, we will see how to import CSV and create items from it.

About the Author

Ramakant Chandel is working as a Sitecore Professional. He likes to explore challenging and new technical concepts in Sitecore. He is passionate about sharing his knowledge with everyone.

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up
Categories