Reduce Downtime, Reduce Costs. Find Your Overworked Assets.

In this week's post we dive into a tech tip using Maintenance Connection to sort your assets that have had the most work orders raised against them. A CMMS is all about reducing downtime of equipment and reducing costs. High frequency of work on a particular asset earmarks it as an asset that:

  •  breaks down alot.
  •  may need replacement.
  •  may need inspections.
  •  may need PM work to minimise the breakdowns.

Below we discuss how to configure your Maintenance Connection CMMS to allow you to easily find the assets that are getting the most use.

Background

We need a report that shows the Assets that have had the most work orders against them. We don’t want to see all the assets, just the top 10 Assets that have had work orders raised against them.

t1

Challenge

We need to group the work orders by the Asset ID, sort by a count field from highest to lowest and just show the top 10. Whilst the SQL language has a TOP statement, It can be a challenge in how to fit TOP statements in the MC reporter.

New Call-to-action

Pre-Requisite

The approach listed below requires knowledge of SQL Query Syntax and some knowledge of the table and field names used in the MC Database. Many fieldnames can be looked up by finding them in the front end application and clicking the field label.

t2

t3

Solution

MC allows SQL statements to be run with minimal interpretation if they are placed as full SQL statements in the From (SQL) field in the advanced setup tab of the report setup.

t4

Steps

  1. Copy an existing work order list report
  2. Rename the report and clear out all the unnecessary fields
  3. In the From (SQL) Field paste the following SQL expression

 

SELECT TOP 10 AssetID, COUNT(*) AS WorkOrderCount

FROM WO

WHERE AssetID IS NOT NULL

GROUP BY AssetID

ORDER BY COUNT(*) DESC

 

Limitations

Future edits of the report require advanced SQL Knowledge

Reports that use GROUP BY Statements will need where clauses hard coded

in the WHERE line of the statement.

Runtime filtering applied in the Criteria Window will be ignored.

Possible Enhancements:

  • Only include work orders requested in the last 30 days

WHERE AssetID IS NOT NULL AND Requested > GETDATE()-30

  • Only include work orders that are break down work orders

WHERE AssetID IS NOT NULL AND Type <> 'PM'

  • Include AssetName also

SELECT TOP 10 AssetID, AssetName, COUNT(*) AS WorkOrderCount

FROM WO

WHERE AssetID IS NOT NULL AND Type <> 'PM'

GROUP BY AssetID, AssetName

ORDER BY COUNT(*) DESC

Get more news articles like this