SMP - Reports - Departments Location

 SMP

Reports

Department's Location

Settings | Notification Server | Resource and Data Class Settings | Resource Associations | CMDB Assocation Types |  Department's Location

Details

Settings | Notification Server | Resource and Data Class Settings | Resource Types | Organizational Types |  Department

Settings | Notification Server | Resource and Data Class Settings | Resource Types | Organizational Types |  Location

If you go to a Department in

Home | Service and Asset Management | Manage Configuration Items

CI Management | Organisational Types |  Department

Scroll down to near the bottom there is a section for

Department's Location

This allows you to edit which Location a Department is in.

If you then need this information in a Report you can use the following SQL.

DECLARE @LocationName AS nvarchar(255) = NULL
DECLARE @DepartmentNameTerm AS nvarchar(255) = NULL

SELECT
  d.[Guid] AS [DepartmentGuid],
  d.[Name] AS [DepartmentName],
  l.[Guid] AS [LocationGuid],
  l.[Name] AS [LocationName]
FROM
  vRM_Department_Item d
  LEFT OUTER JOIN vRM_Location_Item l
  INNER JOIN ResourceAssociation AS ra1 ON l.Guid = ra1.ChildResourceGuid 
    ON ra1.ParentResourceGuid = d.Guid 
    AND ra1.ResourceAssociationTypeGuid = '9e4ced04-c03f-473c-b0c3-1c1d1b8df49b'
WHERE
(
((@LocationName IS NULL and (l.Name LIKE '%' or l.Name IS NULL)) OR (@LocationName IS NOT NULL AND l.Name = @LocationName))
AND
((@DepartmentNameTerm IS NULL and (d.Name LIKE '%' or d.Name IS NULL)) OR (@DepartmentNameTerm IS NOT NULL AND d.Name LIKE '%' + @DepartmentNameTerm + '%'))