how to connect two pivot table in libreoffice calc by filter ?
Good question. Two different pivot tables must have a shared cache. How to do it?
Does the interface provide this capability? And can this be done programmatically?
There is the SourceRange property, no Cashe one found.
Perhaps you need to connect to the same data source located in Base.
when i select a criteria form a pivot table that type of criteria must be selected to other pivot table also…How do i do that??
There is a lot you can do in Excel. For example, create a new pivot table based on an existing one.
We must look towards the registered data source and the use of LibreOffice Base. Maybe @Villeroy can help you…
Let’s think together what we can do here
I downloaded the sample data https://trumpexcel.com/wp-content/uploads/2016/05/Pivot-Table-Data-for-Slicers.xlsx
What is the information we are after? I don’t understand the Excel videos.
OP in two different posts wants to create a filter (that is, a slicer) to manage multiple pivot tables with shared cache.
OK, OK. Now I got it. It is just another UI gimmick. Slicers do not add any new functionality.
What’s the difference between Slicers and Report Filters?
Slicers look super cool and are easy to use. Pivot Table’s strength lies in the fact that you don’t need a lot of skill to use it.
If 2 pivots are derived from the same source table, it is just a matter of a few minutes to create a third one or expand any of the two.
With Base forms you can create your own user interface for filter criteria.
https://forum.openoffice.org/en/forum/viewtopic.php?t=88516&p=416210#p416210
They are always open. And they can be connected to tables. This is an external object, and the filter is internal.
UPDATED:
Slicers are visual filters and with just one click, you can filter the pivot table report (a report filter requires several clicks to filter).
Based on the example data for the slicer, what information can we get from Excel that we can’t get from Calc?
Well, an example task should be clarified by the OP.
Off topic: To my surprise I found something that Excel can do since 8 years but nobody ever asks to do this in Calc:
https://www.pcworld.com/article/440704/how-to-create-relational-databases-in-excel-2013.html
People simply don’t get the concept of a relational database, even if it is an Excel feature.
this is not my answer.
The Slicer object is supported since Excel 2010.
You can support the enhancement request #tdf119807 by Roman Kuznetsov.
@kesu, let’s say I created two pivot tables based on the same data source and applied a standard filter to one table and programmatically synchronized the second with the first one. BUT. I am unable to add an event listener to the first table in order to automatically invoke the sync routine on the second table.
Who knows how to intercept the data filtering event in a pivot table 1?
Sub SyncDataPilotsByFilter()
''' Synchronize DataPilot1 with DataPilot2 data by filter using the filter set in DataPilot1.
On Local Error GoTo HandleErrors
Dim oSheet As Object, oTables As Object, oTable1 As Object, oTable2 As Object
oSheet = ThisComponent.Sheets.getByName("PivotTables")
oTables = oSheet.DataPilotTables
' Define table names if necessary.
'oTables(0).Name = "DataPilot1"
'oTables(1).Name = "DataPilot2"
Rem Xray oTables: End
oTable1 = oTables.getByName("DataPilot1") 'oTables(0)
oTable2 = oTables.getByName("DataPilot2") 'oTables(1)
Rem Xray oTable2: End
Dim oFilterDsc1 As Object, oFilterDsc2 As Object
oFilterDsc1 = oTable1.FilterDescriptor
oFilterDsc2 = oTable2.FilterDescriptor
With oFilterDsc1
oFilterDsc2.FilterFields = .FilterFields
End With
oTable2.refresh
Exit Sub
HandleErrors:
Msgbox "Error" & Err & ": " & Error _
, MB_ICONSTOP, "macro:SyncDataPilotsByFilter"
End Sub
This instruction does all the work:
oFilterDsc2.FilterFields = oFilterDsc1.FilterFields
Suggest any listener connection code.
In this case, the standard filter will replace the slicer.
Connecting a new table will not be difficult either. The code is simple.
I tried it. Error occured.
oTable1.addModifyListener(oEventListener)
Expects 0 args. Why?!
Global oEventListener As Object
Sub StartEventListener()
Dim oTable1 As Object
oTable1 = ThisComponent.Sheets.getByName("PivotTables").DataPilotTables.getByName("DataPilot1")
If IsNull(oEventListener) Then 'just to be shure it doesn't start twice
oEventListener = CreateUnoListener("EventListener_", "com.sun.star.lang.XEventListener")
oTable1.addModifyListener(oEventListener)
End If
End Sub
Sub StopEventListener()
Dim oTable1 As Object
oTable1 = ThisComponent.Sheets.getByName("PivotTables").DataPilotTables.getByName("DataPilot1")
If Not IsNull(oEventListener) Then 'only if still running
oTable2.removeModifyListener(oEventListener)
oEventListener = Nothing 'to know later the listener has stopt
End If
End Sub
Sub EventListener_notifyEvent(oEvent As Object)
Print oEvent.EventName
Call SyncDataPilotsByFilter
End Sub
Sub EventListener_disposing(oEvent)
MsgBox "EventListener_disposing does work now !?"
Call StopEventListener
End Sub
Error in line:
oTable1.addModifyListener(oEventListener)
We are waiting for help. And so everything works and just connects.