PxPlus User Forum

Main Board => Discussions => Programming => Topic started by: Thomas Bock on November 30, 2023, 02:57:10 AM

Title: create a pivot table
Post by: Thomas Bock on November 30, 2023, 02:57:10 AM

Has someone created a Pivot table with an average column so far?
I recorded a macro in MS-Excel

Code: [Select]

    With ActiveSheet.PivotTables("Pivot1").PivotFields("Percent")
        .Caption = "Average %"
        .Function = xlAverage
    End With

This is my translation to PxPlus

Code: [Select]

pivotName$ = "Pivot1"
fieldName$ = "Percent"
title$ = "Average %"
piTable = sheet'pivotTables(pivotName$)
piField = piTable'pivotFields(fieldName$)
piTable'addDataField(*piField)
lastItem = piTable'dataPivotField'pivotItems()'count
lastPiField = piTable'dataPivotField'pivotItems(lastItem)
lastPiField'caption$ = title$
piField'function = -4106 ! xlAverage

I always receive an error 88 on the last line.
The macro seems to be wrong, because the caption can only be set on lastPiField instead of piField.
Help is appreceated.

Title: Re: create a pivot table
Post by: Thomas Bock on December 01, 2023, 04:24:13 AM

SOLVED!

The trick is to use piTable'dataFields()

Code: [Select]

lastItem = piTable'dataPivotField'pivotItems()'count
lastPiField = piTable'dataFields(lastItem)
lastPiField'function = -4106
lastPiField'numberFormat$ = mask$
lastPiField'caption$ = title$


Title: Re: create a pivot table
Post by: Devon Austen on December 01, 2023, 11:50:07 AM

Glad you resolved your issue.

Thanks for sharing the answer.