Print Page - create a pivot table

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

    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.

PxPlus User Forum

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