If you are using Asset, you most likely have locations setup in a hierarchy. Building #1 has 3 floors, each floor has 3 sections, each section has two areas, etc. How can you find out easily how many assets are located in any part, or all of that building based upon location? If you were going through a hardware refresh and needed to know how many Dell Optiplex 745 desktops were still in a building, could you find the answer? Could you break that answer down by locations within that location?
This SQL will answer that question and many more very quickly. Using SQL that was posted under another download (recursive location search) as a base, we will expand upon that and allow it to do something really useful for us. The attached SQL will allow you to filter by Asset Type, Manufacturer and Model number from any point within the location hierarchy, but you can easily expand this to search for anything.
The data displayed in Organization View and Groups is a starting point, but it isn't very friendly for doing in-depth analysis of data.
I have already made this kind of work to get all the subfolders in a specific folder, and the compete path of the objects inside, but in this case there is a useful table named FolderBaseFolder which give you the depth degree so it's easier.
To get hierarchy for Organizational Types, your solution is good but perfectible because it's not a real recursive loop (until existing node), and also you filter on the Location Name that can be the same for several locations, and if you want only US\Headquarter and not Worldwilde\Headquarter you cannot...
To resolve this, I just use a default function fnAssetHierarchyTreeExcludeChildren to get all the nodes in the hierarchy, and also I can filter the full hierarchy path name. Then you just have to join the result of the selected hierarchy to your asset list.
Here is an exemple of the usage with Location :
SELECT a.Guid as [Asset Guid], a.Name as [Asset Name], h.Guid as [Location Guid], h.[Path] as [Location Path] FROM vItem a INNER JOIN ResourceAssociation ra on ra.ParentResourceGuid=a.Guid and ra.ResourceAssociationTypeGuid='05DE450F-39EA-4AAE-8C5F-77817889C27C' INNER JOIN dbo.fnAssetHierarchyTreeExcludeChildren ('834BC951-D70F-48F4-9E8E-D7E32C68788D' ,NULL ,0x0 ,0x0 --option 1 : filter by StartNodeGuid ,1) h on h.Guid=ra.ChildResourceGuid WHERE [Path] like 'France%' --option 2 : filter by PathName ORDER BY [Sort]