jqxpivotgrid getCellValue

[hhm8152Participant]

Hi,
I’m using jqwidget’s react-pivotgrid.

I’m trying to render data (data type = string) in the pivotgrid but jqxpivotgrid didn’t support string as a value in the pivot table.
If I put the string as a value, the default of pivot function is count. So I used customAggregationFunctions to render the value of string type in the table but “Nan” is returned.
Is there any way to get cell’s value (if cell’s value has string type)?

Here is the link where I put the images to help your understanding
https://docs.google.com/document/d/1d_LXydOk3AgAlQyhrrvnat_3OGYEofvMaIVJsdcxmic/edit?usp=sharing

I thought it should be related with _getCellValueFromDataSource function in jqxall.js.
Is there any way to put more arguments in _getCellValueFromDataSource function to get string value?

getCellValue: function(e, d) {
            var f = this._getCellValueFromDataSource(e, d);
            if (isNaN(f)) {
                return ""
            }
            var b = this.pivotValuesOnRows ? e.boundField : d.boundField;
            var c = this._formatValue(f, b.formatSettings, b.formatFunction);
            return {
                value: f,
                formattedValue: c
            }
        },
        getCellFormatSettings: function(d, c) {
            var b = this.pivotValuesOnRows ? d.boundField : c.boundField;
            if (b) {
                return b.formatSettings
            }
            return undefined
        },
        _getCellValueFromDataSource: function(p, c) {
            if (p == undefined) {
                throw "Invalid rowItem"
            }
            if (c == undefined) {
                throw "Invalid columnItem"
            }
            var b = p.isTotal || c.isTotal;
            var g = this._internalDrillThroughPivotCell(p.key, c.key, b);
            if (g.length == 0) {
                return ""
            }
            var h = this.pivotValuesOnRows ? p.boundField : c.boundField;
            if (null == h) {
                return undefined
            }
            var j = h["function"];
            var n = j || "count";
            if (typeof (n) == "String") {
                n = n.toLowerCase()
            }
            try {
                var d = [];
                for (var k = 0; k < g.length; k++) {
                    var f = g[k];
                    var l = this.dataAdapter.records[f];
                    var o = this._getBoundFieldValue(l, h.dataField);
                    d.push(parseFloat(o))
                }
                if (typeof (n) == "string") {
                    n = this.aggregationFunctions[n]
                }
                if (typeof (n) == "function") {
                    return n(d)
                }
            } catch (m) {
                return NaN
            }
            return NaN
        },

Here is the code what I did

// this needs to be loaded before JqxPivotGrid
require('jqwidgets-framework/jqwidgets/jqx-all');

// use required to make sure this is loaded after jqwidgets
const JqxPivotGrid = require('jqwidgets-framework/jqwidgets-react/react_jqxpivotgrid.js').default;

// import JqxPivotGrid from 'jqwidgets-framework/jqwidgets-react/react_jqxpivotgrid.js';
import React from 'react';
import ReactDOM from 'react-dom';

class App extends React.Component {
	render() {
	// prepare sample data
	let data = new Array();
	let countries =
	[
		"Germany", "France", "United States", "Italy", "Spain"
	];
	let dataPoints =
	[
		"2.25", "1.5", "3.0", "3.3", "4.5"
		];
		let date = [
			"2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00"
		];
		let cityName = ["paris", "Seoul", "SF", "Tokyo", "Busan"];

	for (var i = 0; i < countries.length * 2; i++) {
		var row = {};
		var value = parseFloat(dataPoints[Math.round((Math.random() * 100)) % dataPoints.length]);
		row["country"] = countries[i % countries.length];
		row["value"] = value;
		row["date"] = date[i % countries.length];
		row["cityName"] = cityName[i % countries.length];
		data[i] = row;
	}
	// create a data source and data adapter
	let source =
	{
		localdata: data,
		datatype: "array",
		datafields:
		[
				{ name: 'country', type: 'string' },
				{ name: 'value', type: 'number' },
				{ name: 'date', type: 'string' },
				{ name: 'cityName', type: 'string'}
		]
	};
	let dataAdapter = new jqx.dataAdapter(source);
	dataAdapter.dataBind();
	// create a pivot data source from the dataAdapter
	let pivotDataSource = new jqx.pivot(
		dataAdapter,
		{
			pivotValuesOnRows: false,
			customAggregationFunctions: {
				'printStringValueForDate': function (values) {
					console.log("this is console.log for the date");
					console.log(values);
					return values;
				},
				'printStringValueForCityName': function (values) {
					console.log("this is console.log for the cityName");
					console.log(values);
					return values;
				}
			},
			rows: [{ dataField: 'country', width: 190 }],
			columns: [],
			values: [
				{ dataField: 'date', 'function': 'printStringValue', width: 216, text: 'date', formatSettings: { align: 'left', prefix: '', decimalPlaces: 2 } },
				{ dataField: 'cityName', 'function': 'printStringValue', width: 216,  text: 'cityName', formatSettings: { align: 'left', prefix: '', decimalPlaces: 2 } }
			]
		});

			return (
		<JqxPivotGrid
			style={{ width: 800, height: 400 }}
			source = {pivotDataSource}
			treeStyleRows = {true}
			autoResize = {true}
			multipleSelectionEnabled = {true}
					/>
			);
	}
}
ReactDOM.render(<App />, document.getElementById('app'));

