Using a Pivot Table with non numerical data

[GABRIELE COSSU]

Good morning, we have a lot of data we would like to present in a pivot table-fashion, to make it easy to filter and aggregate as needed. We have 2 main problems:

1. Most of our data is not numerical, relating to physical objects and buildings, much of the information we need to show is textual in nature
2. We have a very limited amount of columns/rows we can work with (3 or at most 4) and we would often need to filter both columns and rows on the same indexes
   

As a practical example

{
  "pset": "Item",
  "attribute": "GUID",
  "value": "2f5ac800-8b13-492c-ad79-098157139982",
  "ids": [
    46
  ]
}

this is an single row of the data we have. The "ids" field is just used for some internal processing. We only have the other 3 variables, and we need to filter them independently on the rows and columns. We want to show all 3 variables on both axes. The reason for this is that we may want to filter all objects that have one specific "value" given a "pset" and "attribute", and see what those objects have as "value" in a totally different "pset" and "attribute". Putting the first in the rows and the second in the columns, would give us an intersection that contains a list of all values that are present there.

Since none of this data can be assumed to be numerical, aggregation does not work. It also would require to be drilled down even further, upon user interaction.

A Pivot Table does not seem the solution for our problem, but at the same time it provides a lot of features that are very close to our needs. Some of which would have to be manually implemented again in a Grid component. Filtering on the same key on both axis does not seem to be possible, and easily aggregate and analyze the aggregated data. Ideally, the aggregated cell could expand to show all the possible values found at the intersection.

Our question is: is there a way to make this work in a Pivot Table component? Would another Syncfusion component be better? Would we need to develop a custom one? If so, how should we approach it? We can have a few thousands rows in the dataSource, so it would need to be able to handle large quantities efficiently.

Thank you for your time, I'll be happy to give further information if needed.

[Yashvanth Ganesan]

Hi GABRIELE COSSU,

   We are validating your requirements at our end and we will update further details in two business days (Feb 21, 2024).

We appreciate your patience until then.

Regards,

Yashvanth G G

[AngelinFaithSheeba PaulvannanRajadurai]

Hi Gabrielle,

Please find the response below.

Query	Comments
Most of our data is not numerical, relating to physical objects and buildings, much of the information we need to show is textual in nature	We would like to inform you that, the aggregation type for the value fields which had non-number type such as string, date, datetime, boolean, etc., will be considered as "Count" by default. Thus, if you bind the string fields in the value axis, the values are displayed with “Count” aggregation. This is the default behavior of the pivot table. However, you can able to display its unique string data values using aggregateCellInfo event. Please refer the below code example.   Code example: aggregateCellInfo(args: any) {         // Triggers for every value cell and you can get the value cells by its field name.         if (args.fieldName == "Products" && args.cellSets[0] != null) {             // Assign its unique string data values here.             args.value = args.cellSets[0][args.fieldName];         } }   Output screenshot:   Meanwhile, we have prepared a sample for your reference.   Sample https://stackblitz.com/edit/angular-xynwqt-kkvnzh?file=src%2Fapp.component.html,src%2Fapp.component.ts   Also, please refer the below documentation to know more about the “aggregateCellInfo” event.   Document: https://ej2.syncfusion.com/angular/documentation/pivotview/aggregation#aggregatecellinfo
We have a very limited amount of columns/rows we can work with (3 or at most 4) and we would often need to filter both columns and rows on the same indexes. We only have the other 3 variables, and we need to filter them independently on the rows and columns. We want to show all 3 variables on both axes. The reason for this is that we may want to filter all objects that have one specific "value" given a "pset" and "attribute", and see what those objects have as "value" in a totally different "pset" and "attribute". Putting the first in the rows and the second in the columns, would give us an intersection that contains a list of all values that are present there.	Yes, we have member (i.e., headers) filtering and label filtering option to filter the row and column fields based on field members and member labels. Also, we have value filtering that helps to perform filter operation based only on value fields and its resultant aggregated values over other fields defined in row and column axes. Please refer to the below document to know more about the “filtering” feature in pivot table.   Document: https://ej2.syncfusion.com/react/documentation/pivotview/filtering
As a practical example {   "pset": "Item",   "attribute": "GUID",   "value": "2f5ac800-8b13-492c-ad79-098157139982",   "ids": [     46   ] } this is an single row of the data we have. The "ids" field is just used for some internal processing.	We regret to let you know that currently, we don’t have direct support to bind the complex data to the pivot table. However, if you want to bind the complex data to the pivot table, you need to convert the complex object into flat objects. Then using the flat data as a data source, you can render the Pivot Table component and perform additional actions. Please refer the below document for more details.   Document: https://ej2.syncfusion.com/react/documentation/pivotview/how-to/bind-complex-data-to-the-pivot-table
Since none of this data can be assumed to be numerical, aggregation does not work. It also would require to be drilled down even further, upon user interaction.	Binding the numeric value or string value in the pivot table won’t affect the user interactions such as drill operations (expand/collapse), filtering and so on.
Filtering on the same key on both axis does not seem to be possible, and easily aggregate and analyze the aggregated data. Ideally, the aggregated cell could expand to show all the possible values found at the intersection.	We would like to let you know that the binding the same field in row and column axis is not suggestable in pivot table. Because if we bind the same field in both axes, the UI interactions will not work properly and if we apply any options like drill up/down, filtering, sorting to that field, it will affect the field that binds in both axes. Thus, binding the same field repeatedly is not advisable based on our current architecture.
Our question is: is there a way to make this work in a Pivot Table component? Would another Syncfusion component be better? Would we need to develop a custom one? If so, how should we approach it? We can have a few thousands rows in the dataSource, so it would need to be able to handle large quantities efficiently.	We recommend using the virtualization option to avoid performance problems while binding large amount of data to the pivot table. For more information about the virtualization feature, please refer to the UG document below.   Virtualization: https://ej2.syncfusion.com/angular/documentation/pivotview/virtual-scrolling Online Demo: https://ej2.syncfusion.com/angular/demos/#/material3/pivot-table/performance   In addition, if your data source contains more combinational sets, please enable the allowDataCompression option, which allows the data to be compressed based on the uniqueness of the raw data, and unique records will be provided as input for the Pivot Table. That compressed data will be always used for further operations, reducing the looping complexity and improving the performance of the pivot table. Please refer to the documentation below to know more about the “dataCompression” option.   Data compression: https://ej2.syncfusion.com/angular/documentation/pivotview/virtual-scrolling#data-compression   On the other hand, we also have paging feature, which will only load the current page data. As a result, even if the pivot table has a huge width and height, the number of rows and columns will be rendered and displayed for the current view port area only, improving pivot table performance. Please refer to the documentation below to know more about the paging. And we have shared the online demo and local sample for your reference. Please find it from the below attachment.   Paging: https://ej2.syncfusion.com/angular/documentation/pivotview/paging Online Demo: https://ej2.syncfusion.com/angular/demos/#/material3/pivot-table/paging

Please let us know if you have any concerns.

Regards,

Angelin Faith Sheeba.