Dear Sir/Madam,
I'm working with PivotTables. On question to which I have not yet found an answer is how to filter the report.
Assume you have a source data range spanning several months. And, for a good reason, you do not want to navigate in the source data to find the subset you want to pivot...
The created pivot table has the month field as the (only) row-type field (PivotFieldType.Row). How do I achieve that only certain items (rows) are shown, - compare to the filter function when manipulating a pivot table in Excel?
BR
/Niklas
Hi Niklas,
Thanks for your inquiry.
We are not very clear your requirement. We appreciate if you could create a sample pivot table in MS Excel and also point out how do you perform filtering the items for a field in a pivot table report. You need to attach the sample template file here. We will check it soon.
Thank you.
Enclosed find a sample;
The worksheet "unfiltered" is exactly that, all months are visible
The sheet "filtered" have only the months in Q4 visible. Though, the pivottable have exactly the same source data as the "unfiltered". Narrowing down to the months of Q4 is accomplished by the filter function (drop down in cell A4 of "filtered").
Hope this make it clearer.
/Niklas
Hi Niklas,
Well, I think you can use PivotField.HideItem method to hide/filter your desired items.
I have written a sample code using your template file to accomplish the task. I have created the same pivot table as created in “Filtered” sheet. You may change / amend the code accordingly, see the sample code below:
Sample code:
//Instantiating an Workbook object
Workbook workbook = new Workbook();
workbook.Open(“f:\test\filtered+pivot.xlsx”);
//Obtaining the reference of the first worksheet
Worksheet sheet = workbook.Worksheets[“Sheet1”];
Cells cells = sheet.Cells;
//Adding a new sheet
Worksheet psheet = workbook.Worksheets[workbook.Worksheets.Add()];
//Naming the sheet
psheet.Name = “PivotTable”;
//Getting the pivottables collection in the sheet
PivotTables pivotTables = psheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Sheet1!A1:C37", “A3”, “PivotTable1”);
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Draging the first field to the row area.
int rowFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Draging the second field to the column area.
int colFieldIndex = pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
//Draging the third field to the data area.
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
PivotField cf = pivotTable.ColumnFields[colFieldIndex];
cf.IsAutoSort = true;
PivotField pf = pivotTable.RowFields[rowFieldIndex];
string [] items = pf.Items;
for (int i = 0; i < pf.ItemCount; i++)
{
//Hide all items but for fourth quarter.
if ((items[i].Substring(0, 2) == “10”) | (items[i].Substring(0, 2) == “11”) | (items[i].Substring(0, 2) == “12”))
{ }
else
{
pf.HideItem(i, true);
}
}
//Saving the Excel file
workbook.Save(“f:\test\out_PivotTable.xlsx”);
Thank you.