Jump to content
Changes to the Jaspersoft community edition download ×

How to create Year to date function in jaspersoft server for dashboard


felix.yiu

Recommended Posts

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

HI This JRXML allows to create data using the calendar class,

    <parameter name="cal" class="java.util.Calendar" isForPrompting="false">
        <defaultValueExpression><![CDATA[Calendar.getInstance()]]></defaultValueExpression>
    </parameter>

 

I am sute you can use this to get the FirstDayOFThisYear

 

(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_YEAR, 1)
)
? null : $P{cal}.getTime()

 

 

 

    <parameter name="cal" class="java.util.Calendar" isForPrompting="false">
        <defaultValueExpression><![CDATA[Calendar.getInstance()]]></defaultValueExpression>
    </parameter>

 

 

______________________________________________________________________________________

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.3.1.final using JasperReports Library version 6.3.1  -->
<!-- 2017-10-19T13:06:30 -->
<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="dates" language="groovy" pageWidth="595" pageHeight="842" whenNoDataType="BlankPage" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="4d748c5c-baf3-4519-8e7b-0a775a31aad9">
    <property name="ireport.zoom" value="1.5"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <parameter name="runDate" class="java.util.Date">
        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <parameter name="cal" class="java.util.Calendar" isForPrompting="false">
        <defaultValueExpression><![CDATA[Calendar.getInstance()]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[select id from categories]]>
    </queryString>
    <field name="id" class="java.lang.Integer"/>
    <variable name="yesterday" class="java.util.Date">
        <variableExpression><![CDATA[$P{runDate}-1]]></variableExpression>
        <initialValueExpression><![CDATA[$P{runDate}-1]]></initialValueExpression>
    </variable>
    <variable name="lastMonth" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1)
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1)||

    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="lastDayOfLastMonth" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="lastWeek" class="java.util.Date">
        <variableExpression><![CDATA[$P{runDate}-7]]></variableExpression>
        <initialValueExpression><![CDATA[$P{runDate}-7]]></initialValueExpression>
    </variable>
    <variable name="firstDayOfLastMonth" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="lastYear" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.YEAR, -1)
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.YEAR, -1)
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="lastSunday" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.DAY_OF_MONTH, $P{cal}.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY ? -7 : Calendar.SUNDAY - $P{cal}.get(Calendar.DAY_OF_WEEK))
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(     $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||     $P{cal}.add(Calendar.DAY_OF_MONTH, $P{cal}.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY ? -7 : Calendar.SUNDAY - $P{cal}.get(Calendar.DAY_OF_WEEK)) ) ? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="FirstDayOFLASTYear" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.YEAR, -1)||

    $P{cal}.set(Calendar.DAY_OF_YEAR, 1)
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.YEAR, -1)||

    $P{cal}.set(Calendar.DAY_OF_YEAR, 1)
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="LastDayOFLASTYear" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.YEAR, -1)||

    $P{cal}.set(Calendar.DAY_OF_YEAR, $P{cal}.getActualMaximum(Calendar.DAY_OF_YEAR))
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.YEAR, -1)||

    $P{cal}.set(Calendar.DAY_OF_YEAR, $P{cal}.getActualMaximum(Calendar.DAY_OF_YEAR))
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="FirstDayOFThisYear" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_YEAR, 1)
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_YEAR, 1)
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="firstDayOfTheMonth" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="lastDayOfTheMonth" class="java.util.Date">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()]]></variableExpression>
        <initialValueExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()]]></initialValueExpression>
    </variable>
    <variable name="WeeKOFYEAR" class="java.lang.String">
        <variableExpression><![CDATA[(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||


    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.WEEK_OF_YEAR))
)
? null : $P{cal}.getTime()]]></variableExpression>
    </variable>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="350" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="316" height="34" uuid="b99a7eee-147c-4d0b-9b43-2883648157d1"/>
                <textElement>
                    <font fontName="Arial" size="18" isBold="true"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Date calculation demo report]]></text>
            </staticText>
            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">
                <reportElement x="129" y="54" width="130" height="20" forecolor="#666666" uuid="1ccfe108-5021-45cf-a74f-ff224a229ac2"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{yesterday}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="54" width="129" height="20" forecolor="#666666" uuid="a60cc2df-2738-43d3-ad7c-e04b8904dd1e"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Yesterday]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="34" width="129" height="20" uuid="49ae7aaf-763d-4a0b-addd-c23dc7fadf93"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Run Date]]></text>
            </staticText>
            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">
                <reportElement x="129" y="34" width="130" height="20" uuid="6a24e99e-ca58-4fbe-883e-ad329b190d2c"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$P{runDate}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">
                <reportElement x="129" y="114" width="130" height="20" forecolor="#666666" uuid="5be98499-8117-4510-91eb-aa6a5083f92f"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{lastMonth}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="114" width="129" height="20" forecolor="#666666" uuid="19c1330f-9990-4960-b60a-49ce1154b4e5"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Last Month]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="154" width="129" height="20" forecolor="#666666" uuid="977f8a33-da4f-4d1f-bb06-e186af032c32"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Last Day of Last Month]]></text>
            </staticText>
            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">
                <reportElement x="129" y="154" width="130" height="20" forecolor="#666666" uuid="f51f8c39-c1ed-4485-b3bc-aa51deb99632"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{lastDayOfLastMonth}]]></textFieldExpression>
            </textField>
            <textField pattern="dd/MM/yyyy">
                <reportElement x="129" y="74" width="130" height="20" forecolor="#666666" uuid="2f277bbc-158e-42b0-9d1e-e7d8899aa8bb"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{lastWeek}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="74" width="129" height="20" forecolor="#666666" uuid="9ebaba65-ebf2-408c-a7ee-b0cf6586893d"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Last Week]]></text>
            </staticText>
            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">
                <reportElement x="129" y="134" width="130" height="20" forecolor="#666666" uuid="73516181-8fc9-4e0f-ad2d-34c2c3a6c356"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{firstDayOfLastMonth}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="134" width="129" height="20" forecolor="#666666" uuid="066c07e6-1112-4674-941b-21aca9f38b73"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[First Day of Last Month]]></text>
            </staticText>
            <staticText>
                <reportElement x="0" y="174" width="129" height="20" forecolor="#666666" uuid="d9d4351e-c776-42ad-a29e-f11786dc35c0"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Last Year]]></text>
            </staticText>
            <textField isStretchWithOverflow="true" pattern="dd/MM/yyyy">
                <reportElement x="129" y="174" width="130" height="20" forecolor="#666666" uuid="92c40161-ba2d-4ddc-b0db-db1a6fae2698"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{lastYear}]]></textFieldExpression>
            </textField>
            <textField pattern="dd/MM/yyyy">
                <reportElement x="129" y="94" width="130" height="20" forecolor="#666666" uuid="f0743b6d-d4ea-4db7-9fdb-43bcf48953c1"/>
                <textElement textAlignment="Right">
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{lastSunday}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="94" width="129" height="20" forecolor="#666666" uuid="523473bd-5d57-479e-8006-92eaa703d859"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Last Sunday]]></text>
            </staticText>
            <textField pattern="dd/MM/yyyy">
                <reportElement x="129" y="194" width="130" height="20" forecolor="#666666" uuid="8620c79f-9e0b-4a06-ae30-bba1c1cc8b8c"/>
                <textElement textAlignment="Right">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{FirstDayOFLASTYear}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="194" width="129" height="20" forecolor="#666666" uuid="6be8e9c9-9435-4c10-b4bf-ada237dcfc50"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[First Day of Last Year]]></text>
            </staticText>
            <textField pattern="dd/MM/yyyy">
                <reportElement x="129" y="214" width="130" height="20" forecolor="#666666" uuid="434ff04f-cc0e-4e4f-962d-c913f4d022f7"/>
                <textElement textAlignment="Right">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{LastDayOFLASTYear}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="214" width="129" height="20" forecolor="#666666" uuid="acf14ac9-5535-4102-b85d-c052f8b42a20"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[Last Day of Last Year]]></text>
            </staticText>
            <textField pattern="dd/MM/yyyy">
                <reportElement x="129" y="234" width="130" height="20" forecolor="#666666" uuid="39eedcfe-b1a5-43e5-acbe-14dea776882a"/>
                <textElement textAlignment="Right">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{FirstDayOFThisYear}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="234" width="129" height="20" forecolor="#666666" uuid="c291ecef-a173-41f6-9ee1-e01a1220a9b6"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[First Day of This Year]]></text>
            </staticText>
            <textField>
                <reportElement x="129" y="254" width="130" height="20" forecolor="#666666" uuid="94682c69-0b2c-4762-a799-d1830bd94718"/>
                <textElement textAlignment="Right">
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{firstDayOfTheMonth}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="254" width="129" height="20" forecolor="#666666" uuid="cb8a2ed3-61eb-4990-89e9-4e205be8720c"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[First Day of Selected Month]]></text>
            </staticText>
            <textField>
                <reportElement x="129" y="274" width="130" height="20" forecolor="#666666" uuid="b5db2c39-99e5-479d-883b-409d35d35737"/>
                <textElement>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <textFieldExpression><![CDATA[$V{lastDayOfTheMonth}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="0" y="274" width="129" height="20" forecolor="#666666" uuid="36bd8f83-e53f-497c-bf07-7280bfb64ab8"/>
                <textElement>
                    <font fontName="Arial"/>
                    <paragraph lineSpacing="Single"/>
                </textElement>
                <text><![CDATA[LastDay of Selected Month]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band splitType="Stretch"/>
    </detail>
    <columnFooter>
        <band splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band splitType="Stretch"/>
    </summary>
</jasperReport>
 

Link to comment
Share on other sites

I would use a variable with the below expression. Posted my sample jrxml for your reference

    <variable name="YTD" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[YEAR($F{MYDATE}).equals(YEAR(new Date())) == true &&  $F{MYDATE}.before( new Date() ) == true  ? $F{SALES_AMOUNT} : BigDecimal.ZERO]]></variableExpression>    </variable>[/code]

 

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.4.0.final using JasperReports Library version 6.4.1  --><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="YTD" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="430f67fd-0e51-4761-a175-467cfcb8f8d1">    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>    <queryString>        <![CDATA[select to_date('05/05/2017', 'MM/dd/yyyy') as mydate, 5 as sales_amount from dual unionselect to_date('05/10/2017', 'MM/dd/yyyy') as mydate, 6 as sales_amount from dual unionselect to_date('05/15/2017', 'MM/dd/yyyy') as mydate, 4 as sales_amount from dual unionselect to_date('05/20/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('05/25/2017', 'MM/dd/yyyy') as mydate, 8 as sales_amount from dual unionselect to_date('05/30/2017', 'MM/dd/yyyy') as mydate, 4 as sales_amount from dual unionselect to_date('06/04/2017', 'MM/dd/yyyy') as mydate, 6 as sales_amount from dual unionselect to_date('06/09/2017', 'MM/dd/yyyy') as mydate, 6 as sales_amount from dual unionselect to_date('06/14/2017', 'MM/dd/yyyy') as mydate, 6 as sales_amount from dual unionselect to_date('06/19/2017', 'MM/dd/yyyy') as mydate, 7 as sales_amount from dual unionselect to_date('06/24/2017', 'MM/dd/yyyy') as mydate, 7 as sales_amount from dual unionselect to_date('06/29/2017', 'MM/dd/yyyy') as mydate, 7 as sales_amount from dual unionselect to_date('07/04/2017', 'MM/dd/yyyy') as mydate, 7 as sales_amount from dual unionselect to_date('07/09/2017', 'MM/dd/yyyy') as mydate, 7 as sales_amount from dual unionselect to_date('07/14/2017', 'MM/dd/yyyy') as mydate, 7 as sales_amount from dual unionselect to_date('07/19/2017', 'MM/dd/yyyy') as mydate, 8 as sales_amount from dual unionselect to_date('07/24/2017', 'MM/dd/yyyy') as mydate, 8 as sales_amount from dual unionselect to_date('07/29/2017', 'MM/dd/yyyy') as mydate, 8 as sales_amount from dual unionselect to_date('08/03/2017', 'MM/dd/yyyy') as mydate, 8 as sales_amount from dual unionselect to_date('08/08/2017', 'MM/dd/yyyy') as mydate, 8 as sales_amount from dual unionselect to_date('08/13/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('08/18/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('08/23/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('08/28/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('09/02/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('09/07/2017', 'MM/dd/yyyy') as mydate, 9 as sales_amount from dual unionselect to_date('09/12/2017', 'MM/dd/yyyy') as mydate, 10 as sales_amount from dual unionselect to_date('09/17/2017', 'MM/dd/yyyy') as mydate, 10 as sales_amount from dual unionselect to_date('09/22/2017', 'MM/dd/yyyy') as mydate, 10 as sales_amount from dual unionselect to_date('09/27/2017', 'MM/dd/yyyy') as mydate, 10 as sales_amount from dual unionselect to_date('10/02/2017', 'MM/dd/yyyy') as mydate, 10 as sales_amount from dual unionselect to_date('10/07/2017', 'MM/dd/yyyy') as mydate, 10 as sales_amount from dual unionselect to_date('10/12/2017', 'MM/dd/yyyy') as mydate, 11 as sales_amount from dual unionselect to_date('10/17/2017', 'MM/dd/yyyy') as mydate, 11 as sales_amount from dual unionselect to_date('10/22/2017', 'MM/dd/yyyy') as mydate, 11 as sales_amount from dual unionselect to_date('10/27/2017', 'MM/dd/yyyy') as mydate, 11 as sales_amount from dual]]>    </queryString>    <field name="MYDATE" class="java.sql.Timestamp"/>    <field name="SALES_AMOUNT" class="java.math.BigDecimal"/>    <variable name="YTD" class="java.math.BigDecimal" calculation="Sum">        <variableExpression><![CDATA[YEAR($F{MYDATE}).equals(YEAR(new Date())) == true &&  $F{MYDATE}.before( new Date() ) == true  ? $F{SALES_AMOUNT} : BigDecimal.ZERO]]></variableExpression>    </variable>    <background>        <band splitType="Stretch"/>    </background>    <columnHeader>        <band height="20" splitType="Stretch">            <staticText>                <reportElement x="0" y="0" width="80" height="20" uuid="226e1b32-e2af-4907-92e0-722b9b3ade13"/>                <text><![CDATA[MYDATE]]></text>            </staticText>            <staticText>                <reportElement x="80" y="0" width="100" height="20" uuid="dc9053b9-bd60-481e-9f92-86a71745670d"/>                <textElement textAlignment="Right"/>                <text><![CDATA[sALES_AMOUNT]]></text>            </staticText>        </band>    </columnHeader>    <detail>        <band height="20" splitType="Stretch">            <textField pattern="MM/dd/yyyy">                <reportElement x="0" y="0" width="80" height="20" uuid="75031fc7-1a04-441c-844c-4bf3cae3b232"/>                <textFieldExpression><![CDATA[$F{MYDATE}]]></textFieldExpression>            </textField>            <textField isStretchWithOverflow="true" pattern="#,##0.00" isBlankWhenNull="true">                <reportElement x="80" y="0" width="100" height="20" uuid="bbdac3b0-75b9-4f95-9bc7-068c8b3b08b8"/>                <textElement textAlignment="Right" verticalAlignment="Top"/>                <textFieldExpression><![CDATA[$F{SALES_AMOUNT}]]></textFieldExpression>            </textField>            <textField evaluationTime="Auto">                <reportElement x="280" y="0" width="100" height="20" uuid="c7339c20-5f2d-4710-933a-f5099e505b40"/>                <textFieldExpression><![CDATA[YEAR($F{MYDATE}).equals(YEAR(new Date())) == true &&  $F{MYDATE}.before( new Date() ) == true  ? $F{SALES_AMOUNT} : BigDecimal.ZERO]]></textFieldExpression>            </textField>        </band>    </detail>    <columnFooter>        <band height="20">            <textField>                <reportElement x="80" y="0" width="100" height="20" uuid="9dfdad33-1e39-45f3-9e8b-40fbe8942d4d"/>                <textElement textAlignment="Right"/>                <textFieldExpression><![CDATA[$V{YTD}]]></textFieldExpression>            </textField>            <staticText>                <reportElement x="0" y="0" width="80" height="20" uuid="c51f151c-9f6b-4533-b313-94aa9e78f291"/>                <text><![CDATA[YTD]]></text>            </staticText>        </band>    </columnFooter></jasperReport>[/code]

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...