Neha: I spend most of my time building Analytics reports, and I've seen those OOB measures return some pretty strange results, so I normally avoid using them. I calculate the raw time open to date on one of our standard Analytics reports. The formula I use is as follows:
[Time Open to Date - Raw] = If(IsNull([Submitted].[Incident Last Resolved Date Time])) Then FormatNumber(Floor([Seconds Open to Date]/86400) ;"0") + " day(s)" + FormatNumber(Floor(Mod([Seconds Open to Date];86400)/3600) ;"00") + ":" + FormatNumber(Floor(Mod(Mod([Seconds Open to Date];86400);3600)/60) ;"00") + ":" +FormatNumber(Mod(Mod(Mod([Seconds Open to Date];86400);3600) ; 60);"00") Where [Seconds Open to Date]=If(IsNull([Incident Last Resolved Date Time])) Then (DaysBetween([Incident Reported Date Time] ;LastExecutionDate()) * 86400 + (ToNumber(FormatDate(LastExecutionDate();"HH")) * 3600 + ToNumber(Left(FormatDate(LastExecutionDate() ;"mm:ss") ;2)) * 60 + ToNumber(FormatDate(LastExecutionDate() ;"ss")) - (ToNumber(FormatDate([Incident Reported Date Time] ;"HH")) * 3600 + ToNumber(Left(FormatDate([Incident Reported Date Time] ;"mm:ss") ;2)) * 60 + ToNumber(FormatDate([Incident Reported Date Time] ;"ss")) )) Thanks, Natalie Stroud SAIC @ Sandia National Laboratories ARS-ITSM Reporting Specialist Albuquerque, NM USA [email protected] ITSM 7.6.04 SP2 – Windows 2003 – SQL Server 2008 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Neha Khandelwal Sent: Friday, November 22, 2013 12:02 AM To: [email protected] Subject: [EXTERNAL] Re: Total Time Spent on an incident Hi, Thanks a lot for your valuable responses. My purpose is to provide Time spent on an open ticket till now, and show this data in report through BMC Analytics. While browing Incident class of BMC Analytics, I found there are some measures like this- 1. Age of Incident (minute) 2. Age of Incident (hours) 3. Age of Incident (days) I think they area lready built in , in ITSM universe, and can be used for my purpose. Has anyone of you used above measures earlier in your reports? Can anyone please suggest? Regards Neha Khandelwal _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
