We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Excel Pivot Table set option Subtotals > Show all Subtotals at Top of Group

This actually refers to the Syncfusion Excel Pivot table.

I was trying to set this option Subtotals > Show all Subtotals at Top of Group in the Pivot table, but did not find a way to do so. Neither on Pivottable object but also not on the PivotField object. Perhaps I overlook it but could not find a reference in the documentation. Does someone know how this option could be set in code? At least on this level I could not find it:

pivotTable.Fields[11].Axis = PivotAxisTypes.Row;
pivotTable.Fields[11].Position = 0;
pivotTable.Fields[11].ShowBlankRow = false;
pivotTable.Fields[11].Subtotals = PivotSubtotalTypes.None;
pivotTable.Fields[11].PivotFilters.Add();

As a workaround for now I create a macro on opening the workbook that sets these options, but that will produce an xlsm file which is often restricted to open by the user. The macro code looks like the following and does work:

ActiveSheet.PivotTables(""PVTTableAuditProjectStaat"").PivotFields(""Contractor""). _
        Subtotals = Array(True, False, False, False, False, False, False, False, False, False, _
    False, False)

// And that for all fields...

    ActiveSheet.PivotTables(""PVTTableAuditProjectStaat"").SubtotalLocation xlAtTop


Thanks for any input!


2 Replies

AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team December 5, 2022 11:53 AM UTC

Hi Christian.


We have implemented the feature "Provision to display row sub-totals at the bottom of the group", and we are now testing with other feature combinations. For your convenience, we have attached a screenshot of the pivot table with the feature below. However, this feature will be available in our upcoming Vol 4 main release, which is scheduled to be released at end of December, 2022. You can track the same by using the feedback link.


https://www.syncfusion.com/feedback/30271/provision-to-display-sub-totals-at-top-of-the-group


Output screenshot:

Sub-totals at top of the group:


Sub-totals at bottom of the group:


Regards,

Angelin Faith Sheeba.



RG Rajeshkannah G Syncfusion Team December 22, 2022 11:51 AM UTC

Hi Christian,


We are glad to announce that our Essential Studio 2022 Volume 4 Main Release V20.4.38 is rolled out and the feature “Provision to display sub-totals at top of the group
” has been included in this release. Please refer to the below code example, screenshot and release notes.


Code Snippet:

<SfPivotView>

<PivotViewDataSourceSettings SubTotalsPosition="SubTotalsPosition.Top"

       ShowSubTotals="true" >

        <PivotViewColumns>

</SfPivotView>


Release notes: https://blazor.syncfusion.com/documentation/release-notes/20.4.38?type=all#pivot-table


Output screenshot:


Meanwhile, we have prepared a sample for your reference.


Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/SyncfusionPivotBlazor347650000


We thank you for your support and appreciate your patience in waiting for this release. Please get in touch with us if you would require any further assistance.


Regards,

Rajeshkannah Gopalakrishnan






Loader.
Live Chat Icon For mobile
Up arrow icon