Pivot Grid PivotGridControl working with arbitrary data

[Andreas]

Dear all!

We have something like a BI solution: arbitrary queries create a datatable that we display as a result to the user. We want to allow the user to do data analytics on it using the pivot grid.

This is half-working as per my understanding.

1) aggregatable columns

First of all, I thought that when assigning the datatable, all would be fine. The user could add any numeric column to the "Values" box and they would be aggregated as a sum. But that does not work. I have to explicitly create calculated columns like

                    foreach (DataColumn col in dt.Columns)

                    {

                        if (col.DataType == typeof(Decimal) || col.DataType == typeof(double) || col.DataType == typeof(int))

                            pivotGridControlResult.PivotCalculations.Add(new PivotComputationInfo { FieldName = col.ColumnName, SummaryType = SummaryType.Sum, CalculationName = "SUM_" + col.ColumnName, });

                    }

Then as soon as a value is entered into the "rows" box for grouping, all the columns from above are automatically added. Though I would rather like them to add single items.

Though also I do not understand the logic: why do I need the code above at all?

2) calculated columns

When I have 3 columns, e. g. quantity, value and increasement, I need price = quantity * value * increasement. (Though the data is arbitrary, I can identify those columns by name.) Can I add such a column? It would be necessary to get real price information.

3) time-grouping

In the dataset, I have rows containing DateTimes. For analysis, I want users to be able to group the dates based on day, week, month, quarter and year to see the timeframe they choose. How can I achieve this?

Many many thanks!

[Vijayarasan Sivanandham]

Hi Andreas,

Currently, we are analyzing your requirement of “explicitly create calculated columns and custom grouping in PivotGrid” We will validate and update you the details on or before April 12, 2022.

We appreciate your patience until then.

Regards,

Vijayarasan S

[Vijayarasan Sivanandham]

Hi Andreas,

We are still working on this. So, we need two more business days to validate this. We will update with further details on or before April 18, 2022.

We appreciate your patience and understanding.

Regards,

Vijayarasan S

[Andreas]

Thanks a lot, watching this post eagerly and providing many page views. :)

[Vijayarasan Sivanandham]

Hi Andreas,

Please find answer for your queries below

Queries	Solutions
First of all, I thought that when assigning the datatable, all would be fine. The user could add any numeric column to the "Values" box and they would be aggregated as a sum. But that does not work. I have to explicitly create calculated columns like                           foreach (DataColumn col in dt.Columns)                       {                           if (col.DataType == typeof(Decimal) || col.DataType == typeof(double) || col.DataType == typeof(int))                               pivotGridControlResult.PivotCalculations.Add(new PivotComputationInfo { FieldName = col.ColumnName, SummaryType = SummaryType.Sum, CalculationName = "SUM_" + col.ColumnName, });                       }   Then as soon as a value is entered into the "rows" box for grouping, all the columns from above are automatically added. Though I would rather like them to add single items.   Though also I do not understand the logic: why do I need the code above at all?	Pivot grid control is designed to display the bounded data in a tabular format. The PivotComputationInfo holds the information needed for the calculations that appear in a Pivot control. For more information related to, please refer the below user guide documentation link,   UG Link: https://help.syncfusion.com/windowsforms/pivot-grid/pivot-calculations
When I have 3 columns, e. g. quantity, value and increasement, I need price = quantity * value * increasement. (Though the data is arbitrary, I can identify those columns by name.) Can I add such a column? It would be necessary to get real price information.	Your requirement can be achieved by setting the calculation type as “Formula” and by specifying the appropriate formula using the Formula property of PivotComputationInfo object. Please refer the below code snippet,   pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo() {                     FieldName = "Price",                     CalculationType = CalculationType.Formula,                     Formula = "[Count] * [Section] * [Weight]",                     Format = "C" });   UG Link: https://help.syncfusion.com/windowsforms/pivot-grid/pivot-calculations#expression-field-calculations
In the dataset, I have rows containing DateTimes. For analysis, I want users to be able to group the dates based on day, week, month, quarter and year to see the timeframe they choose. How can I achieve this?	We are still working on this. So, we need two more business days to validate this. We will update with further details on or before April 20, 2022.

Please find the sample in the attachment and let us know if you have any concerns in this.

Regards,

Vijayarasan S

Attachment: Sample_dad3edbc.zip

[Vijayarasan Sivanandham]

Hi Andreas,

We deeply regret for the delay.

We are still working on this. We will update further details on April 22, 2022.

We appreciate your patience and understanding.

Regards,

Vijayarasan S

[Andreas]

Many many thanks!

Also no problem about the delay.

I tried using your results from 2nd last post, but failed:

1) I still don't understand. Why do I need to use a PivotCalculations column at all? Also, when I add a PivotCalculations column, I cannot normally use it, like it is missing from the list of columns when I first display the control?

2) Does not work. When grouping, I would expect the value to be

a1*b1*c1+...+a22*b22*c22

but I get

(a1+..+a22)*(b1+..+b22)*(c1+..+c22)

The results are totally off. The formula seems to work on the aggregate rather than on the rows and aggregating their results.

Also, what is the allowed syntax here? Is it https://docs.microsoft.com/en-us/dotnet/api/system.data.datacolumn.expression?view=net-6.0  ?

3) If you get something, great. Though with the restrictions from 1) and 2), I wonder whether if you got something, I could use it.

[Vijayarasan Sivanandham]

Hi Andreas,

Please find answer for your queries below

