How can I shift the "subitems" in an Excel PivotTable to another column?

Question

I generate an Excel sheet which contains data formatted like so:

IOW, the "Total Packages", "Total Purchases", "Average Price", and "% of Total" values are located in a column of their own (Data) for each overarching (or sidearching) description.

When I PivotTablize this data, it places these values beneath each description:

This makes sense, but those accustomed to the previous appearance want it to be replicated in the PivotTable. How can I shift the Description "subitems" in the PivotTable to their own column?

This is the code I use to generate the PivotTable:

private void PopulatePivotTableSheet()
{
    string NORTHWEST_CORNER_OF_PIVOT_TABLE = "A6";
    AddPrePivotTableDataToPivotTableSheet();
    var dataRange = rawDataWorksheet.Cells[rawDataWorksheet.Dimension.Address];
    dataRange.AutoFitColumns();
    var pivotTable = pivotTableWorksheet.PivotTables.Add(
                        pivotTableWorksheet.Cells[NORTHWEST_CORNER_OF_PIVOT_TABLE], 
                        dataRange, 
                        "PivotTable");
    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;

    // Row field[s]
    var descRowField = pivotTable.Fields["Description"];
    pivotTable.RowFields.Add(descRowField);

    // Column field[s]
    var monthYrColField = pivotTable.Fields["MonthYr"];
    pivotTable.ColumnFields.Add(monthYrColField);

    // Data field[s]
    var totQtyField = pivotTable.Fields["TotalQty"];
    pivotTable.DataFields.Add(totQtyField);

    var totPriceField = pivotTable.Fields["TotalPrice"];
    pivotTable.DataFields.Add(totPriceField);

    // Don't know how to calc these vals here, so have to grab them from the source data sheet
    var avgPriceField = pivotTable.Fields["AvgPrice"];
    pivotTable.DataFields.Add(avgPriceField);

    var prcntgOfTotalField = pivotTable.Fields["PrcntgOfTotal"];
    pivotTable.DataFields.Add(prcntgOfTotalField);
}

So there is one RowField ("MonthYr") with values such as "201509" and "201510", one ColumnField ("Description") and four DataFields, which align themseles under the Description column field. I want to shift those four fields to the right, to their own column, and the Description label to be vertically centered between those four values to their left. [How] is this possible?

Salvador · Accepted Answer · 2016-10-27T23:34:41.240

2

Try changing the layout of your table with

pivotTable.RowAxisLayout xlTabularRow
pivotTable.MergeLabels = True

this is the result:

A little script in C# with Interop.Excel. Included the using ;)

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

var excelApp = new Excel.Application();
Excel.Workbook wb = excelApp.Workbooks.Open(@"e:\42\TestSO.xlsx");
Worksheet ws = wb.Worksheets["SheetName"];
PivotTable pt = ws.PivotTables("DynamicTableName");
pt.RowAxisLayout(XlLayoutRowType.xlTabularRow);
pt.MergeLabels = true;
wb.Save();
wb.Close();
Marshal.ReleaseComObject(ws);

edited Oct 27 '16 at 23:34

answered Oct 27 '16 at 16:59

Salvador

153
4

Due to other things not quite working in EPPlus, I will probably need to implement this in Excel Interop instead; is the code the same for that? – B. Clay Shannon-B. Crow Raven Oct 27 '16 at 21:47
I think it is, have not tried it though: https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivottable.aspx – Salvador Oct 27 '16 at 22:20
I haven't been able to suss out what property it is there, nor is there any "layout" or "tabular" in some legacy code where it does work that way. – B. Clay Shannon-B. Crow Raven Oct 27 '16 at 22:51
1

@B.ClayShannon Updated my answer using Excel Interop. – Salvador Oct 27 '16 at 23:35

score 2 · Answer 2 · answered Oct 27 '16 at 17:16

2

It's all about PivotTable layout / design... here's the manual way - Salvador has the VBA way :)...

answered Oct 27 '16 at 17:16

CRUTER

866
4
12

How can I shift the "subitems" in an Excel PivotTable to another column?

2 Answers2