Date range parameters

bubba72834 · December 16, 2008

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

dprogrammer · December 17, 2008

Can you use >= and <= instead of Between? I use this code for my reports and it works.

CREATE_DAT>=$P{StartDate} And

CREATE_DAT<=$P{EndDate}

mdahlman · December 17, 2008

bubba72834,

You define the parameter StartDate with a default value in terms of itself: $P{StartDate}.after( java.util.Date )

This isn't going to work. Remove this part of the problem by using a correct default date like one of these:
new java.util.Date()
new SimpleDateFormat("yyyy-MM-dd").parse("2008-01-01")

You've also got problems with your parameter syntax: BETWEEN DATE'$P!{StartDate}' AND DATE'$P{EndDate}'

You were probably trying a few different things in succession, but you should use $P{param} for both instead of $P!{param}.

Regards,
Matt

bubba72834 · December 17, 2008

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

Sign In

Date range parameters

Recommended Posts

bubba72834

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

dprogrammer

Link to comment

Share on other sites

mdahlman

Link to comment

Share on other sites

bubba72834

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore