Use PowerShell to report on SharePoint content with custom (dingbat) characters

Summary: Learn how to use PowerShell to retrieve SharePoint list item values as dingbat symbols to make HTML reports simple and clean.

Can you show me an example of how to report on my SharePoint data by using something other than the standard ASCII characters?

Today we have a special guest MVP, Chendrayan Venkatesan, to help us out!

Chendrayan Venkatesan (Chen V) is a SharePoint IT Professional, Cloud and Datacenter Management MVP, Microsoft Community Contributor, Author of Windows PowerShell 5.0 for .NET developers, speaker, blogger, Microsoft TechNet Wiki Ninja, and a fun-loving person. Chen V is a PowerShell lover and thinks about automation every day. He loves to explore the world and to learn more languages just by connecting with people. So, please feel free to reach him on any of your favorite social networks.

Twitter LinkedIn Facebook

Now that we’ve introduced you to my friend, sit back as he explains this cool process!

In our frame of reference, what is a dingbat? (Sean commenting here…No, it’s not the sound of Batman running into a large bell that’s a Bat-Ding!)

Dingbat in computing describes the fonts that have symbols and shapes in the positions designated for alphabetical or numeric characters. The dingbats block in Unicode range is U+2700 till U+27BF.

To list all 192 dingbat symbols and shapes in the PowerShell integrated scripting environment (ISE), use the following one liner:

(0x2700..0x27BF).ForEach({[char]$_})

Sample output (first 20 symbols):

There are many ways to show the dingbat characters in the PowerShell ISE, and here are a few:

# Using ToChar Method 0x2764.ToChar($_)

# Using System.Convert [Convert]::ToChar(0x2764)

#Using Type Casting [char]0x2764

Output

Using ToChar Method: 

Using System.Convert:

Using Type Casting:

Here’s one more example to print the numbers as symbols by using PowerShell:

Is dingbat useful for reporting? The answer is Yes, only if we need to show the output as HTML and make simple, clean, and colorful reports. Before getting into the demo, let me explain the reason we built this PowerShell script. We have scheduled a script that sends HTML reports to content managers as normal text and numbers. Later, I came to know that the content management team manually copies and pastes the symbols to show the customer satisfaction (CSAT) values as heart, check mark or cross in one of the internal web portals. To avoid manual intervention, we used the Power of PowerShell! Now, the HTML report with required styling is sent directly to the internal web portal server, and CSAT reports appear with text and symbols.

For this demo we have created an issue tracking list in SharePoint Online. The list has default columns like Title, Issue Status, Assigned To, etc. Add a custom field as choice (Type)and name it as CSAT to collect customer ratings where 1 means poor and 5 means excellent. Refer to the rating table that we used for our demo. (Emoji is also supported in PowerShell ISE, so we can choose any supported symbol!)

Let’s see the script logic. Here we used a simple PowerShell trick to get the values between 1 and 5 to show the respective symbols.

Download the full script from the TechNet Gallery. In this script, instead of user input, the script reads the list item value, $item[“CSAT”], and turns it to symbols.

Note: The Dingbat characters will not appear in console host, and make sure that you have permission to query SharePoint list item values.

Example 1

Get-xSPOListItemAsDingBat -Url "https://contoso.sharepoint.com" `

-Title "ListName" `

-Credential “SPAdmin@constoso.onmicrosoft.com”

Sample output

Example 2: To export the output as HTML, use the below code:

Get-xSPOListItemAsDingBat -Url https://contoso.sharepoint.com `

-Title DemoList `

-Credential "SPAdmin@contoso.onmicrosoft.com" |

ConvertTo-Html -Fragment |

Out-File C:\Temp\CSATReport.html

Choose the desired location to save your report file.

Sample output

Note: You can use CSS to style your HTML and make reports more rich and colorful.

References:

• About SharePoint List
• SharePoint Field User Value Class
• SharePoint Online CSOM Assemblies
• How to retrieve SharePoint list items?
• Know about PowerShell Type Conversion Magic
• About Splatting
• About PowerShell Operator
• Using Switch Statement in PowerShell

That’s all there is to using “Dingbats” today. I personally thought this was something pretty unique to learn, and I’m already starting to play with this at work right now! Thanks Chen!

I invite you to follow the Scripting Guys on Twitter and Facebook. If you have any questions, send email to them at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow.

Until then always remember that with Great PowerShell comes Great Responsibility.

Sean Kearney Honorary Scripting Guy Cloud and Datacenter Management MVP