PivotTable.ShowValuesRow has no effect after moving DataField to columns

Hi,


I’m trying to create a PivotTable with multiple PivotFieldType.Data fields. After adding multiple fields, the “data” field gets moved to RowFields. As described here, the workaround is to move PivotTable.DataField to ColumnFields:

pivotTable.AddFieldToArea( PivotFieldType.Column, pivotTable.DataField );

Unfortunately, this appears to cause a couple of problems for me:

  1. pivotTable.ShowValuesRow = false now has no effect. The resulting pivot table has the associated option checked after opening the workbook.

  2. pivotTable.GetCellByDisplayName now returns null, even if it previously returned results ( and the cell is still clearly present in the table ).

Any ideas how to hide the values row and be able to lookup cells and show the data columns as columns instead of rows?

Hi Chris,


Thank you for contacting Aspose support.

It would be of great help in understanding your presented scenario if you can share an executable standalone sample application along with its dependencies, input/output spreadsheets and the desired results (you may create manually in Excel application).

Looking forward to the above requested to assist in this regard.

Thanks,


While reducing the problem to an example program, I found that the 2nd issue ( the problem with GetCellByDisplayName ) was a confusion resulting from moving things around. Please ignore that portion of the problem statement. The ShowValuesRow property still seems to have no effect, however.

I’ve created a small program that generates a pivot table that exhibits the problem with ShowValuesRow not working properly. I’ve attached the program and resulting workbooks:
  • asposecells_showvaluesrow_problem.cs.txt – the program that generates the problematic pivot table ( your uploader doesn’t allow .cs extensions )

  • asposecells_showvaluesrow_noeffect.xlsx – the result of running the program. The pivot table in the pivot sheet shows a “Data” row above the data columns and the “Show the Values row” option on the pivot table remains checked.

  • asposecells_showvaluesrow_manualoptionchange.xlsx – the result of manually changing the “Show the Values row” row on the pivot table.

Hi Chris,


Thank you for sharing the samples.

I have executed your code against the latest version of Aspose.Cells for .NET 8.6.1.2 and I’m able to notice the said issue. In order to further investigate the matter, a ticket with Id CELLSNET-44044 has been logged in our database. Our product team will further look into the details of this problem and we will keep you updated with analysis results.

Thanks Babar!


At the moment, this is the last blocking issue I know of that’s preventing me from presenting Aspose.Cells as a viable solution to my team. I’d love to check out any fixes you’ve got as soon as they’re available!

Also, I figured out the problem with the interaction between GetCellByDisplayName and ShowValuesRow, it’s a little tricky:


Here’s an example of the problematic code:

/* 1 / pivotTable.AddFieldToArea( PivotFieldType.Column, pivotTable.DataField );
/ 2 / pivotTable.ShowValuesRow = false;
/ 3 / pivotTable.RefreshData();
/ 4 / pivotTable.CalculateData();

/
5 */ var cell = pivotTable.GetCellByDisplayName( “Advertiser” );

Even when the cell named “Advertiser” exists, at the end of this code ‘cell’ will be null. However, if you either:
a) remove line (1) or
b) move line (2) after line (4),
then ‘cell’ will not be null.

There’s some weird interaction around ShowValuesRow with these other fields that seems likely to be related to the general problem of this field not working properly at all.

Hi Chris,


Actually the ticket logged earlier as CELLSNET-44044 is currently pending for analysis and is in the queue with other priority tasks therefore it is too early to comment on the source of the problem. As soon as we have completed the investigation, we will be in a better position to provide problem cause and ETA for the fix (if applicable). That said, we have recorded your observations to the aforementioned ticket, and have requested the concerned member of the product team to include these details in their investigation. We will keep you posted with updates in this regard.
Hi again,

This is to update you that we have fixed the problem logged earlier as CELLSNET-44044. We will shortly provide the fix here after ensuring the quality of the build and incorporating other enhancements.

Hi,


Please try our latest version/ fix: Aspose.Cells for .NET v8.6.1.6

We have fixed your issue “CELLSNET-44044” now.

Let us know your feedback.

Thank you.

The 8.6.1.6 build appears to fix both problems described in this thread. Thanks!

Hi,


Good to know that your issue is sorted out by the new fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.

The issues you have found earlier (filed as CELLSNET-44044) have been fixed in this update.


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