how to sum the h:mm values in obiee report

kavya-Oracle

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

I have tried in obiee report  but i couldnt resolve this issue. please help me .

please share ideas.

Thanks in advance,

A.kavya.

Martin van Donselaar

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

Joel

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

Robert Angel

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.

Martin van Donselaar

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

Thomas Dodds

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.

kavya-Oracle

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.

can you please explain briefly.

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

Thanks in advance martin

Thanks,

A.kavya.

Martin van Donselaar

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

kavya-Oracle

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