Hi,
Is it possible to add filters to the pivot table?
I didn’t find any example on this so asking a question might be the fastest solution for me.
Here is my code:
if (_excelDataSheet.Rows.Count > 1)
{
// cache data
PivotCache cache = returnExcelFile.PivotCaches.AddWorksheetSource("'Data Sheet'!" + kitchenOrdersDataRange);
// new pivot sheet
_excelPivotSheet = returnExcelFile.Worksheets.InsertEmpty(0, "Pivot Sheet");
// create pivot
var pivot = _excelKitchenOrdersPivotSheet.PivotTables.Add(cache, "PivotTable", "A1");
// add data
AddPivotField(pivot, "Quantity", NumberFormatBuilder.Number(2, useThousandsSeparator: true), PivotFieldCalculationType.Sum);
// add rows
pivot.RowFields.Add("Date");
pivot.RowFields.Add("Issue");
pivot.RowFields.Add("Product");
// headers
pivot.RowHeaderCaption = "Product";
pivot.ColumnHeaderCaption = "Values";
// IsForSale contains values ("YES" / "NO")
//pivot.Filter.ByCustom("IsForSale", FilterOperator.Equal, "YES");
pivot.Filter.ByCustom("IsForSale", FilterOperator.Equal, value: "YES");
}
Hi Branko,
Yes, you can add filters to the PivotTable
. For instance, try this:
var isForSaleField = pivot.ColumnFields.Add("IsForSale");
pivot.Filter.ByCustom(isForSaleField, FilterOperator.Equal, "YES");
The result should be something like this:
I hope this helps.
Regards,
Mario
Hi Mario, sry for slow response.
Thank You for suggestion, I tried that solution but it is not what I needed.
I need “real” filter fields like in picture:
Hi Branko,
I see, in that case, try this:
var isForSaleField = pivot.DataFields.Add("IsForSale");
table.PageFields.Add(isForSaleField);
Is this what you were looking for?
Regards,
Mario
Hi Mario,
Thank You very much, that was what I needed to finish project.
Compliments for speed and help !
With graditude,
Branko
Hi,
I have a question that builds upon the previous one: is it possible to programmatically mark selected values for a filter? In the example, I have a pivot table and a filter for transaction types, and I would like to programmatically mark three values as shown in the image. I am using the GemBox version 45.0.35.1148.
Regards,
Matea
Matea_Z,
can you send the file to us so we can investigate and provide the solution? You can open a support ticket and attach the file there.