Calc macro to refresh an advanced filter, or formula to workaround?

[Wildcard]

First time posting a question here—hello!

In a nutshell: How can I make a one button macro in Calc to “reapply advanced filter (including copying the results to the defined output range)” so that a non-technical user can easily refresh the needed report? (It doesn’t have to be a macro but it seems that is the only plausible solution.)

System details: I’m running LibreOffice 3.4.4 on Mac OS 10.7.4.

Now I’ll give the additional details which are usually left out of questions like this, resulting in a zillion back-and-forth exchanges on why all the proposed solutions won’t work. The basic question is already listed up above.

The situation is too complex for an auto filter or a standard filter and not complex enough to warrant creating an actual database. All that I really need are subsets of the spreadsheet data—I don’t need it summarized in any way (or at least not in any way that I can’t easily do with Calc). I’m pretty adept with Calc but I’ve never touched macros at all.

I set up a “user input” section upon which the report is based, complete with drop down lists, and set up the advanced filter criteria based on the user input. The advanced filter works beautifully and I’ve set it to “copy results to” a pre-defined output sheet. Only trick is, since the filter criteria themselves are based on user input (but cannot be done with just an auto filter), the output sheet from the advanced filter needs to be refreshed (i.e. generated newly based on the changed criteria) after the user makes any change to the drop down user-input section.

It seems likely to me that a macro could be set up to use my already defined “criteria range” and “data range” and “output range” and just apply the advanced filter newly. Maybe this could be tied to a button right next to the “user input” section; whatever. The thing is, I have no idea how to write a macro like that and hunting the web (with a zillion tabs open by now) has turned up absolutely nothing matching that requirement. I’m really hoping that someone here will know how to do that…

Thanks for reading and I hope you can help!

UPDATE: Thank you John Sun! Your non-macro approach is great.

One more issue with it, though—I can’t work out any way to sort the output with this approach. Generally I’ll want to sort it by student, then by subject, then by date. Sometimes I would use slightly different sort orders, but because all the data cells in the report are dynamic, I can’t sort them at all without manually copying first and “pasting special” without formulas. This is why I initially gave up on the formula approach. By contrast, advanced filters give a static output when used with “copy results to…”, and so can easily be sorted by the user.

So the two issues that remain unsolved (so far) through the workaround: 1. How to sort the report by default (predefined sort criteria), and 2. How to allow the user to sort the report.

I’d settle for either one, preferably #1 so the report can look nice without any extra user effort, but I do have to solve sorting it.

Any more ideas on this?

[JohnSUN]

OK, Wildcard, I’m sure you can solve this problem without using a standard filtering mechanism. There are a few fun tricks.

[Wildcard]

Thanks! That is helpful—unfortunately, I’ve already been through those types of approach. I even got a functioning formula for what I need, without standard filtering…and then when I tried to “fill down” LibreOffice hung and I had to force quit. You see, I’m dealing with about 6000 rows of data that I need to filter from.

But still, nice to see that method! I had never seen that specific approach. Cool.

I wish I could upload a copy of the file I’m working with for you to see…

[JohnSUN]

How many columns in the table of source data? 6K rows - OK, but how many columns copied to report? How many filter conditions fields? If they are really a lot, then maybe still makes sense to think about the database

[Wildcard]

It’s a spreadsheet of student course completions throughout the year. There are only about 5 filter conditions per report, but since two of them are date criteria (greater than and less than), it winds up with only max about 100 rows in the report, usually [much] less.

[JohnSUN]

Try this small example, please FilterWithoutFilter.ods

[Wildcard]

Hmmm! That is VERY helpful!

Actually, I had tried that approach and got it working—mostly. You improved on my way a lot (AND instead of nested ifs, two columns instead of one, check for blank filter so not all columns must be filtered) so I may just abandon macros for this approach.

There’s still one more major issue that I have, making this approach less than ideal, but I’ll put it as an update to my question because of the character limit on comments.

[JohnSUN]

Custom sort order of rows in the report should not be a problem. Since the rows are taken into report in the same order in which they are located in the source data range. Just sort the original table and get a new result.While we detail the problem, I am convinced that database it is more suitable solutions.

[Wildcard]

You’re right. I just came to that conclusion myself (about a DB being suitable). The tipping point was learning I don’t have to IMPORT data into Base, I can simply query the Calc file itself from within Base. Of course that raises all kinds of new questions, but it now actually seems much more doable and closer to achievement. Thanks for your help; I really appreciate it!! (And now I’ve posted a new and different question about parameters in Base—which is for this same project.

[Regina]

I think that you can do it with calling uno commands via dispatcher.

The recorded macro is (all rem removed):

sub RefreshAdvancedFilter
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "DbName"
args1(0).Value = "Quelle"
dispatcher.executeDispatch(document, ".uno:SelectDB", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:DataAreaRefresh", "", 0, Array())
end sub

I have called my data source range “Quelle” and set the filter to “Keep filter criteria”. The cursor is moved to the top left cell of the data source range. You might need to add parts to remember the old position and set the cursor back.

You can generate a button in a toolbar to execute the macro. That is done in Tools > Customize. Or you assign the macro to the “mouse button pressed” event of an object, a button or picture for example.

[Wildcard]

Thanks! As it turns out macros are even more specialized knowledge than I thought. And I also found out that they won’t play nice with Excel, which is a downside for me. So I won’t be using your answer for this project—but I appreciate you taking the time to write the macro!

[flywire]

@Regina - Implementation should refresh sheet without setting range name and leave cursor where it is. It would be nice if it was standard functionality, maybe single-click install in the interim.

[flywire]

@Ratslinger - can you get this to work better so it refreshes sheet without specifically naming the range?

[torreone]

@Regina : very interesting, it also works with nested filters

[Wildcard]

This question has generated more views than anything else I’ve posted here, so I thought I would come back and write up how I finally solved this situation. I never did get advanced filters to refresh automatically. Instead, I did the following:

1. Create an additional column alongside the data to precalculate the more complicated parts of the criteria. This just needs to be a boolean and evaluate to TRUE or FALSE.
   1a. If there is data in your table you need to handle in more than one way, or group in some fashion, create extra columns to handle the grouping. For example if you may need to filter by week or by month sometimes, and you already have a “date” column, add a “week” and a “month” column which are precalculated based on the “date” column.
2. Create a pivot table. Put all the column headings you need in the “rows” section and nothing at all in the “columns” section of the pivot table design.
3. If there is one of the columns that the user will routinely want filtered for only one value, add that column heading to the “page” section of the pivot table design to allow easy filtering.
4. Refresh the pivot table before each use.

In my case, I set the pivot table to sort by student, then subject, then date, by simply putting these three column headers into the “rows” section of the pivot table design in that sequence. I created a “semester” column in the original data table which just displayed the semester according to the date entered in each row, and added “semester” to the pivot table design in the “page” section, to allow the user to filter for any one specific semester. I created another column in the original data entitled “FOR_GPA” with a complicated formula simply designed to determine, yes or no, whether the data in that row should be calculated into the student’s GPA (e.g. yes for usual tests, no for electives, etc.) This worked beautifully and totally solved the “how to reapply advanced filter” problem as well as allowing for greater flexibility in how the data was presented and summarized.

If any part of the above is unclear please comment below and I will try to clarify.