bubba72834

Matt, Thanks for your reply. You are correct, the parameter fields are screwed up because I had been trying a number of things, including the two suggestions you made in your post... still to no avail. I am not sure, but I think the problem is in the way the date is being passed to the DB server, which expects it as DATE'yyyy-MM-dd'. The console output is beyond my understanding, but clearly the jdbc-odbc bridge is complaining. Another message I get every time has to do with the windows API and AWT thread (whatever that means). More below... Code: Post Edited by bubba guiteau at 12/17/08 22:24

Hello everyone, I am considering JasperServer/IReport as a reporting solution for a customer of mine. They are using a rather obscure solution based on NexusDB; I am using the ODBC/JDBC bridge to pull data from said database. So far, I have found IReport quite easy to work with, but am stumped by a date parameter problem. Being new to jasperreports and not knowing the first thing about java doesn't help, either. Hopefuly, one of you guys can give me some answers. I am running the following SQL query against the server without any problems: SELECT bload.PRO_NO AS pro_no, bload.CUSTOMER AS customer, bload.CARRIER AS carrier, bload.SUBTOT AS subtotal, bload.GROSS_PAY AS grosspay, bload.CAR_PAY AS carrierpay, bload.BROK AS profit, bload.ADVANCE AS advance, bload.NETPAY AS netpay, bload.CREATE_DAT AS datebooked, bload.PICKCITY+', '+bload.PICKST AS origin, bload.DROPCITY+', '+bload.DROPST AS destination, emp.FNAME+' '+emp.LNAME as dispname FROM bload INNER JOIN emp ON bload.SID=emp.EMP_NO WHERE bload.CREATE_DAT BETWEEN DATE'2008-11-01' AND DATE'2008-11-30' Now, if I change that last line to make use of the two parameters I created brakes the report: WHERE bload.CREATE_DAT BETWEEN DATE'$P!{StartDate}' AND DATE'$P{EndDate}' I have read thru every existing post regarding date parameters and have tried every suggestion, but to no avail. The full XML definition of the report follows: <?xml version="1.0" encoding="UTF-8"?> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Dispatcher Revenue" pageWidth="842" pageHeight="595" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"> <parameter name="StartDate" class="java.util.Date"> <defaultValueExpression><![CDATA[$P{StartDate}.after( java.util.Date )]]></defaultValueExpression> </parameter> <parameter name="EndDate" class="java.util.Date"> <defaultValueExpression><![CDATA[$P{StartDate}.before( java.util.Date )]]></defaultValueExpression> </parameter> <queryString> <![CDATA[sELECT bload.PRO_NO AS pro_no, bload.CUSTOMER AS customer, bload.CARRIER AS carrier, bload.SUBTOT AS subtotal, bload.GROSS_PAY AS grosspay, bload.CAR_PAY AS carrierpay, bload.BROK AS profit, bload.ADVANCE AS advance, bload.NETPAY AS netpay, bload.CREATE_DAT AS datebooked, bload.PICKCITY+', '+bload.PICKST AS origin, bload.DROPCITY+', '+bload.DROPST AS destination, emp.FNAME+' '+emp.LNAME as dispname FROM bload INNER JOIN emp ON bload.SID=emp.EMP_NO WHERE bload.CREATE_DAT BETWEEN DATE'$P!{StartDate}' AND DATE'$P{EndDate}']]> </queryString> <field name="pro_no" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="customer" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="carrier" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="subtotal" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="grosspay" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="carrierpay" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="profit" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="advance" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="netpay" class="java.lang.Double"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="datebooked" class="java.util.Date"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="origin" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="destination" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <field name="dispname" class="java.lang.String"> <fieldDescription><![CDATA[]]></fieldDescription> </field> <variable name="dispatcher_nettotal" class="java.lang.Double" resetType="Group" resetGroup="dispatcher" calculation="Sum"> <variableExpression><![CDATA[$F{netpay}]]></variableExpression> <initialValueExpression><![CDATA[]]></initialValueExpression> </variable> <variable name="dispatcher_cartotal" class="java.lang.Double" resetType="Group" resetGroup="dispatcher" calculation="Sum"> <variableExpression><![CDATA[$F{carrierpay}]]></variableExpression> </variable> <variable name="dispatcher_profittotal" class="java.lang.Double" resetType="Group" resetGroup="dispatcher" calculation="Sum"> <variableExpression><![CDATA[$F{profit}]]></variableExpression> </variable> <group name="dispatcher" isStartNewPage="true"> <groupExpression><![CDATA[$F{dispname}]]></groupExpression> <groupHeader> <band height="49"> <textField hyperlinkType="None"> <reportElement x="0" y="0" width="100" height="20"/> <textElement> <font isBold="true"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{dispname}]]></textFieldExpression> </textField> <staticText> <reportElement x="0" y="29" width="25" height="20"/> <textElement textAlignment="Center"> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[PRO #]]></text> </staticText> <staticText> <reportElement x="25" y="29" width="45" height="20"/> <textElement textAlignment="Left"> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[ Booked]]></text> </staticText> <staticText> <reportElement x="70" y="29" width="190" height="20"/> <textElement textAlignment="Left"> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Customer]]></text> </staticText> <staticText> <reportElement x="260" y="29" width="95" height="20"/> <textElement textAlignment="Left"> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Origin]]></text> </staticText> <staticText> <reportElement x="355" y="29" width="95" height="20"/> <textElement textAlignment="Left"> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Destination]]></text> </staticText> <staticText> <reportElement x="450" y="29" width="190" height="20"/> <textElement textAlignment="Left"> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Carrier]]></text> </staticText> <staticText> <reportElement x="640" y="29" width="50" height="20"/> <textElement> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Net Pay]]></text> </staticText> <staticText> <reportElement x="690" y="29" width="50" height="20"/> <textElement> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Carrier Pay]]></text> </staticText> <staticText> <reportElement x="740" y="29" width="50" height="20"/> <textElement> <font size="8" isBold="true" isUnderline="true"/> </textElement> <text><![CDATA[Profit]]></text> </staticText> </band> </groupHeader> <groupFooter> <band height="50"> <staticText> <reportElement x="0" y="5" width="53" height="20"/> <textElement> <font size="8" isBold="true"/> </textElement> <text><![CDATA[TOTAL]]></text> </staticText> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="640" y="5" width="50" height="20"/> <textElement> <font size="8" isBold="true"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$V{dispatcher_nettotal}]]></textFieldExpression> </textField> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="690" y="5" width="50" height="20"/> <textElement> <font size="8" isBold="true"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$V{dispatcher_cartotal}]]></textFieldExpression> </textField> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="740" y="5" width="50" height="20"/> <textElement> <font size="8" isBold="true"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$V{dispatcher_profittotal}]]></textFieldExpression> </textField> </band> </groupFooter> </group> <background> <band/> </background> <title> <band height="79"/> </title> <pageHeader> <band height="35"/> </pageHeader> <detail> <band height="24"> <textField pattern="###0" hyperlinkType="None"> <reportElement x="0" y="4" width="25" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$F{pro_no}]]></textFieldExpression> </textField> <textField pattern="MM/dd/yyyy" hyperlinkType="None"> <reportElement x="25" y="4" width="45" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.util.Date"><![CDATA[$F{datebooked}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="70" y="4" width="190" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{customer}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="260" y="4" width="95" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{origin}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="355" y="4" width="95" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{destination}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="450" y="4" width="190" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{carrier}]]></textFieldExpression> </textField> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="640" y="4" width="50" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$F{netpay}]]></textFieldExpression> </textField> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="690" y="4" width="50" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$F{carrierpay}]]></textFieldExpression> </textField> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="740" y="4" width="50" height="20"/> <textElement> <font size="8"/> </textElement> <textFieldExpression class="java.lang.Double"><![CDATA[$F{profit}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="54"/> </pageFooter> <summary> <band height="42"/> </summary> </jasperReport> Could someone please explain where the problem is or, better yet, offer a solution? Best regards Post Edited by bubba guiteau at 12/16/08 21:16