My company has recently been challenged with how we're using Maximo. On one front we're deploying Maximo to new sites that are transitioning off of another EAM platform. On the other front we have a facility that's tripling the size of the installed asset count. Both sites are dealing with information overload and we wanted to find a way to help our technicians more easily identify where equipment is located in the facility.

As I was doing training at one facility they asked why don't we print the Location hierachy on work orders. An example of the Location hierarchy in Maximo is:

I said the hierarchy can get quite complex and to have the waterfalled version you see above would take up too much room on a work order. The maintenance manager was adamant that this should be included as a go live requirement because the system they were moving off had a similar drilldown on the location hierachy on the work order.

I looked back out our project charter and in my own words,

"Ensure parity functionality between \<Prod-X> and Maximo."

Seeing I needed to meet this request I started looking at how I've queried on the Location Hiearchy in the past. Normally when the Loc Hiearchy has been selected the results would be multiple rows.

I knew I could suppress the duplicate location field, but that wouldn't work because of the space it would take up on the work order print. Doing some research I found a SQL statement that could concatenate row results into a single line: for xml path. ¹

The concept of the for xml path statement is take the result rows from a select statement and essentially transpose the results in to a single field. ² After playing around with the for XML path function I got a statement that would return the location hierarchy in a single result row.

select stuff((select ' / ' + a.ancestor + ' (' + b.description + ')'
from locancestor a
join locations b on b.location=a.ancestor and b.siteid=a.siteid
where a.location='burner_3_2' order by a.locancestorid desc for xml path ('')), 1, 1, '')

This worked and gave me the results in a single line:

I added the select statement into our WO print report and added an addition reference back to the work order's original location:

+ " workorder.location, locations.description as locdesc, workorder.istask, workorder.orgid, workorder.cinum, locations.lo1, "
+ " (select stuff((select ' / ' + a.ancestor + ' (' + b.description + ')' "
+ " from locancestor a join locations b on b.location=a.ancestor and b.siteid=a.siteid "
+ " where a.location=workorder.location order by a.locancestorid desc " 
+ " for xml path ('')), 1, 1, '')) as loc_path, "
+ " ...

So now when the WO is printed, there is a Location Hierarchy list that technicians can use to identify where they should be working.

The caveat to this function is:

1. If the work order is not charged against a Location, the hierarchy will be empty. ³
2. If the work order is charged against an orphaned location, the hierarchy will be empty.

An orphaned location is that the location has been added to Maximo, but it was not associated with a System and does not have a parent. Since an orphaned location does not have a parent it won't be on the locancestor, and therefore would return zero results in our new select statement.