disha.patel

@akshaytawar Can you please explain more on that?

I have a GROUP and TOTAL named column in database table. Group contains 4 record like GROUP A , GROUP B, GROUP C and GROUP D. Each GROUP relates to some amount. I want to display like below example in a different column. GROUP A | GROUP B | GROUP C | GROUP D 20 | 30 | 40 | 50 Please help me. Thank you!

I tried it. But in my case in same row if I am using two diffrent variable TOTAL field like "$F{PAYMENT}.equals( "cash" ) ? true : false" and TOTAL field like "$F{PAYMENT}.equals( "cashrefund" ) ? true : false" , it is not printing in same line.

I am using jasper studio and I have Field called PAYMENT and TOTAL. PAYMENT is string type which contains values like Cash, Card, Cash Refund and Card Refund. I am using "print when expression" with TOTAL field like "$F{PAYMENT}.equals( "cashrefund" ) ? true : false". When a condition is true I am getting value, but if the condition is false, it includes a blank record in the report. How can I avoid printing blank record in the report? Is it possible if TOTAL prints only where there is any value?

Thank you for the reply Hozawa. I tried changing the layout to horizontal. It is working.

I want to make the format of the column display. Column is like : card cash free I want to display like: card cash free Below is the code: <band height="15"> <textField pattern="" isBlankWhenNull="false"> <reportElement key="textField-20" mode="Opaque" x="110" y="0" width="225" height="15" forecolor="#000000" backcolor="#FFFFFF" uuid="5efa72ed-9373-4517-ac98-3b0b731b3069"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#FFFFFF"/> </box> <textElement textAlignment="Left" verticalAlignment="Top" rotation="None"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[str("transpayment." + (String)field_PAYMENT.getValue())]]></textFieldExpression> </textField> <textField pattern="" isBlankWhenNull="false"> <reportElement key="textField-3" mode="Opaque" x="435" y="0" width="100" height="15" forecolor="#000000" backcolor="#FFFFFF" uuid="0e01585f-785f-4dd4-b1cb-33d78d86580f"/> <box> <topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/> <bottomPen lineWidth="0.0" lineColor="#000000"/> <rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#FFFFFF"/> </box> <textElement textAlignment="Right" verticalAlignment="Top" rotation="None"> <font fontName="SansSerif" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/> <paragraph lineSpacing="Single"/> </textElement> <textFieldExpression><![CDATA[com.openbravo.format.Formats.CURRENCY.formatValue($F{TOTAL})]]></textFieldExpression> </textField> </band>

SELECT CLOSEDCASH.HOST, CLOSEDCASH.HOSTSEQUENCE, CLOSEDCASH.MONEY, CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND, PAYMENTS.PAYMENT, SUM(PAYMENTS.TOTAL) AS TOTAL FROM CLOSEDCASH, PAYMENTS WHERE CLOSEDCASH.MONEY = PAYMENTS.MONEY GROUP BY CLOSEDCASH.HOST, CLOSEDCASH.MONEY, CLOSEDCASH.DATESTART, CLOSEDCASH.DATEEND, PAYMENTS.PAYMENT ORDER BY CLOSEDCASH.HOST, CLOSEDCASH.DATESTART Here is my query if I can get help with it. I don't know why it is saying unicentaopos.CLOSEDCASH' doesn't exist. I have that table in my database. Please advise. Thank you!

Thank you Hozawa. I removed error . I am getting other error as below now net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: customers_list. at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:536) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$17(ReportController.java:511) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:429) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: customers_list. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:456) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at java.lang.Thread.run(Unknown Source) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'unicentaopos.CUSTOMERS' doesn't exist 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:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 more

Hi I am getting below error. Please advise to resolve it. Thank you! net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: closedpos. at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:536) at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$17(ReportController.java:511) at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:429) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: closedpos. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:456) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:578) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135) at java.lang.Thread.run(Unknown Source) 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 '.PAYMENT, SUM(PAYMENTS.TOTAL), AS TOTAL FROM CLOSEDCASH, PAYMENTS, RECEIPTS, TA' at line 9 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:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 more