NReco build Pivot Table chart

Question

I am using NReco to build PivotTables and I wanted to display a chart that corresponds to the data shown in the table. I'm trying to accomplish this with c3 charts(really like the look), but another kind of chart is welcomed.

I'm sending the NReco Pivot Table's JSON through ajax and following the examples on http://c3js.org/samples/data_rowed.html, and building my JSON in the following code:

        var pivotTable = new PivotTable(lines, columns, pivotData);

        var htmlResult = new StringWriter();
        var jsonResult = new StringWriter();
        var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
        var chartJsonWr = new PivotTableJsonWriter(jsonResult);
        pvtHtmlWr.Write(pivotTable);
        chartJsonWr.Write(pivotTable);
        
        return new   {Pvt = htmlResult.ToString(), Chart = jsonResult.ToString()};

Resulting in the following JSON

"Chart":"{\"Columns\":[\"Year\"],\"Rows\":[\"Spot\"],\"ColumnKeys\":[[2004],[2005]],\"RowKeys\":[[\"A\"],[\"B\"],[\"C\"]],\"Values\":[[1483.0,1396.0],[1237.0,1700.0],[18276.0,17643.0]],\"GrandTotal\":41735.0,\"ColumnTotals\":[20996.0,20739.0],\"RowTotals\":[2879.0,2937.0,35919.0],\"MeasureLabels\":[\"Sum"],\"PivotDataVersion\":\"TRIAL http://www.nrecosite.com/pivot_data_library_net.aspx\"}"

In my ajax call I parse the Json and try to build the chart

    success: function (response) {
            $("#pvtTable").html(response.Pvt);
          
            var crt = JSON.parse(response.Chart);
            var rows = crt.RowKeys;
            var vals = crt.Values;
            
            var chart = c3.generate({
                bindto:"#pvtChart",
                data: {
                    rows: [rows, vals]
                       
                }
            });

But the chart only displays the row's labels

I tried rebuilding the arrays but with no success. So my questions are: What am I doing wrong? Can I accomplish this with NReco Json and c3 or other charts?

score 0 · Accepted Answer · answered Jul 20 '17 at 18:49

Take a look to the widget from PivotData examples package that renders pivot chart with ChartistJS library by JSON structure returned by PivotTableJsonWriter: http://pivottable.nrecosite.com/Scripts/jquery.nrecopivotchart.js

It is used in the following way:

<div id='pivotChart'></div>
<script type="text/javascript">
$('#pivotChart').nrecoPivotChart( { 
  chartType : "bar",
  pivotData : JSON.parse( jsonProducedByPivotTableJsonWriter )
});
</script>

You are not obligated to use only ChartistJS of course but you can explore "nrecoPivotChart" plugin code to get understanding how to prepare data for your favorite charting library. Speaking about C3.js, it expects an input like:

var chart = c3.generate({
    bindto: '#chart',
    data: {
      columns: [
        ['data1', 30, 200, 100, 400, 150, 250],
        ['data2', 50, 20, 10, 40, 15, 25]
      ]
    }
});

To display your 2D pivot table data correctly you may also need to specify custom axis labels too.

score 0 · Answer 2 · edited Sep 28 '17 at 13:51

I tried with your solution with the same results, how you can see the chart result:

var pivotData = {
        "Columns": [],
        "Rows": ["country_code"],
        "ColumnKeys": [],
        "RowKeys": [["AR"], ["AT"], ["AU"], ["BE"], ["BG"], ["BR"], ["CA"], ["CH"], ["CY"], ["CZ"], ["DE"], ["DK"], ["EE"], ["ES"], ["FI"], ["FR"], ["GB"], ["GR"], ["HK"], ["HR"], ["HU"], ["IC"], ["ID"], ["IE"], ["IN"], ["IS"], ["IT"], ["JP"], ["KR"], ["LT"], ["LU"], ["LV"], ["MC"], ["MT"], ["MX"], ["NL"], ["NO"], ["PL"], ["PT"], ["RO"], ["RS"], ["RU"], ["SE"], ["SI"], ["SK"], ["TW"], ["UA"], ["US"]],
        "Values": [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []],
        "GrandTotal": [1099930.98],
        "ColumnTotals": [],
        "RowTotals": [[212.0], [8037.5], [1199.80], [5885.6], [559.79], [269.1], [2027.49], [10291.71], [488.2], [60119.58], [67519.10], [1340.0], [180.0], [28126.66], [2473.98], [30158.81], [30894.91], [3580.03], [290.0], [7354.7], [83371.2], [48.50], [325.8], [854.34], [173.50], [319.0], [161664.01], [323.0], [506.0], [786.8], [544.3], [1618.66], [2882.0], [98.90], [2745.0], [12125.1], [52.0], [3151.6], [2894.69], [7968.99], [119.99], [538542.9], [1126.05], [599.18], [2223.32], [70.5], [400.0], [13386.69]],
        "MeasureLabels": ["Sum of total_product_paid"]
    };
$('#_1506339485927').nrecoPivotChart( {
            pivotData: pivotData,
            chartType: "bar"})

NReco build Pivot Table chart

2 Answers2