Pivot table reading filter criteria from fields

English
#1

I tried reading filter criteria for a pivot table from data fields, such as startdate and enddate. Alternativly I would like to see the criteria beside the pivot table.

I found this macro, but it fails with
BASIC runtime error, property or method not found: getByName
What can I do? This is LibreOffice 24.2.4.2

Sub ApplyDateFilterToPivotTable()
    Dim oSheet As Object
    Dim oPivotTables As Object
    Dim oPivotTable As Object
    Dim oFilterDescriptor As Object
    Dim startDate As String
    Dim endDate As String
    Dim oField As Object
    Dim oFilterField As Object
    Dim criteria(1) As Object
    Dim criterion(1) As Object

    ' Get the active sheet
    oSheet = ThisComponent.CurrentController.ActiveSheet
    
    ' Read the start and end dates from the criteria table
    startDate = oSheet.getCellRangeByName("A1").String ' Adjust cell reference as needed
    endDate = oSheet.getCellRangeByName("B1").String ' Adjust cell reference as needed
    
    ' Get the pivot table
    oPivotTables = oSheet.DataPilotTables
    oPivotTable = oPivotTables.getByIndex(0) ' Adjust if you have multiple pivot tables
    
    ' Get the filter field descriptor
    oField = oPivotTable.getByName("Date") ' Adjust the field name as needed
    oFilterField = oField.createFilterDescriptor(True)
    
    ' Set the filter criteria
    criterion(0) = "Date"
    criterion(1) = ">=" & startDate
    criteria(0) = criterion
    
    criterion(0) = "Date"
    criterion(1) = "<=" & endDate
    criteria(1) = criterion
    
    oFilterField.Criteria = criteria
    
    ' Apply the filter
    oField.setFilter(oFilterField)
    
    ' Refresh the pivot table
    oPivotTable.refresh()
End Sub
#2

Don’t guess names of methods and properties. You’ve got to inspect the pivot table. The filter in the sample document https://ask.libreoffice.org/uploads/short-url/j0ncdghMn3QhMXyeCv7WSpmSuEE.ods does not filter the pivot field. It filters the ingoing data. This filter descriptor belongs to the pivot table itself rather than any of its pivot field.
P.S. And the string of a cell is completely useless if the cell value is numeric.

#3

pivot_240702-2145.ods (102.0 KB)

#4

Thanks. I tried your pivot_240702-2145.ods
You have a From and Until field in H1 and H2, they either take the min and max of the list or the value in J. But how do you apply these values to the Pivot-Table?
I typed “01.12.21” to J2 (Until). But the Pivot Table filter is not changed by that.

And I did not guess methods, that example came from ChatGPT which obviously is often wrong, but a good starting point.

#5

ChatGPT just guesses things. It is not intelligent. It’s just smart guessing. ChatGPT can not understand what it’s trying to do. For people who can not program, it is useless. Someone needs to have some understanding of the matter.

Using my macro and all the helper routines requires a little bit of setup according to the constants defined on “Module1”. You can merge the two modules into one or just keep them in the same library within your document or under “MyMacros”.

  1. Our date column is the first one in the pivot table’s source range. It’s filter field index is zero, the second column would be 1. Adjust this source table index to your own. If your pivot source is X1:Z99 with the dates in Y2:Y99, the index would be 1(second column of source range).
  2. I defined two named cells “From” and “Until” for the two dates, G1 and G2 on the pivot sheet. They can be anywhere in the same document.
  3. A named cell “CF_Cell” where the conditional formatting is taken from. Can be any cell in the same document as long as the conditional formatting can be copied from that cell.

With the right column index and 3 named cells, the macro should do the same with any pivot table to be filtered by a date range.
If you want to use different cell names, change the red constants in double-quotes according to your cell names.
P.S. Why should a Microsoft owned ChatGPT write good macro code for LibreOffice?

#6

I’m going to understand your solution.
I see 7 functions in file.ods/Standard/Helpers and 1 main function “refresh_Pivot_WIth_CF” in file.ods/Standard/Module1.
That’s really a lot for getting formatting into the Pivot-Table. I’m not sure if I understand every detail to be able to port that into my file…

To see how it works:
Doing a native Pivot-table refresh looses formatting, but the REFRESH button re-applies the format, good.
Doing a data change in Data and pressing the REFRESH-Button refreshes the Pivot table and the calculation, good.

Changing the Until value in J2 to “01.11.2021” and pressing REFRESH doesn’t work. Doing a native Refresh doesn’t work either. Then pressing again the REFRESH button works and applies the new Until date. So there is still something missing here.

What I don’t understand, where is that named CF-Cell, I can’t see it. Where is the formatting stored?

Some other question, is that set of features with LibreOffice scripts generally possible to be exported to MS Office .xlsx formats and working?

#7

pv = getPilotAtActiveCell() The macro references the pivot at the active cell.

	rg = getRangeByAddress(doc, adr)
	rg.clearContents(lFlag)

The macro references the range which includes, column fields, row fields and data fields and removes hard formatting attributes, including hard formatting.
Then it converts the cell values into ISO strings, because I found out that ISO strings are used in the filter descriptor when setting the filter manually. From the 2 ISO strings, it creates 2 new filter fields and and attaches them to the filter descriptor.
After applying the modified filter descriptor and refreshing the pivot table, it references the new pivot table range again and applies the conditional format that was found at the named cell “CF_Cell”.

All this requires fundamental programming skills. You can’t understand the code without profound knowledge about data types, sturcts, objects, methods, properties.

#8

I think I’m going to drop the requirement to have a formattting in the Pivot-Table, as I don’t have the programming knowledge yet.

I just tried exporting to .xlsx, but it seems the features are not compatible.
pivot_240702-2145.xlsx (83.7 KB)
Opening in LibreOffice, the alternating colors in the Pivot Table are lost.

Opening in MS Office, it seems that all the Pivot-Table is lost and just a plain table. Isn’t Pivot-Table portable at all when saving as Excel 2007-365 (.xlsx)?

#9

Edit: It seems that MS Office 2019 can read .ods files… that works.

#10

If Pivot tables don’t support reading filter parameters from the table without sophisticated Basic programming, is it somehow possible to display the current filter settings in the table?

#11

Here is my approach, it fails in line 37 of the Basic script:
pivot_bt_2400708-1232.ods (113.2 KB)

criteriaStr = oFilterFields(i).Value
Method not found: Value.

Where do I find the reference documentation?
I searched in
https://wiki.documentfoundation.org/Documentation/SDKGuide/Functions_and_Data_Analysis#Pivot_Tables_in_the_API
https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star.html
https://wiki.documentfoundation.org/Documentation/BASIC_Guide#API_Reference

#12

Ok, I solved it by reading other examples like
https://forum.openoffice.org/en/forum/viewtopic.php?p=242156&sid=3846fa117a221119d1407b49b815d439#p242156
and replaced the .Value method by .StringValue.
Anyway I would like to see the reference spec for this API.

#13

https://api.libreoffice.org/docs/idl/ref/structcom_1_1sun_1_1star_1_1sheet_1_1FilterFieldValue.html

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

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