tuanfgy

Hi all, please help me to fix this error. When I export report that include grouping level in report definition, it has many blank pages. So I don't want to have these blank pages. I'm using jasper report 3.7 the .jrxml file is in the attachment. I attach 2 jrxml file to you compare between use "Grouping level" and don't use "Grouping Level". Is there any infomation you want to know, please let me know. thanks, Tuan

thanks Sanda for your support. The problems are solve. It comes from permission of DB.

Hi Sanda, I don't know which log is stacktrace. I don't is this or not? Could you please review it. 12/05/2012 10:10:57 [iNFO] Added trigger: Trigger 'vsreports.jobid=40': triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: Wed Dec 05 10:10:57 ICT 2012 12/05/2012 10:10:58 [iNFO] Job vsreports.jobid=40 threw a JobExecutionException: org.quartz.JobExecutionException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) [see nested exception: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})] at com.lucent.vital.vq.reportserver.quartz.QuartzReportJob.execute(QuartzReportJob.java:122) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546) Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) 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.JRFillSubreport.getParameterValues(JRFillSubreport.java:552) at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:460) at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateParameterValues(JRFillSubreport.java:359) at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:339) at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:263) at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:258) at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:499) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2026) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:757) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:269) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:127) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:942) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:860) at net.sf.jasperreports.engine.fill.AsynchronousFillHandle$ReportFiller.run(AsynchronousFillHandle.java:148) at java.lang.Thread.run(Thread.java:662) Caused by: com.lucent.vital.vq.exceptions.vqSQLException: BEGIN CREATE TABLE VART_TMP_1354677058073(Available FLOAT,Color VARCHAR(20),DateTime DATETIME,FullNamebyFirstName VARCHAR(100),FullNamebyLastName VARCHAR(100),ItemCode VARCHAR(60),ItemDescription VARCHAR(1024),Make VARCHAR(80),Model VARCHAR(80),PaymentType VARCHAR(20), VARCHAR(10),Total FLOAT,UnitCost FLOAT,UnitPrice FLOAT);INSERT INTO VART_TMP_1354677058073 SELECT Avail AS Available,Color AS Color,null AS DateTime,null AS FullNamebyFirstName,null AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,null AS PaymentType, AS ,Total AS Total,Cost AS UnitCost,Price AS UnitPrice FROM INV_TABLE;INSERT INTO VART_TMP_1354677058073 SELECT null AS Available,Color AS Color,Dttm AS DateTime,FNAME + ' ' + MI + '. ' + LNAME AS FullNamebyFirstName,LNAME + ', ' + FNAME + ' ' + MI + '.' AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,BillingType AS PaymentType, AS ,null AS Total,Cost AS UnitCost,Price AS UnitPrice FROM TRANS_BILLING_VIEW WHERE TransType IN (0) AND FNAME + ' ' + MI + '. ' + LNAME LIKE 'Tuan' AND LNAME + ', ' + FNAME + ' ' + MI + '.' LIKE 'Vuong' AND (Dttm>={ts '2012-12-03 00:00:00'} AND Dttm<{ts '2012-12-06 00:00:00'}) AND (CONVERT(DATETIME, CONVERT(VARCHAR, Dttm, 1))={ts '2012-11-26 00:00:00'}) AND FiscalQuarter=3; END: CREATE TABLE permission denied in database 'DUED'. at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:624) at com.lucent.vital.vq.report.exec.SectionExec.getMSSQLSelect(SectionExec.java:1854) at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1826) at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1795) at DUED32Test322_1354677058519_410226.evaluate(DUED32Test322_1354677058519_410226:449) at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:182) ... 17 more Caused by: java.sql.SQLException: CREATE TABLE permission denied in database 'DUED'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546) at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723) at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160) at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:620) ... 22 more 12/05/2012 10:10:58 [iNFO] Finalized trigger: Trigger 'vsreports.jobid=40': triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: null 12/05/2012 10:11:09 [iNFO] Cron schedule upgrade job triggered at Wed Dec 05 10:11:09 ICT 2012 12/05/2012 10:14:36 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite shutting down. 12/05/2012 10:14:36 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite paused. 12/05/2012 10:14:36 [WARN] Database connection shutdown unsuccessful. java.sql.SQLException: There is no DataSource named 'vnmaster' at org.quartz.utils.DBConnectionManager.shutdown(DBConnectionManager.java:136) at org.quartz.impl.jdbcjobstore.JobStoreSupport.shutdown(JobStoreSupport.java:651) at org.quartz.core.QuartzScheduler.shutdown(QuartzScheduler.java:610) at org.quartz.core.QuartzScheduler.shutdown(QuartzScheduler.java:556) at org.quartz.impl.StdScheduler.shutdown(StdScheduler.java:198) at com.lucent.vital.scheduler.VSQuartzScheduler.stop(VSQuartzScheduler.java:258) at com.lucent.vital.scheduler.VSQuartzScheduler.main(VSQuartzScheduler.java:385) 12/05/2012 10:14:36 [iNFO] Scheduler shutdown 12/05/2012 10:14:36 [iNFO] Scheduler un-bound from name 'Quartz Scheduler_$_VitalSuite' in RMI registry 12/05/2012 10:14:36 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite shutdown complete. 12/05/2012 10:14:43 [iNFO] Scheduler bound to RMI registry under name 'Quartz Scheduler_$_VitalSuite' 12/05/2012 10:14:43 [iNFO] Initialized Scheduler Signaller of type: class org.quartz.core.SchedulerSignalerImpl 12/05/2012 10:14:43 [iNFO] Quartz Scheduler v.1.7.3 created. 12/05/2012 10:14:43 [iNFO] JobFactory set to: com.lucent.vital.scheduler.VSScheduleJobFactory@12452e8 12/05/2012 10:14:43 [iNFO] New VSSchedulerListener created for Quartz Scheduler 12/05/2012 10:14:43 [iNFO] Connecting to vnmaster database on VSDEV-94-36 12/05/2012 10:14:45 [iNFO] Job execution threads will use class loader of thread: main 12/05/2012 10:14:45 [iNFO] Connecting to vnmaster database on VSDEV-94-36 12/05/2012 10:14:45 [iNFO] Enabling jobs for role VNMaster 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAMaster 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VHMaster 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VNReptServer 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAReptServer 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VHReptServer 12/05/2012 10:14:45 [iNFO] Enabling jobs for role VNAggr 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAAggr 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VHPost 12/05/2012 10:14:45 [iNFO] Enabling jobs for role VNPoller 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAHConfig 12/05/2012 10:14:45 [iNFO] Disabling jobs for role VAPost 12/05/2012 10:14:45 [iNFO] Enabling jobs for role VARTMaster 12/05/2012 10:14:45 [iNFO] JDBCJobStore threads will inherit ContextClassLoader of thread: main 12/05/2012 10:14:45 [iNFO] Using thread monitor-based data access locking (synchronization). 12/05/2012 10:14:45 [iNFO] Removed 1 Volatile Trigger(s). 12/05/2012 10:14:45 [iNFO] Removed 1 Volatile Job(s). 12/05/2012 10:14:45 [iNFO] class com.lucent.vital.scheduler.VSJDBCJobStore initialized. 12/05/2012 10:14:45 [iNFO] Quartz scheduler 'Quartz Scheduler' initialized from default resource file in Quartz package: 'quartz.properties' 12/05/2012 10:14:45 [iNFO] Quartz scheduler version: 1.7.3 12/05/2012 10:14:45 [iNFO] Freed 0 triggers from 'acquired' / 'blocked' state. 12/05/2012 10:14:45 [iNFO] Recovering 0 jobs that were in-progress at the time of the last shut-down. 12/05/2012 10:14:45 [iNFO] Recovery complete. 12/05/2012 10:14:45 [iNFO] Removed 0 'complete' triggers. 12/05/2012 10:14:45 [iNFO] Removed 0 stale fired job entries. 12/05/2012 10:14:45 [iNFO] Monitoring cron file D:PROGRA~2VSCommonprogramcron.txt 12/05/2012 10:14:45 [iNFO] Added trigger: Trigger 'VSCronManagement.cronReader': triggerClass: 'org.quartz.SimpleTrigger isVolatile: true calendar: 'null' misfireInstruction: 0 nextFireTime: Wed Dec 05 10:14:45 ICT 2012 12/05/2012 10:14:45 [iNFO] Scheduler Quartz Scheduler_$_VitalSuite started. 12/05/2012 10:14:46 [iNFO] Cron schedule upgrade job triggered at Wed Dec 05 10:14:46 ICT 2012 12/05/2012 10:15:43 [iNFO] Added trigger: Trigger 'vsreports.jobid=41': triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: Wed Dec 05 10:15:43 ICT 2012 12/05/2012 10:15:48 [iNFO] Job vsreports.jobid=41 threw a JobExecutionException: org.quartz.JobExecutionException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) [see nested exception: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})] at com.lucent.vital.vq.reportserver.quartz.QuartzReportJob.execute(QuartzReportJob.java:122) at org.quartz.core.JobRunShell.run(JobRunShell.java:199) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546) Caused by: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) 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.JRFillSubreport.getParameterValues(JRFillSubreport.java:552) at net.sf.jasperreports.engine.fill.JRFillSubreport.getParameterValues(JRFillSubreport.java:460) at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateParameterValues(JRFillSubreport.java:359) at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluateSubreport(JRFillSubreport.java:339) at net.sf.jasperreports.engine.fill.JRFillSubreport.evaluate(JRFillSubreport.java:263) at net.sf.jasperreports.engine.fill.JRFillElementContainer.evaluate(JRFillElementContainer.java:258) at net.sf.jasperreports.engine.fill.JRFillBand.evaluate(JRFillBand.java:499) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2026) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:757) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:269) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:127) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:942) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:860) at net.sf.jasperreports.engine.fill.AsynchronousFillHandle$ReportFiller.run(AsynchronousFillHandle.java:148) at java.lang.Thread.run(Thread.java:662) Caused by: com.lucent.vital.vq.exceptions.vqSQLException: BEGIN CREATE TABLE VART_TMP_1354677346364(Available FLOAT,Color VARCHAR(20),DateTime DATETIME,FullNamebyFirstName VARCHAR(100),FullNamebyLastName VARCHAR(100),ItemCode VARCHAR(60),ItemDescription VARCHAR(1024),Make VARCHAR(80),Model VARCHAR(80),PaymentType VARCHAR(20), VARCHAR(10),Total FLOAT,UnitCost FLOAT,UnitPrice FLOAT);INSERT INTO VART_TMP_1354677346364 SELECT Avail AS Available,Color AS Color,null AS DateTime,null AS FullNamebyFirstName,null AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,null AS PaymentType, AS ,Total AS Total,Cost AS UnitCost,Price AS UnitPrice FROM INV_TABLE;INSERT INTO VART_TMP_1354677346364 SELECT null AS Available,Color AS Color,Dttm AS DateTime,FNAME + ' ' + MI + '. ' + LNAME AS FullNamebyFirstName,LNAME + ', ' + FNAME + ' ' + MI + '.' AS FullNamebyLastName,Code AS ItemCode,Description AS ItemDescription,Make AS Make,Model AS Model,BillingType AS PaymentType, AS ,null AS Total,Cost AS UnitCost,Price AS UnitPrice FROM TRANS_BILLING_VIEW WHERE TransType IN (0) AND FNAME + ' ' + MI + '. ' + LNAME LIKE 'Tuan' AND LNAME + ', ' + FNAME + ' ' + MI + '.' LIKE 'Vuong' AND (Dttm>={ts '2012-12-03 00:00:00'} AND Dttm<{ts '2012-12-06 00:00:00'}) AND (CONVERT(DATETIME, CONVERT(VARCHAR, Dttm, 1))={ts '2012-11-26 00:00:00'}) AND FiscalQuarter=3; END: CREATE TABLE permission denied in database 'DUED'. at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:624) at com.lucent.vital.vq.report.exec.SectionExec.getMSSQLSelect(SectionExec.java:1854) at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1826) at com.lucent.vital.vq.report.exec.SectionExec.getSQLSelect(SectionExec.java:1795) at DUED32Test322_1354677347555_324703.evaluate(DUED32Test322_1354677347555_324703:449) at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:182) ... 17 more Caused by: java.sql.SQLException: CREATE TABLE permission denied in database 'DUED'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546) at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:723) at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1160) at com.lucent.vital.vq.report.AggrDataType.getSQLStatement(AggrDataType.java:620) ... 22 more 12/05/2012 10:15:48 [iNFO] Finalized trigger: Trigger 'vsreports.jobid=41': triggerClass: 'org.quartz.SimpleTrigger isVolatile: false calendar: 'null' misfireInstruction: 1 nextFireTime: null

