Forum Stats

  • 3,769,816 Users
  • 2,253,026 Discussions
  • 7,875,216 Comments

Discussions

how to sum the h:mm values in obiee report

kavya-Oracle
kavya-Oracle Member Posts: 17 Employee

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.

k1.png

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

k1.png

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

please share ideas.

Thanks in advance,

A.kavya.

Thomas DoddsMartin van DonselaarJoel AchaChristian Berg-0raclekavya-Oracle

Best Answer

  • kavya-Oracle
    kavya-Oracle 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

    2017-11-24 11_47_11-Oracle BI Answers.png

    30min+30min=1hr

    2017-11-24 11_45_20-Oracle BI Answers.png

    Thanks to every one...

    Thanks &Regards,

    A.kavya

Answers

  • Joel Acha
    Joel Acha 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

    Thomas Doddskavya-Oraclekavya-Oracle
  • Robert Angel
    Robert Angel 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.

    Thomas Dodds
  • Martin van Donselaar
    Martin van Donselaar 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. 

    Thomas Dodds
  • Thomas Dodds
    Thomas Dodds 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.

    Martin van DonselaarJoel AchaChristian Berg-0racle
  • kavya-Oracle
    kavya-Oracle 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.

    k1.png

    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
    Martin van Donselaar 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

    kavya-Oraclekavya-Oracle
  • kavya-Oracle
    kavya-Oracle 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

    2017-11-24 11_47_11-Oracle BI Answers.png

    30min+30min=1hr

    2017-11-24 11_45_20-Oracle BI Answers.png

    Thanks to every one...

    Thanks &Regards,

    A.kavya

This discussion has been closed.