We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

How to set a calculated field in a Pivot table

Hi,

Can any one advise me how to add a new calculated field in a pivot table.

Please see the below statement and let me know if this is correct.

PivotTables[0].AddCalculatedField("TotalError", "=SUM(GETPIVOTDATA('ERROR TYPE',$A$5,'PERIOD',2007.03,'REGION','Asia Pacific')-GETPIVOTDATA('ERROR TYPE',$A$5,'ERROR TYPE','CORRECT','PERIOD',2007.03,'REGION','Asia Pacific'))", false);

The generated Excel sheet is corrupted and i could not open the excel sheet.

My requirement is that, i generate a pivot table. The data field contains some 5 columns. Now i need to add another column next to the grand total. The values for this column is calulated, which gives the difference between the grand total and sum of all the data fields leaving the first column of the datafield.

Please let me know if you could understand the problem.

Also, suppose my pivot table is placed in the area "A5:G20", i could not able to retrive the values of the cells in between these areas. If i could get these cell values, then also my problem will be solved.

Thanks

Suchindhraa

Hi Suchindhraa,

Thank you for considering Aspose.

Well, you requirement is not very clear. Can you please Post your template file and explain your requirement in a bit more detail, so that we can provide you with a proper solution to you issue.

Thank you & Best Regards,

Hi Aspose Support Team,

I couldn't post the template, but let me explain in detail.

In pivot table you have Row fields, Column fields. The Row Grand Total and Column Grand Total fields are generated automatically.

Now my requirement is similar to the Column Grand Total, but instead of adding all the column fields, i would like to add some selected columns / in wise versa, I can also subtract some column values from the Grand Column Total.

Please advice me if it is possible?

Thanks

Suchindhraa

Hi,<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” />

Thank you for Considering Aspose.

We tried to add such calculated field to the pivot table, but we were unable to do it.
It seems MS Excel could not use GetPivotData function in the calculated field of the pivot table. Please post your created file with the calculated field, so we can provide you with an appropriate solution.

Also, if your file contains confidential data, you can email to us, so we can resolve your issue. To email please follow these under mentioned steps,

1: click the Contact button in the Post.

2: In the drop down list options click "Send Nausherwan an Email”.

3: Attach the template file and send it.

4: Once you have done it, kindly confirm us on this thread.

Also, We checked the function GetPivotData in MS Excel Help, the params(“Data_Field”,“Field1”,“Item1”.etc) of the function GetPivotData should be string value. You should use your parameters as string values.

You can see the details about GetPivotData function from the following link,

Thank you & Best Regards,

Hi Nausherwan,

I hope you would have received my mail. Please check and let me know if you could understand my problem.

Thanks

Suchindhraa

Hi Suchindhraa,

Thank you for Considering Aspose.

Yes, we have received your template file. We will get back to you soon.

Thank you & Best Regards,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Hi Suchindhraa,

Thank you for considering Aspose.

After analyzing your provided template file, we think, you can insert Calculated item into the pivot table as per your requirement using MS Excel.

Inserting the calculated item to pivot table In MS Excel:

Selected Cell "C6" in the worksheet "Pivot By Region"=> Pivot table table=> Formulas=>Calculated Item=>Input the name and formula.

Please check the attached file.

Butcurrently, the feature of inserting the Calculated item into Pivottable is not supported in Aspose.Cells.

Thank you & Best Regards,

Dear Aspose Team,

Please let me know if you could include the following enhancement in your Aspose.Cells.dll

1. To add a calculated item in the pivot table

2. To add some more style properties like drawing cell borders for the data field

3. To define some colors for the pivot table column headers.

Thank you

Suchindhraa

Hi,

Ok, we will check the feasibility of your desired features if these can be incorporated.

Thank you.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have support to add Pivot calculated item to pivot field.

See the following sample code:


//Instantiating an Workbook object
Workbook workbook = new Workbook();
//Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;

//Setting the value to the cells
Aspose.Cells.Cell cell = cells["A1"];
cell.PutValue("Sport");
cell = cells["B1"];
cell.PutValue("Quarter");
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);

PivotTables pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=A1:C8", "E20", "PivotTable1");
//Accessing the instance of the newly added PivotTable
PivotTable pivotTable = pivotTables[index];
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
//Dragging the second field to the column area.
pivotTable.AddFieldToArea(PivotFieldType.Column, 1);
pivotTable.AddFieldToArea(PivotFieldType.Data, 2);
pivotTable.ColumnFields[0].AddCalculatedItem("CountQtr3", "=COUNT('Qtr3')");
//"Qtr3" should be the pivot item name of the pivot field.
//Please add "'" before and after the pivot item name.Suche as 'Qtr3'.
workbook.Save(@"F:\FileTemp\dest.xls");


Following is the summary information of the PivotField.AddCalculatedItem method for your reference.
///


/// Add a calculated item to the pivot field.
///

/// The item's name.
/// The item's formula
///
/// Only supports to add calculated item to Row/Column field.
///
public void AddCalculatedItem(string name,string formula)

Thank You & Best Regards,

Dear Aspose Team,

Thanks a ton for supporting this feature in your latest DLL. I should really appreciate your effort in this.

Once again you prove that you are always by the side of your customers, supporting in all terms and conditions.

Thank you once again.

Regards,

Suchindhraa

Dear Aspose Team,

Now i have a new problem with the Pivot Table - Column - Add Calculate Item.

The Excel report is corrupted and i couldn't open my report, if i add more than one calculate item in the Pivot column field. Please consider the below code.

String[] ColumnArray = xlObj.ExcelWorkBook.Worksheets["Pivot By Region"].PivotTables[0].ColumnFields[0].Items;

String GrandTotalFormula = "=";

String ErrorsFormula = "=";

for (int i = 0; i < ColumnArray.Length; i++)

{

GrandTotalFormula = GrandTotalFormula + "'" + ColumnArray[i] + "'";

if (ColumnArray[i].ToString() != "CORRECT")

{

ErrorsFormula = ErrorsFormula + "'" + ColumnArray[i] + "'";

if (i != ColumnArray.Length - 1)

ErrorsFormula += "+";

}

if (i != ColumnArray.Length - 1)

GrandTotalFormula += "+";

}

xlObj.ExcelWorkBook.Worksheets["Pivot By Region"].PivotTables[0].ColumnFields[0].AddCalculatedItem("GrandTotal", GrandTotalFormula.ToString());

xlObj.ExcelWorkBook.Worksheets["Pivot By Region"].PivotTables[0].ColumnFields[0].AddCalculatedItem("Errors", ErrorsFormula.ToString());

Can you advise me if there is any problem in my code or how can i achieve the feature of adding more than one calculate item.

Thank you

Regards,

Suchindhraa

Hi,

I could not find the problem you have described as I added two calculated items for the column field(s). Could you post your template files(input + output etc.) and paste your code here or alternatively you may create a sample console application to reproduce the issue, we will check it soon.

Thank you.

Hi,

After looking into your issue more closely we found the bug. Could you try the attached version(4.5.1.24) as we have fixed it now.

Your feedback is highly appreciated in this regard.

Thank you.