create a pivot table

[Thomas Bock]

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.

[Thomas Bock]

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$


[Devon Austen]

Glad you resolved your issue.

Thanks for sharing the answer.