SharePoint Chart View WebPart : Sum the column

Question

I have one list which have data like below :

Month        Hours
-----        -----
Jan,2016      45
Feb,2016      05
Jan,2016      25
Jan,2016      15
Mar,2016      10
Jan,2017      45
Feb,2017      12
Mar,2017      31

Now i want the grouping on column "Month" and then sum the column "Hours" and need to display on chart. So finally i want the below result

Month        Hours
-----        -----
Jan,2016      85
Feb,2016      05
Mar,2016      10
Jan,2017      45
Feb,2017      12
Mar,2017      31

How can i achieve this using the Chart View Web Part? Is there any functionality in the properties of the Chart View Web Part ?

Any help would be appreciated.

Answer 1

You can use Client Side Open Source Chart API to achieve your functionality. You need to fetch data client side and provide data in array or JSON format to chart function and bang your chart is ready

I am providing you and code snippet with Google Charts as Charts API. You can change your list name and Column Names and add this snippet in Content Editor web part and it should work.

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div" style="width: 900px; height: 500px;"></div>
<script>
$(document).ready(function() {
    google.charts.load('current', {
        'packages': ['bar']
    });
    google.charts.setOnLoadCallback(getMonthlyData);
});

function getMonthlyData() {
    var monthArray = [];
    var monthlyData = {};
    var ChartArray = [];
    ChartArray.push(['Month', 'Hours']);
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('ListName')/Items?$orderby=Month",
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function(data) {
            console.log(data.d.results);
            $.each(data.d.results, function(i, val) {
                //for unique Page Unique User Count                 
                if (monthArray.indexOf(val["Month"]) < -1) {
                    monthArray.push(val["Month"]);
                    monthlyData[val["Month"]] = {
                        Month: val["Month"],
                        Hours: parseIntval(["Hours"])
                    };
                } else {
                    monthlyData[val["Month"]]["Hours"] = parseInt(monthlyData[val["Month"]]["Hours"] + val["Hours"]);
                }
            });
            $.each(monthlyData, function(ind, val) {
                ChartArray.push([val["Month"], parseInt(val["Hours"])]);
            });

            drawChart(ChartArray);
        },
        error: function(error) {
            alert(JSON.stringify(error));
        }
    });
}

function drawChart(ChartArray) {
    var data = google.visualization.arrayToDataTable(ChartArray);
    var options = {
        chart: {
            title: 'Month vs Hour',
            subtitle: 'Subtitle',
        },
        bars: 'vertical',
        vAxis: {
            format: 'decimal'
        },
        height: 400
    };
    var chart = new google.charts.Bar(document.getElementById('chart_div'));
    chart.draw(data, options);
};
}
</script>