Pivot table with page fields issue

Aspose.Cells Product Family
1

This is the issue I reported earlier, but now I have a standalone sample to reproduce the issue.


The attached script in PivotPageFieldIssue.cs is obtained by recording Aspose.Cells API calls performed by our reporting engine. The source.xlsx file is used as a template to generate a report. It contains a list table (data in the table is updated) and two pivot tables, each has two report fields. The script updates data in the list data and refreshed the worksheet, saved to result.xlsx and result.html.

You can observe that result.xlsx opened in Excel shows completely different values of Report Filters (see expected.xlsx for expected values, I expect them to match source.xlsx). The saved HTML in result.html displays expected Report Filters put the first pivot table is rendered incorrectly, as if Report Filters are Is UST = false, Execution Type=Electronic instead (which is not expected as well).

Thanks
2

Hi Dmitry,


Thank you for the code and samples.

We have evaluated your presented scenarios while using the latest version of Aspose.Cells for .NET 8.3.2.7, and we are able to notice 3 issues as detailed below.

  • CELLSNET-43473: Report filter selection changes for the Pivot Table in worksheet “Electronic” after filling in the data source and refreshing the Pivot Table. Report filter selections are changed from “Is UST = False” & “Execution Type = Voice” to “Is UST = True” & “Execution Type = Electronic”.
  • CELLSNET-43474: Same Pivot Table as discussed above does not refresh while rendering the results in HTML format.
  • CELLSNET-43475: Pivot Table in worksheet “Voice” shown in HTML has different data view as compared to the spreadsheet. However, the report filter selection remains intact, that is; “Execution Type = Voice” & “Is UST = True”.

Please spare us little time to properly analyze these issues to figure out the problem cause. As soon as we have any updates for you, we will post here for your kind reference.

We are sorry for your inconvenience.
3

Btw can I ask the devs to review the script as it reflects the implementation of our reporting engine:

1. All refreshes we do at the end to properly render a generic template, any excess or missing refresh/calculate methods they might notice
2. Performance recommendations. We use ImportTwoDimensionArray in order to push data in a table, and copy ranges to copy cell formatting, and we do it in blocks by columns, so we can skip columns with formulas. We noticed that as the table grows every iteration takes longer almost in linear progression.

4

Hi Dimtry

dzmitry.martynau:
1. All refreshes we do at the end to properly render a generic template, any excess or missing refresh/calculate methods they might notice

You are refreshing the Pivot Tables correctly, however, the Workbook.CalculateFormula has to be called once after importing the data. Calling this method second time will not make any difference unless you have changed the cell data on which formula fields are dependent. Anyway, I have evaluated the performance concerns after commenting out the second Workbook.CalculateFormula call without observing any considerable difference in the execution time for the given data.

dzmitry.martynau:
2. Performance recommendations. We use ImportTwoDimensionArray in order to push data in a table, and copy ranges to copy cell formatting, and we do it in blocks by columns, so we can skip columns with formulas. We noticed that as the table grows every iteration takes longer almost in linear progression.

In your given code, you are calling the Cells.ImportTwoDimensionArray method for 184 times. If you could change your application structure to use Cells.ImportDataTable, the performance would be better for large data sets. You may split the DataTable into pieces so that formula columns are not overwritten but the consecutive data should remain in single DataTable in order to reduce the number of Cells.ImportDataTable calls. I will further test this scenario by converting your source to use DataTables, and will keep you posted with updates in this regard. You may also try the approach on your side for testing purpose and let us know of your feedback.

We have also logged your concerns to one of the tickets for product team's review. As soon as we receive any updates, we will let you know here.

5

Thanks, Babar.


1. I added an extra CalculateFormula after I found a report which used a formula in a table that depended on the order of rows. Not sure if it’s a valid scenario, i.e. using such formulas gives deterministic behavior.
2. I will investigate it more.
6

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for .NET v8.4.0.3 for these issue and let us know your feedback.

About CELLSNET-43473,CELLSNET-43474 and CELLSNET-43475, there are some tips for you:

1. If you change the data source of PivotTable, the PivotItems of PivotField will be changed too.So if you want to use the same filtering conditions, you need set it again.

Because the index of PivotItem will be changed too.

2.If you want to verify it, you can read the “excepted.xlsx” file, you will discover that the CurrentPageItem of PivotField in PivotTable.PageFields is changed.

Please use the codes as follows to print it:

PivotFieldCollection pages = workbook.Worksheets[0].PivotTables[0].PageFields;

Console.WriteLine(pages[0].CurrentPageItem + " " + pages[1].CurrentPageItem);

pages = workbook.Worksheets[1].PivotTables[0].PageFields;

Console.WriteLine(pages[0].CurrentPageItem + " " + pages[1].CurrentPageItem);

3. Changing the codes as follows:

var pivottable0 = worksheet2.PivotTables[0];

pivottable0.RefreshData();

pivottable0.CalculateData();

var worksheet3 = workbook.Worksheets[1];

var pivottable1 = worksheet3.PivotTables[0];

pivottable1.RefreshData();

pivottable1.CalculateData();

To:

var pivottable0 = worksheet2.PivotTables[0];

pivottable0.RefreshData();

int pageCount = pivottable0.PageFields.Count;

PivotField tempField = null;

for (int i = 0; i < pageCount; i++)

{

tempField = pivottable0.PageFields[i];

for (int j = 0; j < tempField.ItemCount; j++)

{

if (tempField.Name.Equals("Execution Type") && (string)tempField.PivotItems[j].Value == "Electronic")

{

tempField.CurrentPageItem = (short)j;

break;

}

if (tempField.Name.Equals("Is UST") && (bool)tempField.PivotItems[j].Value == true)

{

tempField.CurrentPageItem = (short)j;

break;

}

}

}

pivottable0.CalculateData();

var worksheet3 = workbook.Worksheets[1];

var pivottable1 = worksheet3.PivotTables[0];

pivottable1.RefreshData();

pageCount = pivottable1.PageFields.Count;

for (int i = 0; i < pageCount; i++)

{

tempField = pivottable1.PageFields[i];

for (int j = 0; j < tempField.ItemCount; j++)

{

if (tempField.Name.Equals("Execution Type") && (string)tempField.PivotItems[j].Value == "Voice")

{

tempField.CurrentPageItem = (short)j;

break;

}

if (tempField.Name.Equals("Is UST") && (bool)tempField.PivotItems[j].Value == true)

{

tempField.CurrentPageItem = (short)j;

break;

}

}

}

pivottable1.CalculateData();
7

Thanks, Shakeel. This version fixed my issue. I will test it against other similar scenarios.


One more question. Since I want to preserve original page field values, I need to support the case when data don’t contain the page field value I want to preserve. In this case PivotItems doesn’t contain the value I need to restore. What can I do in this case, is there a way to insert a corresponding PivotItem via API?


8
Hi Dimtry,

dzmitry.martynau:
Thanks, Shakeel. This version fixed my issue. I will test it against other similar scenarios.

It is good to know that you are up & running again. Please feel free to test the latest build and let us know if you face any difficulty.

dzmitry.martynau:
One more question. Since I want to preserve original page field values, I need to support the case when data don't contain the page field value I want to preserve. In this case PivotItems doesn't contain the value I need to restore. What can I do in this case, is there a way to insert a corresponding PivotItem via API?

I am afraid, I didn't get the point. Could you please elaborate this scenario with the help of a sample spreadsheet, that you may create manually using Excel application?
9

The issues you have found earlier (filed as CELLSNET-43475;CELLSNET-43474;CELLSNET-43473) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.