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.
They are always open. And they can be connected to tables. This is an external object, and the filter is internal.
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:
People simply don’t get the concept of a relational database, even if it is an Excel feature.
this is not my answer.
@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.
You can use addModifyListener.
I tried it. Error occured.
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:
We are waiting for help. And so everything works and just connects.