facebooktwittermenuarrow-up

GemBox Support Forum

Adding filters to Pivot Table

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:

pivot-table-with-column-filter

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