ITMS Administrator Group

 View Only

  • 1.  query to build location hierarchy

    Posted Feb 08, 2014 08:33 PM

    Hi SQL gurus, I need help to pull out the location hierarchy for assets almost like below 

     

    Computer

    Computer Location
    PC1 Europe\Germany\Berlin
    PC2 Americas\USA\Kansas\Hays
    PC3 Americas\Canada\Ontario\Toronto
       

     

    Not sure how to build this type of hierarchy. Please help.



  • 2.  RE: query to build location hierarchy

    Posted Feb 09, 2014 03:49 AM
    Do you already have that org group structure created within the console? Does each region represent an org view, or are all region org groups part of one custom org view?


  • 3.  RE: query to build location hierarchy

    Posted Apr 09, 2014 10:45 AM

    Hi skhs,

     

    I have already created an automation with connector to populate an attribute in a dataclass with the full path of a location (that works also with cost center and department), used then as a resourcekey to mapping asset to location during file imports. Here is (in comment the check of the dataclass/attribute populated) :

    SELECT Guid, Path
    FROM dbo.fnAssetHierarchyTreeExcludeChildren('834BC951-D70F-48F4-9E8E-D7E32C68788D', NULL, 0x0, 0x0, 1)
    AS h
    --LEFT JOIN Inv_Location_Hierarchy dch ON dch._ResourceGuid=h.Guid
    --WHERE h.Path<>ISNULL(dch.[Location Hierarchy],'')
    ORDER BY Sort