Hi Sanda, I did acccording to you. But I got this error: org.quartz.JobExecutionException: net.sf.jasperreports.engine.JRException: Duplicate declaration of variable : 1ExecutionTime [see nested exception: net.sf.jasperreports.engine.JRException: Duplicate declaration of variable : 1ExecutionTime] I think the "1ExecutionTime" was declared some where. I'm new with JR so that I don't know many. Could you please help to check my code and show me where is not correct JRDesignVariable var_1ExecutionTime = new JRDesignVariable();var_1ExecutionTime.setName("1ExecutionTime");subparm = new JRDesignSubreportParameter();subparm.setName("QUERY");expression = new JRDesignExpression();expression.setValueClass(java.lang.String.class);expression.addParameterChunk(section.getName());expression.addTextChunk(".getArtSection().getSQLSelect(");expression.addVariableChunk(section.getName()+"_WHERE");expression.addTextChunk(",");//expression.addVariableChunk("1ExecutionTime");expression.addTextChunk(")");// expression.addVariableChunk(section.getName()+"_QUERY");subparm.setExpression(expression);subrpt.addParameter(subparm);addVariable(var_1ExecutionTime); All code of this file is in attachment. Thank you for your support.Tuan

Hi all, Please help me to fix this bug. I got this error message when create a report with union data type using Jasper Report org.quartz.JobExecutionException: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime}) [see nested exception: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression : Source text : $P{Test}.getArtSection().getSQLSelect($V{Test_WHERE},$V{1ExecutionTime})] I think the error occur because the below code. subparm = new JRDesignSubreportParameter(); subparm.setName("QUERY"); expression = new JRDesignExpression(); expression.setValueClass(java.lang.String.class); expression.addParameterChunk(section.getName()); expression.addTextChunk(".getArtSection().getSQLSelect("); expression.addVariableChunk(section.getName()+"_WHERE"); expression.addTextChunk(","); expression.addVariableChunk("1ExecutionTime"); expression.addTextChunk(")"); subparm.setExpression(expression); subrpt.addParameter(subparm); Do you see any problem with this code. I you need to see all code please find the attachment. Thanks in advance