Filter on a pivotField

[cpboland]

I have a pivot table that declares a pivotField like below.
Is it possible to iterate through the values in the pivot field and if it equals a certain value deselect it from the filter set.
Same as how you would set the field to Multi Select and select / deselct values in a standard pivot table.

PivotCache cache = wb.PivotCaches.Add(dataRange);
//PivotReportFilter filter = new PivotReportFilter("BYPASSED", true);
PivotTable ptArea = sheetPivotTable.PivotTables.Add("AreaPivotTable", sheetMain.Range["A1"], cache);
var r = ptArea.PivotFields["AREA"];
r.Axis = AxisTypes.Row;
ptArea.Options.RowHeaderCaption = "AREA";

[Annika.Zhou]

Hello,

Thank you for your inquiry.
Based on your description, please refer to the sample code provided below to meet your requirements:

Code: Select all

...
  List<string> lists = new List<string>();
  foreach (CellRange ranges in dataRange)
  {
      if (!ranges.DisplayedText.Equals("a certain value"))
      {
          lists.Add(ranges.DisplayedText);
      }
  }
  PivotReportFilter filter = new PivotReportFilter("BYPASSED", ptArea);
  // whether the value of the filter is multi-selected
  filter.IsMultipleSelect = true;
  // Set the selected value for this filter
  filter.FilterItemStrings = lists;
  // Adding Filters
  ptArea.ReportFilters.Add(filter);
...

If you need any further assistance or have any additional questions, please feel free to reach out.

Sincerely,
Annika
E-iceblue support team

[cpboland]

Filter.png (30.08 KiB) Viewed 1596 times

Thanks for reply, this method add a Filter to the Pivot Table. I was looking to apply filtering to the values in a Specific PivotField. In the attached image filter on the Area

[Annika.Zhou]

Hello,

Thank you for your feedback.
I apologize for the misunderstanding of your requirements earlier. Based on the screenshot you provided, it appears that you need to filter column fields. Please use the code snippet below to add column fields. Kindly note that this functionality does not involve filtering.

Code: Select all

var r1 = ptArea.PivotFields["AREA"];
r1.Axis = AxisTypes.Column;
var r2 = ptArea.PivotFields["KILL"];
r2.Axis = AxisTypes.Column;

var r3 = ptArea.PivotFields["EDC"];
r3.Axis = AxisTypes.Column;

If you have any further questions or need clarification on the implementation, please do not hesitate to let us know. We are here to assist you further.

Sincerely,
Annika
E-iceblue support team

[cpboland]

Thanks

What does the ShowBlankRow do below. I want to remove the blank in the Status in the attached pivot chart, that is bound to the pivot table.

var r2 = ptPats.PivotFields["STATUS"];
r2.Axis = AxisTypes.Column;
r2.ShowBlankRow = false;

[Annika.Zhou]

Hello,

Thank you for your feedback.
If you wish to remove blank rows in a pivot table, please refer to the code snippet provided below:

Code: Select all

...
int blankHi = 0;

for (int i = 0; i < dataRange.Rows.Length;i++)
{
   
    if (dataRange.Rows[i].IsBlank)
    {
        blankHi = i;
    }
}
PivotCache cache = wb.PivotCaches.Add(dataRange);
...
ptArea.Options.RowHeaderCaption = "AREA";
ptArea.CalculateData();   
//Delete item, must be after ptArea.CalculateData(), otherwise there is no item in XlsPivotField
(r1 as XlsPivotField).HideItem(blankHi-1, true);
...

Please let us know if you need any further assistance or if you have any other questions.

Sincerely,
Annika
E-iceblue support team