nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Sum labour hours on report

steve_vh

I am trying to write a simple report which groups labour transactions by date, then laborid, then labortype. I have got the format set up fine, but having issues with the aggregation.

I have summed the the 'regularhrs' field, and this displays, but as a decimal. The 'regularhrs' is in an hh:mm format though, so for example the entries 03:00 and 00:15, the sum is shown as 3.0, rather than 03:15 (or even 3.25). Does anyone know how I can get the total to display as a hh:mm format?

Find more posts tagged with

Comments

kclark

You can create a computed column to convert the HH:MM to a float which should make it easier to aggregate. I used this code 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>temp = row["regularhrs"].split(":");
minutes = 60 * parseInt(temp[0]) + parseInt(temp[1]);
minutes/60;</pre>
 
Then I added the computed column to the table and aggregated on that. Then I hid the column with the computed column showing the aggregated time under the REGULARHRS column. 
 
I've attached an example with some sample xml.

steve_vh

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="114208" data-time="1360866425" data-date="14 February 2013 - 11:27 AM">
You can create a computed column to convert the HH:MM to a float which should make it easier to aggregate. I used this code 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>temp = row["regularhrs"].split(":");
minutes = 60 * parseInt(temp[0]) + parseInt(temp[1]);
minutes/60;</pre>
 
Then I added the computed column to the table and aggregated on that. Then I hid the column with the computed column showing the aggregated time under the REGULARHRS column. 
 
I've attached an example with some sample xml. </blockquote>
 
Brilliant, that worked exactly like I wanted. Thanks kclark. 
 
[edit] 
Spoke too soon. Some of my results are returning NaN.

kclark

Can you post your rptdesign with some sample xml?

steve_vh

I can certainly give you a copy of the report design file, but I am stuggling to find a way to give you example XML. I've googled it and found this http://www.developer.com/xml/article.php/3732446/Developing-an-Eclipse-BIRT-XML-Report-Rendering-Extension.htm but I have a feeling this is a little out of date (2008).

Sorry for being a newbie. I might have to admit defeat on this one and go to our external developers :mellow:

steve_vh

I don't know if it helps at all, but the following values cause a NaN when aggregating:

08:54
08:23
09:00
08:00
00:08
08:16

Other values seem to aggregate correctly, e.g.:

00:15 = 0.25
00:10 = 0.167
00:30 = 0.5
00:40 = 0.667
02:45 = 2.75

Could it be that certain values are having issues with the calculation in the temp field?

eong

Hello,
 
What's the best way to convert the float back to HH:MM? Please advise. Thanks!