Free Support Forum - aspose.com

Calculated member in pivots

Can a calculated member be created in Pivot Sheets? We need this functionality so that summary values of percentages total correctly.

Hi,

Thanks for considering Aspose.

Yes you can, The latest hotfix has this feasibility, you may use PivotTable.AddCalculatedField() method for your need.

Here is the sample code and attached is the template file:

Workbook workbook = new Workbook();
workbook.Open(@"D:\test\Aspose_Source.xls");//source sheet
Worksheet sourceSheeet = workbook.Worksheets[0];
Cells cells = sourceSheeet.Cells;
workbook.Worksheets.Add();
Worksheet pivotSheeet = workbook.Worksheets[1];
pivotSheeet.Name = "Pivot Table";
pivotSheeet.Move(0);
PivotTables pTables = pivotSheeet.PivotTables;

string sourceData = "=Raw Data!A1:C6";
string destCellName = "A3";
int index = pTables.Add(sourceData, destCellName, "PivotTable1");
// access the new pivottable
PivotTable pTable = pTables[index];
pTable.AddFieldToArea(PivotFieldType.Data, 1);
pTable.AddFieldToArea(PivotFieldType.Data, 2);
pTable.AddCalculatedField("Total","= Price * quantity",true);
pTable.AddFieldToArea(PivotFieldType.Column, 0);
workbook.Save(@"D:\test\Aspose_Dest.xls", FileFormatType.Excel2003);

And please use the latest hotfix (also attached) for it.

Thank you.

Hi Amjad,

The hotfix is greatly appreciated. However I wonder if this has caused a new problem, maybe the new dll is not full backward compatible? Prior to the new dll (I was using version 4.1.2) the following lines of code worked fine...

if (pivotTable.DataField != null)
pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);

But now when my application gets to the AddFieldToArea line (the if statement evaluates to true) I get a null reference exception. If I remove the reference to the new Aspose.Cells dll and go back to the previous one I was using it works as expected.

Does the new dll have another method for moving DataFields to Columns that I should be using?

Thanks,
Justin

Hi,

Please try this fix. We have fixed this bug.

Hi Warren,

Thank you for the hot fix and the fast response times. The hot fix provided is working great except for one problem. In subtotaled groups an extra row is being injected. This never happened before. I have attached a sample so you can see the behavior.

Thank you,
Justin

Hi Justin,

Thanks for you file. It help me find the problem quickly.

Please try this fix.

Hi Warren,

Thank you for all the help, that fix worked. Unfortunately I have found one more issue (hopefully this will be the last time I have to bug you with this feature). When the computed column is the last column in the pivot then everything works fine. However if you have a computed column with a lower field index (meaning it would be to the left of the other columns) then it shows up properly for the specified field index but it also over writes the last column with the name of the computed column followed by the number 2.

You can see what I am talking about in the samples attached. good.xls shows the properly formatted pivot table. Bad.xls shows what happens if you have data columns that come after the computed column.

Thanks,
Justin

Hi Justin,

Thanks for the template files.

We will figure out the issue soon.

Thank you.

Hi Justin,

If you call AddCalculatedField method, we will add a base field to the pivot table(You will see the calculated field in field list in Excel.). So the lastest base field is the calclulated field. So if you call pTable.AddFieldToArea(PivotFieldType.Data, pTable.BaseFields.Count -1), it will drag the calculated field to data area again.

Please confirm you do not call pTable.AddFieldToArea(PivotFieldType.Data, pTable.BaseFields.Count -1) after adding calculated field.

I am calling pTable.AddFieldtoArea after pTable.AddCalculatedField since the order is determined by the user through the web form interface. Is there a way the AddCalculatedField method could be modified to accept an index like the AddFieldToArea method does so the index could be explicitly set?

Thanks,
Justin

Hey guys,

I appreciate the work that has already been done on this issue already. Are there plans to modify the AddCalculated field to allow inserting it at a specific index? With the curernt behavior we would also be limited to only a single calculated field which would also be a problem.

Thanks,
Justin

Hi Justin,

Please call pivotTable.AddCalculatedField(string name, string formula,false)method. This method only appends a calculated field to base field lists and does not drag the calculated field to the data area.You can drag the the calculated field to the data area with pivotTable.AddFieldToArea() method.

