Protiviti / SharePoint Blog

SharePoint Blog

July 21
BI Reporting with Chart.JS and the Rest API

Data is fascinating. I could talk all day about numbers, metrics, and reporting visualizations. It is often said “A picture is worth a thousand words” and to me a chart tells a very similar story.
I’m the first one to say how much I love working with SQL Server Reporting Services, Tableau, and Power BI. However, not all businesses need such a complex and detailed tool as one of these three. Some business may just have a few key pieces of data that they are interested in tracking. For this, they might want to think about using lightweight HTML5 based JavaScript charts. Chart.js lets you add simple graphs that really go a long way in showcasing your data. Chart.js has a great website with a lot of documentation.

Since Chart.js is JavaScript based, we can easy supply the data through the Rest API. I’ve come up with a small example using Chart.js and the Rest API. Imagine you maintain an online educational resource for programmers on an O365 SharePoint site. You want to make sure that you are providing the most useful articles to your subscription base. It is decided that you should poll your users to see what their favorite programming language is. This example will go through what is needed in the SharePoint list and the code to create a chart from that list. In the next blog post, we will go through how to create a simple html form and utilize the Rest API to collect the data from your users. Right now we will just focus on the already captured data and the display.

The first step is to create a Custom List in your SharePoint site. I renamed the Title field to “Question” for aesthetics. Then added the IsActive, Labels, and Values fields like below:

Once the list is built. Let’s go ahead and add a new poll question. You will see that you can put in your question and then there are some instructions on how to fill out the Labels and Values fields. Right now you can populate your Values field as if the data collection piece of the poll was already working. 

Once you click save, the new item shows up in your list:

Now to the code. The following is a function that takes two parameters: A URL and an item ID. It is then making an ajax call to gather the item from the URL based on the item ID. Once it gets the item, it parses out the Labels and Values. It then determines the colors for the chart. In this example we are creating a doughnut chart. Once we have all of the pieces we can create the data object and the options object. For this example, I’m setting the legend to false so that I can generate and style my own legend. Finally, I find my parent containers by IDs and insert my question text, chart, and legend.  

To use this function, I’m doing a little work to dynamically build my URL. I find my subsite Name, build my domain URL, and supply my list name. I then use the standard format for the rest of the URL to get the list and associated item by ID. I’m then building out my html structure dynamically. This is more important when we are capturing the data and showing the graph instantaneously to the user in the next blog post. 

To make the above script work you are going to need to reference JQuery and Chart.js in your site. The finished result will look like the below chart. What you do not see from this static image is the animation when the chart is loading. You will also notice that there is a hover over effect that tells you the value of each piece. With generating a custom legend, I’m able to add the colors besides each of the programing languages.

Going back to our scenario as an owner for the online educational resource for programmers, I might focus on providing more resources on C# and JavaScript based on the above data.

This is just a very simple example of how you can report off of list data. In the next blog post, we will see how we can poll users and show them this graph once they have answered the question.

Quick Launch

© Protiviti 2021. All rights reserved.   |   Privacy Policy