Hi,
I have a pivot table in Excel using data from another worksheet as its datasource. So when the worksheet is populated with data the pivot table pivots the data from the worksheet. that works fine. What I'm trying to do now is set the report filter to specific selection so when the pivot table loads it will automatically filter out the data I don't need.
My pivot table shows market value by Asset Type with a report filter for Security Type. Is there a way to set the report filter (Security Type) to show only specific security types when it loads. Since my datasource only has smart markers Excel doesn't know the list of security types until run time.
thanks
Hi NJ,
Thank you for using Aspose products.
It would be of great help in understanding your exact requirements if you can share your sample spreadsheet containing the PivotTable in question and your desired results that you may create using the MS Excel application on the same spreadsheet.
As far as we understood your requirement, you probably wish to select a item from Report Filter dropdown, and based on that selection, you wish to dynamically populate the PivotTable. You can achieve this by switching the PivotItem’s IsHidden property. If set to false, the item will be selected and vice versa. For elaboration purposes, please check the below provided code snippet.
C#
public static void setFilterOptionByString(PivotTable table, string filterName, string value)
{
PivotField field = table.PageFields[filterName];
field.IsMultipleItemSelectionAllowed = true;
PivotItemCollection collection = field.PivotItems;
bool all = false;
if(value.Equals("(All)"))
{
all = true;
}
for (int i = 0; i < collection.Count; i++)
{
PivotItem item = collection[i];
if (all)
{
item.IsHidden = false;
}
else
{
if (item.Name.Equals(value))
{
item.IsHidden = false;
}
else
{
item.IsHidden = true;
}
}
}
table.RefreshData();
table.CalculateData();
table.RefreshDataOnOpeningFile = true;
}
In case you find some difficulty or have ambiguities, please share the spreadsheets as requested at the start of this post.
Hi,
Attached is a spreadsheet with four tabs; two are template and two have data. In the PivotWithData tab I'm excluding security types FWD and ETF. These selections I would like to exclude in the Security Type filter on the PivotTemplate tab. but I cannot since you it only sees the smart marker.
In your example above is the collection the list of fields or the list of data within the field/filter?
Hi NJ,
Thank you for providing the sample spreadsheet.
Please check the below code snippet that we have used to hide/de-select the required fields from the report. Please note, before executing the code we made all the fields visible by selecting “All” from the dropdown.
C#
var book = new Workbook(myDir + “pivotsample.xlsx”);
var sheet = book.Worksheets[“PivotWithData”];
var pivot = sheet.PivotTables[0];
PivotField field = pivot.PageFields[“SecurityType”];
field.IsMultipleItemSelectionAllowed = true;
PivotItemCollection collection = field.PivotItems;
for (int i = 0; i < collection.Count; i++)
{
PivotItem item = collection[i];
if (item.Name.Equals(“ETF”) || item.Name.Equals(“FWD”))
{
item.IsHidden = true;
}
}
pivot.RefreshData();
pivot.CalculateData();
pivot.RefreshDataOnOpeningFile = true;
book.Save(myDir + “output.xlsx”);
Moreover, we have performed the operation on the PivotTable and not on it's data source that remained intact after the operation at "Data!$A$1:$D$18".