Issue with Pivot Report Connections selection

Aspose.Cells Product Family
,
1

Hello Team,

We are trying to select report connections using VB.net code but we are not able to find property in aspose.cells.

Please let us know how we can set those check boxes to be selected by default. those are unchecked.

Please find attached image for reference.

PivotReportConnection.PNG (6.1 KB)

Thanks in advance

2

@setsight,
We are analyzing your issue and will share our feedback soon.

3

@setsight,

Thanks for the screenshot.

Could you please provide a sample Excel file (please zip the file prior attaching) and some more screenshots (taken in MS Excel) with step details (on how to set/retrieve those options in MS Excel (2016/2019) manually), we will check it soon.

4

@Amjad_Sahi Thank you for your response.

In the attached excel we have slicers and report connections in pivot sheet and summary sheet.

When we click on slicer it is showing report connections but those are unchecked by default we need to check those using Aspose.cells

Please guide us to achieve this.

Acme_Target_2104061010_WklySalsPIV.zip (2.2 MB)

5

@setsight,

We understand your requirements now. We have logged a ticket with an id “CELLSNET-47964” for your requirements. We will investigate and look into on manipulate Slicer report connections for pivot table and pivot chart feature.

Once we have an update on it, we will let you know.

6

@setsight,
Please try our latest version/fix: Aspose.Cells for .NET v21.4.1 (attached)
Aspose.Cells21.4.1 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.4.1 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.4.1 For .NetStandard20.Zip (5.5 MB)

There are some tips for you:

  1. Adds Slicer.AddPivotConnection(PivotTable pivot) method which adds PivotTable connection

  2. Adds Slicer.RemovePivotConnection(PivotTable pivot) method which removes PivotTable connection

  3. Your source file has some issues, the slicer has been linked to the PivotTable, but it is not shown in the file. Please check the compressed file “ChannelSlicer.zip”.
    ChannelSlicer.zip (47.3 KB)

  4. When we directly add or delete the link between the slicer and the PivotTable in the source file named “Acme_Target_2104061010_WklySalsPIV.xlsx”, we don’t get the correct result. If we use Excel 2016 to open and save the source file, and then add or delete the link between the slicer and the pivot table, we can get the correct result.Acme_Target_2104061010_WklySalsPIV_resaved.zip (2.0 MB)

  5. Please check the compressed file“SampleFile.zip". SampleFile.zip (34.6 KB)

The sample code in .NET:
Workbook wb = new Workbook(filePath + “sample.xlsx”);
PivotTableCollection pivots = wb.Worksheets[“Sheet1”].PivotTables;
SlicerCollection slicers = wb.Worksheets[“Sheet1”].Slicers;
//get the slicer which needs to add pivot connections
Slicer slicer = slicers[0];
int pivotCount = pivots.Count;
for (int i = 0; i < pivotCount; i++)
{
PivotTable tempPivot = pivots[i];
slicer.AddPivotConnection(tempPivot);
}
wb.Save(filePath + “sample_add.xlsx”);

slicer.RemovePivotConnection(pivots[0]);
wb.Save(filePath + “sample_remove.xlsx”);

Your issue should be fixed in it.
Let us know your feedback.