Missing source data name property for PivotTable

Aspose.Cells Product Family
1

Consider the following example file: PivotTableReferences.xlsx.zip (40.1 KB)

This file contains two pivot tables. We would like to read the data source of the pivot tables as shown in Excel. In Excel, after selecting the pivot table we can go to Pivot Table Analyze > Change Data Source and view the source. See the following screenshots:

PivotTable1 Data Source Excel.png (59.1 KB)
PivotTable2 Data Source Excel.png (59.1 KB)

However, in Aspose.Cells only the ExternalConnectionDataSource property is set to an external connection named ThisWorkbookDataModel. We cannot get the source of the pivot table from that connection. The problem is that the DataSource property of these pivot tables is null. See the following screenshots:

PivotTable1 Data Source Aspose.png (42.6 KB)
PivotTable2 Data Source Aspose.png (43.1 KB)

I looked in the underlying XML and found that the missing source is present for these pivot tables. See the following screenshots:

PivotTable1 Data Source XML.png (50.4 KB)
PivotTable2 Data Source XML.png (50.0 KB)

Can you make the sourceDataName as shown in the screenshots available in a property for the pivot tables? Then we are able to find the right connection and obtain the correct data source.

We’re using Aspose.Cells 24.8.0 for .NET.

2

@perfectxl

Thank you for those provided resources to demonstrate the issue and requirement. We will investigate it and give feedback soon.

3

@perfectxl
It is feature of power pivot which we have not supported yet. It is complicated feature and we are afraid we cannot support it soon. Anyways, you will get updated here when we have some news for it.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-56780

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

Thanks for the update. We understand that support of Power Pivot is a complicated feature. But in the current situation we are unable to show the correct source of these types of pivot tables at all.

As a quick fix, it would be very helpful if you can simply read the value of sourceDataName in the XML (if present) and add it as property to the PivotTable object. A suggestion for the property name is SourceDataName.

An alternative quick fix is to replace the value of the ExternalConnectionDataSource field (now set to ThisWorkbookDataModel) to the connection corresponding to the value of sourceDataName in the XML. Then the pivot table is linked to the actual DataModelConnection, allowing to show the original source.

Both solutions do not require full support of the Power Pivot data model, only an extension of the PivotTable object. Let us know if one of the two alternatives sound reasonable, we are happy to think along.

5

@perfectxl,

We appreciate your suggestions to address your requirements (for now). We have recorded them against your issue (Ticket ID: “CELLSNET-56780”) in our database. Please allow us some time to evaluate them thoroughly. We will provide you with new updates after our analysis.

6

@perfectxl
We will add PivotTable.SourceDataConnectionName property to get the name of connection.
And we also will support getting connection of PivotTable.ExternalConnectionDataSource property in the next version 24.10.

 Workbook workbook = new Workbook(Constants.PivotTableSourcePath + "CELLSNET56780.xlsx");
 PivotTable pt = workbook.Worksheets[0].PivotTables[0];
 Assert.AreEqual("WorksheetConnection_Sheet3!$B$2:$C$114", pt.SourceDataConnectionName);
 Assert.IsTrue(pt.ExternalConnectionDataSource != null);

 pt = workbook.Worksheets[1].PivotTables[0];
 Assert.AreEqual("WorksheetConnection_PivotTableReferences.xlsx!Data3", pt.SourceDataConnectionName);
 Assert.IsTrue(pt.ExternalConnectionDataSource != null);
7

@perfectxl
We compared another power pivot table from other user :Inquiry about the Possibility of Support for Pivot Tables with Relationships
We found one pivot table may refers to one or more external connection(s), and sourceDataName is the name of the primary data connection for the PivotTable. So we decided to change api as
public ExternalConnection[] GetSourceDataConnections()
public string[] GetNamesOfSourceDataConnections()
and oboleted pt.ExternalConnectionDataSource property.

public void CELLSNET56780()
 {
     Workbook workbook = new Workbook(Constants.PivotTableSourcePath + "CELLSNET56780.xlsx");
     PivotTable pt = workbook.Worksheets[0].PivotTables[0];
     Assert.AreEqual("WorksheetConnection_Sheet3!$B$2:$C$114", pt.GetNamesOfSourceDataConnections()[0]);
     Assert.IsTrue(pt.GetNamesOfSourceDataConnections()[0] != null);

     pt = workbook.Worksheets[1].PivotTables[0];
     Assert.AreEqual("WorksheetConnection_PivotTableReferences.xlsx!Data3", pt.GetNamesOfSourceDataConnections()[0]);
     Assert.IsTrue(pt.GetNamesOfSourceDataConnections()[0] != null);
 }
8

Thanks for the update. We’re glad that support for this property will be added in the next version.

9

@perfectxl
You are welcome!

10

The issues you have found earlier (filed as CELLSNET-56780) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi