[Solved] Setting Data Pilot filter in a macro

[pastim]

I have a number of data pilots (pivot tables) retrieving data from a database. Many of them have filters. This works fine under manual control.

However, I would like to be able to set several filters, on different data pilots (which are in separate sheets) relating to different types of data for the same entity, to the same value. I have tried using basic macros and set what is displayed as the value of the filter I want (using setString, setFormula and InsertString methods), but the data pilot ignores what is on display. What it want me to do is click on the drop-down and select the item I want, but I can't see how to do this in a macro.

I have also looked into programmatically changing the underlying filter value but can find no method of doing so. It seems you can create brand new data pilots using basic, but once created there appears to be no way of changing the filter value.

I'm no OOo expert. I've been using Andrew Pitonyak's excellent book, but can't say I have yet really got a grip on much of the detail yet (UNOs, dispatchers and so on). Nonetheless I managed to create an object representing the data pilot using 'getDataPilotTables', but cannot get at the fields contained therein because I need a DataPilotdescriptor. It seems you can create a brand new one, but not get at an existing one. I would not be surprised if I have some fundamental misunderstanding of what's going on.

Has anyone else found a way to do this?

[FJCC]

I can change the filter conditions on a Data Pilot table with this code

Code: Select all

Dim Filter(0) as New com.sun.star.sheet.TableFilterField
  Filter(0).StringValue = "3"
  Filter(0).Field = 1
  Filter(0).IsNumeric = False
  Filter(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
  oSheets = ThisComponent.Sheets
  oObj_1 = oSheets.getByName("Sheet1")
  oDataPilotTables = oObj_1.getDataPilotTables()
  
  oObj_2 = oDataPilotTables.getByIndex(0)
  oFilterDescriptor = oObj_2.FilterDescriptor
  oFilterDescriptor.FilterFields = Filter

I haven't played with any of the parameters beyond confirming that the table appearance changes if the StringValue is changed. I used the MRI extension to create most of the code and it can help you see all the properties and methods of the DataPilot.

[Villeroy]

Database queries filter more transparently and more efficiently than the pivot table filters. Macros add a high level of complexity. Did you try a single data pilot with page field filter?

The pivot table gets database records from a whole table or some query like this:

Code: Select all

SELECT <fields>
FROM <tables>
WHERE <query conditions> =True

With the incoming records the pivot table does the following:

Code: Select all

SELECT <column fields>,<row fields>,<calculated fields>
FROM <incoming records>
WHERE <pivot filter conditions> =True
GROUP BY <column fields>,<row fields>
HAVING <selected page field conditions> =True

The only difference between the above database query and the pivot table is the flexible cross table layout with grouped column headers whereas the query has a fixed count of columns.
The <pivot filter conditions> do the same as a set of <query conditions>

[pastim]

I can change the filter conditions on a Data Pilot table with this code ...
I haven't played with any of the parameters beyond confirming that the table appearance changes if the StringValue is changed. I used the MRI extension to create most of the code and it can help you see all the properties and methods of the DataPilot.

Thanks. As far as I can see this creates a brand new Data Pilot rather than modifying an existing one. The old one disappears completely. On inspecting OFilterDescriptor prior to setting = Filter, there are no filter fields at all in the descriptor. This rather confirms my fear that one cannot modify an existing pilot, only create new ones. This is true even though OObj_2 seems to contain a skeleton matching the current cell range of the data pilot.

It will take me some time to get to grips with MRI. I have installed it but so far failed to understand it (I am rather slow at times). I'll keep trying!

[pastim]

Database queries filter more transparently and more efficiently than the pivot table filters. Macros add a high level of complexity. Did you try a single data pilot with page field filter?

I'm afraid you've lost me. When you talk about using a database query instead are you describing some other way of getting data into a Calc sheet, or about the underlying query behind the data pilot? Forgive me, but I don't understand the point you are making with your two examples.

To try and make myself clear, I have several quite complex database queries set up in my Base database, each of which collects and extracts different sorts of information from the underlying MySql database. These return data which include the year and person I later want to select (using a filter). The data takes no time at all to collect, but it is tedious setting the filters on many different data pilots. Once I have the data it's useful to be able to browse through each year for a person, or each person for a year.

The macros aren't inherently complex or difficult. The UNO for this stuff is hard because there's so much of it, but once you are near the flavour of widget you want it's usually fairly easy to manipulate. It's just that in this case there doesn't seem to be a representation of the existing data pilot that one can tinker with.

[Villeroy]

OK, filtering is tedious for many pilots but if the many pilots use the same set of criteria, you may attach an input form on a sheet where the user enters all the criteria into a separate filter table.
One pilot's source may look like this (Base syntax):

Code: Select all

SELECT "S".*
FROM "Somewhere" AS "S", "FilterTable"AS "F" 
WHERE "F"."RowNum"=1
  AND "Date" BETWEEN "F"."Date1" AND "F"."Date2"
  AND "CategoryName"= "F"."Text"

This assumes that you wrote 3 user criteria into the filter table at "RowNum" 1.
A macro to refresh all pilots loops through all sheets, all pilots on each sheet and calls the refresh() method.

[pastim]

OK, filtering is tedious for many pilots but if the many pilots use the same set of criteria, you may attach an input form on a sheet where the user enters all the criteria into a separate filter table....
This assumes that you wrote 3 user criteria into the filter table at "RowNum" 1.
A macro to refresh all pilots loops through all sheets, all pilots on each sheet and calls the refresh() method.

There must be some fundamental thing I'm missing here - I still don't understand. How are you getting the SQL query to get data from the calc sheets?

If FilterTable is a gadget on a Calc sheet (although I don't know what a FilterTable is - I can't find a way to insert/create one), how can the SQL use it as a table to select FROM? Surely all tables have to be in the database?

What sort of source is the data pilot using - Query, sql or sql (Native) (I've never understood the distinction between the latter 2).

I really appreciate your help and advice, but I suspect you think I know a lot more than I do!

[pastim]

After some thought I now believe you are suggesting I write my filtering criteria into a new table in the SQL database itself. This hadn't occurred to me (I have a bad habit of thinking of database information as being relatively static and, or at least reflecting something real and substantial, rather than using tables for highly dynamic user-choice in formation). If that's correct then I understand the SQL, and see the main idea behind what you are proposing.

However, can I insert data into a database from within Calc, or have I got to open up Base and create a form there? I've done the latter many times, but it's a bit of a nuisance to have to employ both Calc and Base in this situation. I had this idea of a user interface that's self-contained in Calc and thus easy to use.

[pastim]

I have changed all my Base Queries and Calc Data Pilots as suggested, and I have a form, in Base, that sets the criteria I want Calc to use. I can also refresh all the Data Pilots with one button. That all works fine, and didn't take too long once I understood what I was trying to do.

I have spent all day trying to find out whether it is possible to include a form (or set of controls) in Calc (as opposed to Base) that would let me set the selection criteria I want from within Calc.

The documents on Calc and Writer implies this might be possible. I have (I think) inserted a form control in a sheet, but can't see anything on the sheet. It has dimensions (the small green markers on the frame) when selected in navigator, but doesn't seem to be otherwise visible. I've inserted a couple of list box controls on the sheet but can't get them to access the database.

Am I trying to do something that can't be done, or is it that I have thus far failed to understand the documents, help, and controls? It would be a great help to me to know whether I am wasting my time (because it's impossible) or should press on until the penny drops.

[Villeroy]

Most forms are just ordinary Writer documents embedded in the Base document. Any stand-alone Office document can have database forms. Just create them in the same way as you should do in Base (no, the insane "wizard" is no way).
View>Toolbars>Form Design
Button #5 form navigator
Add a form and bind it to a row set
View>Toolbars>Form Controls
Add controls and bind them to fields
Add subforms and bind them to related row sets
Add controls to the subforms and bind them to fields
...

[pastim]

I guess you are saying 'yes' this is possible, but my ignorance is getting in the way of implementing anything.

I created a 'form' as you describe, and defined the table I wanted it to use (I assume that is what you mean by 'bind' to a row set, but I'm not 100% sure).

I can't see the 'form' anywhere in Calc. Where is it? The form seems to have no display properties. How can I add controls to something I can't see? Is there some strange meaning to the term 'form' that I don't understand? In Base, for instance, a form is a visible discrete entity. I this not so in Calc?

The following really isn't a complaint, rather an attempt to explain why this is is all so hard for me, and maybe others as well.

When I started in IT I wrote bits of operating systems, interrupt handlers, terminal handlers, file handlers and the like. I also got to be quite familiar with SQL. When I stopped writing much code and went into technical management (around 1986) after a while I lost the conceptual framework that others were creating and developing. I now find it incredibly hard to grasp the overall scheme within which all these widgets fit.

I looked for 'Form' in Andrew Pitonyak's macro document (since the main Calc and Writer Documents seem to say nothing I can comprehend about forms). It says, and I quote "When a control is inserted into a document, it is stored in a form. Forms are contained in draw pages, which implement the com.sun.star.form.XFormsSupplier interface. The visible portion of the control—what you see—is stored in a draw page and it is represented by a ControlShape. The data model for the control is stored in a form and it is referenced by the ControlShape. The method getForms() returns an object that contains the forms for the draw page (see Table 238)." I'm sure this is accurate, but I don't understand it at all.

Suffice to say that I have yet to find an 'object' that supports getForms(). How does anyone get to grips with this? I need some form of description that gives me a clue as to what this is all about in terms I can understand. I'll keep trying, but it hurts...

[Villeroy]

The form is an abstract (invisible) container for form controls. The form determines the row set, the controls access the fields of the form's row set.
Add form controls and see.

[pastim]

Add controls to what? If it is abstract and invisible I am at a loss to understand what a form is, or how I can add controls to it. Where do I 'click' to do anything with it?

I seem to be stuck with this idea of a form I can see and manipulate. Abstract and invisible forms seem just that - abstract, invisible, and hence not something one can work with.

Is this described anywhere that I can sit down and read to get my head round it?

[pastim]

By ignoring the fact that I was manipulating a form that seems not to exist, being both invisible and abstract, I managed to attach the form to the database table containing my selection criteria and create visible and concrete controls showing that data. However, when I change the data I also have to save the form to update the criteria.

I have one button which refreshes all the data pilots (that use the criteria in the database) in the Calc document using a Basic Macro. The last thing I wanted to do was, therefore, to get that same button to save the current record in the form before refreshing the pilots. It took me ages to find out how to address the form in basic (it seems 'forms' are held with 'drawpages') and what method would update the date (it's 'updateRow' - obvious - but only when you know the method exists).

So, with apologies for being so slow and dumb, and thanks for the help. It takes me ages to bend my brain some of these concepts, and there is so much information out there I find it really hard to know where to start to look.

[pharmankur]

I can change the filter conditions on a Data Pilot table with this code

Code: Select all

Dim Filter(0) as New com.sun.star.sheet.TableFilterField
  Filter(0).StringValue = "3"
  Filter(0).Field = 1
  Filter(0).IsNumeric = False
  Filter(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
  oSheets = ThisComponent.Sheets
  oObj_1 = oSheets.getByName("Sheet1")
  oDataPilotTables = oObj_1.getDataPilotTables()
  
  oObj_2 = oDataPilotTables.getByIndex(0)
  oFilterDescriptor = oObj_2.FilterDescriptor
  oFilterDescriptor.FilterFields = Filter

I haven't played with any of the parameters beyond confirming that the table appearance changes if the StringValue is changed. I used the MRI extension to create most of the code and it can help you see all the properties and methods of the DataPilot.

This is amazing , astonishing and simply fantastic !
You have not played with this, but I did.

Using your code , tried to give a simple " For ... Next " loop for the list of filter values. I was able to automate pivot report to select filter value OneByOne and save the individual reports by filtered name as PDF ! And Saved Huge Amount of Our Time in just manually selecting filter and saving reports !

The details you can find in enclosed file. Just press button "Pivot_Autofilter_PDF_Report" and see the magic !

Test.zip

Demo file with Auto Pivot Filter OneByOne

(124.82 KiB) Downloaded 177 times

Thanks a lot for your code, I can buy you a coffee