Aggregating getDuration as a Sum - ignores minutes/fractions

I have a report that is displaying regular hours and overtime hours in columnar format. These fields are fetched as getDuration data. The Data set identifies these fields as "string." I have a group SUM for these fields. The problem is that it only sums the integer and ignored fractions of the hour.
 
For instance:
2:00
4:30
1:00

7
 
How can I get the sum to work correctly?
 
Thanks in advance.

Find more posts tagged with

Comments

micajblock

What version of BIRT are you using? Also can you share how the data is being retrieved from your data source? Easier to build an actual example.

Matthew L.

You can split the string value and convert it into seconds for the SUM aggregation, then convert the seconds back into a time formatted string via onRender method.
 
See attached example.

rkraus10

I am using BIRT for Maximo through Eclipse 3.7.1
 
My fetch statement for the startdate is:
row["regularhrs"] = maximoDataSet.getDuration("regularhrs");
 
My output column is defined as a String
 
I have standard SQL select script to grab regularhrs which has the data type of FLOAT.

micajblock

Does the example above not work for you?

rkraus10

Matthew L - the conversion works as it converts the time to seconds, but the on Render does not run.
I get 36000 for 10:00.

Matthew L.

Attached is a modified version that hopefully will work for you.
All the code is now in the Aggregations Expression section.
The Aggregation Data Type and Function must be changed to display the String output.
The Aggregation settings are:
Data Type: String
Function: LAST
 
Let me know if there are any other issues.

rkraus10

Also what I need is a decimal, not minutes, so I should get 7.5 and not 7:30.
It seems like it should be a real easy thing to convert.
Am I wrong?

Matthew L.

To return a value without the time:format, you would just divide out the hours and minutes from the seconds total.
See example

rkraus10

I made some changes to get decimal equivalent of the minutes. Here is the script:
 
====
 
var parts = row["Column1"].split(':'); //Split string 
if(!seconds){var seconds=0;} //If seconds isnt set 
seconds = parseInt(seconds) + parseInt((+parts[0]) * 60 * 60 + (+parts[1]) * 60); //Add seconds up per each loop 
var output = toHHMMSS(parseInt(seconds)); //Set output var (See last line of this code section) 
//From: <a data-ipb='nomediaparse' href='http://fiddle.jshell.net/stefanz/XKBeT/1/'>http://fiddle.jshell.net/stefanz/XKBeT/1/</a> 
function toHHMMSS(sec) { 
 var sec_num = parseInt(sec, 10); // don't forget the second parm 
 var hours = Math.floor(sec_num / 3600); 
 var minutes = Math.floor((sec_num - (hours * 3600)) / 36); //THIS -> converts the minutes into a fraction of an hour 
 var seconds = sec_num - (hours * 3600) - (minutes * 60); 
 //if (hours < 10) {hours = "0"+hours;} //Leading 0 
 if (minutes < 10) {minutes = "0"+minutes;} //Leading 0 
 if (seconds < 10) {seconds = "0"+seconds;} //Leading 0 
 var time = hours+'.'+minutes;//+':'+seconds; //Added a dot instead of a colon. 
 return time; 
} 
output; //Return value
 
=======
 
I will try it in my Maximo Birt report and see if it flies

Matthew L.

You could also just drop the function and use this code instead:
 
var parts = row["Column1"].split(':'); //Split string 
if(!seconds){var seconds=0;} //If seconds isnt set 
seconds = parseInt(seconds) + parseInt((+parts[0]) * 60 * 60 + (+parts[1]) * 60); //Add seconds up per each loop 
var output = (parseInt(seconds) / 60) / 60; 
output; //Return value

rkraus10

It works!
I was using this aggregate as a group total, so I needed to zero out the hours, minutes and seconds at the top of the script:
 
hours=0; 
minutes=0; 
seconds=0;
 
thanks!!!