Jump to content
Changes to the Jaspersoft community edition download ×

calculate quarter sum out of monthly values


wegnerk

Recommended Posts

Hi all...

 

I have to group my monthly values in quarter of the year. I tried to make a new variable which counts to 3 after the month-value (it is bigDecimal) and another, which gives out the sum of the months...

 

but it does not work and i really have a bit of a problem now...

 

Any ideas? would be great...

 

regards

katya

Link to comment
Share on other sites

  • Replies 11
  • Created
  • Last Reply

Top Posters In This Topic

Seeing your query might help. As I've mentioned to others before, must of this "processing" can be done straight from the query avoiding report conversion issues.

 

I've found many approaches to do alike things:

 

1. Let the query do that for me.

 

2. Create a java function, include it in the classpath (as a .jar) and call it from the report.

 

#1 is always the option to use, Ã've had used the second when the DBMS doesn't allow to perform the specified operation.

Link to comment
Share on other sites

the sql-statement is following:

 

Code:

select HAERTEFALL, JAHR, MONAT, MENGE1, MENGE2, MENGE1+MENGE2
from eegextern.prognose_berichte_hnd
where APPLIKATION = "EEGINTERNET" and SESSIONID = "QWERTY123" and IDENTID=4711 and ANFORDERUNGID = 2
order by HAERTEFALL, JAHR, MONAT;

 

it is in german, sorry... but haertefall is a name, the other five aree bigDecimal values. My SQL-Knowlegde isn't so amazing. Is there a possibility to group in sql by 3 months?

Link to comment
Share on other sites

yes, month and year are correct, haertefall is a customername, and menge is an amount, amount1 and amount2.

 

but i found a solution; we made a view in the sql-database where the sums for the quarters are calculated. that was also necessary, because java can't work with the SQL-Calculation (amount1+amount2) as a new column. So i had to go another way.

Was a good idea to do this in sql. makes many things easier.

 

thanks a lot

Link to comment
Share on other sites

Is nice you found the solution and I was of some help. But you're indeed wrong in one thing, java can do the sql calculation. Also iReport has a way of working with them but are some unusual things like:

 

Code:
new java.lang.Integer($F{val1}+$F{val2})

 

Just as I menationed in one of my replies is just easier to get all you can from the sql and use java for special situations.

 

Glad being of any help.

Link to comment
Share on other sites

Hi dreamer..

 

yes, i know now, that java can calculate, but i think i wrote it wrong. I meant, that it can't handle a column, which is not in the database. when i use IReport, it works, but if i compile and fill the report in a java-class, i get the error that there is an unknown column. That was my greatest problem. ;-)

 

but with the way over sql, it works...

 

so long

Link to comment
Share on other sites

I needed to create a fucntion to calculate the working days between two dates like NetWorkDays function in Excel. So basically I grabed my NetBeans 5.5, coded and created a jar. Added the jar to iReport's class path and called from a fiel like this:

 

Code:

new Double (Dreamer.DateTools.DateCalculator.NetWorkdays($F{OPEN_TIME},$F{CLOSE_TIME},0).doubleValue())

 

Dreamer= package

DateTools= subpackage

DateCalculator = class

NetWorkdays = function

 

As you might notice I have $F{OPEN_TIME}, an iReport variable as a function parameter. iReport does the trick of replacing the iReport variable for it's value before sending it to the function. Just make sure that the function's parameters, field/variable sent, function return value and variable type in iReport are from the same type!

 

Hope this helps.

Link to comment
Share on other sites

If the month field is numeric you get the quarter by declaring a variable of type Integer that is set to

 

new Integer(($F{MONAT}.intValue() - 1) / 3)

 

If the field is a string AND your database supports the CASE statement you can do something along the line of

 

select JAHR, CASE MONAT

when 'JAN' then 1

when 'FEB' then 1

when 'MAR' then 1

when 'APR' then 2

...

END as Quarter

rest of select

 

Excuse the bad mixture of German and English.

 

-Barry

Link to comment
Share on other sites

Hi Barry,

 

that was the way, i first tried... but now, we have realized it by creating a view in the sql-database, where the monthly values are grouped to quarters. We used the ELT statement, i didn't know that before:

Code:

...
ELT(MONAT,'1. Quartal','1. Quartal','1. Quartal',
'2. Quartal', '2. Quartal', '2. Quartal',
'3. Quartal', '3. Quartal', '3. Quartal',
'4. Quartal', '4. Quartal', '4. Quartal') QUARTAL
...

 

like this, i have all necessary values for more than one report in one view and the calculation of the sums is much easier.

 

But thanks for all your help!!

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...