Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Friday, January 23, 2015

Easy way of Substracting,Adding Months or Days from parameter date in Jasper studio or iReport : Ex : Substracting 2 months from current date

This is re-blogging of Right outer join post in simple understanding.


This post avoid the complexity usage & guide you the simplest way.

Aim : 
1) Subtract 1 month or 2 months or 3 months and etc from current date or selected date.
2) Subtract1 day or 2 day or 3 day or etc from current date or selected date
3) First Day of last month, Last day of last month & 15 th day of last month
4) Number of days from the current date or selected date.
5) Other Date formats

Sample output:


Way of getting solution :

1) Very first thing is to import org.apache.commons.lang.time package by opening XML source.  You have to add this code after properties selection ( The first high lighted code )

2) Take a parameter called MyDate and it's type should of java.util.Date

3) Now do experiment with various methods that available from org.apache.commons.lang.time

4) For eg :  Subtracting 2 months from MyDate parameter 
  Lets say MyDate parameter default value new Date() [1/2/15/ 1:04 P.M]
Below expression in a text field gives you the the exact subtraction of 2 months

<textFieldExpression><![CDATA[DateUtils.addMonths($P{MyDate},-2)]]></textFieldExpression>


5) Copy the below code and paste in your jrxml and see the magic.

6) Now how to get the patterns of dates
Lets create another parameter  say PatternDate and in its default expression write below code...
The type of this parameter should be of java.lang.String
$P{PatternDate} = new SimpleDateFormat("MMM-yyyy").format($P{MyDate})

Place this parameter in Title section and check the pattern....

Or you can directly set pattern by going properties if you are printing dates in a Text field.

