logo
Documents & Forms
Microsoft 365 & SharePoint Tools
Classic SharePoint Tools

Data Source

The Data Source tab of Charts allows you to configure and process the data source of a chart. Here you can define a data source such as SharePoint list or library or pre-populate data via JavaScript in init handler on the Advanced tab with static data or using an external web service.

Next, you can group the data and calculate additional fields with aggregate functions, e.g. sum, average, count, max, or min on the Aggregation tab. Here you can add your own aggregate fields via JavaScript in aggregationSuccess handler.

Finally, you can click the Process button at the bottom of the page to retrieve and process data. All errors and debug information will be output to the console under the Process button. With this, you will know where an error has occurred or how your data was transformed at each stage of processing.

Also, the console allows you to analyze the processed data. It works like the browser console where you can execute JavaScript expressions. The processed data is stored in a global JavaScript variable called ‘data,’ so you can trace it in the browser console as well. This is the best option to check whether the data was processed properly and to adjust the transformation and repeat processing if something went wrong.

The processed data must have the following format; otherwise the chart builder will not be able to render a chart based on that data:

{
    items: [
        {
            Field1: Value1,
            Field2: Value2,
            Field3: Value3,
            ...
        }
        ...
    ],
    groups: [
        {
            items: [item1, item2, ...],
            aggregateField1: Value1,
            aggregateField2: Value2,
        }
        ...

    ]
}

Items array contains initial data before the aggregation. It has to contain the number of objects with an identical set of fields.

Groups array is an optional field. It contains an array of grouped items and their aggregated values defined on the Aggregation tab or in the JavaScript handler. Groups[i].itemsis a subset of the original Itemsarray belonging to the current group.

For instance, the original data is:

{
    items: [
        {
            orderId: 5,
            customer: 'Nancy Davolio',
            total: 1500
        },
        {
            orderId: 6,
            customer: 'Andrew Fuller',
            total: 5000
        },
        {
            orderId: 7,
            customer: 'Janet Leverling',
            total: 500
        },
        {
            orderId: 8,
            customer: 'Nancy Davolio',
            total: 600
        },
        {
            orderId: 9,
            customer: 'Janet Leverling',
            total: 100
        }
    ]
}

Let’s say, we group data by the customer field and calculate an average order size and a sum of all orders for each customer. The result will be as follows:

{
    items: [
        { orderId: 5, customer: 'Nancy Davolio', total: 1500 },
        { orderId: 6, customer: 'Andrew Fuller', total: 5000 },
        { orderId: 7, customer: 'Janet Leverling', total: 500 },
        { orderId: 8, customer: 'Nancy Davolio', total: 600 },
        { orderId: 9, customer: 'Janet Leverling', total: 100 }
    ],
    groups: [
        {
            field: "customer",
            value: "Andrew Fuller",
            items: [
                { orderId: 6, customer: 'Andrew Fuller', total: 5000 }
            ],
            TotalOrder: 5000,
            AvgOrder: 5000
        },
        {
            field: "customer",
            value: "Janet Leverling",
            items: [
                { orderId: 7, customer: 'Janet Leverling', total: 500 },
                { orderId: 9, customer: 'Janet Leverling', total: 100 }
            ],
            TotalOrder: 600,
            AvgOrder: 300
        },
        {
            field: "customer",
            value: "Nancy Davolio",
            items: [
                { orderId: 5, customer: 'Nancy Davolio', total: 1500 },
                { orderId: 8, customer: 'Nancy Davolio', total: 600 }
            ],
            TotalOrder: 2100,
            AvgOrder: 1050
        }
    ]
}