Multiple filters on reports ?

[arfgh]

Hello.

I have several report documents that are exactly the same but each one perform a sql query to filter the results.
I have used in forms a listbox that call a basic macro to perform several filterings and the result is very very useful with only one form.

So in the reports we cant insert controls, but we can add hyperlinks that i have read we can call macros from them.
So my idea is to find a way to filter the report document using for example hyperlinks before the result tables.

I have read on the forum about to perform filterings from just forms, but i want to do it from the own report document.

I need help

thx in advance

[Villeroy]

If you would use a filter table instead of macros you could have reports filtered by the same criteria as the forms.
viewtopic.php?t=88023

[arfgh]

Ok, i will check that 'intermediate table' to filter, but anyways and because the reports seems very different than the forms, just want to know how to mess with this i want to do but with macros. I used that way to filter the forms with amazing results, just only one form, with a listbox with several and different filters that call a subroutine.

Somebody know how to filter a report from the own report document, using for example several hyperlinks ?

thx in advance

[Villeroy]

Somebody know how to filter a report from the own report document, using for example several hyperlinks ?

Find out by yourself and tell us about the results. It's very easy to do with a spreadsheet but you know that already.

[arfgh]

you wrong, first off i'm not english. the most time i dont know what you all are speaking about with technical language such that spreadsheet, and tons others.

Can someone help with the main question ? thx

[Villeroy]

OpenOffice Calc is a spreadsheet program. It can be used for reporting as demonstrated and documented here: viewtopic.php?t=88516&p=416210#p416210

I copied a filter form from my FilterData.odb into a report of that database. The result is attached here. The OK button is linked to 3 lines of code which close and re-open the report. The OK button is attached to another logical form taking away the focus from the filtering form so the filtering form is saved to the database table.

[arfgh]

no.

like i said before, i want to do it from the own report document. No forms involved.
For that reason i have thought if it is possible to do it with some hyperlinks, or other thing, that we will add before the result table into the report document.
Maybe exist other way to call a macro from a report other than hyperlink, i dont know, but because the idea is to have only one report document, and several hyperlinks that call a function that filters depending from example.. the hyperlink name or text.

[Villeroy]

I forgot one thing:
Open my report named "qReport" for editing.
Select all form controls and set property "Printable"=No
Save the report and database document.
Now the form controls are not visible when you print or export to PDF.

[arfgh]

umm...
i will check that in deep, but i dont understand certains things like for example the 'export.csv' 0 Bytes file when using the first report...
I didnt know that we can add controls in reports....

and... only 3 files in the macro ?

[Villeroy]

This is a very old document. It is related to many topics. Your item of interest is the report qReport based on the query qReport and the 3 lines of macro code in Standard.Module1.Main

[arfgh]

ok Villeroy, your example db is very very tricky, i am messing with it to see the way.
But tell me a thing, is there a way from basic to specify the filter like i did in forms ?
In your example the listbox is who command the data table results, but imagine that i want to specify the filters name i go to use, and on the basic function i want to set the report 'command to process'.

I did this but it didnt work:

Code: Select all

    Form = Event.source.Model.parent
		Form.filter = query
		Form.ApplyFilter = True
		Form.reload()

where 'query' is my custom filter for the selected item on the listbox.

In my way the listbox is not linked, because it call a function on event, and it is the fuction who select what filter to apply. But it isnt working...

thx in advance

[Villeroy]

All the filtering in that document works reliably without any macro code based on form controls and SQL.

The little macro I have added reloads the report "qReport" after changing the filter criteria on that report.

Code: Select all

SELECT "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" 
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F" 
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID"
  AND "F"."FID" = 1 
  AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) 
  AND ( "D"."CID" = "F"."INT2" OR "F"."INT2" IS NULL ) 
  AND ( "D"."D" >= "F"."D1" OR "F"."D1" IS NULL ) 
  AND ( "D"."D" <= "F"."D2" OR "F"."D2" IS NULL ) 
ORDER BY "Date" DESC

reads all the data from 3 data tables and from line #1 in the "Filter" table.
WHERE "C"."ID" = "D"."CID" AND "P"."ID" = "D"."PID" maps the ID-fields to their corresponding category and person names.
AND "F"."FID" = 1 reduces the filter table to this particular line
AND ( "D"."PID" = "F"."INT1" OR "F"."INT1" IS NULL ) returns True when INT1 of the filter table is Null (not given) OR when it is equal to the person-ID.

[arfgh]

yes, i know, but i want to do it by other way that isnt working and i dont know why. In forms worked, not here.
just with the 'Form.ApplyFilter'

my listbox isnt linked because the items on it are just names for the filters. Then when i select some item on the listbox, the basic function should perform all. But the Form.Reload() isnt chaning the reported table data.... neither closing and re-opening like you did.

[RPG]

Hello

i want to do it by other way that isnt working and i dont know why

You have to study harder. When you have study enough then you do have the answer. It is the same answer as Villeroy give.

