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

Free Support Forum - aspose.com

How to set a custom calculation for data field in a pivot table?

Hi, I’ve got a question when using pivottable in Aspose.Cells.
I see there’s “BaseFiledIndex” property in PivotField, but i don’t know how to set a custom calculation for data field in pivot table.
Anybody can help me out?ShowValueAsInExcel.png (35.8 KB)

@lewis95,

I think you may try to use PivotField.Function to apply the consolidation function for your needs. See the sample code segment for your reference:
e.g
Sample code:

.......
    //Get the first data field in the table.
    PivotField pvtField = pivotTable.DataFields[0];

    pvtField.Function = ConsolidationFunction.Sum;
    pvtField.DisplayName = "Sum of abc";
     ......

Yes, we have relevant APIs for it, see the sample lines of code:
e.g
Sample code:

........
PivotField pf = pivotTable.BaseFields["MyField"];
//Or you can get it via its indexed position.
PivotField pf = pivotTable.BaseFields[0];

Sorry, I didn’t make my question clearly.
I know how to add a function to a data field in Aspose.
What i want to know is how to set “ShowValueAS” in a data field as the picture i’ve attached.

@lewis95,

Could you share your current output and expected Excel file containing your desired pivot table, we will check your issue soon.

PS. you may zip the files prior attaching.

ok, I’d show how to set ShowValueAs in MSExcel, and you can tell if aspose could do this.
In excel,

  1. set range value
    image.png (14.3 KB)
  2. add a pivot table, and drag some fields.
    image.png (36.8 KB)
  3. select a data field and click value field settiing
    image.png (17.7 KB)
  4. select tab show value as, and choose % of grand total
    Untitled.png (40.7 KB)
  5. then you can see all data field’s data are calculated as percent of grad total.
    image.png (13.7 KB)
    here’s .xlsx file:
    ShowValueAs.zip (11.3 KB)

Does aspose support this feature?

@lewis95,

It looks like the relevant APIs are not as I could not find in the APIs set . I tried the following sample code and could not evaluate on how to accomplish the task.
e.g.
Sample code:

Workbook workbook = new Workbook("e:\\test2\\ShowValueAs.xlsx");
            Worksheet worksheet = workbook.Worksheets[0];

            PivotTable pt = worksheet.PivotTables[worksheet.PivotTables.Add("=Sheet1!A1:F16", 18, 7, "Test pivot")];

            pt.AddFieldToArea(PivotFieldType.Row, 2);
            pt.AddFieldToArea(PivotFieldType.Row, 1);
            pt.AddFieldToArea(PivotFieldType.Column, 5);
            pt.AddFieldToArea(PivotFieldType.Data, 3);

            var categoryField = pt.RowFields["Category"];
            categoryField.SetSubtotals(PivotFieldSubtotalType.Automatic, true);
            categoryField.ShowInOutlineForm = true;
            categoryField.ShowSubtotalAtTop = true;
            categoryField.ShowCompact = true;
            categoryField.IsAutoSort = true;
            categoryField.IsAscendSort = true;

            var productField = pt.RowFields["Product"];
            productField.SetSubtotals(PivotFieldSubtotalType.Automatic, true);
            productField.ShowInOutlineForm = true;
            productField.ShowSubtotalAtTop = true;
            productField.ShowCompact = true;
            productField.IsAutoSort = true;
            productField.IsAscendSort = true;

            pt.DataFields[0].Function = ConsolidationFunction.Sum;

            pt.ColumnFields[0].IsAutoSort = true;
            pt.ColumnFields[0].IsAscendShow = true;

            workbook.Save("e:\\test2\\out1.xlsx");

I have logged a ticket with an id “CELLSNET-47634” for evaluation and thorough investigations. We will check and try to figure your issue out soon. We will either provide a new fix with new APIs set (with details) for the task or we will provide a code segment (workaround), so you could include/update it to accomplish your task for your needs.

Once we have an update on it, we will let you know.

Thanks a lot!
Looking forward to your news.

@lewis95,

Sure, once we figure it out, we will update you here.

@lewis95,

Please try the following sample code (see the lines in bold), it works fine for your needs:
e.g.
Sample code:

Workbook workbook = new Workbook(“e:\test2\ShowValueAs.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];

        PivotTable pt = worksheet.PivotTables[worksheet.PivotTables.Add("=Sheet1!A1:F16", 18, 7, "Test pivot")];

        pt.AddFieldToArea(PivotFieldType.Row, 2);
        pt.AddFieldToArea(PivotFieldType.Row, 1);
        pt.AddFieldToArea(PivotFieldType.Column, 5);
        pt.AddFieldToArea(PivotFieldType.Data, 3);

        var categoryField = pt.RowFields["Category"];
        categoryField.SetSubtotals(PivotFieldSubtotalType.Automatic, true);
        categoryField.ShowInOutlineForm = true;
        categoryField.ShowSubtotalAtTop = true;
        categoryField.ShowCompact = true;
        categoryField.IsAutoSort = true;
        categoryField.IsAscendSort = true;


        var productField = pt.RowFields["Product"];
        productField.SetSubtotals(PivotFieldSubtotalType.Automatic, true);
        productField.ShowInOutlineForm = true;
        productField.ShowSubtotalAtTop = true;
        productField.ShowCompact = true;
        productField.IsAutoSort = true;
        productField.IsAscendSort = true;

pt.DataFields[0].Function = ConsolidationFunction.Sum;
pt.DataFields[0].DataDisplayFormat = PivotFieldDataDisplayFormat.PercentageOfTotal;

        pt.ColumnFields[0].IsAutoSort = true;
        pt.ColumnFields[0].IsAscendShow = true;



        workbook.Save("e:\\test2\\out1.xlsx");

Thanks a lot! That’s exact what I need!

@lewis95,

You are welcome. In the event of further queries or issue, feel free to write us back. We will be happy to assist you soon.