PivotGrid datasource with master detail data

[farsha]

I have big problem with pivotgrid datasource .

I have master table and three(maybe become more) details. based these tales i provided a json array, detail tables is in json as a array field.

But when set datasource i get error: "Cannot read property 'index' of undefined".

Could you please help me.

[Manikandan Murugesan]

Hi Farsha, 

 

Thanks for contacting Syncfusion Support. 

 

As of now, we have processed fields in the row, column, filter and value axis based on the first record of the raw data source. As per your query, we suspect that you have bind (or add) the field which doesn’t exist in the first record. So, please modify the first record with all fields. You can modify data source in load event. Please refer the following code snippet. 

let dataSourceSettings = { 

    enableSorting: true, 

    columns: [{ name: 'Year' }, { name: 'Quarter' }], 

    valueSortSettings: { headerDelimiter: ' - ' }, 

    values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' }], 

    dataSource: getPivotData(), 

    rows: [{ name: 'Country' }, { name: 'Products' }], 

    formatSettings: [{ name: 'Amount', format: 'C0' }], 

    expandAll: true, 

    filters: [], 

    showHeaderWhenEmpty: false 

}; 

function getPivotData() { 

    let pivotData = [ 

        { 'Sold': 31, 'Amount': 52824, 'Year': 'FY 2015', 'Quarter': 'Q1' }, 

        { 'Sold': 51, 'Amount': 86904, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q2' }, 

        { 'Sold': 90, 'Amount': 153360, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q3' }, 

        { 'Sold': 25, 'Amount': 42600, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2015', 'Quarter': 'Q4' }, 

        { 'Sold': 27, 'Amount': 46008, 'Country': 'France', 'Products': 'Mountain Bikes', 'Year': 'FY 2016', 'Quarter': 'Q1' } 

    ]; 

    return pivotData; 

} 

export class Default extends SampleBase { 

  onload (args) { 

    args.dataSourceSettings.dataSource[0]["Country"] = undefined; 

    args.dataSourceSettings.dataSource[0]["Products"] = undefined; 

  } 

    render() { 

        return (<div className='control-pane'> 

                <style> 

                    {SAMPLE_CSS} 

                </style> 

                <div className='control-section' style={{ overflow: 'auto' }}> 

                    <PivotViewComponent id='PivotView' dataSourceSettings={dataSourceSettings} width={'100%'} height={'300'} gridSettings={{ columnWidth: 140 }} load={this.onload.bind(this)} > 

                    </PivotViewComponent> 

                </div> 

            </div>); 

    } 

} 

 

render(<Default />, document.getElementById('sample')); 

 

 

Sample: https://stackblitz.com/edit/react-tsg6sf?file=index.js 

Please let us know if you have any other queries. 

 

Regards, 

Manikandan. 

[farsha]

Hi Manikandan,

I can't make array in this way. In details tables just "masterId" is common. I have multiple records in each of details record.

If i merge them i have a lot of duplicate record. Although there is problem with merge three or more tables. 

[Manikandan Murugesan]

Hi Farshad, 

 

Thanks for your reply. 

 

You don’t need to merge the entire tables. You just want to make the first record of JSON data source with all fields in all tables. We have prepared sample for your reference. 

 

Sample: https://stackblitz.com/edit/react-tsg6sf-yjfagl?file=index.js 

 

 

If this doesn’t resolve your problem, then please reproduce the issue in the provided sample (or) send your sample that replicating the problem. This would be a very helpful for us to investigate the reported problem at our end and provide the solution at earliest. 

 

Regards, 

Manikandan. 

[farsha]

Hi,

Thanks Manikandan,

In your sample there isn't what i need. your datasources don't have any relation. 

I repeat that i have a datasource contains master fields and two fields that contains json array. master and its fields have primary key and foreign key.

Your data doesn't any key to relation together.

[Sivamathi Natarajan]

Hi Farsha, 

  

We are unable to find the root cause of the problem with the provided information. So, could you please share your sample to us that replicating the reported problem. This would be very helpful to analyze the problem at our end and provide you the solution at earliest. 

  

Regards, 

Sivamathi Natarajan 

[farsha]

I'm sorry to unable to understand you my problem.

var order=[{Id:1,Title:"order1",supply:[{id:1,orderId:1,supplyTitle:"supply1"},{id:2,orderId:1,supplyTitle:"supply2"}],stuff:[{id:1,orderId:1,stuffTitle:"stuff1"},{id:2,orderId:1,stuffTitle:"stuff2"}]},{Id:2,Title:"order2",supply:[{id:3,orderId:2,supplyTitle:"supply3"},{id:4,orderId:2,supplyTitle:"supply4"}],stuff:[{id:3,orderId:2,stuffTitle:"stuff3"},{id:4,orderId:2,stuffTitle:"stuff4"}]},{Id:3,Title:"order3",supply:[{id:5,orderId:3,supplyTitle:"supply5"},{id:6,orderId:3,supplyTitle:"supply6"}],stuff:[{id:5,orderId:3,stuffTitle:"stuff5"},{id:6,orderId:3,stuffTitle:"stuff6"}]}]

This is my dataSource just with more fields for each table.

As you see order is main table. and it has 2 detail tables (supply and stuff). every record's Id in order has foreign key in supply and stuff. In your sample all records in three dataSource is completely separate. 

for example: My expect is when i drag "stuffTitle" to the field list, show to me "supplyTitle" and its value not another records.

How recognize this relation, or how should i change the dataSource with this relations?

[Manikandan Murugesan]

Hi Farshad, 

Thanks for the reply. 

We don’t have the support for JSON data source like this. So, you need to convert your JSON data source like below as per your relations. 

Code Snippet: 

var pivotData = [ 

    { 

      Id: 1, 

      Title: "order1", 

      id: 1, 

      orderId: 1, 

      supplyTitle: "supply1", 

      id: 1, 

      orderId: 1, 

      stuffTitle: "stuff1" 

    }, 

    { 

      Id: 1, 

      Title: "order1", 

      id: 2, 

      orderId: 1, 

      supplyTitle: "supply2", 

      id: 2, 

      orderId: 1, 

      stuffTitle: "stuff2" 

    }, 

    { 

      Id: 2, 

      Title: "order2", 

      id: 3, 

      orderId: 2, 

      supplyTitle: "supply3", 

      id: 3, 

      orderId: 2, 

      stuffTitle: "stuff3" 

    }, 

    { 

      Id: 2, 

      Title: "order2", 

      id: 4, 

      orderId: 2, 

      supplyTitle: "supply4", 

      id: 4, 

      orderId: 2, 

      stuffTitle: "stuff4" 

    }, 

    { 

      Id: 3, 

      Title: "order3", 

      id: 5, 

      orderId: 3, 

      supplyTitle: "supply5", 

      id: 5, 

      orderId: 3, 

      stuffTitle: "stuff5" 

    }, 

    { 

      Id: 3, 

      Title: "order3", 

      id: 6, 

      orderId: 3, 

      supplyTitle: "supply6", 

      id: 6, 

      orderId: 3, 

      stuffTitle: "stuff6" 

    } 

  ] 

 

Meanwhile, we have prepared sample for your reference. 

Sample: https://stackblitz.com/edit/react-vx2uoq?file=index.js 

Please let us know if any concerns. 

Regards, 

Manikandan.