Create a chart in SharePoint and Office 365 from SharePoint list using Jquery

Since SharePoint discontinued it’s chart part from SharePoint 2013, it’s been a difficult task to create charts in SharePoint. You can still write a server side program using the class Microsoft.Office.Server.WebControls.ChartWebPart and include it in your web part. However if you don’t have server side access then it’s a dead end, not always.

In this article we will create a chart from a SharePoint list using Jquery and display inside a SharePoint.

Process

  1. Create a SharePoint list and add data into it.
  2. Create an HTML file in a Secured SharePoint document library (it should be at least  read only for everyone)which will have the code for the application.
  3. Use rest api to get the data from the list .
  4. Use chart.js or j query to create chart.
  5. Save the html file.
  6. Create a SharePoint web part page and add a content editor web part.
  7. Edit the content editor tool part.
  8. In the link to content of the content editor web part provide the link to the HTML.
  9. file and save the page.

Create a list

I created a list named Employee Summary.

Added few fields Status,Department,Organization, Title.

I added bunch of records in the list. I have Status values equal to one of these Onboard,Vacant, Detail. And Department as IT10,IT12,IT13,IT14 and other field values . The image following shows my list.

chart_01
Employee Summary List

Now we have our list lets do the rest.

Add Html File

Open your site in SharePoint designer go to the library where you want to store the html file.

From the menu click add a file and pick html.

chart_02
Create a HTML file

Once you have the HTML file edit it in advanced mode. If you right click on the file itself in the SharePoint designer then you can see the option.

Edit in Advanced Mode
Edit in Advanced Mode

Once you have the html file open in the code mode let’s focus on what we will be adding.

Use Rest Api to get data

We will use Jquery and rest api for the whole process. Make sure you add the necessary Jquery source file at the top. Following is the example of the rest api function we will use in this process.

$.ajax({
url:”https://mysite.com/_api/Web/lists/GetByTitle(‘Staff%20Summary’)/items?top=2000&$orderby=Department”,
type: “GET”,
headers: {
“Accept”: “application/json;odata=verbose”
},
success: function(data)
{//retrieve the data here and pass to the chart}

});

Here we are retrieving the top 2000 items from the List and sorting  it by Department. If you don’t mention the item limit then it will display only 100. If you have more than 100 than change your item limit number in the top value to the number that you are aware of.

Once we retrieve the json data from the rest service we will write our logic inside the block in next section.

Filter the data and Add it to Chart

The chart that we are trying to create is the number of employees for each department whose status is onBoard. 

we use chart.js for creating a chart in this article.

You can visit http://www.chartjs.org for more on the chart.js, they have lot of great examples that will get you start on how to use the library. To use chart.js first we need to add the source to chart.js.
https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.3/Chart.min.js

you can download these files or add a live link.

Now from the rest api section let’s revisit our success function block and add our logic.

[code language=”Javascript”]
success: function(data)
{
var counterIT10=0;
var counteraIT12=0;
var counterIT13=0;
var counterIT14=0;
$.each(data.d.results, function(index, item)
{
if(item.Department==’IT-10′ && item.Status==’OnBoard’)
{             counterIT10++; }
if(item.Department==’IT-12′ && item.Status==’OnBoard’)
{            counterIT12++;      }
if(item.Department==’IT-13′ && item.Status==’OnBoard’)
{            counterIT13++;   }
if(item.Department==’IT-14′ && item.Status==’OnBoard’)
{            counterIT14++;   }
});

}
//Chart Code
var data = {
labels: [“IT10”, “IT02”, “IT03”, “IT04”, “TotalOnboard”],
datasets: [{
label: “Total Onboard”,
borderWidth: 2,
hoverBackgroundColor: “rgba(255,99,132,0.4)”,
hoverBorderColor: “rgba(255,99,132,1)”,
data: [counterIT10,counterIT12,counterIT13,counterIT14,counterIT10+counterIT12+counterIT13+counterIT14],
backgroundColor:”rgba(255, 99, 132, 0.2)”,
borderColor:”rgba(255,99,132,1)”,
}
]};
var option = {responsive: true,
scales: {
xAxes: [{stacked: false, display: true,
gridLines: {display:false},
ticks: { min: 0,fontSize:20,fontWeight:900,barThickness : 173 }
}],
yAxes: [{stacked: false,
gridLines: {display:false} }],
}
var ctx = document.getElementById(“chart”).getContext(“2d”);
var myBarChart = Chart.Bar(ctx,
{
type: ‘bar’,   data: data,   options:option  });
}
//this is where the success function of rest api ends

[/code]

Following summarizes the steps completed in above java script code

  • Retrieve all list items and order by Department.
  • Filter each item with department and status.
  • Create a counter variable for each  condition.
  • Update the counter for all the number of items that meet that criteria.
  • Provide those values to the chart data to display.
  • These values will dynamically updated as we add, delete or edit data from the list.

Now, Save your html page and copy link to the page to use it later. Full code has been added at the bottom of this article Now go back to your SharePoint portal add a web part page. Edit the page add a content editor web part. Edit the content editor web part and in the content link section  add the link to html file.

addhtmlfile_link.png
Add html file link to content editor webpart

Save your page. Once everything is completed you should see a chart like below

chart_display_04
Chart displayed in SharePoint Online using Jquery

As seen above it is a very simple chart and if you hover over each bar you will the value for each bar. We can do various customization as desired and combine multiple charts as well. The source code below will have a little improved chart. Here we are adding data at the top of each bar which displays the actual value of the bar in numbers.

Source Code

CSS Used

[code language=”css”]
#h3_staffsum{
color:red;
padding-left: 10%;
padding-bottom: 10px;
font-size: 20px;
font-weight: bold;
}
.chart-container {
position: relative;
}
#dashbrd_content{
background-color:aliceblue;
width: 45%;
padding: 10px;
}
[/code]

