# how to sum the h:mm values in obiee report

161
Views
8
✭✭✭
edited August 13

Hi ,

I want to sum the hour minute values based on time based analysis.

for example , i have spent 1hr 55min on project1 work & 1hr 45min on project2 work then total hours should be 3hr 40min . pls see the below screen shot.

here No.of hours assigned column  data coming from fact.hour_assigned_actual column . data type is number.

in obiee report level data like 1.55, 1.45, 3:13,3:20,0:59,404:13..(data is not extracted from date column. it is fact data ).

But in obiee report  sum of hours showing as 3hrs. I think it taking as actual sum. It is nt summing the time based.

total hours showing wrong in obiee report .pls see the below screen shot

A.kavya.

• First of all, This data is stored incorrectly in my opinion, It should not be stored as a decimal number because it is not a decimal number and it should be stored in no of minutes because that is your actual grain.

So, if possible, adapt your ETL to include a no of minutes column.

If you have this column than you can use something like floor(fact.minutes/60)||':'||lpad(Mod(fact.minutes,60),2,0).

If you cannot adapt your ETL (which i strongly suggest) then you can calculate the number of minutes with a calulation like this:  floor(fact.hour_assigned_actual *60) + (fact.hour_assigned_actual -floor(fact.hour_assigned_actual ))*100

• ✭✭✭✭✭

Agree with @Martin van Donselaar

alternatively, you could derive this directly from the start timestamp and end timestamp and use the TIMESTAMPDIFF function to derive the time difference and format that using a duration format mask like: [duration(sec)] hh:mm:ss

• ✭✭✭✭✭

Another alternative is to convert the time into decimal time (sic) by multiplying the value after the dp by 100 / 60, add that to the column before the dp, and then translate the number after the dp by 60 / 100 - but I concur, your ETL developers should have given you a display time value (text!!!) and a timestamp or oracle data value to provide means to do what you are attempting.

• Yes, that would be even better if you have those, but the OP doesn't make clear if timestamps are available.

• ✭✭✭✭✭

USE of information drives data design ... the above have given you that in various forms.

If you plan on summing time, then store it in a 'summable' manner.

• Hi Martin,

In database , fact.hour_assigned_actual  column data is looks like as following below.

how to convert above data into  h:mm and how to calculate the sum the h:mm values based on time analysis.

0.60 (h:mm) means 1 hour.  how it is possible in obiee report.

Please give idea and tell me how to resolve this issue.

Thanks,

A.kavya.

• So 2.6 really means 3? As I told you, your data is a mess an you should store minutes, not force a h:mm format  in a decimal number.

I've already given you all the information you need, did you try it?

Hint: You can also nest expressions

• Hi Martin,

Thanks Martin ..data should be store in mintues.

As you I have tried. but problem is total sum duration is incorrect.

Finally I have applied custom format  in column properties in obiee report as follwing below.

[duration(min)][opt:dd]mm:ss.

Now our data is coming correct in obiee report as per time based

30min+30min+15min= 1hr 15mins

30min+30min=1hr

Thanks to every one...

Thanks &Regards,

A.kavya