[HristoParticipant]

Hello hhm8152,

Please avoid posting code from our sources this violating the EULA.
You could use the cellsRenderer callback for specific pivotCell where you want to set text. You could use this demo as a base.
But I think more suitable for your purpose will be the jqxGrid.
I would like to suggest you use the jqxGrid, the example that you share looks that do not need from such complex features.
Please, take a look at this demo:
https://www.jqwidgets.com/react/react-grid/react-grid-customroweditor.htm

Best Regards,
Hristo Hristov

jQWidgets team
http://www.jqwidgets.com

[hhm8152Participant]

Hi, Thanks for replying.
By the way, I have to use jqxpivotgrid not jqxgrid, so jqxgrid is not proper option for me.

Can you check https://docs.google.com/document/d/1d_LXydOk3AgAlQyhrrvnat_3OGYEofvMaIVJsdcxmic/edit?usp=sharing

I tried cellrender based on your suggestion but pivotcell.value is always generated by values’s function.

If I put string type data in the value field and the function undefined, pivotcell.value is always number (the number of stirngs in the array).
If I I put string type data in the value field and the customFunction(to get string values), pivotcell.value is always undefined.

customAggregationFunctions: {
	'printStringValueForDate': function (values) {
	  // console.log("this is console.log for the date");
	  return values;
	}
}

let customCellsRenderer = function (pivotCell) {
			console.log(pivotCell);
			return pivotCell.value;
};'

