Excel Web Part Services have been catering to the dashboard requirements of SharePoint because of their performance, but let’s face it – they are very heavily dependent on various services/features and require quite some effort in customization and configuration.
This is where the fast and responsive ChartJS comes into the picture. In one of my previous articles, I wrote about creating easy dashboards in HTML using ChartJS. If you are not familiar with ChartJS, the previously linked article will get you up to speed quickly.
Let us create a dashboard for the below list in SharePoint Online.
This list contains a list of candidate names and their cities. We will create a dashboard out of this data that will display the below charts.
- A bar graph depicting the Number of Persons in each city
- All other forms of graphs available in ChartJS
The idea is to create a button click event, on click of which, the dashboards will be generated. That data will be queried from the SharePoint list using REST API.
Getting Started
Let us start with a simple skeleton of HTML5. Place just a canvas element with the id “myChartContainer.
Similar to what was explained in the previous article, ChartJS requires jQuery, Bootstrap, and ChartJS libraries. You can link them from CDN in your HTML page.
At this point, we will just create a button, with the function RunChart(), and the rest of the HTML and the webpage would look like below.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Chart JS Demo</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/[email protected]/dist/Chart.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" />
</head>
<body>
<br><br>
<button onclick="RunChart()">Generate Chart</button>
<br><br>
<div style="position: relative; height: 800px; width: 800px">
<canvas id="myChartContainer" style="border: 1px solid"></canvas>
</div>
</body>
</html>
Adding the Rest API Data retrieval functions and ChartJS elements inside your HTML.
Our HTML skeleton is ready at this point. Next, let us follow these steps.
- Write methods to capture the data from the list using the REST API.
- Pass the data (labels and data in ChartJS basically) to the ChartJS element
This is how the entire code looks. The detailed explanation is presented after the code.
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Chart JS Demo</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/[email protected]/dist/Chart.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" />
<script>
var AllUniqueCities = [];
var AllCandidateCount = [];
function RunChart() {
var cities = [];
var candidatepercity = [];
cities = GetAllCities();
candidatepercity = GetCandidatesPerCity(cities);
var ctx = document.getElementById('myChartContainer').getContext('2d');
var myChart = new Chart(ctx, {
type: 'bar', // bar, horizontalBar, pie , line, doughnut, radar, polarArea
data: {
labels: cities,
datasets: [{
label: 'Participants vs City',
data: candidatepercity,
backgroundColor: ['green', 'red', 'blue', 'purple', 'black'],
borderWidth: 1,
borderColor: 'black',
hoverBorderWidth: 3,
hoverBorderColor: 'black',
}]
},
options: {
title: {
display: true,
text: 'Participants vs City',
fontSize: 20
},
scales: {
yAxes: [{
ticks: {
beginAtZero: true
}
}]
}
}
});
}
function GetAllCities() {
var myURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Score')/Items?$top=5000&$select=City";
var AllCities = [];
$.ajax({
url: myURL,
async: false,
method: "GET",
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
if (data.d.results.length > 0) {
AllCities = data.d.results;
for (var i = 0; i < Number(AllCities.length); i++) {
AllUniqueCities.push(AllCities[i].City);
}
AllUniqueCities = removeDuplicates(AllUniqueCities);
}
},
error: function (error) {
alert("Error: " + JSON.stringify(error));
}
});
return AllUniqueCities;
}
function GetCandidatesPerCity(cityArray) {
var searchText;
for (var i = 0; i < cityArray.length; i++) {
searchText = cityArray[i];
var _count = FetchCityCount(searchText);
AllCandidateCount.push(_count);
}
return AllCandidateCount;
}
function FetchCityCount(searchItem) {
var myURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Score')/Items?$top=5000&$select=Title&$filter=City eq '" + searchItem + "'";
var myCandidateCount = 0;
$.ajax({
url: myURL,
method: "GET",
async: false,
headers: { "Accept": "application/json; odata=verbose" },
success: function (myData1) {
if (myData1.d.results.length > 0) {
myCandidateCount = myData1.d.results.length;
}
},
error: function (error) {
alert("Error: " + JSON.stringify(error));
}
});
return myCandidateCount;
}
function removeDuplicates(arr) {
var unique_array = [];
for (var i = 0; i < arr.length; i++) {
if (unique_array.indexOf(arr[i]) == -1) {
unique_array.push(arr[i])
}
}
return unique_array
}
</script>
</head>
<body>
<br><br>
<button onclick="RunChart(); return false;">Generate Chart</button>
<br><br>
<div style="position: relative; height:800px; width:800px;">
<canvas id="myChartContainer" style="border:1px solid"></canvas>
</div>
</body>
</html>
The primary functions are explained below.
RunChart() is the initializing function. First, it calls two other functions.
- GetAllCities(), which returns the unique cities present in the list.
- GetCandidatesPerCity() queries the list for a number of candidates in the list based on the city name passed as a parameter.
Please note that the REST Calls are synchronous (async: false). It means that until it gets the data, the function won’t proceed to the next line/function. This has to be done to ensure that the dashboard has data before it's loaded.
And this is how ChartJS is instantiated in your HTML DOM.
var ctx = document.getElementById('myChartContainer').getContext('2d');
var myChart = new Chart(ctx, {
//... the usual ChartJS configs...
});
The labels and data are provided by the previous methods.
data: {
labels: cities,
datasets: [{
data: candidatepercity,
// other configs
}]
},
The code is done now! Click on the button to get the result.
To get a pie chart, change the type to “pie” and the dashboard will display as a pie chart now.
Fun fact about Pie charts in ChartJS – click on any of the legends, and see the pie chart’s interactive response with the data.
A few other visualizations of the same data are as follows.
Type line
Type doughnut
Type radar
Type polarArea
The code for this dashboard can also be found in my GitHub repo.
Hope you enjoyed the article. Please leave your thoughts/queries in the comments below.