Calc GETPIVOTDATA through macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Calc GETPIVOTDATA through macro

Post by eeigor »

Posted a question on Ask. Maybe someone here can help me, too, who doesn't look there.
https://ask.libreoffice.org/t/calc-getp ... acro/90758

For those who want a better understanding of the formula GETPIVOTDATA, I uploaded an example (I don't claim authorship, and I forgot the owner's name) that will make this post a little more interesting :) (I did not provide it on Ask).

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 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 https://ask.libreoffice.org/t/macro-to- ... 51/5?=true
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 2 https://learn.microsoft.com/en-us/offic ... tpivotdata
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?

GETPIVOTDATA tutorial (thanks to the author):
Attachments
test_getpivot-modified.ods
(52.43 KiB) Downloaded 87 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc GETPIVOTDATA through macro

Post by Lupp »

What seems to be tried to achieve in your example is the creation of 2D-organised tables based on some data elements. However, the formulas don't even access the pair of elements they should get values from a pivot table for. These entries in the header positions positions of the table are inserted literally again into the formulas.

As far as I understand the function GETPIVOTDATA() (which I never used before), is a way to use field names instead of indices to select values from a pivot table. Not actually an urgent thing. Where needed we can do it using MATCH(). What the purpose of calls to the function from running user code should be, is still very unclear to me.

I have generated a more real-world example, following the given example by using a donor-recipient situation.
Using the sample data and the associated pivot tables, it should now be easier to make the actual intentions clear.

You can access the pivot (DataPilotTables) identifying tzhem (e.g.) With the help of their output ranges, and you can work with the respective DataArray structures. However, this should not be an end in itself, but serve a purpose, and GETPIVOTDATA() applied via a FunctionAccess object may not play a role in it.

See attached example.
PivotMania_eeigor.ods
(95.67 KiB) Downloaded 91 times
(Yes. I know that related tasks are better solved with the help of databases.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: Calc GETPIVOTDATA through macro

Post by eeigor »

Example-pivottable:
Attachments
example-pivotable.ods
(75.77 KiB) Downloaded 87 times
Снимок экрана от 2023-04-23 09-20-58.png
Снимок экрана от 2023-04-23 09-20-58.png (38.29 KiB) Viewed 1502 times
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc GETPIVOTDATA through macro

Post by Villeroy »

Lupp wrote: Sat Apr 22, 2023 9:49 pm (Yes. I know that related tasks are better solved with the help of databases.)
Accounting software never uses spreadsheets as storage medium.
https://ask.libreoffice.org/uploads/sho ... DWOs1z.odb (includes [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes instead of a refresh button on form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Calc GETPIVOTDATA through macro

Post by Lupp »

Though I had to handle lots of data by "professionally" developed databases (and often had to fight problems), and was charged with a lot of things concerning IT at a time, I don't know every accounting software, and surely not what many people otherwise created as their actually working solutions (*).
Just tinkered a bit around in the sheets the questioner had attached.
I now think he tried to get the data for some prints per "debitor" with the help of the GETPIVOTDATA() function to automate serial printing. I still think that the mentioned function isn't really uf much use for the task, and I don't feel sure to generally have understood its purposes sufficiently.

For the sake of the Russian-German relations I attach the result hoping it might be of some use.
example_pivotable_RE.ods
(45.76 KiB) Downloaded 77 times
Editing 2023-04-24 about 13:15 UTC
I beg your indulgence for the superfluous remark above. I should have anticipated expectable comments.

(*) ...for small-scale-cases with some special requirements
Last edited by Lupp on Mon Apr 24, 2023 2:16 pm, edited 2 times in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc GETPIVOTDATA through macro

Post by Villeroy »

Lupp wrote: Mon Apr 24, 2023 1:19 am For the sake of the Russian-German relations I attach the result hoping it might be of some use.
example_pivotable_RE.ods
As a metaphor why they are about to lose their war?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply