manshack_one

I realized I was still getting the error because I was choosing "true" for my $P{completed} parameter. By moving the string from that true clause into the report query I found that it was still having trouble with the same error. I gave $P{completed} a value of false and it ran the report so it's something to do with the string class for that parameter. Am I not formatting it correctly?

Here is my jrxml file. I moved the sql with the parameters back to the report query and still get the same error. What am I overlooking?

Thought I'd add some more info. My report query is just a parameter $P{querychoice}. It's a string and it's default value is set to : $P{completed}==Boolean.TRUE? "select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant, b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate} and $X{IN,a.Auditor,auditorName} and $X{IN,a.UnitName,unitName};" : "select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;" I added back in the other two parameters I need which are 2 collection values. Whether I put my report query as $P{querychoice} or $P!{querychoice} I get the same result: Version:0.9 StartHTML:-1 EndHTML:-1 StartFragment:0000000111 EndFragment:0000006097 Error filling print... Error evaluating expression : Source text : $P{completed}==Boolean.TRUE? "select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant, b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate} and $X{IN,a.Auditor,auditorName} and $X{IN,a.UnitName,unitName};" : "select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;" net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{completed}==Boolean.TRUE? "select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant, b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate} and $X{IN,a.Auditor,auditorName} and $X{IN,a.UnitName,unitName};" : "select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;" at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:195) at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:589) at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:557) at net.sf.jasperreports.engine.fill.JRFillDataset.setFillParameterValues(JRFillDataset.java:638) at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:586) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1272) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:892) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:841) at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58) 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:858) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: groovy.lang.MissingPropertyException: No such property: X for class: ORA_Summary_Worksheet_1274128077191_632086 at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:49) at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:59) at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.invokeMethodN(ScriptBytecodeAdapter.java:169) at ORA_Summary_Worksheet_1274128077191_632086.getProperty(calculator_ORA_Summary_Worksheet_1274128077191_632086) at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.getGroovyObjectProperty(ScriptBytecodeAdapter.java:532) at ORA_Summary_Worksheet_1274128077191_632086.evaluate(calculator_ORA_Summary_Worksheet_1274128077191_632086:161) at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:182) ... 13 more

I have 2 other parameters that need to be added which will be Collection datatypes. It doesn't like those either. This query runs fine without any parameters. But I need to filter out the records appropriately so I'll have to include them at some point.

java.util.Date()

This is my query: $P{completed}==Boolean.TRUE? "select a.AuditDate, a.UnitName, a.Auditor, b.NumReview, b.NumApplicable, b.NumCompliant, b.NumIncompliant, b.Compliant, b.NA, c.qnum, c.question, c.discipline, c.type from ORA_AUDIT a join ORA_DETAIL b on a.AuditID = b.AuditID join ORAQ c on b.QID = c.pk where a.AuditDate = $P{auditDate};" : "select s.AuditDate, s.UnitName, s.Auditor, d.NumReview, d.NumApplicable, d.NumCompliant, d.NumIncompliant, d.Compliant, d.NA, s.qnum, s.question, s.type, s.discipline from( select a.AuditID as aid, a.AuditID, a.AuditDate, a.UnitName, a.Auditor, q.pk as qid, q.qnum, q.question, q.type, q.discipline from ORA_AUDIT a, ORAQ q) s left outer join ORA_DETAIL d on d.AuditID = s.aid and d.QID = s.qid order by s.aid, s.qid;" It's placed into a variable so that I can run two separate queries based off of one checkbox input control. Without the parameter $P{auditDate} it will run. With the parameter I get an error message: Version:0.9 StartHTML:-1 EndHTML:-1 StartFragment:0000000111 EndFragment:0000004171 Error filling print... Error executing SQL statement for : ORA_Summary_Worksheet net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ORA_Summary_Worksheet at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:686) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:606) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1273) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:892) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:841) at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58) 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:858) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'parameter_auditDate.getValue())' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137) ... 11 more What am I doing wrong? Are parameters not supported in the where statement of a join?

SUCCESS!! Replacing the jar worked fine. :) Thanks for letting me know to try 3.7.1.

I had 3.7.0 for iReport and JS CE. With that version there was a problem with the List component stopping at the bottom of the page it was created on. If you had more data than would fit it just truncated at the bottom of the page. Tracker ID is 4530. When I upgraded iReport to 3.7.2 the problem went away but JS is still on 3.7.0 so it works in iReport but not on JS.

