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

Aspose.Cells Product Family
1

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)

2

@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];
3

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.

4

@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.

5

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?

6

@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.

7

Thanks a lot!
Looking forward to your news.

8

@lewis95,

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

9

@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");
10

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

11

@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.