bubba72834
-
Posts
2 -
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
Posts posted by bubba72834
-
-
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 dispnameFROM bload INNER JOIN emp
ON bload.SID=emp.EMP_NOWHERE 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 dispnameFROM bload INNER JOIN emp
ON bload.SID=emp.EMP_NOWHERE 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
Date range parameters
in Products
Posted
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...
Post Edited by bubba guiteau at 12/17/08 22:24