3 Replies Latest reply on Jul 26, 2011 8:46 AM by gwsmith

    calculation for total time spent by user today

    Apprentice

      We like to track the amount of time a support users spend working on an incident, so in Incident.Note we have a field called "Incident Time Spent".  When they add a note to an incident, they enter the time they spent in minutes.  Incident has a field called "Total Time" that uses a calculation to show the total time spent on all notes.  That was all set up by consultants who knew more than I do.

       

      Now I'm trying to figure out how to display the total time reported by a user for today.  I can do this very easily with a SQL query in Query Analyzer.  Trying to figure out a way to do it in Service Desk is nearly impossible (for me).  I added a field in User called "Time Today", then added a calculation that looks like this:

       

      import System
      static def GetAttributeValue(User):
      Value = 0
      now = DateTime.Now
      while User._IncidentCol.Notes.im_creation_date >= now.AddDays(-1):
         Value = Value + User._IncidentCol.Notes._IncidentTimeSpent
      return Value

       

      My biggest accomplishment here is actually managing to get the syntax to check successfully.  When I create a query in Web Access based on User, the "Time Today" column is blank.  It ought to be zero at the very least, because the third line sets Value to 0.  _IncidentTimeSpent is mandatory with default and backfill values of 0, so it shouldn't be null.

       

      Of course, I have no clue what I'm doing. I know some SQL and VBscript, but not Boo or C#, etc.

       

      Is a calculation on User the best way to get the total time reported by the user, or is there some easier way to do this?  Am I even on the right track?  (If Query Designer had an advanced mode that allowed you to run regular SQL queries then this would be a peice of cake.)

        • 1. Re: calculation for total time spent by user today
          Apprentice

          I decided to do this the easy way.  I created a VBScript/ASP web page, then put the URL into a web page gadget on a dashboard.

          • 2. Re: calculation for total time spent by user today
            dmshimself ITSMMVPGroup

            A calculation would be OK but perhaps as a scheduled calculation so you can fire it off once a day?  I'd also be a bit careful about relationships from a user to every note they have ever added as this might become a bit large as time goes by.

             

            One suggestion - a regular query can do averages, sums etc across numbers in the query, (agregations), so at first glance those would have been a way forward, but you've already found another.

            • 3. Re: calculation for total time spent by user today
              Apprentice

              I'd also be a bit careful about relationships from a user to every note they have ever added as this might become a bit large as time goes by.

              I will be limiting my query to time range and a specific user or group, so it shouldn't be a problem.

              One suggestion - a regular query can do averages, sums etc across numbers in the query, (agregations), so at first glance those would have been a way forward, but you've already found another.

              Thanks.  I didn' t know an Aggregates tab would show up in the attribute properties if I tried to use grouping.  Unfortunately, it only works in Console and we have everyone using Web Access.  Still, it might be fun to play with.