Full JavaScript

Add following URL source in the file
https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.3/Chart.min.js
https://code.jquery.com/ui/1.11.1/themes/smoothness/jquery-ui.css
https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js
https://code.jquery.com/ui/1.11.1/jquery-ui.min.js

[code language=”javascript”]
$(document).ready(function () {
$.ajax({
url: “/_api/Web/lists/GetByTitle(‘Employee Summary’)/items?$top=2000&$orderby=Department”,
type: “GET”,
headers: { “Accept”: “application/json;odata=verbose” },
success: function(data){
var counterIT10=0;
var counterIT12=0;
var counterIT13=0;
var counterIT14=0;
$.each(data.d.results, function(index, item) {
if(item.Department==’IT-10′ && item.Status==’OnBoard’)
{ counterIT10++; }
if(item.Department==’IT-12′ && item.Status==’OnBoard’)
{ counterIT12++;}
if(item.Department==’IT-13′ && item.Status==’OnBoard’)
{ counterIT13++; }
if(item.Department==’IT-14′ && item.Status==’OnBoard’)
{ counterIT14++;} });
var data = {
labels: [“IT10”, “IT12”, “IT13”, “IT14”, “TOTAL”],
datasets: [{
label: “AOB”,
borderWidth: 2,
hoverBackgroundColor: “rgba(255,99,132,0.4)”,
hoverBorderColor: “rgba(255,99,132,1)”,
data: [counterIT10,counterIT12,counterIT13,counterIT14,counterIT10+counterIT12+counterIT13+counterIT14],
backgroundColor:”rgba(255, 99, 132, 0.2)”,
borderColor:”rgba(255,99,132,1)” } ]};
var option = {
responsive: true,
scales: {
xAxes: [{stacked: false, display: true,gridLines: {display:false}, ticks: { min: 0,fontSize:20,fontWeight:900,barThickness : 173 } }],
yAxes: [{stacked: false,gridLines: {display:false} }], }, tooltips: {enabled: false },
animation: { onProgress: function () {
var ctx = this.chart.ctx;
ctx.font = Chart.helpers.fontString(Chart.defaults.global.defaultFontFamily, ‘normal’, Chart.defaults.global.defaultFontFamily);
ctx.textAlign = ‘center’;
ctx.textBaseline = ‘bottom’;
this.data.datasets.forEach(function (dataset){
ctx.fillStyle = “red”;
for (var i = 0; i < dataset.data.length; i++) {
for(var key in dataset._meta) {
var model = dataset._meta[key].data[i]._model;
ctx.fillText(“AOB-“+dataset.data[i], model.x, model.y -15);
}
}
});
}
}
}
var ctx = document.getElementById(“chart”).getContext(“2d”);
var myBarChart = Chart.Bar(ctx,{type: ‘bar’,data: data, options:option  });
}
});
});

[/code]

HTML Code

[code language=”html”]

ACTUAL ONBOARD – TODATE

//Add canvas here

[/code]

Point to Note: There is an canvas id not sure why word-press keep on striping this  make sure you add that <canvas id=”chart”></canvas>
Make sure you look at the chart options data we added an animation to display data above the bar
So the above source code if used properly should give you a chart like below

chart_display_withdata
Bar chart from SharePoint list with data value

So here we are able to add a chart in SharePoint online comfortably .

Advertisements

One thought on “Create a chart in SharePoint and Office 365 from SharePoint list using Jquery

Leave a Reply

Your email address will not be published. Required fields are marked *