Maximo Open Forum

 View Only

 Report that shows the time each of my tickets spent in each status?

  • Analytics
BJ Hui's profile image
BJ Hui posted 05-26-2022 08:09
Hi, i'd like to see if it's possible to generate a report that shows the time that each of my tickets spent in each status.  Or, perhaps if there is a way to generate a historical report that displays the status of tickets at a specified historical point in time.   Figuring option 1 is more feasible than 2, thx in advance for any feedback or info!
Eric Godel's profile image
Eric Godel posted 05-27-2022 09:21
Hi BJ, yes both are possible, these sound like a fun reports to create!  For tickets I'm showing the TKSTATUS table keeps track of the status history.  SR's, which are a view of tickets, use this table.  Work orders would use the WOSTATUS table for the same information.

I am picturing a report ordered by date that will display row by row the current status and the time it spent there per ticket/work order/service request, and the time it takes would be a calculation between the rows, this would be using the LEAD() or LAG() functions.  There may be another fancy way to select the data you need to do the calculation, but this is the first that comes to my mind.

For the 2nd report, getting the current status at a point in time, I've seen this done quite a bit in automation scripts and reporting.  I am picturing at least a date parameter you would feed the report that is the date you want to know what status the ticket/sr/wo was in at that point in time.  Then you would query the respective status history tables looking for the first status less than that date.

Hope that helps!  Helps!  have fun with your reports!

Eric
BJ Hui's profile image
BJ Hui posted 05-27-2022 10:20
Hi Eric, thank you and great to hear positive feedback on this!  Unfortunately, I'm a newbie and thinking maybe I'm missing something in my search terms?  When i create a report, i see WOSTATUS but it outputs only the current WO status.  Thanks again for any help on this!
Eric Godel's profile image
Eric Godel posted 05-27-2022 15:54
I'm not sure how you are writing your query, but I can defiantly tell you the WOSTATUS table holds all the status's per work order with dates associated with it.

Send me a direct message and maybe I can lend you a little more personal help.

Thanks
Adam Bearden's profile image
Adam Bearden posted 07-26-2023 13:53

I have a select from one of my coworkers.  My only thing is I am having a hard time turning this select into a where clause as I have a request to create a result set showing each status and how many days it was in that particular status.  I just cannot get it modified correctly.  Here is the select:

select SITEID, ORGID, WONUM, CALNUM, STATUS, CHANGEDATE, NEXT_CHANGEDATE,

       --to_char(CHANGEDATE,'YYYYMMDD HH24:MI:SS') CHANGEDATE,

       --to_char(NEXT_CHANGEDATE,'YYYYMMDD HH24:MI:SS') NEXT_CHANGEDATE,

       ASSET_UTILITIES.CALENDAR_TIME_HOURS

          (ORGID, CALNUM, CHANGEDATE, NEXT_CHANGEDATE, CHANGEDATE, NEXT_CHANGEDATE) STATUS_HOURS

from (

      select W.WONUM, W.SITEID, W.ORGID, W.ASSETNUM, A.CALNUM, S.STATUS, S.WOSTATUSID,

             S.CHANGEDATE,

             nvl(lead(S.CHANGEDATE) over ( order by S.WONUM, S.SITEID, S.CHANGEDATE, S.WOSTATUSID),

                 sysdate) NEXT_CHANGEDATE

        from WORKORDER W

        left join ASSET A

          on W.ASSETNUM = A.ASSETNUM

         and W.SITEID = A.SITEID

        left join WOSTATUS S

          on W.WONUM = S.WONUM

         and W.SITEID = S.SITEID

       where W.WONUM in ( '624671','624672','624673','624674')

         and W.SITEID = 'LEAD'

      ) A

order by WONUM, SITEID, CHANGEDATE, WOSTATUSID

Related Content