values: [
{ dataField: ‘date’, ‘function’: ‘printStringValueForDate’, width: 216, text: ‘date’, formatSettings: { align: ‘left’, prefix: ”, decimalPlaces: 2 } },
{ dataField: ‘cityName’, ‘function’: ‘printStringValueForCityName’, width: 216, text: ‘cityName’, formatSettings: { align: ‘left’, prefix: ”, decimalPlaces: 2 } }
]`

<strong>Whole Code</strong>

import JqxPivotGrid from 'jqwidgets-framework/jqwidgets-react/react_jqxpivotgrid.js';
import React from 'react';
import ReactDOM from 'react-dom';

class App extends React.Component {
	render() {
	// prepare sample data
	let data = new Array();
	let countries =
	[
		"Germany", "France", "United States", "Italy", "Spain"
	];
	let dataPoints =
	[
		"2.25", "1.5", "3.0", "3.3", "4.5"
		];
		let date = [
			"2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00","2016-09-25 00:00:00"
		];
		let cityName = ["paris", "Seoul", "SF", "Tokyo", "Busan"];

	for (var i = 0; i < countries.length * 2; i++) {
		var row = {};
		var value = parseFloat(dataPoints[Math.round((Math.random() * 100)) % dataPoints.length]);
		row["country"] = countries[i % countries.length];
		row["value"] = value;
		row["date"] = date[i % countries.length];
		row["cityName"] = cityName[i % countries.length];
		data[i] = row;
	}
	// create a data source and data adapter
	let source =
	{
		localdata: data,
		datatype: "array",
		datafields:
		[
				{ name: 'country', type: 'string' },
				{ name: 'value', type: 'number' },
				{ name: 'date', type: 'string' },
				{ name: 'cityName', type: 'string'}
		]
	};
	let dataAdapter = new jqx.dataAdapter(source);
	dataAdapter.dataBind();
	// create a pivot data source from the dataAdapter
	let pivotDataSource = new jqx.pivot(
		dataAdapter,
		{
			pivotValuesOnRows: false,
			customAggregationFunctions: {
				'printStringValueForDate': function (values) {
					console.log("this is console.log for the date");
					// console.log(values);
					return values;
				},
				'printStringValueForCityName': function (values) {
					console.log("this is console.log for the cityName");
					// console.log(values);
					return values;
				}
			},
			rows: [{ dataField: 'country', width: 190 }],
			columns: [],
			values: [
				{ dataField: 'date', 'function': 'printStringValueForDate', width: 216, text: 'date', formatSettings: { align: 'left', prefix: '', decimalPlaces: 2 } },
				{ dataField: 'cityName', 'function': 'printStringValueForCityName', width: 216,  text: 'cityName', formatSettings: { align: 'left', prefix: '', decimalPlaces: 2 } }
			]
		});

		let customCellsRenderer = function (pivotCell) {
			console.log(pivotCell);
			return pivotCell.value;
		};

			return (
		<JqxPivotGrid
			style={{ width: 800, height: 400 }}
			source = {pivotDataSource}
			treeStyleRows = {true}
			autoResize={true}
			cellsRenderer={customCellsRenderer}
			multipleSelectionEnabled = {true}
		/>
			);
	}
}
ReactDOM.render(<App />, document.getElementById('app'));

[hhm8152Participant]

Based on my understanding, pivotCell.value is brought from the return value of value field’s function.

For example,
If I put string type data in the value field and the function undefined, pivotcell.value is always number (the number of stirngs in the array).

rows: [{ dataField: 'country', width: 190 }],
columns: [],
values: [
	{ dataField: 'date', width: 216, text: 'date' },
	{ dataField: 'cityName' }
]

let customCellsRenderer = function (pivotCell) {
	console.log(pivotCell);
	// console result; 
        // {value: 2, formattedValue: "2.00", isSelected: false, pivotRow: a.jqx.j…d.pivotItem, pivotColumn: a.jqx.j…d.pivotItem}
	return pivotCell.value;
};

rows: [{ dataField: 'country', width: 190 }],
columns: [],
values: [
	{ dataField: 'date', 'function': 'printStringValueForDate'},
	{ dataField: 'cityName', 'function': 'printStringValueForCityName' }
]

If I I put string type data in the value field and the customFunction(to get string values), pivotcell.value is always undefined.

customAggregationFunctions: {
	'printStringValueForDate': function (values) {
		console.log("this is console.log for the date");
		// console.log(values);
		return values;
	},
	'printStringValueForCityName': function (values) {
		console.log("this is console.log for the cityName");
		// console.log(values);
		return values;
	}
},

let customCellsRenderer = function (pivotCell) {
	console.log(pivotCell);
	// console result; 
        // {value: undefined, formattedValue: undefined, isSelected: false, pivotRow: a.jqx.j…d.pivotItem, pivotColumn: a.jqx.j…d.pivotItem}
	return pivotCell.value;
};

So my question is how get I render string’s list in the value on the pivot grid.
Thanks.

[HristoParticipant]

Hello hhm8152,

Please, take a look at this example:

render() {
            // prepare sample data
            let countryData = [
                { country: 'France', date: '"2016-09-25 00:00:00","2016-09-26 00:00:00","2016-09-27 00:00:00"', cityname: 'Paris', ID: 1000 },
                { country: 'South KOREA', date: '"2016-09-25 00:00:00"', cityname: 'Seoul', ID: 1001 },
                { country: 'Italy', date: '"2016-09-25 00:00:00"', cityname: 'Rome', ID: 1010 },
                { country: 'Spain', date: '"2016-09-25 00:00:00"', cityname: 'Barcelona', ID: 1100 }
            ];

            // create a data source and data adapter
            let source =
            {
                localdata: countryData,
                datatype: "array",
                datafields:
                [
                    { name: 'country', type: 'string' },
                    { name: 'date', type: 'string' },
                    { name: 'cityname', type: 'string' },
                    { name: 'value', type: 'number', map: 'ID' }
                ]
            };

            let dataAdapter = new $.jqx.dataAdapter(source);
            dataAdapter.dataBind();

            let getDateByID = function (id)
            {
                for (let i = 0; i < countryData.length; i++) {
                    if (countryData[i].ID == id) {
                        return countryData[i].date;
                    }
                }
            };
            let getCityByID = function (id)
            {
                for (let i = 0; i < countryData.length; i++) {
                    if (countryData[i].ID == id) {
                        return countryData[i].cityname;
                    }
                }
            };

            // create a pivot data source from the dataAdapter
            let pivotDataSource = new $.jqx.pivot(
                dataAdapter,
                {
                    pivotValuesOnRows: false,
                    rows: [{ dataField: 'country', width: 190 }],
                    columns: [],
                    values: [
                        { dataField: 'value', width: 200, text: 'date', 'function': 'max' },
                        { dataField: 'value', width: 200, text: 'cityName', 'function': 'max' }
                    ]
                });

            
            let customCellsRenderer = function (pivotCell)
            {
                let id = pivotCell.value;
                let city = getCityByID(id);
                let date = getDateByID(id);

                let cellText = '';
                if (pivotCell.pivotColumn.text == 'date') {
                    cellText = date;
                }

                if (pivotCell.pivotColumn.text == 'cityName') {
                    cellText = city;
                }

                return "<div style='width: calc(100%-8px); height: 100%; padding: 4px; margin: 0px;'>" + cellText + "</div>";
            };

            return (
			    <JqxPivotGrid 
				    style={{ width: 800, height: 400 }}
				    source = {pivotDataSource}
				    treeStyleRows = {true}
				    autoResize = {false}
				    multipleSelectionEnabled = {true}
				    cellsRenderer = {customCellsRenderer}
                />
            );
        

It is based on this demo:
https://www.jqwidgets.com/react/react-pivotgrid/react-pivotgrid-cell-values-alignment.htm

Best Regards,
Hristo Hristov

jQWidgets team
http://www.jqwidgets.com

[Author]

Posts