calculate quarter sum out of monthly values

[wegnerk]

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

[javydreamercsw]

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.

[wegnerk]

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?

[javydreamercsw]

I'll need translation of fields. I expect month (monat?) and year (jahr?) to be there somewhere.

 

Translate the field names to english if possible.

[wegnerk]

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

[javydreamercsw]

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.

[wegnerk]

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

[javydreamercsw]

Still wrong. I have managed to call java prodedures from the report itself using any type of field as parameter. If you want can send you a sample later in the week. Anyway I'm glad you found an answer.

[wegnerk]

would be great if you can send me a sample. i think i will have a lot of work in future with jasperreport, so i am glad about every help i can get!

[javydreamercsw]

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.

[bklawans]

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

[wegnerk]

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