This project has moved. For the latest updates, please go here.

How do I get count instead of Sum [SOLVED]

Oct 5, 2012 at 12:23 PM
Edited Oct 5, 2012 at 12:46 PM

In the datafield of my pivot table, i've set the subtotalfunction to count. But it doesnt reflect when the excel sheet is generated and it still shows the sum and not the count. Is there any solution to this? :)

Oct 8, 2012 at 5:15 AM

Bump... Anyone? :(

This is the code i'm trying.

 

var wsPivot = package.Workbook.Worksheets.Add("Pivot");

                var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A55"], dataRange, "Table");
                pivotTable.MultipleFieldFilters = true;
                pivotTable.RowGrandTotals = true;
                pivotTable.ColumGrandTotals = true;
                pivotTable.Compact = true;
                pivotTable.CompactData = 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;
                pivotTable.FirstDataCol = 3;
                pivotTable.RowHeaderCaption = "Caption";

                var pageField = pivotTable.Fields["field1"];
                pivotTable.PageFields.Add(pageField);

                var rowField = pivotTable.Fields["cust"];                    
                pivotTable.RowFields.Add(rowField);

                var dataField = pivotTable.Fields["id"];

                dataField.SubTotalFunctions = OfficeOpenXml.Table.PivotTable.eSubTotalFunctions.Count;
                pivotTable.DataFields.Add(dataField);

Oct 8, 2012 at 7:16 AM

I figured it out. This worked for me

 

pivotTable.DataFields.Add(dataField);
pivotTable.DataFields[0].Function = DataFieldFunctions.Count;