Expression Editor: Last day of last month

0

First day of this month:

new SimpleDateFormat("yyyy-MM-dd").format(java.util.Date.parse(MONTH(NOW( )) + "/1/" + YEAR(NOW())))

Last day of this month:

new SimpleDateFormat("yyyy-MM-dd").format(java.util.Date.parse(MONTH(NOW( )) + "/" + DAYSINMONTH(NOW())+"/" + YEAR(NOW())))

First day of last month:

new SimpleDateFormat("yyyy-MM-dd").format(java.util.Date.parse((MONTH(NOW( )) -1 ) + "/1/" + YEAR(NOW()))    )

NOT WORKING:

Last Day of Last month: set the days to DaysInMonth (LAST MONTH FIRST OF MONTH)

new SimpleDateFormat("yyyy-MM-dd").format(java.util.Date.parse(
(MONTH(NOW( )) -1 ) 
+ "/" + 
DAYSINMONTH(MONTH(NOW( )) -1 ) + "/1/" + YEAR(NOW()))    
+ "/" + 
YEAR(NOW())
)

I've tried this a dozen different ways, but just can't get the Last Day of Last Month to work.

(Common question as many business users run a report for the last month.)

Can anyone more experienced than I with this shed some light?

(I could do this easy with SQL, Maybe I could set the default expression from a seperate SQL query field?

danoldenkamp@gmail.com's picture
Joined: Aug 29 2018 - 11:12am
Last seen: 3 days 10 hours ago

Thanks C-Box.

Importing the library for DateUtil,

report > Imports > org.apache.commons.lang3.time.*;

Parameter default expression: DateUtils.addSeconds(DateUtils.ceiling(DateUtils.addMonths(new Date(), -1), Calendar.MONTH), -1)

Worked great.  You the man now, dog!  Thank you!

danoldenkamp@gmail.com - 3 weeks 4 days ago

No problem! Thanks for your feedback

regards from Dresden/Germany and goof luck with "Jaspering"

C-Box

C-Box - 3 weeks 4 days ago

3 Answers:

0

Hi,

I'd advice using the also in JasperReports used Apache DateUtils....  the commons-lang3.jar should be available in your classpath of course .... so take a look at this:

DateUtils.addSeconds(DateUtils.ceiling(DateUtils.addMonths(new Date(), -1), Calendar.MONTH), -1)

It will calculate the last Day of the previous month from Today (better: the latest second in the previous month :-) ).

So an sysout will show you, if you start it today (13.05.2019):

Tue Apr 30 23:59:59 CEST 2019

hth + regards

C-Box

C-Box's picture
1434
Joined: Jul 19 2006 - 5:58pm
Last seen: 4 min 50 sec ago
0

Thanks for the suggestion.  I'm using out of box Jaspersoft Studio.

I don't think I have access to that JAR.

 

I tried using that for my Paramater default value expression (Class: Java.util.Date) :

Error evaluating expression for source text: DateUtils.addSeconds(DateUtils.ceiling(DateUtils.addMonths(new Date(), -1), Calendar.MONTH), -1)

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: DateUtils.addSeconds(DateUtils.ceiling(DateUtils.addMonths(new Date(), -1), Calendar.MONTH), -1) 

danoldenkamp@gmail.com's picture
Joined: Aug 29 2018 - 11:12am
Last seen: 3 days 10 hours ago
1

Hi again,

just tried with JasperSoftStudio 6.6.0 ... also out of the box and it works well. The required library is also included within that JRStudio version:

e.g. "C:\Jasper\Jaspersoft Studio-6.6.0\plugins\org.apache.commons.lang3_3.1.0.v201403281430.jar"

 

 

Here is my design - just take a look at the imports at the beginning. Probably you should define the "JasperReports Dependencies" to your design-workspace classpath as well.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.5.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="TestLastDayOfPrevMonth" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="5cf53290-7072-4385-835a-aba7cba4db1c">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="One Empty Record"/>
    <import value="org.apache.commons.lang3.time.*"/>
    <parameter name="ParamLastDayCalced" class="java.util.Date" isForPrompting="false">
        <defaultValueExpression><![CDATA[DateUtils.addSeconds(DateUtils.ceiling(DateUtils.addMonths(new Date(), -1), Calendar.MONTH), -1)]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="79" splitType="Stretch">
            <textField>
                <reportElement x="230" y="41" width="281" height="30" uuid="2b9e697e-3d75-4fc7-a96d-41e7901c474c"/>
                <textFieldExpression><![CDATA[$P{ParamLastDayCalced}]]></textFieldExpression>
            </textField>
            <staticText>
                <reportElement x="10" y="41" width="217" height="30" uuid="24c36840-3b6c-4eac-a79b-71a4db495427"/>
                <text><![CDATA[Last Day of Last month:]]></text>
            </staticText>
            <staticText>
                <reportElement x="10" y="10" width="217" height="30" uuid="68d423b1-c47c-4c53-a736-130cd8641d5c"/>
                <text><![CDATA[Today:]]></text>
            </staticText>
            <textField>
                <reportElement x="230" y="11" width="281" height="30" uuid="090cdc6e-d2e5-4848-826d-d91ac909763d"/>
                <textFieldExpression><![CDATA[new Date()]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="35" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="61" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="125" splitType="Stretch"/>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

so works as expected.

hth + regards

C-Box

C-Box's picture
1434
Joined: Jul 19 2006 - 5:58pm
Last seen: 4 min 50 sec ago
Feedback
randomness