Collapse Pivot Table in Aspose cells SSRS

Aspose.Cells Product Family
1

Hello,

I see that Aspose supports collapsing a Pivot table to a particular level.

How would I use this functionality in the SSRS version? thanks,

Charles

2

@Charlie.R

Thanks for using Aspose APIs.

Please share your RDL files, exported Excel files and screenshots highlighting your issues. Please also let us know the Microsoft SQL Server you are using e.g. 2012/2014/2016 etc.

Are you using Adventure Works database to render your RDL files? Please share the steps you take to create your RDL file. We will look into it and help you asap.

3

@shakeel.faiz I don’t have any particular example I could share with as I am testing on production data but it is just a normal pivot table.

are you saying that normally Aspose would return the data collapsed? Base on other messages it seemed that the default behavior was to have everything expanded.

In the other tickets you add codes to run the spreadsheet but I don’t see how I could insert the same in my spreadsheet.

I am running SSRS 2014

thanks,

Charlie

4

@Charlie.R

Thanks for using Aspose APIs.

Collapsing and Expanding Pivot Fields looks same in MS-Excel and Aspose.Cells for Reporting Services. Will you please try the steps as shown in the following screenshot.

Screenshot:

5

dear @shakeel.faiz

I have tried this already and unfortunately it seems that Aspose does not save the state on publish. Are you saying that the above worked for you when you loaded the report and sub field were all collapsed?

piv1.png (9.7 KB)

piv2.png (44.7 KB)

6

@Charlie.R

I have tested this issue both on .NET and Reporting Services but it seems, this feature is not available in Reporting Services.

Please remember PivotTable.ShowDrill property in .NET is not for expanding or collapsing. It just hides or displays expand/collapse buttons.

Please use

public void PivotField.HideDetail(bool isHiddenDetail)

method in .NET for this purpose. Here is a Sample Code in C# (given below). It is of no use to you because you are using Reporting Services, so we have logged your issue as

  • CELLSRS-528 - Collapse or Expand Pivot Table Fields in Aspose.Cells for Reporting Services

Once, we will have some update for you, we will let you know asap.

C#

Workbook wb = new Workbook("sample.xlsx");

Worksheet ws = wb.Worksheets[0];

PivotTable pt = ws.PivotTables[0];
pt.RowFields[0].HideDetail(true);

pt.RefreshDataOnOpeningFile = true;

wb.Save("output.xlsx");
7

@shakeel.faiz thank you for your response.

Just logging a comment to let you know I am still very interested in having ASpose remember the collapsed state of the Pivot Table.

thanks

8

@Charlie.R

We are afraid, there is no update for you regarding this feature. However, we have logged your comment in our database for consideration and implementation. Once, we will have some news for you, we will let you know asap.

9

@Charlie.R,

Please try our latest version/fix: Aspose.Cells for RS (Latest Version)

We added new feature for pivot table collapse row and column. Now we only support collapse row entire field and collapse column entire field options. You may modify report from add pivot table tab page for setting collapse property. The feature needs several conditions:

  1. The data source of pivot table is name.
  2. Data range by the name referred is equal to data range of the table report item.
  3. Set the pivot table attributes of the table report item.

Let us know your feedback.
picturemessage_ubny3sso.llz.png (4.2 KB)

10

The issues you have found earlier (filed as CELLSRS-528) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi