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.
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.
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.
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.
Steps
- Copy an existing work order list report
- Rename the report and clear out all the unnecessary fields
- 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
