getting yesterdays date, last months dates etc.

2

There seems to be a huge missing feature in jasper studio. It seems its not possible for a mortal to get yesterdays date, or do date arithmentic in general. We need a report which shows summaries for today, yesterday, last month, today last month, last quarter etc.

I have read many forum posts postulating ways to get yesterdays date, and things like last month.  It seems it is not feasible.

This was suggested: DATE(YEAR(TODAY( )),MONTH(TODAY( )),DAY(TODAY( ))-1)

But this is wrong - if it is the 1st today, then yesterday needs to be the last day of the previous month, not just day -1 of the current month.

Other suggestions include writing java classes (you may as well say build a rocket to land a girbil on the moon), or using groovy. We tried using grovy, but the expression editor doesnt recognis groovy syntax.

Is there no solution for this for normal report writers?

We could duplicate the report 10 times, and hard code the requred date ranges in SQL, but this is a maintenance nightmare as we change the columns requently, and there are a lot of them.  If we are goijng to do this, there is no point in us using Jasper - we may as well stick to manually producing reports.

john.v.little's picture
Joined: Aug 28 2014 - 3:29am
Last seen: 5 years 3 weeks ago

1 Answer:

3

The attached report (date-calculations.jrxml) uses expression and variables to do those calculations, at least it will give you a start. 

I'll look in the built in date functions (DAY(), etc.), I had a sample but I cannot find it now.

Attachments: 
AttachmentSize
Binary Data date-calculations.jrxml10.65 KB
marianol's picture
4708
Joined: Sep 13 2011 - 8:04am
Last seen: 1 year 2 weeks ago

I tried using your examples, but when I put the same parameters into my project, I get this error: "The operator || is undefined for the argument type(s) of void, void"
Any ideas?

john.v.little - 5 years 1 month ago

Also, yesterday variable express gives "The operator - is undefined for the argument types(s) Date, int.
I have no idea how you got this to work - my version of jasper studio (5.6) has none of the date options that your code seems to have

john.v.little - 5 years 1 month ago

Add language="Groovy" in
<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" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="f1ec6247-46a3-4412-93fe-65e8b1b6d378">

saravana.kumar231288 - 3 years 9 months ago
show 1 more...

This example worked for me straight out of the box as a stand alone report but I think Saravana's comment highlights John's problem because I suspect his report is written in Java and this report is written in Groovy.... I tried changing the language to Java and it gave the same errors John was seeing. I doubt he has the option of rewriting every report in groovy (I certainly don't) so I guess we are both still looking a for a simple solution to this problem. Otherwise its a great little resource to have tucked away...

ronnie.macleod - 2 years 1 week ago
Feedback
randomness