How to alias the workitem as ID to make the ID clickable when one of the joined tables also has ID

Carol_Morrell

Help!  I have created a dashboard and the SQL works fine until I join in a related item - it works fine in SQL, but in my dashboard it doesn't - I think it's because one of the tables I am joining also has an Id - the SQL that works in the dashboard is:

SELECT Wi.WorkItemId AS [Id], Title, StatusDS.DisplayString AS 'Status', AssignedUser, PriorityId, AffectedUser,
CatDS.DisplayString AS 'Category', TierDS.DisplayString AS 'SupportGroup',
LastModified, Created, isparent
FROM [ServiceManagement].[dbo].[WorkItem] Wi
LEFT JOIN DisplayString [StatusDS] ON StatusId = StatusDS.ElementId AND StatusDS.LocaleID = 'ENU'
LEFT JOIN DisplayString [TierDS] ON TierId = TierDS.ElementId AND TierDS.LocaleID = 'ENU'
LEFT JOIN DisplayString [CatDS] ON CategoryID = CatDS.ElementId AND CatDS.LocaleID = 'ENU'
WHERE ClassID = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
AND StatusDS.DisplayString != 'Resolved' AND StatusDS.DisplayString != 'Closed'

But the SQL that I need which works in SQL, but not in the dashboard, has two further joins, see below - but when I add these into the dashboard SQL, it returns no rows (but returns the correct records in SQL) - I am just adding in the Location:

LEFT JOIN Related_CI$WorkItem_ConfigurationItem [RCi] ON wi.Id = RCi.WorkItemId

LEFT JOIN ConfigurationItem [CLoc] ON RCi.ConfigurationItemId = CLoc.Id AND CLoc.ObjectClassName = 'Cireson.AssetManagement.Location'

Carol_Morrell

No worries, I've figured it out, just describing the issue by posting it helped me understand what I was doing wrong - now all sorted!

Carol_Morrell

These joins are just to give me the CLoc.DisplayName AS 'Location', so that I can output the Location in my custom view - but the custom view won't run when I add these into my SQL Table widget.

Carol_Morrell

No worries, I've figured it out, just describing the issue by posting it helped me understand what I was doing wrong - now all sorted!