Home › Forums › Users › General User's Discussion ›

How to filter using OR in filter boxes

Submitted by pyevet on Fri, 10/07/2015 - 11:09
in

G'day all,

I've tried a few variations on 'or' statements in a report filter box with no joy.  What is the correct syntax?

The immediate application is to tidy up some mis-named products using the Product List Report.  For example we have products in the same brand with names beginning with "r/c" and "Royal Canin" and would like to see them all on one report.

While I'm asking: What about AND useage?  Would this also work in other search bars for on-screen displays eg customers, patients etc?

Regards Simon

‹ printing reminders How do I deal with in house use of meds and stock control ›

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: How to filter using OR in filter boxes

Submitted by tanderson on Fri, 10/07/2015 - 11:54.

This is not supported. For reports, you could change the SQL queries so that they accept two product names to match on.

If you look at the SQL of the Product List Report in iReport or JasperSoft Studio, the where clause has:

where e.name like concat(ifnull( $P{Product Name},""),"%")

You could add a second product name parameter (e.g. Product Name 2) and change the query to:

where (e.name like concat(ifnull( $P{Product Name},""),"%") 
       or e.name like concat(ifnull( $P{Product Name 2},""),"%"))

 

-Tim

Re: How to filter using OR in filter boxes

Submitted by tgething (not verified) on Fri, 10/07/2015 - 12:13.

Simon - I don't know about the on screen selection boxes - I suspect not.

Certainly my reports (and the ones in the 1.8 package) use the SQL syntax like the following:

where e.name like concat(ifnull( $P{Product Name},""),"%")

I had a play with some SQL injection but cannot get it to come out correctly.

Suggestions:

1. hack the sql in the report do that you can do some sql injection

2. hack the report so that there are two product name parameters and tweak the sql to select if ... or ....

3. pull the sql from the report and use in MySQL workbench (or phpAdmin) to run the query you want.

4. run the report selecting everything - export the output then massage with Excel or OO Calc to extract what you want.

Regards, Tim G

Re: How to filter using OR in filter boxes

Submitted by pyevet on Mon, 13/07/2015 - 10:43.

Thanks Tims,

For the purpose of this 1-off job (hopefully) I'll use the sql in mysql directly as needed and pipe the tabulated output to lp.  It doesn't look pretty, but for temporary internal reports I do it quite often and the staff are used to it.

My version of this report didn't have the concat(...) phrases, so I'll modify the report to suit and make another version with the extra parameter, in reality we probably will need it again.

Thanks , Simon.

Simon Slater

Registered Linux User #463789 @ http://linuxcounter.net

Syndicate content