SQL Table Widget - Using multiple system definied variables

Steffen_Rakers

Hi everyone,

I'm currently working on a new Team Work view using the SQL Table widget. I have already been able to filter all the relevant data using the system variable "User Support Groups" and a custom filter on the Status Id. I had to do this via sub-select as otherwise it won't show the correct data.

Now I have the problem with implementing the User Language code. I tried it with a sub-sub-select and directly in the first select, but it doesn't work. Is this a limitation of the functionality, a bug or a mistake on my part?

SELECT *
FROM (
	SELECT WI.WorkItemId
		,WI.Title
		,WI.StatusId
		,WI.PriorityId
		,WI.AssignedUser
		,WI.CreatedByUser
		,WI.AffectedUser
		--,WI.TierId
		,SupportGroupDisplay.DisplayString
		,WI.LastModified
		,WI.Created
		,WI.ParentWorkItemId
		,WI.SourceId
		,CASE 
			WHEN WI.Impact IS NULL
				THEN WI.PriorityId
			ELSE WI.Impact
			END AS Impact
		,WI.Urgency
	FROM WorkItem AS WI
	JOIN SplitString(@MySupportGroup, ',') MySupportGroups
		ON MySupportGroups.Item = WI.TierId
	JOIN DisplayString AS SupportGroupDisplay
		ON SupportGroupDisplay.ElementID = WI.TierId
	) AS WorkItems
WHERE @createdFilter
	AND WorkItems.StatusId NOT IN (
		'C7B65747-F99E-C108-1E17-3C1062138FC4'
		,'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF'
		,'2B8830B6-59F0-F574-9C2A-F4B4682F1681'
		)

Geoff_Ross

I don't see LanaguageCode in your query. In the JOIN with DisplayString, add

AND SupportGroupDisplay.LocaleID = @LanguageCode

Geoff_Ross

I don't see LanaguageCode in your query. In the JOIN with DisplayString, add

AND SupportGroupDisplay.LocaleID = @LanguageCode

Steffen_Rakers

Thanks @Geoff_Ross, that was the obvious mistake I missed. Now it works like a charm :)