Run time queries in Jasper Studio reports

[gregory.west]

I have a report, in it have a parameter $P{Location Desired}    In the dataset and query dialog I am trying to get Jasper Server to ask the use for the location they want on the report.   Everything I have tried gives me an error.   Here is the SQL code I last tried.

select * from parts where bin like $P{Location Desired}

bin is the table field with the location information in it.

[j.t.casperson]

Have you tried:
select * from parts where bin = $P{Location Desired}

[j.t.casperson]

Are you using MySQL?

[hozawa]

It's working OK with me. You just have to fix the error. Unfortunately, you haven't provided any error message so it's not possible to say further.

[RamaKrishna.B]

hi,

We need to use parameter as below when we are using like operator in the query

select * from parts where bin like '%' ||$P{Location Desired} || '%'

Thanks

B Rama Krishna

[gregory.west]

The exact SQL code is:

select * from parts where not(parts.obsolete) && (parts.bin like ($P{Location} || '%')

Here are the errors I am getting:

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: data.
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:342)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1129)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:696)
    at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:437)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:523)
    at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:164)
    at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:474)
    at com.jaspersoft.studio.data.reader.DatasetReader.start(DatasetReader.java:197)
    at com.jaspersoft.studio.property.dataset.dialog.DataPreviewTable$4.run(DataPreviewTable.java:241)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 77
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:332)
    ... 9 more
 

[gregory.west]

the database in question is Postgresql.    Also I am never prompted for an input.

[j.t.casperson]

Sorry, I'm not too familiar with Postgresql. But, I would suggest compiling and previewing the report in JasperStudio (if possible) before trying it on JasperServer. Sounds like the issue is Jasper not liking your sql. I would try simplifying it to something like "select * from parts where not(parts.obsolete)" and then adding on more stuff until it throws the error again. Sorry I couldn't give a more concrete answer.