JRXML Code from Right outer join post :

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.final using JasperReports Library version 6.0.0  -->
<!-- 2015-01-23T18:37:08 -->
<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="Commons-Lang-Examples" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="9715a38f-ab05-4d3a-b5bd-8f55a035a9be">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <import value="org.apache.commons.lang.time.*"/>
    <parameter name="MyDate" class="java.util.Date">

        <defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>
    </parameter>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="439" splitType="Stretch">
            <textField>
                <reportElement x="0" y="40" width="255" height="20" uuid="165305e3-bb69-4c57-b028-ac0d9d765fff"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$P{MyDate}]]></textFieldExpression>
            </textField>

            <textField>
                <reportElement x="255" y="40" width="300" height="20" uuid="ea63bc38-963e-4207-ac7a-8eacdd040cf3"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} (unmodified)"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="60" width="300" height="20" uuid="7d856c36-27a3-4b10-a0ef-853ebfb19087"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} + 1 day"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="60" width="255" height="20" uuid="15dd62fe-d3fd-45e2-8135-53884236ed7e"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.addDays($P{MyDate},1)]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="80" width="300" height="20" uuid="b508649e-09f4-428f-94e8-23f5a6afbff7"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} - 1 month"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="80" width="255" height="20" uuid="92429a95-0c43-4a5e-b30b-0e9278a13c81"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.addMonths($P{MyDate},-2)]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="180" width="300" height="20" uuid="d72a3203-350e-4065-aaba-b63cbfafb57f"/>
                <textFieldExpression><![CDATA["Last day of previous month"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="180" width="255" height="20" uuid="3bb732b3-0bac-42d5-8a06-e623942f78b8"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.addDays(
    DateUtils.truncate($P{MyDate},java.util.Calendar.MONTH),
    -1
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="255" y="160" width="300" height="20" uuid="fa5f6972-53ff-4249-9721-ca72eda43993"/>
                <textFieldExpression><![CDATA["First day of previous month"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="160" width="255" height="20" uuid="ede9293e-7d3e-449b-aef6-c53a6a2c6b7f"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.truncate(
    DateUtils.addMonths($P{MyDate},-1),
    java.util.Calendar.MONTH
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="255" y="120" width="300" height="20" uuid="4279db76-2ba7-4f38-a14b-897c13041296"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} with time component removed"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="120" width="255" height="20" uuid="0a6eb011-8bcd-4e55-a1e7-2b21327e37c8"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.truncate($P{MyDate},java.util.Calendar.DATE)]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="100" width="300" height="20" uuid="bbbaa2d6-b691-4c1c-b1ac-50ada10c1955"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} Rounded to the nearest hour"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="100" width="255" height="20" uuid="f46cf21a-b4af-4c34-af62-eea466c810b8"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.round( $P{MyDate},java.util.Calendar.HOUR )]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="555" height="40" uuid="dc842c45-ae62-46c7-8309-3a69d46babb5"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Useful date calculations with Java Commons Lang"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="220" width="255" height="20" uuid="ddf5c047-cafd-4da8-94cd-61064e169fcf"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[new Long(
    DateUtils.getFragmentInDays( $P{MyDate},java.util.Calendar.YEAR )
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="255" y="220" width="300" height="20" uuid="59d6413f-e146-4523-86df-e93e209c91c9"/>
                <textFieldExpression><![CDATA["Days since January 1"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="200" width="255" height="20" uuid="74234013-db8b-4b74-a71a-fc70ea8c48b7"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateUtils.truncate(
    DateUtils.setDays( $P{MyDate}, 15 ),
    java.util.Calendar.DATE
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="255" y="200" width="300" height="20" uuid="e93ed83f-a8a6-43d6-ab2f-5c4e77abcd17"/>
                <textFieldExpression><![CDATA["The fifteenth day of the month"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="300" width="255" height="20" uuid="961aa26f-7ddc-427f-8edb-094252856ff9"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateFormatUtils.format(
    $P{MyDate},
    DateFormatUtils.ISO_DATE_FORMAT.getPattern()
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="0" y="260" width="555" height="40" uuid="2101dfa6-2956-4685-8819-8e2f5570e8ac"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="14" isBold="true"/>
                </textElement>
                <textFieldExpression><![CDATA["Useful date formatting with Java Commons Lang"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="300" width="300" height="20" uuid="8fa2ea6e-e989-409a-9933-b6cf0fca13c5"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} (unmodified) in ISO Date Format"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="320" width="255" height="20" uuid="4c336368-d27b-48b3-b15a-532657f18294"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateFormatUtils.format(
    $P{MyDate},
    DateFormatUtils.ISO_TIME_FORMAT.getPattern()
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="255" y="320" width="300" height="20" uuid="4e9c7a33-282d-4dc8-a409-18e0dc409d74"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} Time only in ISO8601 format no time zone"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="340" width="255" height="20" uuid="5089020f-10d1-4c28-8e39-8c5ed8e4b5d8"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateFormatUtils.format(
    $P{MyDate},
    DateFormatUtils.ISO_TIME_TIME_ZONE_FORMAT.getPattern()
)]]></textFieldExpression>

            </textField>
            <textField>
                <reportElement x="255" y="340" width="300" height="20" uuid="d9f37168-1863-4f58-a809-f4d3267a31f8"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} Time only in ISO8601 format with time zone"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="360" width="300" height="20" uuid="10768478-a19f-42c6-9e92-7d2de1a41baa"/>
                <textFieldExpression><![CDATA["$" + "P{MyDate} in New Zealand timezone"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="360" width="255" height="20" uuid="e615cb87-2c1a-476a-b7b2-f57592590fdc"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[DateFormatUtils.format(
    $P{MyDate},
    DateFormatUtils.ISO_DATETIME_FORMAT.getPattern(),
    TimeZone.getTimeZone("Pacific/Auckland")
)]]></textFieldExpression>

            </textField>
        </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>
    <lastPageFooter>
        <band height="60">
            <textField>
                <reportElement x="0" y="0" width="555" height="20" uuid="1c56c313-3e0b-47d4-a7f0-86556ebeeb92"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA["Sample report created by Matt Dahlman to show the use of Java Commons Lang date functions in JasperReports"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="20" width="255" height="40" uuid="3c27ca9b-3a71-4397-9b8b-f5c2890667f9"/>
                <box rightPadding="20"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA["version:\n"
+ "last modified:"]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="255" y="20" width="300" height="40" uuid="8140f760-5be7-4fc8-a2a7-0c50ff057b4e"/>
                <textFieldExpression><![CDATA["0.1\n"
+ "9 Sept 2009"]]></textFieldExpression>
            </textField>
        </band>
    </lastPageFooter>
</jasperReport>

References :
1)   https://mdahlman.wordpress.com/2009/09/09/jasperreports-first-dates/