I have been searching for a while now in the Report Forum and have not found an answer to my question. I have also used a Business Objects Resource book as well. Ok enough yacking…I need to sum Time Durations. We have a"Time Spent" field the amount of time a ticket is open and all the harry details. The techs enter in their time spent by hours and minutes, so there can be several entries for “Time Spent” in one ticket. So to avoid manually adding them I looking for a Business Objects Resource to guide me.
So I get my Time Spent field as it is an object in my universe. But when I try to sum the time, the sum operation under calculations is grayed out.
So do I need to create a object in the universe that counts all the time spent? Or what is the best to go about this, and how do I do it? I am new at Business Objects, I am really enjoying how powerful the tool is. I want to become an expert! So please if anyone knows how to do this I would greatly appreaciate direction.
So the time is not stored as minutes (data type: number), but in “hours:minutes” format (data type: time or date)? If so you need to convert your “hours:minutes” format to minutes for example:
1:53 --> 1*60 + 53 = 113 minutes
You can do this conversion either on the DB side (recommended) or in each separate Business Objects document with a local report formula (cumbersome). Once you have converted your time format into a “pure” number format you should be able to sum it up.
How to convert a date/time format into a number format with Business Objects Reporter: see here
Which DBMS (Oracle, MS SQL server, etc.) are you using?
Thank you for the info I copied the formula and set it as a variable in the bo report but I get an error "not enough parameters or parenteses) one of those errors… I am so new to BO and nor familiar enough to taylor the variable to work. Please help.
I tried making an object in the Universe to convert Time Spent using the formula you gave me. But when I try to insert the formula in the select statment and I parse it out I get “Not enough Parenthses” So I have no clue what so ever how to determine where the missing parentses should go…I have it broken down in Word and I am trying to determine but I am at a loss. So I tried the formula in a local variable and still get the same error? Any ideas???
And on another note would you happen to know how to average time? I have a call center report with time that needs to be averaged. Alex has responded to me with a answer of: convert the time to seconds, then average the total seconds, then apply the formula to format back to time with a variable. Then create a new variable named “Differently” with the same formula and apply to the average. I have done all of that with no success. It keeps returning the last number in the row. I thought it was just a coinicidence, but no I resorted the numbers and what ever is the last in the row is the average. So if you have any ideas on my sticky situations of summing “Time Spent” and averaging time that would be great!
I should mention that both the Universes are different, the one for averaging time is a access database imported as a universe. I dump the CMS (Telephony, aka CMS Supervisor) files into access, then I report from Business Objects. The “Time Spent” Is Ffrom the HP Service call Universe straight from Oracle. Oh I could go on… but with all of this being said I hope I made sense, and most of all I hope I can Sum Time and average time after conversing with you!
(BOB member since 2002-06-20)