PxPlus User Forum

Twitter Twitter Twitter

Author Topic: create a pivot table  (Read 1587 times)

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 179
    • View Profile
create a pivot table
« 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.

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 179
    • View Profile
Re: create a pivot table
« Reply #1 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$

Devon Austen

  • Administrator
  • Diamond Member
  • *****
  • Posts: 386
  • Don’t Panic
    • View Profile
    • PVX Plus Technologies
Re: create a pivot table
« Reply #2 on: December 01, 2023, 11:50:07 AM »
Glad you resolved your issue.

Thanks for sharing the answer.
Principal Software Engineer for PVX Plus Technologies LTD.