Set format and field caption for datasource fields not defined in rows, columns, values or filters

Question

If the datasource for a pivot grid contains fields that are not added to rows, columns, values or filters it is, as far is I know, not possible to set format or field caption, right?

Let's use the example from here: https://help.syncfusion.com/js/pivotgrid/relational-getting-started

var pivotData = [

{ Amount: 100, Country: "Canada", Product: "Bike", Quantity: 1 },

{ Amount: 200, Country: "Germany", Product: "Van", Quantity: 1 },

{ Amount: 300, Country: "Germany", Product: "Car", Quantity: 1 },

....

]

$(function () {

$("#PivotGrid1").ejPivotGrid({

dataSource: {

//Datasource bound to PivotGrid control.

data: pivotData,

//Required fields in row, column, value and filter areas of PivotGrid control.

rows: [{fieldName: "Country", fieldCaption: "Country"}],

columns: [{fieldName: "Product", fieldCaption: "Product"}],

values: [{fieldName: "Amount", fieldCaption: "Amount"}]

}

});

The "Quantity" field is available in the datasource but it is not added as rows, columns, values or filters. In the field-list "Quantity" is still available as selectable value.

If I would add it e.g. to the values array I could easily define format and field caption there, but then it would be automatically selected in the field-list and added to the pivot grid by default.

What I want it to achieve is that "Quantity" is available in the field-list, but I don't want it to be selected from the start. So basically the same behaviour as it is now, But additionally with the option to define format and field caption.

In the WPF PivotGridControl for example I have exactly the behaviour I also need in web. By adding a PivotItem to the PivotFields it is available in the field-list, it is not selected by default and I can define its format, field caption etc. there.

So the question is, am I missing something in javascript or is it currently only available as described above?

Sivamathi Natarajan · Answer

Hi Harald, 
 
Thanks for contacting Syncfusion support. 
 
You can change the fieldCaption and Format for the fields available in the data source using following code example. 
 
Code Example: 
 
Here we have changed the caption and formatting for Quantity Field, which is not added in any axis initially. 
 




$(function () { 
            $("#PivotGrid1").ejPivotGrid({ 
                dataSource: { 
                    //Datasource bound to PivotGrid control. 
                    data: pivotData, 
                    //Required fields in row, column, value and filter areas of PivotGrid control. 
                    rows: [{ fieldName: "Country", fieldCaption: "Country" }], 
                    columns: [{ fieldName: "Product", fieldCaption: "Product" }], 
                    values: [{ fieldName: "Amount" , fieldCaption: "Amount" }] 
                }, 
                renderSuccess: "RenderFieldList", 
            }); 
        }); 
 
        function RenderFieldList(args) { 
            $("#PivotSchemaDesigner1").ejPivotSchemaDesigner({ 
                pivotControl: args, 
                layout: ej.PivotSchemaDesigner.Layouts.Excel 
            }); 
            var fieldMembers = $("#PivotSchemaDesigner1").data("ejPivotSchemaDesigner").model.pivotTableFields; 
            var treeviewList = $("#PivotSchemaDesigner1").data("ejPivotSchemaDesigner")._tableTreeObj._liList; 
            // To change the field caption in field list button. 
            for (var i = 0; i < fieldMembers.length; i++) { 
                if (fieldMembers[i].name == "Quantity") { 
                    fieldMembers[i].caption = "Qty"; 
                    fieldMembers[i].format = "currency"; 
                } 
            } 
            // To change the field caption in field list treeview. 
            for (var i = 0; i < treeviewList.length; i++) { 
                if (treeviewList[i].querySelector('.e-text').innerText == "Quantity") { 
                    treeviewList[i].querySelector('.e-text').innerText = "Qty"; 
                } 
            } 
        } 
 
Meanwhile, we have prepared a sample for your reference. Kindly check the below sample link for your reference. 
 
Sample Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/packagemanger-741235370 
 
Please check the below screenshot. Caption changed for “Quantity” to “Qty” 
 
  
We hope that the above sample meets your requirement if not kindly share us more details along with screen-shots/video if possible. 
 
Regards, Sivamathi.

Harald · Answer

Thank you so much, that is exactly what I need.Works like a charm! :)

Sivamathi Natarajan · Answer

Hi Harald, 
 
Thanks for the response. As always, we will happy to assist you. 
 
Regards, 
Sivamathi.