## Forum Stats

• 3,769,816 Users
• 2,253,026 Discussions

Discussions

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

Member Posts: 17 Employee
edited Nov 24, 2017 1:53AM

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.

Tagged:

• Member Posts: 17 Employee
edited Nov 24, 2017 1:53AM Accepted Answer

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

• Member Posts: 1,370 Gold Trophy
edited Nov 22, 2017 7:33AM

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

• Member Posts: 4,535 Bronze Crown
edited Nov 22, 2017 7:39AM

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.

• Member Posts: 307
edited Nov 22, 2017 7:58AM

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

• Member Posts: 1,698
edited Nov 22, 2017 9:23AM

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.

• Member Posts: 17 Employee
edited Nov 23, 2017 12:25AM

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.

• Member Posts: 307
edited Nov 23, 2017 3:30AM

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

• Member Posts: 17 Employee
edited Nov 24, 2017 1:53AM Accepted Answer

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

This discussion has been closed.