EPPlus: Values in column of pivot table

Question

When you create a pivot table in Excel and add fields to Rows and Values, you get an additional field [Symbol Sigma] Values in the GUI, that you can drag and drop to Columns.

I am creating a pivot table using EPPlus. How can I add this [Symbol Sigma] Values field to my column fields?

Edit: Here is some code. I dont' see how this is supposed to help, what it was asked for.

private static ExcelWorksheet CreatePivotWorksheet(ExcelPackage excel, ExcelWorksheet dataWorksheet)
{
    string worksheetName = "Pivot";

    ExcelWorksheet pivotWorksheet = excel.Workbook.Worksheets.Add(worksheetName);

    ExcelRangeBase dataRange = dataWorksheet.Cells[dataWorksheet.Dimension.Address];
    ExcelPivotTable pivotTable = pivotWorksheet.PivotTables.Add(pivotWorksheet.Cells[1,1], dataRange, "pivotTable");

    pivotTable.RowGrandTotals = false;

    pivotTable.RowFields.Add(pivotTable.Fields["BaseValue"]);
    pivotTable.RowFields.Add(pivotTable.Fields["Remaining Runtime"])
        .AddNumericGrouping(0, 500000, 30);
    pivotTable.RowFields.Add(pivotTable.Fields["Emittent"]);
    pivotTable.RowFields.Add(pivotTable.Fields["CountIfs"]);
    pivotTable.RowFields.Add(pivotTable.Fields["ISIN"]);

    var stressField = pivotTable.DataFields.Add(pivotTable.Fields["StressScenario - Percent"]);
    stressField.Function = DataFieldFunctions.Average;
    stressField.Format = "0.00%";
    var pessimisticField = pivotTable.DataFields.Add(pivotTable.Fields["PessimisticScenario - Percent"]);
    pessimisticField.Function = DataFieldFunctions.Average;
    pessimisticField.Format = "0.00%";
    var mediumField = pivotTable.DataFields.Add(pivotTable.Fields["MediumScenario - Percent"]);
    mediumField.Function = DataFieldFunctions.Average;
    mediumField.Format = "0.00%";
    var optimisticField = pivotTable.DataFields.Add(pivotTable.Fields["optimisticScenario - Percent"]);
    optimisticField.Function = DataFieldFunctions.Average;
    optimisticField.Format = "0.00%";

    // remove subtotals, this has to be done _after_ adding the field (see https://stackoverflow.com/a/34768357/5909613)
    foreach (ExcelPivotTableField rowField in pivotTable.RowFields)
    {
        rowField.SubTotalFunctions = eSubTotalFunctions.None;
    }
    return pivotWorksheet;
}

That's the symbol for Sum/Total. The symbol doesn't matter anyway, values only appear as aggregates in a pivot table's cells. The GUI simply selects an appropriate default depending on the value type, eg sum for numbers, count for text etc. You'll have to specify this explicitly in your code — Panagiotis Kanavos, Jun 07 '18 at 11:00
@PanagiotisKanavos I added code, but this won't help with my question. — Jerome Reinländer, Jun 07 '18 at 11:06
There's no `Σ` column or field in the GUI. That's just the *values* region in the pivot table. It uses `Σ` because that's the math symbol for sums. The fields you set there are set throu the `DataFields` collection in the API. — Panagiotis Kanavos, Jun 07 '18 at 11:28
The code is essential to answer the question. It shows how you added the Row/Column/PageFields and whether you added anything to the `DataFields`. It looks like you only added *row*fields. — Panagiotis Kanavos, Jun 07 '18 at 11:30
@PanagiotisKanavos I added an image with the desired result. I do indeed add `DataFields`, see `stressField` etc. — Jerome Reinländer, Jun 07 '18 at 11:31
Doesn't change anything. There's still no `Σ` field, that's the Values region and you need to set them through the `DataFields` collection. The Interop/VBA/EPPlus API is the same so you can use almost any tutorial as a guide — Panagiotis Kanavos, Jun 07 '18 at 11:35
@PanagiotisKanavos Yes, there is Values region in the bottom right. But there is also a Values field in the top right region (Columns). Or how would you call that? — Jerome Reinländer, Jun 07 '18 at 11:36
A thing that *you* have to explain. What did you do to put that there? That's *doesn't* appear in the UI of simple pivot tables. — Panagiotis Kanavos, Jun 07 '18 at 11:37
@PanagiotisKanavos In Excel (I'm using Excel2016) create a pivot table, add 1 table column to the Rows section, and 2 (numeric?) table columns in the Values section. Then this new field will appear in the Columns section. It does also appear in my pivot tables created using `EPPlus` when I open them in Excel, but in the Rows section. — Jerome Reinländer, Jun 07 '18 at 11:46
That's not a regular field. Check [this explanation](http://yoursumbuddy.com/identify-pivot-table-values-field/). In this simple case it doesn't really matter. If you add more column fields though it does - it controls whether the totals will appear inside each column group or vice versa. Which means the important question is how to change its order. — Panagiotis Kanavos, Jun 07 '18 at 12:37

score 3 · Answer 1 · answered Jul 13 '18 at 09:13

3

Try to set pivotTable.DataOnRows = false to get such pivot table behaviour

answered Jul 13 '18 at 09:13

byxworm

51
2

score 1 · Accepted Answer · answered May 30 '19 at 04:21

1

try to change Values Row filed to column. Here 3 is Values filed index.

  PivotField row = (PivotField)oPivotTable.RowFields[3];
  row.Orientation = XlPivotFieldOrientation.xlColumnField;

answered May 30 '19 at 04:21

stay_hungry

1,448
1
14
21

1

I can not justify marking this as accepted as I cannot verify it, because I don't have the code anymore. If someone else will comment that this works, I will accept it. Thanks for taking your time to answer old questions. – Jerome Reinländer May 30 '19 at 11:49
@JeromeReinländer Actually I faced the same issue and fixed the issue yesterday using above code. That is why I posted the code here.. Cheers! – stay_hungry May 31 '19 at 04:14

score 0 · Answer 3 · answered Jan 15 '20 at 15:19

I have found solution for your case. You should add a couple strings in your code for datafields. Example for datafield "stressField":

    stressField.Field.Outline = true;
    stressField.Field.ShowInFieldList=true;

As well as for other:

    pessimisticField.Field.Outline = true;
    pessimisticField.Field.ShowInFieldList=true;

    mediumField.Field.Outline = true;
    mediumField.Field.ShowInFieldList=true;

    optimisticField.Field.Outline = true;
    optimisticField.Field.ShowInFieldList=true;

EPPlus: Values in column of pivot table

3 Answers3