Adding filters to Pivot Table

GemBox.Spreadsheet
1

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");
}
2

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

3

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:

image
image1559×671 383 KB

4

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

5

Hi Mario,

Thank You very much, that was what I needed to finish project.

Compliments for speed and help !

With graditude,
Branko

6

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

pivot2
pivot2686×583 30.8 KB

7

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.

8

Hi Marko, I have the same issue like Matea_Z, have you been able to help him out? If yes, can you share this information?
In my case I have to programmatically select all entries with “2024”:
image

9

Hi Adi,

I’ll need to investigate your PivotTable to help you out with it.
Can you please send us your Excel file (see our Contact page)?

Regards,
Mario