Maximo Open Forum

I am looking for some help building either a report or KPI that will show the change history of work orders.

We are looking to add some metrics to the staff that updates/closes work orders throughout the day. To achieve this I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range.

I built a QBR that shows me the history within a date range, but there are a few issues.

1. The QBR reports back the entire change history for a work order even if those changes happened outside the designated date range. Is there a way to filter out these entries and only return the dates within the range from the long list?
2. Also the QBR does not show what status the work order was changed to, it only shows what the current status of the work order is.

I attached a copy of what I was able to produce with a QBR for reference. This would typically only be needed on smaller date ranges, say 1 month max. Just looking for the total number of status updates a user has done within the date range.

Thanks in advance for any help/insights!

Can you include the SQL used in your report?

For starters, it seems it is showing every status change, so it looks like the report is using WORKORDER and WOSTATUS and they are properly joined (good!), but in the report design the field WORKORDER.STATUS was used, where it ought to be WOSTATUS.STATUS.  (Actually, if that's all you need in the report, you could JUST use the WOSTATUS table.  WOSTATUS has the WONUM attribute too.)

Then it seems like whatever has been done for parameter binding on a date range might also have been done to the WORKORDER table directly instead of to WOSTATUS.

Related additional comment: I don't think it would be possible to do this as a KPI.  If you just wanted to see how many had a status change, that might be doable; but since it sounds like you also want to see what status it was changed to. . .pretty sure you can't have a Group By in there.  A KPI just gives one aggregated value.

I am looking for some help building either a report or KPI that will show the change history of work orders.

We are looking to add some metrics to the staff that updates/closes work orders throughout the day. To achieve this I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range.

I built a QBR that shows me the history within a date range, but there are a few issues.

1. The QBR reports back the entire change history for a work order even if those changes happened outside the designated date range. Is there a way to filter out these entries and only return the dates within the range from the long list?
2. Also the QBR does not show what status the work order was changed to, it only shows what the current status of the work order is.

I attached a copy of what I was able to produce with a QBR for reference. This would typically only be needed on smaller date ranges, say 1 month max. Just looking for the total number of status updates a user has done within the date range.

Thanks in advance for any help/insights!

Travis, 

I built the report using the adhoc report UI in maximo so I'm not 100% sure what the full SQL used for the report is. These are the SQL Queries that are at the bottom of the report:

I used the report manager application to add date parameters to the report, users enter the date range when running the report. You are correct that I used the "STATUSDATE" attribute from the work order table. Should this be changed?

On the KPI note - If I went the KPI route, I was thinking I'd create one for each user, and just have KPI give the number of WO changes that 1 user has made. Not sure if you can add dynamic date ranges to KPIs instead of having it be a predefined set range. If I went this route I wouldn't be concerned with what status it was changed to, and instead would just want to see the total number of status changes made by a user. 

Please ignore my ignorance on some things maximo/SQL I've worked with CMMS software for years, but this is my first venture into the Maximo world. 

Thanks for the help!

Can you include the SQL used in your report?

For starters, it seems it is showing every status change, so it looks like the report is using WORKORDER and WOSTATUS and they are properly joined (good!), but in the report design the field WORKORDER.STATUS was used, where it ought to be WOSTATUS.STATUS.  (Actually, if that's all you need in the report, you could JUST use the WOSTATUS table.  WOSTATUS has the WONUM attribute too.)

Then it seems like whatever has been done for parameter binding on a date range might also have been done to the WORKORDER table directly instead of to WOSTATUS.

Related additional comment: I don't think it would be possible to do this as a KPI.  If you just wanted to see how many had a status change, that might be doable; but since it sounds like you also want to see what status it was changed to. . .pretty sure you can't have a Group By in there.  A KPI just gives one aggregated value.

I am looking for some help building either a report or KPI that will show the change history of work orders.

We are looking to add some metrics to the staff that updates/closes work orders throughout the day. To achieve this I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range.

I built a QBR that shows me the history within a date range, but there are a few issues.

1. The QBR reports back the entire change history for a work order even if those changes happened outside the designated date range. Is there a way to filter out these entries and only return the dates within the range from the long list?
2. Also the QBR does not show what status the work order was changed to, it only shows what the current status of the work order is.

I attached a copy of what I was able to produce with a QBR for reference. This would typically only be needed on smaller date ranges, say 1 month max. Just looking for the total number of status updates a user has done within the date range.

Thanks in advance for any help/insights!

>>I used the "STATUSDATE" attribute from the work order table. Should this be changed?

Depends on what you're trying to achieve.

WORKORDER.STATUSDATE is going to have the datetime for the most recent status change.  WOSTATUS.STATUSDATE, with the 1:m relationship between WORKORDER and WOSTATUS, is going to have the datetime for every status change.

For example, suppose you had a Work Order that was:

Created (WAPPR) on 7/1/23

Approved (APPR) on 8/1/23

In Progress (INPRG) on 9/1/23

Completed (COMP) on 10/1/23

Closed (CLOSE) on 1/1/24

If you use WORKORDER.STATUSDATE, the only date under consideration is going to be 1/1/24.  If you use WOSTATUS.STATUSDATE, all of the above dates are candidates.  You originally stated:

"I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range"

So, if the user put in the date range of Q4 2023 (Oct 1 12:00 AM - Dec 31 11:59 PM), then, if the query were based on WORKORDER.STATUSDATE, this WO would not be included, since the Status Date is 1/1/24, just outside the date range; but if based on WOSTATUS.STATUSDATE, it would be included, since it was changed to COMP on 10/1/23 which is within the date range.

But if you meant ". . .see how many Work Orders were Closed within a specific date range" then you'd be fine in using WORKORDER.STATUSDATE.  That's the last STATUS and STATUSDATE the Work Order record would ever get (unless your organization makes a synonym of the CLOSE status; or, there is the Edit History action, which adds another entry into the WOSTATUS table but does not change the Work Order's STATUSDATE).

>>On the KPI note - If I went the KPI route, I was thinking I'd create one for each user

Might be burdensome to keep up with as users/people come and go, but up to you and your company's needs

>>Not sure if you can add dynamic date ranges to KPIs instead of having it be a predefined set range

Absolutely you can!  For example, in SQL Server, I'd include in the Where Clause something like:   statusdate BETWEEN getdate() AND (getdate() - 30)      (that's very simplistic, I know I'd have to adjust it so that it starts at the start of the day instead of whatever moment I happened to run the query)

Travis, 

I built the report using the adhoc report UI in maximo so I'm not 100% sure what the full SQL used for the report is. These are the SQL Queries that are at the bottom of the report:

I used the report manager application to add date parameters to the report, users enter the date range when running the report. You are correct that I used the "STATUSDATE" attribute from the work order table. Should this be changed?

On the KPI note - If I went the KPI route, I was thinking I'd create one for each user, and just have KPI give the number of WO changes that 1 user has made. Not sure if you can add dynamic date ranges to KPIs instead of having it be a predefined set range. If I went this route I wouldn't be concerned with what status it was changed to, and instead would just want to see the total number of status changes made by a user. 

Please ignore my ignorance on some things maximo/SQL I've worked with CMMS software for years, but this is my first venture into the Maximo world. 

Thanks for the help!

Can you include the SQL used in your report?

For starters, it seems it is showing every status change, so it looks like the report is using WORKORDER and WOSTATUS and they are properly joined (good!), but in the report design the field WORKORDER.STATUS was used, where it ought to be WOSTATUS.STATUS.  (Actually, if that's all you need in the report, you could JUST use the WOSTATUS table.  WOSTATUS has the WONUM attribute too.)

Then it seems like whatever has been done for parameter binding on a date range might also have been done to the WORKORDER table directly instead of to WOSTATUS.

Related additional comment: I don't think it would be possible to do this as a KPI.  If you just wanted to see how many had a status change, that might be doable; but since it sounds like you also want to see what status it was changed to. . .pretty sure you can't have a Group By in there.  A KPI just gives one aggregated value.

I am looking for some help building either a report or KPI that will show the change history of work orders.

We are looking to add some metrics to the staff that updates/closes work orders throughout the day. To achieve this I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range.

I built a QBR that shows me the history within a date range, but there are a few issues.

1. The QBR reports back the entire change history for a work order even if those changes happened outside the designated date range. Is there a way to filter out these entries and only return the dates within the range from the long list?
2. Also the QBR does not show what status the work order was changed to, it only shows what the current status of the work order is.

I attached a copy of what I was able to produce with a QBR for reference. This would typically only be needed on smaller date ranges, say 1 month max. Just looking for the total number of status updates a user has done within the date range.

Thanks in advance for any help/insights!

Just so I understand correctly, with your example about the report (while using WORKORDER.STATUSDATE) only considering the 1/1/24 date, does that mean it is not looking at the other dates even though they are on the report? I did try to change the parameter in report administration to use the WOSTATUS.STATUSDATE, but received an error saying 

"Parameters for Ad Hoc reports cannot be bound to related attributes. Specify an attribute that is in the main database table of the application that is selected for the report"

Thinking maybe I am doing something wrong here?

To clarify what I am trying to do, I have been asked to give metrics on how many work orders certain staff members have updated within a date range. So I would like to include all the status changes, not just closed status, so long as they are in the desired date range. 

Maintaining the KPIs isn't much of a concern as the way we are set up only a select few staff members are changing work order status about 99.9% of the time and everyone in those positions has been here for quite some time. So on the rare occasion that I have to swap out a user variable in a KPI, I have no problem doing so.

When I mention entering dynamic dates into a KPI, I mean are you able to enter a date range when running the KPI. In your example it would see that the date would be permanently in the KPI (always looking X amount of days back) where as I would like to enter any date range before running the KPI. Looking for a similar function as entering date parameters when running an Ad Hoc report. I didn't believe it was possible to enter in a date range parameter on a KPI, which is why I headed down the report path. 

>>I used the "STATUSDATE" attribute from the work order table. Should this be changed?

Depends on what you're trying to achieve.

WORKORDER.STATUSDATE is going to have the datetime for the most recent status change.  WOSTATUS.STATUSDATE, with the 1:m relationship between WORKORDER and WOSTATUS, is going to have the datetime for every status change.

For example, suppose you had a Work Order that was:

Created (WAPPR) on 7/1/23

Approved (APPR) on 8/1/23

In Progress (INPRG) on 9/1/23

Completed (COMP) on 10/1/23

Closed (CLOSE) on 1/1/24

If you use WORKORDER.STATUSDATE, the only date under consideration is going to be 1/1/24.  If you use WOSTATUS.STATUSDATE, all of the above dates are candidates.  You originally stated:

"I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range"

So, if the user put in the date range of Q4 2023 (Oct 1 12:00 AM - Dec 31 11:59 PM), then, if the query were based on WORKORDER.STATUSDATE, this WO would not be included, since the Status Date is 1/1/24, just outside the date range; but if based on WOSTATUS.STATUSDATE, it would be included, since it was changed to COMP on 10/1/23 which is within the date range.

But if you meant ". . .see how many Work Orders were Closed within a specific date range" then you'd be fine in using WORKORDER.STATUSDATE.  That's the last STATUS and STATUSDATE the Work Order record would ever get (unless your organization makes a synonym of the CLOSE status; or, there is the Edit History action, which adds another entry into the WOSTATUS table but does not change the Work Order's STATUSDATE).

>>On the KPI note - If I went the KPI route, I was thinking I'd create one for each user

Might be burdensome to keep up with as users/people come and go, but up to you and your company's needs

>>Not sure if you can add dynamic date ranges to KPIs instead of having it be a predefined set range

Absolutely you can!  For example, in SQL Server, I'd include in the Where Clause something like:   statusdate BETWEEN getdate() AND (getdate() - 30)      (that's very simplistic, I know I'd have to adjust it so that it starts at the start of the day instead of whatever moment I happened to run the query)

Travis, 

I built the report using the adhoc report UI in maximo so I'm not 100% sure what the full SQL used for the report is. These are the SQL Queries that are at the bottom of the report:

I used the report manager application to add date parameters to the report, users enter the date range when running the report. You are correct that I used the "STATUSDATE" attribute from the work order table. Should this be changed?

On the KPI note - If I went the KPI route, I was thinking I'd create one for each user, and just have KPI give the number of WO changes that 1 user has made. Not sure if you can add dynamic date ranges to KPIs instead of having it be a predefined set range. If I went this route I wouldn't be concerned with what status it was changed to, and instead would just want to see the total number of status changes made by a user. 

Please ignore my ignorance on some things maximo/SQL I've worked with CMMS software for years, but this is my first venture into the Maximo world. 

Thanks for the help!

Can you include the SQL used in your report?

For starters, it seems it is showing every status change, so it looks like the report is using WORKORDER and WOSTATUS and they are properly joined (good!), but in the report design the field WORKORDER.STATUS was used, where it ought to be WOSTATUS.STATUS.  (Actually, if that's all you need in the report, you could JUST use the WOSTATUS table.  WOSTATUS has the WONUM attribute too.)

Then it seems like whatever has been done for parameter binding on a date range might also have been done to the WORKORDER table directly instead of to WOSTATUS.

Related additional comment: I don't think it would be possible to do this as a KPI.  If you just wanted to see how many had a status change, that might be doable; but since it sounds like you also want to see what status it was changed to. . .pretty sure you can't have a Group By in there.  A KPI just gives one aggregated value.

I am looking for some help building either a report or KPI that will show the change history of work orders.

We are looking to add some metrics to the staff that updates/closes work orders throughout the day. To achieve this I am looking to create a report or KPI that will allow me to see how many work orders have had their status updated in the WOTRACK application within a specific date range.

I built a QBR that shows me the history within a date range, but there are a few issues.

1. The QBR reports back the entire change history for a work order even if those changes happened outside the designated date range. Is there a way to filter out these entries and only return the dates within the range from the long list?
2. Also the QBR does not show what status the work order was changed to, it only shows what the current status of the work order is.

I attached a copy of what I was able to produce with a QBR for reference. This would typically only be needed on smaller date ranges, say 1 month max. Just looking for the total number of status updates a user has done within the date range.

Thanks in advance for any help/insights!