Arfgh do not me send me PM's about question, about filters in a report. Sending the PM's brings me to add this post in this thread. I think it is a good place. I have read all the posts of you not only there you send me a PM but I did read them before. You have really no idea how reports are working. Special not the reports for the new style report builder. All what you do asked is impossible for the new style report builder.

I think the information which Villeroy gives in several answers not only to you but to every one is so important that reading only threads of Villeroy will learn every one how to use OOo. It will also learn to the reader how to use OOo in a more easy way. When you want not follow the easy way of Villeroy then you have to study a real long time. Many time such a person does follow the wrong way: as you do now.

Learning and following the wrong way belongs to each other. But it is real sad when a person continue on the wrong way: as you do now.

Romke

[arfgh]

i am not using report builder and i will not. Because i prefer the reports in base.
And no, i dont understand the way that the reports work, that's clear.
but i asked you about some of yours examples that didnt work in fact....
Nothing is impossible, specially when there are macros to be used. And i can say several things about that 'impossible' you are saying but in other programming language where tons said 'that's impossible' and no, it was possible in fact.

I go to repeat it, i understand maybe the 25% of your technical language, i'm not english.
I really did far enought from that position, maybe you and others need to be on that place some day to UNDERSTAND.

Anyways i back to the main question, i want to do it all from the own report document, without intermediate tables, or intermediate queries. Yes basic involved clearly, so sorry Villeroy, your example is so tricky, but it isnt the way i am looking for.

[RPG]

Hello

Study in the form-navigator the hidden controls. Change maybe some controls. Macros for old-style report do not work for new style-reports and also macros for new-style-reports do work in old-style-reports. I have not test it and you can test it easy if it works by changing some things by typing in other values. It is to you.

Why are the examples of Villeroy so good for a lot of people. Those example work as far I knew always. They work for new-style and old style reports. They work also for all database engines. When you have learned once to understand the example of Villeroy then you can use it always. Macro code does only work most of the time in real clear examples and do not work if there is change a little. That is not a problem for a person who can write his own code. As it is a problem for arfgh you make clear you can not program.

I believe the language is maybe a problem but even if I do write in your own language you can not program in the API. My English is also terrible but I can program, a little, and also program, a little, in the API.

Romke

[arfgh]

what about that 'old style' and 'new style' you are referring, related to reports ?
i use no report building, i never liked it.

I said that the Villeroy's example is very very tricky and very wise, but well... i wanted to do it without intermediate queries or tables.... and should be possible.

[RPG]

Hello

i understand maybe the 25% of your technical language

I can not be sure I under stand you and you do understand what I write. So we can stop the discussion.

Romke

[arfgh]

Villeroy, at the moment following your example i added a listbox to the report with the filters i want. Your trick works fine but, what about if we have an Oracle Report Builder created report ? with it i cant add form controls, hyperlinks and neither events.
What can do with that kind of reports in this case ?

thx in advance

[Villeroy]

You can use a spreadsheet as report.
You can use an old style report.
You can use a macro driven external report (also old style).
And of course you can use a report builder report with a small criteria form in a second window. If your users are unable to handle multiple windows, you have a more serious problem than Base and its limitations.

i am not using report builder and i will not. Because i prefer the reports in base.

[arfgh]

yes yes, i said that ^^ but since your tricky example to filter the report from the own report document, i wanted to try the oracle report builtder to see its virtue or not. And i have just reached that point, i cant add to it form controls like in main reports, the listbox to choose the filter to use. And also in oracle report builder, i cant add hyperlinks or events on the parts, a thing that i can also do in main report.

Villeroy taking your DB example here in the thread, the tricky one that filters the report. Your second report is made with oracle report builder, can you add on it just the same ? the filtering of it. ?

[Villeroy]

No, I can not add any form controls to ORB reports.
Open the form named "ID Filter" and modify the filter form.
Open that second report named "qReport3".
The query is also named "qReport3"

Code: Select all

SELECT "D"."ID", "D"."D" AS "Date", "P"."N" AS "Person Name", "C"."N" AS "Category", "D"."V" AS "Value" 
FROM "Categories" AS "C", "Data" AS "D", "Persons" AS "P", "Filter" AS "F" 
WHERE "C"."ID" = "D"."CID" 
  AND "P"."ID" = "D"."PID" 
  AND "F"."FID" = 5 
  AND "D"."ID" = "F"."INT1"

Selects the record from "Data" ("D") with the same ID as in row #5 of "Filter"."INT1" and looks up the category name and person name.

Close the report, edit the criteria data in the form, reload the report.

[arfgh]

yes, in those details ORB report is more limited in comparisson... very curious...

[Villeroy]

No, ORB is not more limited. I think that nobody ever thought of putting a filter form on a report. I'm pretty sure that I was the first one who ever copied form controls on a report in this topic on 14th May 2017, 16:02
The whole filter form idea is just a creative recombination of the given features. Developers never thought of using form controls in this way to overcome certain limitations.

[arfgh]

well, yes. But the possibility to have a listbox in the report with the filtering idea, is so confortable.

[Villeroy]

But this is not necessary to get things done. Don't waste too much time with unimportant gimmicks.