Adding a Listing KPI in MEX Dashboard

The new MEX Dashboard comes with a suite of standard KPIs to assist you with quickly gaining access to the status of your maintenance activities. To get more out of your system the Dashboard is fully customizable, meaning that just like MEX’s Reports suite, you’re able to edit these standard KPIs, as well as create your own to report on what is relevant to you. In this tech tip we’re going to go through how to create a Listing KPI in the new MEX Dashboard.

Note that in order to create and edit Dashboard KPIs, this will need to be enabled in your User Options;

You’re able to access your MEX Dashboard from the hamburger menu found in the top right-hand corner of your MEX screen;

Once in the Dashboard, login with your credentials. On the left-hand side of the screen, underneath the different Categories, you’ll see an option that says ‘Manage’. Select this option to bring up another dialogue box.

Once in the ‘Manage KPIs’ area, scroll down to the bottom to see additional options. We’re going to select the ‘Create’ button;

Next, when prompted, we’re going to select the KPI type – in this example, we will select ‘Listing’;

In the next screen, we are shown a box with the KPI details. Let’s begin by populating the Name and Description fields, for example:

• Name: Assets Without Documents (List)
• Description: A list displaying which Assets do not have at least one Document attached to them.
• Group: Assets

Next, to add our datasource (SQL Query), we’re going to enter our query underneath the ‘List Datasource’ section on the KPI Details screen. We also need to make sure that the ‘Number of Columns’ and the name of our columns appropriately matches our datasource query.

List Datasource:

SELECT A.AssetNumber, A.AssetDescription, PA.AssetNumber AS ParentAssetNumber FROM Asset A
LEFT JOIN Asset PA ON PA.AssetID = A.ParentAssetID
WHERE A.IsAsset = 1
AND A.AssetID NOT IN (SELECT EntityID FROM Document D WHERE D.EntityName LIKE 'Asset')
ORDER BY A.AssetNumber

Number of Columns: 3

• Column 1 Name: Asset Number
• Column 2 Name: Asset Description
• Column 3 Name: Parent Asset Number

Please note, the above SQL query is a simple example for the purposes of this tech tip – this is a simple listing of what Assets have IDs which do not feature in the Document table. If it were to be run in SQL, the query would return;

Once you’ve filled out those details, exit out of this screen to save by selecting the X button in the top right.

From the ‘Manage KPIs’ screen, you can choose if you wish to make your newly created KPI a ‘Favourite’, meaning it appears upon the first loaded screen of the MEX Dashboard. Close out again to save.

Navigate to the ‘Assets’ category on the left-hand side of the Dashboard. You should now see your newly created KPI displaying which Assets do not have Documents attached;

Listings also have the ability to display multiple views. In this example, we can create a single KPI to list which jobs are due to start this week, and then separate these by the Trade Codes we have in the system.

Repeat the above steps to create a listing KPI, and we will populate the description fields as follows;

• Name: Work Due To Start This Week
• Description: Work Orders due to start this week, separated by Trades assigned.
• Group: Work Orders
• Number of Columns: 5
• Column 1 Name: Trade Name
• Column 2 Name: Trade Code
• Column 3 Name: WO Number
• Column 4 Name: WO Description
• Column 5 Name: Due Start Date

In the SQL Datasource section, we’re going to enter the following query;

SELECT Contact.FirstName + ' ' + Contact.LastName AS TradeName, TradeCode.TradeCodeName, WorkOrder.WorkOrderNumber, WorkOrder.WorkOrderDescription, WorkOrder.DueStartDateTime FROM WorkOrder
LEFT JOIN WorkOrderTrade ON WorkOrderTrade.WorkOrderID = WorkOrder.WorkOrderID
LEFT JOIN TradeCode ON TradeCode.TradeCodeID = WorkOrderTrade.TradeCodeID
LEFT JOIN Contact ON Contact.ContactID = WorkOrderTrade.TradeContactID
WHERE WorkOrder.DueStartDateTime BETWEEN GETDATE() AND DATEADD(day,7,GETDATE())
AND TradeCodeName LIKE 'Tradesman'

We will label this view ‘Tradesman’.

Next, in View 2, we’re going to enter the same query, but note that we will change the last line which says ‘Tradesman’ to be ‘Mechanic’;

SELECT Contact.FirstName + ' ' + Contact.LastName AS TradeName, TradeCode.TradeCodeName, WorkOrder.WorkOrderNumber, WorkOrder.WorkOrderDescription, WorkOrder.DueStartDateTime FROM WorkOrder
LEFT JOIN WorkOrderTrade ON WorkOrderTrade.WorkOrderID = WorkOrder.WorkOrderID
LEFT JOIN TradeCode ON TradeCode.TradeCodeID = WorkOrderTrade.TradeCodeID
LEFT JOIN Contact ON Contact.ContactID = WorkOrderTrade.TradeContactID
WHERE WorkOrder.DueStartDateTime BETWEEN GETDATE() AND DATEADD(day,7,GETDATE())
AND TradeCodeName LIKE 'Tradesman'

(You are able to use this query in your MEX system, and simply change the Trade Code Name at the end of the query to match your system’s Trade Codes.)

Also, in while in the View 2 screen, select ‘Is Enabled?’ for this to display in your KPI.

Feel free to repeat this process in the other Views in the KPI Details screen. Finally, you can also create a view to show what Work Orders have no Trades. We can use this query for that View;

SELECT Contact.FirstName + ' ' + Contact.LastName AS TradeName, TradeCode.TradeCodeName, WorkOrder.WorkOrderNumber, WorkOrder.WorkOrderDescription, WorkOrder.DueStartDateTime FROM WorkOrder
LEFT JOIN WorkOrderTrade ON WorkOrderTrade.WorkOrderID = WorkOrder.WorkOrderID
LEFT JOIN TradeCode ON TradeCode.TradeCodeID = WorkOrderTrade.TradeCodeID
LEFT JOIN Contact ON Contact.ContactID = WorkOrderTrade.TradeContactID
WHERE WorkOrder.DueStartDateTime BETWEEN GETDATE() AND DATEADD(day,7,GETDATE())
AND TradeCodeName IS NULL

Now when you view that KPI, you should see the different view options able to be selected down the bottom of the listing, e.g.;

As a reminder, writing these SQL queries for KPIs is not covered by the Support agreement, however our team are always on hand to help answer your questions as best we can, and steer you in the right direction to get the data you need in your listings. You can reach MEX Support on support@mex.com.au  or on +61 7 3392 4777.