As soon as it comes to custom reporting in Service Manager, you are sooner or later dealing with SQL queries to get the needed data out of the data warehouse and into your reports. One of my customers came up with the request for a business service report that allows him to display information about business services with all related objects, e.g. service owner, contacts, related items etc. Knowing the data warehouse model helps here, but there are still some things that you have to take care about when it comes to reporting on 1:many relationships.

So lets go with the first query that gets Business Services together with some other properties and relationships out of the data warehouse.

SELECT DISTINCT
SERVICE.DisplayName as “Display Name”,
USERCIOWNED.DisplayName as “Service Owner”,
USERCISERVICEDBY.DisplayName as “Service Contacts”,
USERCIIMPACT.DisplayName as “Service Customers”,
CIRELATED.DisplayName as “Related CIs”

FROM ServiceDimvw as SERVICE

— map serviceentitydimkey to configitementitydimkey to get configitemdimkey
INNER JOIN ConfigItemDimvw CIDIM ON SERVICE.EntityDimKey = CIDIM.EntityDimKey

— config item owned by user –> Service Owner
LEFT JOIN ConfigItemOwnedByUserFactvw CIOWNEDBY ON CIOWNEDBY.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN UserDimvw USERCIOWNED ON USERCIOWNED.UserDimKey=CIOWNEDBY.ConfigItemOwnedByUser_UserDimKey

— config item serviced by user –> Service Contact
LEFT JOIN ConfigItemServicedByUserFactvw CISERVICEDBY ON CISERVICEDBY.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN UserDimvw USERCISERVICEDBY ON USERCISERVICEDBY.UserDimKey=CISERVICEDBY.ConfigItemServicedByUser_UserDimKey

— config item impacts user –> Service Customers
LEFT JOIN ConfigItemImpactsCustomersFactvw CICUSTOMERS ON CICUSTOMERS.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN UserDimvw USERCIIMPACT ON USERCIIMPACT.UserDimKey=CICUSTOMERS.ConfigItemImpactsCustomers_UserDimKey

— config item related to config item –> Related Config Items
LEFT JOIN ConfigItemRelatesToConfigItemFactvw CICI on CICI.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN ConfigItemDimvw CIRELATED on CIRELATED.ConfigItemDimKey=CICI.ConfigItemRelatesToConfigItem_ConfigItemDimKey

WHERE SERVICE.displayname IS NOT NULL
ORDER by SERVICE.displayname

The result looks something like this.

The query returns all business services with some property values and relationships, but depending on the exact configuration of a business service, there can be multiple rows per business service because of normalization. If you have a business service that has 2 related items, the query will return two rows for that service, both rows holding one of the related items. When it comes to reporting this can be painful because there are no possibilities that allows you to group or concatenate all those information of a business service together and return it as one single line. So the query needs some modification. By using temporary tables to store query results, it is possible to concatenate the different values.

SELECT DISTINCT
SERVICE.DisplayName as “Display Name”,
USERCIOWNED.DisplayName as “Service Owner”,
USERCISERVICEDBY.DisplayName as “Service Contacts”,
USERCIIMPACT.DisplayName as “Service Customers”,
CIRELATED.DisplayName as “Related CIs”

INTO ##Table1
FROM ServiceDimvw as SERVICE

— map serviceentitydimkey to configitementitydimkey to get configitemdimkey
INNER JOIN ConfigItemDimvw CIDIM ON SERVICE.EntityDimKey = CIDIM.EntityDimKey

— config item owned by user –> Service Owner
LEFT JOIN ConfigItemOwnedByUserFactvw CIOWNEDBY ON CIOWNEDBY.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN UserDimvw USERCIOWNED ON USERCIOWNED.UserDimKey=CIOWNEDBY.ConfigItemOwnedByUser_UserDimKey

— config item serviced by user –> Service Contact
LEFT JOIN ConfigItemServicedByUserFactvw CISERVICEDBY ON CISERVICEDBY.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN UserDimvw USERCISERVICEDBY ON USERCISERVICEDBY.UserDimKey=CISERVICEDBY.ConfigItemServicedByUser_UserDimKey

— config item impacts user –> Service Customers
LEFT JOIN ConfigItemImpactsCustomersFactvw CICUSTOMERS ON CICUSTOMERS.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN UserDimvw USERCIIMPACT ON USERCIIMPACT.UserDimKey=CICUSTOMERS.ConfigItemImpactsCustomers_UserDimKey

— config item related to config item –> Related Config Items
LEFT JOIN ConfigItemRelatesToConfigItemFactvw CICI on CICI.ConfigItemDimKey=CIDIM.ConfigItemDimKey
LEFT JOIN ConfigItemDimvw CIRELATED on CIRELATED.ConfigItemDimKey=CICI.ConfigItemRelatesToConfigItem_ConfigItemDimKey

WHERE SERVICE.displayname IS NOT NULL
ORDER by SERVICE.displayname

SELECT DISTINCT t1.[Display Name], [Service Owner_], [Service Contacts_], [Service Customers_], [Related CIs_]
INTO ##Table2
FROM ##Table1 as t1

CROSS APPLY ( SELECT DISTINCT [Service Owner] + ‘, ‘
FROM ##table1 t2
WHERE t2.[Display Name] = t1.[Display Name]
FOR XML PATH(”) )  T21 ( [Service Owner_] )

CROSS APPLY ( SELECT DISTINCT [Service Contacts] + ‘, ‘
FROM ##table1 t3
WHERE t3.[Display Name] = t1.[Display Name]
FOR XML PATH(”) )  T31 ( [Service Contacts_] )

CROSS APPLY ( SELECT DISTINCT [Service Customers] + ‘, ‘
FROM ##table1 t4
WHERE t4.[Display Name] = t1.[Display Name]
FOR XML PATH(”) )  T41 ( [Service Customers_] )

CROSS APPLY ( SELECT DISTINCT [Related CIs] + ‘, ‘
FROM ##table1 t5
WHERE t5.[Display Name] = t1.[Display Name]
FOR XML PATH(”) )  T51 ( [Related CIs_] )

— clean commas
UPDATE ##table2
SET [Service Owner_] = LEFT([Service Owner_], LEN([Service Owner_])-1)
WHERE LEN([Service Owner_]) > 0

UPDATE ##table2
SET [Service Contacts_] = LEFT([Service Contacts_], LEN([Service Contacts_])-1)
WHERE LEN([Service Contacts_]) > 0

UPDATE ##table2
SET [Service Customers_] = LEFT([Service Customers_], LEN([Service Customers_])-1)
WHERE LEN([Service Customers_]) > 0

UPDATE ##table2
SET [Related CIs_] = LEFT([Related CIs_], LEN([Related CIs_])-1)
WHERE LEN([Related CIs_]) > 0

SELECT * FROM ##table2

DROP table ##table1
DROP table ##table2

Here’s the result if you fire this query.

This time, every business service only shows up once and all the values are concatenated. Now this query can be used to create your report, e.g. by using SQL Report Builder or something.

Cheers
Marcel