That kind of work is over my skill level. :) I'd be a happy camper if someone with more knowledge than myself could take a stab at doing that.

any idea how far away JasperServer update is for support of 3.7.2? There's a bug in the list component that 3.7.2 fixed but if I can't update JasperServer it's useless. I'm surely not going to run these reports for them. :)

Ok. dowloaded iReport 3.7.2 and the list function works there. But when I push it out to JasperServer it's still truncating the list. Can I replace the jasperReport jar file on the server with the one from 3.7.2?

Please help. This is driving me nuts.

bump

bump

I can't be the only person using the list component. :) Seriously, no one else has this problem?

I submitted this a while back. It's not a problem for my users because they finally got enough data on the report that the list is trying to go past the end of the page but it just doesn't work. Steps to recreate: 1) create a list component on the summary page 2) query a table with enough rows that you know it goes past the end of the page 3) see how it cuts off. This is about to be a big issue for me. I can't tell the users to only select half the data so it fits. Help!

For reference, this is the actual query that returns the right data but without the parameters. I'm trying to do the same thing but with the parameters HCD-system, HCD-divreg and HCD-district. (select tblUnit from CAD, UNITS where CAD.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') union (select tblUnit from DIABETES, UNITS where DIABETES.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') union (select tblUnit from HEART, UNITS where HEART.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') union (select tblUnit from HLD, UNITS where HLD.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') union (select tblUnit from HTN, UNITS where HTN.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') union (select tblUnit from RESPIRATORY, UNITS where RESPIRATORY.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') union (select tblUnit from SEIZURES, UNITS where SEIZURES.tblUnit = UNITS.unitdesc and UNITS.system = 'UTMB' and UNITS.divreg = 'OUTPATIENT' and UNITS.district = 'SAN ANTONIO') order by tblUnit;

Here's what's in there right now and it gets an Error but the log is describing index out of bounds problem: (select CAD.tblUnit from CAD, UNITS where CAD.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) union (select DIABETES.tblUnit from DIABETES, UNITS where DIABETES.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) union (select HEART.tblUnit from HEART, UNITS where HEART.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) union (select HLD.tblUnit from HLD, UNITS where HLD.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) union (select HTN.tblUnit from HTN, UNITS where HTN.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) union (select RESPIRATORY.tblUnit from RESPIRATORY, UNITS where RESPIRATORY.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) union (select SEIZURES.tblUnit from SEIZURES, UNITS where SEIZURES.tblUnit = UNITS.unitdesc and $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) order by tblUnit;

Weird, not sure where the other half of those enclosures went to. I'll go double check it.

This is my original query at the bottom of 4 cascading input controls. select unitdesc from UNITS where $X{IN,system,HCD-system} and $X{IN,divreg,HCD-divreg} and $X{IN,district,HCD-district} order by unitdesc; It pulls all the unit descriptions like it's supposed to but now the users are only wanting to get a list of unit descriptions that have actual data. So now I have to query each table and pull back the list of descriptions from there. Here's what I tried to do but get an Error box (Just says error): (select CAD.tblUnit from CAD, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district} union (select DIABETES.tblUnit from DIABETES, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district} union (select HEART.tblUnit from HEART, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district} union (select HLD.tblUnit from HLD, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district} union (select HTN.tblUnit from HTN, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district} union (select RESPIRATORY.tblUnit from RESPIRATORY, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district} union (select SEIZURES.tblUnit from SEIZURES, UNITS where $X{IN,UNITS.system,HCD-system} and $X{IN,UNITS.divreg,HCD-divreg} and $X{IN,UNITS.district,HCD-district}) order by tblUnit;

reloading from the new JS 3.7 CE instead of running a patched RC did the trick. I guess there is something to be said about doing it the right way the first time. :)

maybe it's just a good time to upgrade to 3.7 from the RC. :)

Here's the report. I'm not sure how to export the input control. At first I had it tied to a separate component that was the list of values then for testing I made it create a local list inside the input control.

Here's a small shockwave video of what it's doing. When I click on Reset you can see it pop up for a second then disappear.

Thought about that and changed my labels to match the values exactly (SLCDental, SLCMental) and it's still doing the same thing. I'm going to delete the list and go back and create them using Jasperserver instead of iReport through the repository viewer.