The Usage Report feature is a great way to keep track of what authority terms have or haven’t been used within TMS. The problem is that the Usage Report feature sometimes isn’t extensive enough – in this case covering the Location Authority. I designed this SQL query to help.

SELECT L.LocationID

	,C.ShortName AS Address

	,L.Site

	,L.Room

	,L.UnitType

	,L.UnitNumber

	,L.UnitPosition

	,ObjLocation = 

		(SELECT COUNT(*)

		FROM ObjLocations OL

		WHERE OL.LocationID = L.LocationID )

	,ObjHome = 

		(SELECT COUNT(*)

		FROM ObjComponents OC

		WHERE OC.HomeLocationID = L.LocationID )

	,QuickMove =

		(SELECT COUNT(*)

		FROM QuickMoves QM

		WHERE QM.LocationID = L.LocationID)

	,L.LoginID

FROM Locations L

LEFT JOIN ConAddress C ON L.AddressID = C.ConAddressID

ORDER BY L.[External], L.LocationString

The upside is that unlike the Usage Report, the three functions of the LocationID – Object Locations, Object Home Locations, and Quick Moves – are shown together.

This was part of a project to cleanup and streamline the Location Authority. Since it seemed like a one-off situation, I opted not to design a Crystal Report. If you wanted to create a Crystal Report, I would suggest creating a view as follows:

CREATE VIEW CMP_BI_LocationUsage AS

SELECT L.LocationID

	,ObjLocation = 

		(SELECT COUNT(*)

		FROM ObjLocations OL

		WHERE OL.LocationID = L.LocationID )

	,ObjHome = 

		(SELECT COUNT(*)

		FROM ObjComponents OC

		WHERE OC.HomeLocationID = L.LocationID )

	,QuickMove =

		(SELECT COUNT(*)

		FROM QuickMoves QM

		WHERE QM.LocationID = L.LocationID)

FROM Locations L

You can get the remaining fields by joining the CMP_BI_LocationUsage view and Locations table with an inner join on LocationID.

Thanks for taking the time to read my post – I hope you found it helpful.
Leave feedback in the comments below.

If this post save you time, then consider sharing it with your friends and colleagues. If it saved you some money, then please consider buying me a coffee. If you want to know when I publish new content, then subscribe to my feed. Your support encourages me to write more. Thanks!