0

I am adding data fields to a PivotTable like so:

int TOTALQTY_COLUMN = 4;
int TOTALPRICE_COLUMN = 5;
. . .
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALQTY_COLUMN);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALPRICE_COLUMN);

Or, providing more context:

Aspose.Cells.Pivot.PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
int colcount = COLUMNS_IN_DATA_SHEET; 
string lastColAsStr = ReportRunnerConstsAndUtils.GetExcelColumnName(colcount);
int rowcount = sourceDataSheet.Cells.Rows.Count;
string sourceDataArg = string.Format("sourceDataSheet!A1:{0}{1}", lastColAsStr, rowcount);
int index = pivotTableSheet.PivotTables.Add(sourceDataArg, "A7", "PivotTableSheet");
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, DESCRIPTION_COLUMN);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, MONTHYR_COLUMN);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALQTY_COLUMN);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALPRICE_COLUMN);

I need another data field that is based on the values supplied to those two data fields (from the source data's TOTALQTY_COLUMN and TOTALPRICE_COLUMN columns), specifically TotalPrice divided by Total Qty.

Is this possible? If so, how can I do this?

I could create another column for the source data sheet that would contain this value and just reference it, but if I can avoid doing that by use of some calculation "trickery" it would be preferable.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • Since Aspose.Cells follows the logic of Excel, I was wondering: have you tried if the approach you have in mind is possible in Excel (i.e. when you create the pivot table by hand, without Aspose.Cells)? – Ruud Helderman Nov 19 '16 at 20:10
  • Yes, but I'm porting this to Aspose Cells because Excel Interop is too slow, and in some cases, won't ever generate the file - even after running for several hours. – B. Clay Shannon-B. Crow Raven Nov 21 '16 at 15:40
  • I never created the Pivot Table by hand, but I do create it with Excel Interop, in a former version of this report-generating app. – B. Clay Shannon-B. Crow Raven Nov 21 '16 at 16:15

1 Answers1

2

I solved this by adding an extra column to my source data sheet, populating that by making the calculation described above (TotalPrice / TotalQty):

decimal avgPrice = 0.0M;
if ((TotalPrice > 0.0M) && (Quantity > 0))
{
    avgPrice = TotalPrice/Quantity;
}
cell = sourceDataSheet.Cells[_lastRowAddedPivotTableData, 10];
cell.PutValue(Math.Round(avgPrice, 2));

...and then referencing that new data source column accordingly:

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, AVGPRICE_COLUMN);
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862