How can I create a PivotTable using EPPlus to filter on the rows in one column and also a set of columns?

Question

To create a PivotTable, source data needs to be provided from which to generate the PivotTable.

I have generated a "dummy" sheet for this purpose which contains raw data such as:

I need to generate a Pivot Table from that data so that it is generated like so:

IOW, it needs to have a filter on the "Description" column that allows the rows to be filtered (only show "Peppers" or whatever) AND a filter on which month columns to display (the sheet can have up to 13 month columns (Sep 15, Oct 15, etc.)) so that the user can choose 1..13 of those "month year" columns to display (technically, they could choose 0, but what would be the point)?

How can this be done? I've tried the following:

private void AddPivotTable()
{
    var dataRange 
rawDataWorksheet.Cells[rawDataWorksheet.Dimension.Address];
    dataRange.AutoFitColumns();
    var pivotTable 
usagePivotWorksheet.PivotTables.Add(usagePivotWorksheet.Cells["A6"]
dataRange, "ProdUsagePivot");
    pivotTable.MultipleFieldFilters = true;
    pivotTable.GridDropZones = false;
    pivotTable.Outline = false;
    pivotTable.OutlineData = false;
    pivotTable.ShowError = true;
    pivotTable.ErrorCaption = "[error]";
    pivotTable.ShowHeaders = true;
    pivotTable.UseAutoFormatting = true;
    pivotTable.ApplyWidthHeightFormats = true;
    pivotTable.ShowDrill = true;

    var descPageField = pivotTable.Fields["Description"];
    pivotTable.PageFields.Add(descPageField);
    descPageField.Sort
OfficeOpenXml.Table.PivotTable.eSortType.Ascending;

    var descRowField = pivotTable.Fields["Description"];
    pivotTable.RowFields.Add(descRowField);

    . . . add others later
}

...but only get the filter for "Description" with no data beneath it:

What do I need to do yet to get the desired appearance/functionality?

EEM · Accepted Answer · 2016-10-14T20:52:27.013

Try this:

Create the entire PivotTable as usual and after adding all the required fields and calculations, move the DataPivotFields as RowFields.

In VBA will be something like this:

With PivotTable
    .DataPivotField.Orientation = xlRowField
    .Position = 2
End With

Also need to apply this: PivotTable.MergeLabels = True to have the Description cell merged for all the corresponding totals.

PD. Replace PivotTable with the PivotTable object in your code

How can I create a PivotTable using EPPlus to filter on the rows in one column and also a set of columns?

1 Answers1