bubba72834

Date range parameters

bubba72834 replied to bubba72834's topic in Products

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
Date range parameters

bubba72834 posted a topic in Products

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> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{dispname}]]></textFieldExpression> </textField> <staticText> <reportElement x="0" y="29" width="25" height="20"/> <textElement textAlignment="Center"> </textElement> <text><![CDATA[PRO #]]></text> </staticText> <staticText> <reportElement x="25" y="29" width="45" height="20"/> <textElement textAlignment="Left"> </textElement> <text><![CDATA[ Booked]]></text> </staticText> <staticText> <reportElement x="70" y="29" width="190" height="20"/> <textElement textAlignment="Left"> </textElement> <text><![CDATA[Customer]]></text> </staticText> <staticText> <reportElement x="260" y="29" width="95" height="20"/> <textElement textAlignment="Left"> </textElement> <text><![CDATA[Origin]]></text> </staticText> <staticText> <reportElement x="355" y="29" width="95" height="20"/> <textElement textAlignment="Left"> </textElement> <text><![CDATA[Destination]]></text> </staticText> <staticText> <reportElement x="450" y="29" width="190" height="20"/> <textElement textAlignment="Left"> </textElement> <text><![CDATA[Carrier]]></text> </staticText> <staticText> <reportElement x="640" y="29" width="50" height="20"/> <textElement> </textElement> <text><![CDATA[Net Pay]]></text> </staticText> <staticText> <reportElement x="690" y="29" width="50" height="20"/> <textElement> </textElement> <text><![CDATA[Carrier Pay]]></text> </staticText> <staticText> <reportElement x="740" y="29" width="50" height="20"/> <textElement> </textElement> <text><![CDATA[Profit]]></text> </staticText> </band> </groupHeader> <groupFooter> <band height="50"> <staticText> <reportElement x="0" y="5" width="53" height="20"/> <textElement> </textElement> <text><![CDATA[TOTAL]]></text> </staticText> <textField pattern="¤ #,##0.00" hyperlinkType="None"> <reportElement x="640" y="5" width="50" height="20"/> <textElement> </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> </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> </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> </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> </textElement> <textFieldExpression class="java.util.Date"><![CDATA[$F{datebooked}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="70" y="4" width="190" height="20"/> <textElement> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{customer}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="260" y="4" width="95" height="20"/> <textElement> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{origin}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="355" y="4" width="95" height="20"/> <textElement> </textElement> <textFieldExpression class="java.lang.String"><![CDATA[$F{destination}]]></textFieldExpression> </textField> <textField hyperlinkType="None"> <reportElement x="450" y="4" width="190" height="20"/> <textElement> </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> </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> </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> </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

Sign In

bubba72834

Posts

Joined

Last visited

Content Type

Profiles

Forum

Events

Featured Visualizations

Knowledge Base

Documentation (PDF Downloads)

Blog

Documentation (Test Area)

Documentation

Dr. Jaspersoft Webinar Series

Downloads

Everything posted by bubba72834

Date range parameters

Date range parameters

Activity

Products

Explore