Cireson Support Portal "Open Issues" solution

Peter_Nordqvist · May 2017

Hi
Can Cireson please share how to setup the "Open Issues" page?
Or if someone has done similar solution to show PR tickets..

Brian_Wiest · May 2017

Use the dashboard builder to create the page and table.
SQL Table query someone along the lines of ...

SELECT wi.WorkItemId as [Id],

wi.Title,

dsstatus.DisplayString as [Status],

dssupport.DisplayString as [Support Group],

wi.PriorityId as [Priority],

dscategory.DisplayString as [Category],

dssource.DisplayString as [Source],

Problem.KnownError_4CE16CF0_5098_3CBD_3F72_72C2C5201FDB as [Known Issue],

Case When wi.AssignedUser IS Null Then 'UnAssigned'

Else wi.AssignedUser

End as [AssignedTo],

CONVERT(varchar,DATEADD(hh,-5,wi.Created),100) [Created],

CONVERT(varchar,DATEADD(hh,-5,wi.LastModified),100) [Last Modified],

CItem.DisplayName

FROM [WorkItem] wi

LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId

LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID

LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=wi.StatusId

LEFT JOIN [DisplayString] dscategory ON dscategory.ElementID=wi.CategoryId

LEFT JOIN [DisplayString] dssource ON dssource.ElementID=wi.SourceId

LEFT OUTER JOIN Affected_CI$WorkItem_ConfigurationItem AffCI ON wi.Id = AffCI.WorkItemId

LEFT OUTER JOIN ConfigurationItem CItem ON AffCI.ConfigurationItemId = CItem.Id

JOIN [SC2012_ServiceManager].[dbo].[MTV_System$WorkItem$Problem] [Problem] ON Problem.BaseManagedEntityId = WI.Id

WHERE wi.ClassId = '422afc88-5eff-f4c5-f8f6-e01038cde67f'

AND wi.StatusId NOT IN('25EAC210-E091-8AE8-A713-FEA2472F32FF', '7FF92B06-1694-41E5-2DF7-B4D5970D2D2B')

AND Problem.KnownError_4CE16CF0_5098_3CBD_3F72_72C2C5201FDB = 1

Order by ID DESC

This example includes Known issues as I separate them onto different tables.

Brian_Wiest · May 2017