Incorporating Location Hierarchy on WO Print

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. 1

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. 2 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. 3
  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.

  1. I'm amazed how I ever got stuff down without stack overflow

  2. Similar to pasting rows with transpose in Excel.  

  3. Duh... 

Previous posts:

  1. Setting PM to 1st Day of the Month

    First off, shout out to Bruno Portaluri (@bportaluri) for this tip. He summarized an answer to a question I had just received a few days earlier from one of my Planners. This Planner asked if we could schedule a PM to come out the first Friday of each month. At …

  2. Maximo's Built In Sharing Service

    One of the challenges in my Maximo environment is an efficient way to keep and store 'living documents'. A lot of us have Dropbox, Box, SharePoint, etc., but most of those services are either restricted from widespread access (Dropbox) or have a versioning system (SharePoint) that can make keeping living …

  3. The Division for Destiny Players (Pt 3)

    First and foremost, The Division is a looter shooter. Your character pewpews the bad guys and the bad guys drop new loot. In previous posts I went over how your loot affects your stats and abilities and how your loot creates a class for your character. These two explanation now …

  4. The Division for Destiny Players (Pt 2)

    One of the big differences between The Division and Destiny is your character in The Division (TD) starts out "classless". In Destiny (D1) your first choice was whether you want to be Titan, Hunter, or Warlock.2 Then as you progressed with your character in D1 you gained talents specific …

  5. The Division for Destiny Players (Pt 1)

    A lot of my gaming friends2 picked up The Division when it first came out and then dropped off when the game became a bit of a mess. The Division recently got an update patch that added some new content and fixed some long time plaguing issues. These fixes …