Hi Warren,

We are already using the AddFieldToArea method, this is our last step, if we pass "false" for the last AddCalculatedField method the column does not show up at all. Any thoughts?

Thanks,
Justin

Hi Justin,

If you use "false" , it means that we only add a calculated field to base field lists. You have to drag the calculated field to data area by PivotTable.AddFieldToArea() method.

Hey Warren,

All your help is greatly appreciated. We've got the around the last problem I was asking about but we came across one small problem that I hope might be easily fixable. If I try to add a calculated field with a column name that has a space the pivot table blows up. For example a formula like...

=(Price * Quantity) works just fine but this formula...

=(Unit Price * Quantity) corrupts the worksheet.

I've tried surrounding Unit Price with quotes "Unit Price" and brackets [Unit Price] but neither method helps. Other than removing the space is this fixable?

Thanks,
Justin

Hi Justin,

Please try this fix.

And please change your formula as ='Unit Price' * Quantity. It's same as Excel.

Hi Warren,

I was trying to make sure that any Divide by Zero errors show up as 0 instead. In Excel the following formula would work...

=IF(ISERROR(ColumnA / ColumnB),0,(ColumnA / ColumnB)

But with Aspose it corrupts the pivot table. Can this be fixed?

Thanks,
Justin

Hi Justin,

Are you implementing the forumula to define the calculated field in the pivot table report. i.e.,

pivotTable.AddCalculatedField(name, formula);

Well we will suport this type of formula as calculated item in the pivot table report soon.

Thank you.

Hi Justin,

Could you post your sample file and codes? I tried the following codes, it works fine.

//Instantiating an Workbook object
Workbook workbook = new Workbook();


//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];


Cells cells = sheet.Cells;


//Setting the value to the cells
Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter test");
cell = cells["C1"];
cell.PutValue("Sales");
cell = cells["D1"];
cell.PutValue("Sales1");

cell = cells["A2"];
cell.PutValue("Golf");
cell = cells["A3"];
cell.PutValue("Golf");
cell = cells["A4"];
cell.PutValue("Tennis");
cell = cells["A5"];
cell.PutValue("Tennis");
cell = cells["A6"];
cell.PutValue("Tennis");
cell = cells["A7"];
cell.PutValue("Tennis");
cell = cells["A8"];
cell.PutValue("Golf");


cell = cells["B2"];
cell.PutValue("Qtr3");
cell = cells["B3"];
cell.PutValue("Qtr4");
cell = cells["B4"];
cell.PutValue("Qtr3");
cell = cells["B5"];
cell.PutValue("Qtr4");
cell = cells["B6"];
cell.PutValue("Qtr3");
cell = cells["B7"];
cell.PutValue("Qtr4");
cell = cells["B8"];
cell.PutValue("Qtr3");


cell = cells["C2"];
cell.PutValue(1500);
cell = cells["C3"];
cell.PutValue(2000);
cell = cells["C4"];
cell.PutValue(600);
cell = cells["C5"];
cell.PutValue(1500);
cell = cells["C6"];
cell.PutValue(4070);
cell = cells["C7"];
cell.PutValue(5000);
cell = cells["C8"];
cell.PutValue(6430);

cell = cells["D2"];
cell.PutValue(0);
cell = cells["D3"];
cell.PutValue(0);
cell = cells["D4"];
cell.PutValue(0);
cell = cells["D5"];
cell.PutValue(0);
cell = cells["D6"];
cell.PutValue(0);
cell = cells["D7"];
cell.PutValue(0);
cell = cells["D8"];
cell.PutValue(0);

PivotTables pivotTables = sheet.PivotTables;


//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:D8", "E20", "PivotTable1");


//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];


//Unshowing grand totals for rows.
// pivotTable.RowGrand = false;


//Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);


//Draging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.AddCalculatedField("test", "=IF(ISERROR(Sales/Sales1),0,5)");
// pivotTable.AutoFormatType = PivotTableAutoFormatType.Table1;

pivotTable.DataFields[0].NumberFormat = "yyyy-mm-dd";

//Saving the Excel file
workbook.Save(@"F:\FileTemp\dest.xls");