Calc GETPIVOTDATA through macro

English
, , ,
#1

I have a pivot table with a list of people and some total values for each. I need to go through the list and select only those for which some total values are less than zero in order to do further action.
What’s the best way to do it?
Handle the Pilot OutputRange directly on the sheet? In this case, the formula GETPIVOTDATA is useless.
Going through the entries on the sheet, I would know what to substitute in formula GETPIVOTDATA , but then it wouldn’t be necessary, because the total value would be on the right side of the table row.

What are your thoughts? I’m leaning toward the idea of processing the range on the sheet with a separate procedure. Disadvantages: If the user changes the table view, the procedure will not work. So an additional check of the current table layout is required. This is also inconvenient.

I saw an example:
GETPIVOTDATA from a cell
NOTE: That doesn’t work for me, because between the person’s name and the total value in the data field area, there are some values in the row field area that I have to enter into the formula to fully identify the entry. Otherwise, the formula won’t work because of the ambiguity of the data.
We are talking about fields that it is reasonable to display in the pivot table, but which are related to the person by a one-to-one relationship. But I have to put all these criterion pairs (field-item) into the formula. This adds nothing to the entry identification, but should be entered in the formula. However, the formula GETPIVOTDATA knows nothing about the relationship between fields and does exactly the right thing.

Excel has a corresponding method: Excel PivotTable.GetPivotData
Because of the note above, it would still be inconvenient to use.

UPD. The pivot table interface seems to allow you to build and customize a pivot table, but not to retrieve related data. Or is it possible, using the pilot table API, to retrieve the required record with all related fields?

#2

Please upload your ODF type sample file here.

#3

Crossported:
https://forum.openoffice.org/en/forum/viewtopic.php?t=109854

#4

Everything is in Russian. I see no point in preparing an example with translation, since the questions themselves are general, interface questions. They are either answered or not.

Everyone is obsessed with examples. You should understand them too.
But…

Снимок экрана от 2023-04-22 09-17-55
Снимок экрана от 2023-04-22 09-17-551379×194 35.7 KB

Comment: Person ([ФИО ученика], pupil/student) on the left, data field ([Sum - Долг]) on the right.
Two additional row fields (Class and Personal Account): [Класс], [Лицевой счет]. It is required to extract the sum of debt ([Sum - Долг]) for each group ([№ группы]), the person attends, and for each person.
The data in the table are aggregated: grouped by months.

Example formula:
=GETPIVOTDATA("Sum - Долг";$B$7;"ФИО ученика";"Тулин Кирилл Егорович";"Класс";"9В";"Лицевой счет";"ШК1700170000247";"№ группы";"29")
Answer: -1 750

So for all debtors in the database loop. And for each debtor to generate a receipt for payment.
How would you do it without an example in words?
@Zizi64, the task is before your eyes. Take a closer look.

#5

I don’t think that GETPIVOtDATA can be used in StarBasic. One of the arguments is a cell reference which specifies the pivot table regardless of the cell’s value. callFunction of service c.s.s.sheet.FunctionAccess does not accept cell objects nor range objects.

#6

@Villeroy, in the linked example above, @PYS uses a work cell for this purpose, from which it takes the result of the formula GETPIVOTDATA.

But I don’t know the meaning of the fields, if I go through all the persons only, to form paired criteria for the formula GETPIVOTDATA. And I need them for each record (in the loop), if the concept of “record” makes sense in relation to the pivot table data.

#7

Recorded with MRI:

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oDataPilotTables As Variant
  Dim oObj2 As Variant
  Dim oColumnFields As Variant
  Dim oObj3 As Variant
  Dim oItems As Variant
  Dim oObj4 As Variant
  Dim oObj5 As Variant
  Dim sName As String

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet2")
  oDataPilotTables = oObj1.getDataPilotTables()
  
  oObj2 = oDataPilotTables.getByIndex(0)
  oColumnFields = oObj2.getColumnFields()
  oObj3 = oColumnFields.getByName("Fund Given To")
  
  oItems = oObj3.getItems()
  oObj4 = oItems.createEnumeration()
  oObj5 = oObj4.nextElement()

  REM  the first header of the first column field:
  sName = oObj5.getName()
End Sub
#8

@Villeroy, but e.g. oItems = oObj3.getItems() is a unique set of values (we see it in the Autofilter list), not record values, with repetitions.
Field items, even if those fields are in the row area, are not linked within a single record. I can see the records in the pivot table, but I have no access to them.

