Jasper plugin iReport: parameter Null

Classic Servoy
1

Hi everybody,

I’m trying to make a report with a query containing a “optional parameter”.
In fact depending on the his choice in Servoy the user will be able to filter or not the result of the report.
So I have something like that in my SQL query:

WHERE fld_field1 = $P{field1_value};

If the user wants to filter $P{field1_value} contains a value to filter.
If the user doesn’t want to filter it will contain some kind of wild-card to search for any value.

I don’t really know how to do it.

I use PostgreSQL 8.3, iReport 3.6.1 and fld_field1, $P{field1_value} are Strings

2

Change your code to include the interpretation character"!" as follows:

WHERE fld_field1 = $P!{field1_value};

That way it interprets the content of the brackets properly.

3

Thomas Parry:
Change your code to include the interpretation character"!" as follows:

WHERE fld_field1 = $P!{field1_value};

That way it interprets the content of the brackets properly.

Thanks for the tip.
However I still don’t manage to make it search on anything (it wants a value).

I have managed to use a workaround but it’s not really clean. I use LIKE with % sent as parameter:

WHERE fld_field1 LIKE '$P!{field1_value}'

It works but I don’t really like this solution :?

4

Sorry, I may have “misinterpreted” what your requirement is.
So to state another way is the where clause going to be optional for this parameter?
If so then you could make the where clause a parameter that you build dynamically.
Used in the report like this:

WHERE $P!{WHERE_CLAUSE}

Then you build up the where clause like this:

  1. create a new parameter: WHERE_CLAUSE - ensure it is the LAST parameter in the parameter list.
  2. the expression for this where clause will be something like this:
($P{field1_value} == null "": $P{field1_value})

(the logical statement is: if ($P{field1_value} == null then where_clause = “” else where_clause = $P{field1_value})
(Note the absence of the “!” in the expression

Have a look at my Jasper tips here: http://www.prospect-saas.biz/jasper_parameters.html

5

Yes that what I need I think!
But I can’t make it work my WHERE_CLAUSE is not valid apparently:

($P{epc_no} == null '': 'AND tbl_epc.cal_epc_no ='+$P{epc_no})

I have set it into the Default Expression of the WHERE_CLAUSE parameter. That’s right?! :roll:

In my query I have:

WHERE
  ...
  $P!{where_clause};

where_clause is declared after epc_no

6

oops :oops:
I had a typo, forgot the “?” (That is what you get when you respond too quickly)
so in your case the default expression should be:

($P{epc_no} == null ? '': 'AND tbl_epc.cal_epc_no ='+$P{epc_no})
7

Thomas Parry:
oops :oops:
I had a typo, forgot the “?” (That is what you get when you respond too quickly)
so in your case the default expression should be:

($P{epc_no} == null ? '': 'AND tbl_epc.cal_epc_no ='+$P{epc_no})

II should have spotted it as well :?

Unfortunately the WHERE_CLAUSE is still not valid. Now I’m getting the following compilation error:

net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:

  1. Invalid character constant value = (java.lang.String)((((java.lang.String)parameter_epc_no.getValue()) == null ? ‘’: ‘AND tbl_epc.cal_epc_no =’+((java.lang.String)parameter_epc_no.getValue()))); //$JR_EXPR_ID=0$ <—> 2. Invalid character constant value = (java.lang.String)((((java.lang.String)parameter_epc_no.getValue()) == null ? ‘’: ‘AND tbl_epc.cal_epc_no =’+((java.lang.String)parameter_epc_no.getValue()))); //$JR_EXPR_ID=0$ <—> 3. Invalid character constant value = (java.lang.String)((((java.lang.String)parameter_epc_no.getValue()) == null ? ‘’: ‘AND tbl_epc.cal_epc_no =’+((java.lang.String)parameter_epc_no.getValue()))); //$JR_EXPR_ID=0$ <—> 3 errors at net.sf.jasperreports.engine.design.JRAbstractCompiler.compileReport(JRAbstractCompiler.java:191) at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:492) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)
8

You can try replacing the single quotes with double quotes - but that does not seem likely solution.
Are you using Java as the language or groovy? I assume you are using iReport 3.6.x ?
Can you post a bit more of the iReport - maybe the order of the parameters in the list?

9

Thomas Parry:
You can try replacing the single quotes with double quotes - but that does not seem likely solution.
Are you using Java as the language or groovy? I assume you are using iReport 3.6.x ?
Can you post a bit more of the iReport - maybe the order of the parameters in the list?

With double quotes it compiles!
However when I don’t enter a value for $P{epc_no}, I get the following error:

Error filling print… Error executing SQL statement for : report name
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report name at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:681) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:601) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1245) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:875) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:824) at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:59) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417) at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247) at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:856) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135) … 11 more
Print not filled. Try to use an EmptyDataSource…

tbl_epc.cal_epc_no is a varying char column. So If put nothing I get this error and if I put ‘’ it returns “no page to display” as it doesn’t find any record with the value ‘’

I use iReport 3.6.1. I don’t use Groovy.

10

So it does not like the SQL.
Try the same SQL in some other application that tests the SQL syntax that is produced.
Send me a PM with the database query or the whole iReport and I can have a quick look.
Ensure the syntax being produced is correct for your database.
I am not familiar with postgresql so there might be some syntax required for the equality of a string that is not correct here.

11

PM sent :)