SPJS Charts – Custom Datasource

Using custom datasource in SPJS Charts v7

Query for data

In the Basic setup tab you no have two options for Datasource. The SharePoint list option is the same as before, but the new Custom datasource option lets you load the data-source with a custom JavaScript function.

This way you can for example combine two or more queries to build one chart in a page. I’ll describe this functionality below.

When selecting the Custom datasource radio button, you will see a new textarea for the configuration of Datatable fields and the function name to use to query for the data:

This example chart is set up with a custom datasource built with the below configuration.

This is an example of the “Custom datasource” configuration:

{
 "type":"function",
 "functionName":"getChartCustomDatasource",
 "columns":[
 {"fin":"Date","label":"Date","type":"DateTime","dataType":"date","role":""},
 {"fin":"Madagascar","label":"Madagascar","type":"Text","dataType":"number","role":""},
 {"fin":"Bolivia","label":"Bolivia","type":"Text","dataType":"number","role":""},
 {"fin":"Ecuador","label":"Ecuador","type":"Text","dataType":"number","role":""}
 ]
}

The function name getChartCustomDatasource must be available in the page. Either by adding it to a custom js file loaded trough the Load external JS or CSS tab, or by adding it to a script editor web part in the page.

The below example shows a basic query to get the dataset from a SharePoint list:

function getChartCustomDatasource(chartId){
 var deferred = jQuery.Deferred();
 spjs.charts.getItemsREST({
 "listId":"{5ebb17fb-eb6d-4a01-ac86-a241458ff8f2}",
 "listBaseUrl":"/SPJSCharts",
 "select":["Date","Madagascar","Bolivia","Ecuador"],
 "expand":[],
 "filter":"",
 "rowLimit":""
 }).done(function(data){
 deferred.resolve(data);
 }).fail(function(err){
 // console.log(err);
 });
 return deferred.promise();
}

You can however user any sort of query you can run with JavaScript from within SharePoint, as long as the dataset returned from the query is in this format:

[{
 "Bolivia": "165",
 "Ecuador": "938",
 "Madagascar": "522",
 "Created": "2014-03-24T21:41:00Z"
}, {
 "Bolivia": "135",
 "Ecuador": "1120",
 "Madagascar": "599",
 "Created": "2014-03-24T21:41:08Z"
}, {
 "Bolivia": "157",
 "Ecuador": "1167",
 "Madagascar": "587",
 "Created": "2014-03-24T21:41:08Z"
}, {
 "Bolivia": "136",
 "Ecuador": "691",
 "Madagascar": "629",
 "Created": "2014-03-24T21:41:08Z"
}, {
 "Bolivia": "139",
 "Ecuador": "0",
 "Madagascar": "615",
 "Created": "2014-03-24T21:41:08Z"
}, {
 "Bolivia": "139",
 "Ecuador": "1110",
 "Madagascar": "615",
 "Created": "2016-04-02T22:18:55Z"
}, {
 "Bolivia": "345",
 "Ecuador": "456",
 "Madagascar": "429",
 "Created": "2016-04-03T07:11:10Z"
}, {
 "Bolivia": "333",
 "Ecuador": "22",
 "Madagascar": "555",
 "Created": "2016-04-03T14:58:45Z"
}]

Manually supply data

You can also add the data for the chart manually in the textarea like this:

{
 "type":"manual",
 "columns":[
 {"fin":"Status","label":"Status","type":"Text","dataType":"SplitInSeries","role":""},
 {"fin":"Department","label":"Department","type":"Text","dataType":"string","role":""}
 ],
 "data":[
 {"Department":"North","Status":"Not started"},
 {"Department":"North","Status":"In progress"},
 {"Department":"South","Status":"In progress"},
 {"Department":"South","Status":"Completed"}
 ]
}

Not backwards compatible

Because of the changed query method, v7 is not unfortunately not backwards compatible with v6 and you must rebuild the charts in v7.

To make the upgrade process easier, the two versions can both be installed in the same site, but you cannot load both versions in the same page.