#9

GETPIVOTDATA returns a data field value based on the unique values from the matching row and column fields.
You can write a unique column field value into one cell, a unique row cell value into another cell, and your worker cell with GETPIVOTDATA returns the corresponding data field value if it references the input cells properly.
If you are interested in the records of the source table, the pivot table has a SourceRange.

#10

Which set of records to move by? If the pivot table, I don’t know the values of the fields because there are no related records. If the source range, then the data in the source is not grouped by month (and then why do we need a pivot table as data aggregator?). The whole thing is inconvenient!!

So I have to use oTable.OutputRange for procedural (!) data processing. And this is very bad (procedural approach).

That is, we can aggregate data in the pivot table, then look and print it out. But God forbid we extract anything from it in the loop (only single data by formula on the sheet)… :slightly_frowning_face:

#11

Here is a scetch in python:

def ugly_test(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets["Pivot-Tabelle_Tabelle1_1"]
    pivot = sheet.DataPilotTables.DataPilot1
    row_fields = pivot.getRowFields()
    res_cell = sheet['G19']

    for bezirk in row_fields.Bezirk.Items:
        for tag in row_fields.Tag.Items:
            res_cell.FormulaLocal = ('=GETPIVOTDATA("Umsatz"; A1;'
                                     '"Name"; "April";'
                                     f'"Bezirk"; "{bezirk.Name}";'
                                     f'"Tag"; "{tag.Name}")')
            print(res_cell.Value)

It works (and is included) in the attached:
pivot_90758.ods (26.1 KB)

BUT: IMHO I would do such a job with python, operating directly on the Source.DataArray

#12

@karolus, You may well write examples in Python (I know the syntax and capabilities of the language). The idea is clear. Go through the persons (in my case) and substitute all the items of the other fields. It doesn’t matter here that some field items are irrelevant to the given record: the formula GETPIVOTDATA will calculate 0 for such variants.

But wouldn’t it be easier to use the Data - Subtotals service, hide all the source rows except the totals, and enumerate the visible rows we are interested in?

#13

Maybe… but its your Data - your Decision
For me, it looks like a lot of nested subTotals?

#14

Yes. And such a sheet must be updated manually, unlike the pivot table.
Frankly, I don’t like either solution. In the database, I would just create a SQL query with a grouping of data. And not a single extra line. But I don’t want to complicate an application that an inexperienced admin girl will be working with.

UPD. Why can’t you build a DatabaseRange directly on a sheet with an SQL query as the data source? In Excel I can. But there I can create an empty ADODB.Recordset, add “on the fly” the required fields to its structure and fill with custom data from the source range. Without any databases. Complete freedom of action.

@Zizi64, You can use @karolus's example. It is quite suitable to demonstrate the problem.
#15

I’m not obsessed with examples, and certainly not ones that include playful formats and specifics that are only relevant in a single case.
But I am interested in examples that clarify the core of a question or task. A questioner, should take it upon himself to create a truly “typical” example of small size, but great clarity. This surely doesn’t depend on a translation from Russian to Someslang. If the statement “…since the questions themselves are general, interface questions. They are either answered or not.” can be accepted by every contributor may depend. Those often using the software in a similar way as you do may agree. Those who don’t may also have ideas occasionally. Do not overly limit the group of people to whom a question is directed.

#16

With a database form, you can define what the user can change and what not. https://ask.libreoffice.org/uploads/short-url/1aku0FLh18y7SXNaiucswNsIMhr.odb
“Filter Form” does the same as GETPIVOTDATA. You can choose categories (Person, Category) and/or time intervals and calculate the aggregated amounts for the selected criteria while showing the related source data. No macro involved so far.

1 Like
#19

Example & What to do

Снимок экрана от 2023-04-23 09-20-58
Снимок экрана от 2023-04-23 09-20-581163×210 38.3 KB

example-pivotable.ods (75.8 KB)

@Lupp, the table is large, but I left one pupil for which I need to extract data (debtor). The data must be extracted in a loop.

Is my example small and clear enough? :slightly_smiling_face: The example of @karolus was perfectly adequate.

UPD. I can write any code to extract the data, but the challenge is to minimize the amount of code.

#20

debit_credit.odb (18.2 KB)
based on your spreadsheet with a tiny Python macro inside.

#21

@Villeroy, thank you. But we’ll have to make do with Calc.
(I use Linux OS.)

#17

What about getDrillDownData?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.