I have been tasked to build a report on servicedesk plus that needs to provide information regarding the current mobile phone devices we have assigned to users.

I am able to get the following columns easily through the GUI:

Product Name | Phone Number | Asset State | User | 

However It has been requested that on this report we need to enter the Executive Officer that the user ultimately reports to, this information is sort of available on AD in the sense of we ca see who the user reports to and then who they report to etc. AD is linked with service desk.

So the headers would look like this:

Product Name | Phone Number | Asset State | User | Reports to EO

Iphone | 07712345678 | In Use | Joe Bloggs | Joe Bloggs EO 

I hope this makes sense and you are able to help.

the current code I have is:

2. SELECT
3. "cicd"."COMPONENTNAME" AS "Product Name",
4. "resFields"."UDF_LONG1" AS "Phone Number",
5. "cirest"."DISPLAYSTATE" AS "Asset State",
6. "resUser"."FIRST_NAME" AS "User"  
7. FROM
8. "SmartPhone" 
9. LEFT JOIN
10. "Resources" "cires" 
11. ON "SmartPhone"."CIID"="cires"."CIID" 
12. LEFT JOIN
13. "ComponentDefinition" "cicd" 
14. ON "cires"."COMPONENTID"="cicd"."COMPONENTID" 
15. LEFT JOIN
16. "ResourceState" "cirest" 
17. ON "cires"."RESOURCESTATEID"="cirest"."RESOURCESTATEID" 
18. LEFT JOIN
19. "ResourceOwner" "resOwn" 
20. ON "cires"."RESOURCEID"="resOwn"."RESOURCEID" 
21. LEFT JOIN
22. "AaaUser" "resUser" 
23. ON "resOwn"."USERID"="resUser"."USER_ID" 
24. LEFT JOIN
25. "Resource_Fields" "resFields" 
26. ON "cires"."RESOURCEID"="resFields"."RESOURCEID" 
27. WHERE
28. (
29. "cirest"."DISPLAYSTATE" COLLATE Latin1_General_CI_AS = N'In Use'
30. )  
31. AND cires.CIID IS NOT NULL