Using Open Source Graph In SharePoint/O365

It has been a long-standing problem to represent real-time data from SharePoint lists. For simple and quick graphs, Chart.js and Google Charts are considered quick and robust solutions.

Introduction

 

It has been a long-standing problem to represent real-time data from SharePoint lists. Microsoft has introduced PowerBI as a powerful reporting solution, but it comes with an extra cost. There is a dependency on your organization's infrastructure team to implement integration with PowerBI if you are on premise.

For simple and quick graphs, Chart.js and Google Charts are considered quick and robust solutions.

In this article, I will be covering Chart.js.

Background

 

Many organizations don't allow deployment of SPFx or other custom solutions. Usage of Script Editor Webpart can also provide the same solution. You may need to set up the solution by giving permission in a way that the users are unable to delete the webparts.

 

Prerequisites 

 

Knowledge of SharePoint RestAPI, jQuery, and Chart.js.

 

Create a SharePoint List

 

Create a SharePoint list using the below columns of type text. Add some sample data.

 

Add the Script Editor webpart to this SharePoint page. Add the below snippet.

 

Using the Code

 

Script Editor webpart renders graph.js and the REST API fetches the data from the SharePoint list.

 

Then, the data is boundd to chart.js and is represented in graphical format.

<canvas height="100" id="myChart" width="300"></canvas> // rendering chartjs
//using cdn for chartjs

<script>
var records = [0,0,0,0];

$(function () {
//waiting for sharepoint page to load
ExecuteOrDelayUntilScriptLoaded(queryData, "sp.js");

});

function queryData() {

//looping the countries
var region = ['India','China','United States','United Kingdom'];
$.each(region, function (index, value) {

var siteUrl = _spPageContextInfo.webAbsoluteUrl;
//setting up rest query
var fullUrl = siteUrl + "/_api/web/lists/GetByTitle('Incidents')/items/
              ?$top=5000&$select=Country&$filter=Country eq "  +"'"+ value +"'";

$.ajax({
url: fullUrl,
type: "GET",
headers: {
"accept": "application/json;odata=verbose",
"content-type": "application/json;odata=verbose",
},
success: function onQuerySucceeded(sender, args) {

    //fetching results
records[index] = sender.d.results.length;

var ctx = document.getElementById("myChart").getContext('2d');
//populating and configuring chartjs
var myChart = new Chart(ctx, {
    //define type of chart
    type: 'bar',
    data: {
        labels: ['India','China','United States','United Kingdom'],
        //setting data source
        datasets: [
            {
            //setting header
            label: '# of Incidents by Country',
            data: [parseInt(records[0]), parseInt(records[1]),
                   parseInt(records[2]), parseInt(records[3])],
            //setting background color of bars
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
            ],
            //setting border color of bars
            borderColor: [
                'rgba(255,99,132,1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
            ],
            //setting border width
            borderWidth: 1
        }
       ]
    },
    options: {
    //setting title
    title: {
                    display: true,
                    text: 'No of Incidents'
           },

                layout: {
            padding: {
                left: 0,
                right: 0,
                top: 0,
                bottom: 0
            }
        },
    tooltips: {
            //mode: 'dataset'
            //mode: 'y'
             mode: 'index'
        }
,
    events:["mousemove", "mouseout", "click", "touchstart", "touchmove", "touchend"] ,
        scales: {
            yAxes: [{
            stacked: true,
                ticks: {
                    beginAtZero:true
                }
            }]
        } ,
         legend: {
            display: true,
            labels: {
                fontColor: '#0b6623',
                backgroundColor :'rgba(11, 102, 35, 0.2)'
            }
        }
    }
});
},

error: function onQueryFailed(sender, args) {

alert('Error Retrieving Chart Data' + sender.responseText);
console.log(sender.responseText);
}
});
});
}
</script>

Understanding Code

 

Please see the comments in the above code for an explanation of the flow of data.

 

Script Editor Web Part

 

Let us have a nice view of data from the custom list in a SharePoint Script Editor webpart.

Reference

 

https://www.chartjs.org/