Queries	Solutions
I still don't understand. Why do I need to use a PivotCalculations column at all? Also, when I add a PivotCalculations column, I cannot normally use it, like it is missing from the list of columns when I first display the control?	PivotCalculations is used to display the summary value in the PivotGrid. UG Link: https://help.syncfusion.com/windowsforms/pivot-grid/pivot-calculations#defining-pivot-calculations-using-pivot-computation-information
Does not work. When grouping, I would expect the value to be   a1*b1*c1+...+a22*b22*c22   but I get   (a1+..+a22)*(b1+..+b22)*(c1+..+c22)   The results are totally off. The formula seems to work on the aggregate rather than on the rows and aggregating their results.	We have prepared the simple sample based on provided code snippet from our end. The reported issue “Custom field value update properly when group the PivotGrid” and unable to replicate the issue from our end. It is working fine as expected. Please find the tested sample from the attachment. Please have a look at this sample and let us know if we have missed any customization done in your application. Otherwise try to reproduce the reported issue in this sample and revert to us with the modified sample and steps to replicate the issue. It will be more helpful for us to find the exact cause for the issue and to provide a prompt solution.
Also, what is the allowed syntax here? Is it	We need to define the FieldName of columns with square brackets and write custom logic based on your scenario. Please refer the below code snippet for some custom logic, pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo() {                     FieldName = "Price",                     CalculationType = CalculationType.Formula,                     Formula = "[Count] + [Section] - [Weight]",                     Format = "C" });                   pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo() {                     FieldName = "Variation",                     CalculationType = CalculationType.Formula,                     Formula = "[Count] / [Section]",                     Format = "C" });                   pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo() {                     FieldName = "Difference",                     CalculationType = CalculationType.Formula,                     Formula = "[Weight] - [Section]",                     Format = "C" });   UG Link: https://help.syncfusion.com/windowsforms/pivot-grid/pivot-calculations#expression-field-calculations
In the dataset, I have rows containing DateTimes. For analysis, I want users to be able to group the dates based on day, week, month, quarter and year to see the timeframe they choose. How can I achieve this?	As we mentioned earlier, we will update with further details on or before April 22, 2022.

Regards,
Vijayarasan S

Attachment: PivotGridDemo_7e1bce55.zip

[Andreas]

Many thanks!

My problems are solved.

Here is what I did:

Your calculated column did not work for me at all. I simply did it in the datatable.

if (dt.Columns.OfType().Count(col => new[] { "Anzahl", "Betrag", }.Any(name => name == col.ColumnName)) >= 2)

{

var colErtrag = new DataColumn("Ertrag", typeof(Decimal));

colErtrag.Expression = "[Anzahl] * [Betrag]";

dt.Columns.Add(colErtrag);

}

Having different views on date - I also did that in the datatable:

if (dt.Columns.OfType().Count(col => new[] { "datum", }.Any(name => name == col.ColumnName)) >= 1)

{

gebOQuery &= true;

dt.Columns.Add(new DataColumn("Woche", typeof(int)));

dt.Columns.Add(new DataColumn("Monat", typeof(int)));

dt.Columns.Add(new DataColumn("Quartal", typeof(int)));

dt.Columns.Add(new DataColumn("Jahr", typeof(int)));

foreach (DataRow row in dt.Rows)

{

var date = (DateTime)row["sdatum"];

row["Woche"] = date.GetWeek();

row["Monat"] = date.Month;

row["Quartal"] = date.Quarter;

row["BehandlungsJahr"] = date.Year;

}

}

Finally, from your sample, I got an important piece of information. There seems to be a bug that a lot of things (like filtering, perhaps others) are not working when the visual style is not explicitly set. I tinkered with several things, but that seemed to give a major improvement:

this.pivotGridControlResult.GridVisualStyles = Syncfusion.Windows.Forms.GridVisualStyles.Office2010Black; // Syncfusion.Windows.Forms.GridVisualStyles.Office2007Black; // Syncfusion.Windows.Forms.GridVisualStyles.Office2016Colorful; // Syncfusion.Windows.Forms.GridVisualStyles.Metro;

I am on version 19.4.0.56 of your components, referenced via Nuget.

It also seems that now explicitly specifying PivotCalculations is optional. I can properly drag them in your PivotTableFieldList.

Perhaps I was also missing refreshes that I also got from your code:

this.pivotGridControlResult.TableControl.Refresh(true);

if (pivotGridControlResult.PivotSchemaDesigner != null)

pivotGridControlResult.PivotSchemaDesigner.RefreshGridSchemaLayout();

Many thanks!

[Vijayarasan Sivanandham]

Hi Andreas,

We are glad to know that the reported problem has been resolved at your end.

However, your requirement to group the dates based on day, week, month, quarter and year in pivot grid can be achieved by using custom grouping.

Currently, PivotGridControl does not have a support for custom grouping. We have analyzed and considered your requirement of “Provide the custom grouping support in PivotGridControl” in PivotGridControl and logged feature request for the same. We will implement this feature in any of our upcoming release.

At the planning stage for every release cycle, we review all open features and identify features for implementation based on specific parameters including product vision, technological feasibility, and customer interest. We will let you know when this feature is implemented. We appreciate your patience until then.

Thank you for requesting this feature and helping us define it. We are always trying to make our products better and feature requests like yours are a key part of our product growth efforts.

Feedback link: https://www.syncfusion.com/feedback/34377/provide-the-custom-grouping-support

If you have any more specification/suggestions to the feature request, you can add it as a comment in the portal and cast your vote to make it count.

Regards,

Vijayarasan S

[Andreas]

I marked your last reply as answer, though answers are really spread over all the posts.

Someone else having same issue will have to read them.

Many thanks again!