Python Win32 hide subtotals of pivot table

Question

I've got the following code:

def pivot_table(wb: object, ws1: object, pt_ws: object, ws_name: str, pt_name: str, pt_rows: list, pt_filters: list, pt_fields: list): """ wb = workbook1 reference ws1 = worksheet1 that contain the data pt_ws = pivot table worksheet number ws_name = pivot table worksheet name pt_name = name given to pivot table pt_rows, pt_cols, pt_filters, pt_fields: values selected for filling the pivot tables """

# pivot table location
pt_loc = len(pt_filters) + 2

# grab the pivot table source data
#to edit with new data
pc = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=ws1.Range("A9:Q10807"))

# create the pivot table object
pc.CreatePivotTable(TableDestination=f'{ws_name}!R{pt_loc}C1', TableName=pt_name)

# selecte the pivot table work sheet and location to create the pivot table
pt_ws.Select()
pt_ws.Cells(pt_loc, 1).Select()
# Visiblity True or False
pt_ws.PivotTables(pt_name).ShowValuesRow = False
pt_ws.PivotTables(pt_name).RowGrand = False
pt_ws.PivotTables(pt_name).ColumnGrand = False
pt_ws.PivotTables(pt_name).RowAxisLayout(win32c.xlTabularRow)

Now I would like to hide subtotals for all columns with python. Anyone know how to do this?

I've tried the following:

#pt_ws.PivotTables("User ID").RowGrand = False
pt_ws.PivotTables("User ID").ColumnGrand = False
pt_ws.PivotTables("User Group").RowGrand = False
pt_ws.PivotTables("User Group").ColumnGrand = False
pt_ws.PivotTables("Risk Description").RowGrand = False
pt_ws.PivotTables("Risk Description").ColumnGrand = False

Hpwever this gives an error..

com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'PivotTables method of Worksheet class failed', 'xlmain11.chm', 0, -2146827284), None)

Please help!

Answer 1

As defined in the Subtotals documentation at the end of the Remarks part, you need to set all values of Subtotals tuple to be False.

To hide all subtotals your code should look like that

for field in fields:
    field.Subtotals = tuple(False for _ in range(12))

here field should be an item from the PivotFields collection. For example, to disable subtotals for User Id

pt.PivotFields('User Id').Subtotals = tuple(False for _ in range(12))