Using custom datasource in SPJS Charts v7

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":""}
 ]
}

Specify the type as function, supply the custom function name in the functionName parameter as a string. Ensure this function is 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":""
 }).done(function(data){
 deferred.resolve(data);
 }).fail(function(err){
 // console.log(err);
 });
 return deferred.promise();
}

Parameters

listId (string)

The GUID of the list you are pulling the data from.

Get the GUID by going to a list view, hit F12 > Console and type in this followed by the Enter key:
_spPageContextInfo.pageListId

listBaseUrl (string)

The base url of the site where the list is located.

Get the baseUrl by going to a list view, hit F12 > Console and type in this followed by the Enter key:
_spPageContextInfo.webServerRelativeUrl

select (array of strings)

Add the internal names of the fields you want to pull from the list here. If you want to pull the value from a people picker or a lookup column you must specify the field name like this:

YourFieldName/Title

Then you must also include YourFieldName in the expand array.

expand (array of strings)

If you want to pull the value from a people picker or a lookup column you must include the field name here.

filter (string)

REST filter to filter the dataset – for example like this:

Department eq 'IT'

You can use 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 by specifying “type”: “manual” and supplying the “data” like show here:

{
 "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"}
 ]
}

Example: Pull data from two lists

This setup show how you can combine data from two lists when drawing a chart.

Add this code to a file, upload it to a document library (for example to your SPJS document library) and copy the link and add this to your Load external JS or CSS tab in the chart.

function getChartCustomDatasource(chartId) {
    var deferred = jQuery.Deferred(), deferreds = [];
    if (spjs.charts.customData === undefined) {
        spjs.charts.customData = {};
    }
    spjs.charts.customData[chartId] = [];
    deferreds.push(getDataList1(chartId));
    deferreds.push(getDataList2(chartId));
    jQuery.when.apply(jQuery, deferreds).then(function () {
        deferred.resolve(spjs.charts.customData[chartId]);
    });
    return deferred.promise();
}

// Get data from list 1
function getDataList1(chartId) {
    var d = jQuery.Deferred();
    spjs.charts.getItemsREST({
        "listId": "{fb613ceb-e9ee-44f0-a636-dfcf42a7f184}",
        "listBaseUrl": "/SPJSCharts",
        "select": [
            "Id",
            "Title",
            "FullName",
            "Initials",
            "DirectSupervisor/Title",
            "LastName",
            "JobTitle",
            "EmployeeLevel",
            "Department",
            "JoiningDate",
            "EmployingEntity"
        ],
        "expand": ["DirectSupervisor"],
        "filter": ""
    }).done(function (data) {
        jQuery.each(data, function (i, item) {
            // A people picker is returned as an object and need to be changed to a string.
            item.DirectSupervisor = item.DirectSupervisor.Title;
            spjs.charts.customData[chartId].push(item);
        });
        d.resolve();
    }).fail(function (err) {
        // console.log(err);
    });
    return d.promise();
}

// Get data from list 2
function getDataList2(chartId) {
    var d = jQuery.Deferred();
    spjs.charts.getItemsREST({
        "listId": "{f3a56262-4f16-4186-ad0a-10c3408ec31e}",
        "listBaseUrl": "/SPJSCharts",
        "select": [
            "Id",
            "Title",
            "FullName",
            "Initials",
            "DirectSupervisor/Title",
            "LastName",
            "JobTitle",
            "EmployeeLevel",
            "Department",
            "JoiningDate",
            "EmployingEntity"
        ],
        "expand": ["DirectSupervisor"],
        "filter": ""
    }).done(function (data) {
        jQuery.each(data, function (i, item) {
            // A people picker is returned as an object and need to be changed to a string.
            item.DirectSupervisor = item.DirectSupervisor.Title;
            // If the fields are not the same in list 1 and 2 you must map the value to the correct field name like this:
            // item.field_name_in_list_1 = item.field_name_in_list_2;
            spjs.charts.customData[chartId].push(item);
        });
        d.resolve();
    }).fail(function (err) {
        // console.log(err);
    });
    return d.promise();
}

Then add this to your Create chart from custom datasource:

{
    "type": "function",
    "functionName": "getChartCustomDatasource",
    "columns": [
        { "fin": "DirectSupervisor", "label": "Direct Supervisor", "type": "Text", "dataType": "string", "role": "" },
        { "fin": "Id", "label": "Count of employees", "type": "Text", "dataType": "number", "role": "" }
    ]
}

This example uses the DirectSupervisor field as first column, and the Group by first column is checked and Aggregation function for all columns is set to Count to count the number of “not empty records” in the second column (Id) selected in the Columns array above.

Read the comments in the code and change the listId, listBaseUrl, select, expand and filter.