Home› Analyst Portal

Help with SQL query for custom dashboard

Service_Desk2Service_Desk2 Member IT Monkey ✭
edited January 2019 in Analyst Portal
Hi, 

We are trying to create a dashboard that shows us unassigned workitems for a specified group, and then a count for those. Also the same dashboard should show all open workitems for a specified group.

I found this one to get me started, https://community.cireson.com/discussion/3708/how-to-create-custom-views-on-analyst-portal-contain-both-incident-and-service-requests

The problem is if i use the suggestion in the link above all i see is incidents, i need to see everything: SR#, PR#, CR# and so on.
If i remove the  "AND SdS.LocaleID = 'SVE'" from the suggestion above i get everything, the problem is that i can see the same workitem 2-4 times.

The next thing is that i only want to see open workitems, nothing that is closed or solved.

this is my query
-----
SELECT 
      WorkItemId AS [ID]
      ,Title AS 'TITEL'
      ,sds.DisplayString AS 'STATUS'
      ,AssignedUser AS 'TILLDELAD'
      ,PriorityId AS 'PRIORITET'
      ,AffectedUser AS 'BERÖRD ANVÄNDARE'
      ,CdS.DisplayString AS 'KATEGORI'
      ,tds.DisplayString AS 'SUPPORTGRUPP'
      ,Created AS 'SKAPAD DATUM' 
      
  FROM [CiresonPortal].[dbo].[WorkItem] wi
  LEFT JOIN DisplayString SdS
  ON SdS.ElementID = wi.StatusId
  AND SdS.LocaleID = 'SVE'

  LEFT JOIN DisplayString TdS
  ON TdS.ElementID = wi.TierId
  AND TdS.LocaleID = 'SVE'

  LEFT JOIN DisplayString CdS
  ON CdS.ElementID = wi.CategoryId
  AND CdS.LocaleID = 'SVE'

WHERE
tds.DisplayString = 'Helpdesk' AND sds.DisplayString = 'Aktiv'
  OR 
tds.DisplayString = 'Helpdesk'' AND sds.DisplayString = 'Skickad'
  OR
tds.DisplayString = 'Helpdesk'' AND sds.DisplayString = 'Pågår'
  OR
tds.DisplayString = 'Helpdesk'' AND sds.DisplayString = 'Ny'
  OR
tds.DisplayString = 'Helpdesk'' AND sds.DisplayString = 'Redigering'

So as i said, this will give me all active IR#, but nothing else.
If i remove the "AND ***.LocaleID = 'SVE'" i get everything, but doublets.

I tried to use a search for my dashboard, but there doesn't seem to be any way to filter on support group.

Best Answer

  • Service_Desk2Service_Desk2 Member IT Monkey ✭
    Answer ✓
    We ended up with this query:

    SELECT DISTINCT
          a.WorkItemId AS 'ID',
      a.Title AS 'TITEL',
      (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as b
    WHERE b.ElementID = a.StatusId) AS 'STATUS',
          a.AssignedUser AS 'TILLDELAD',
      (CASE 
    WHEN LEN(a.PriorityId) < 2
    THEN
    a.PriorityId 
    ELSE
    (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as e
    WHERE e.ElementID = a.PriorityId)
    END) AS 'PRIORITET',  
          a.AffectedUser AS 'BERÖRD ANVÄNDARE',
       (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as c
    WHERE c.ElementID = a.TierId) AS 'KATEGORI',
    (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as d
    WHERE d.ElementID = a.CategoryId) AS 'SUPPORTGRUPP',
      a.Created AS 'SKAPAD DATUM'
    FROM 
    [CiresonPortal].[dbo].[WorkItem] AS a
    WHERE (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as f
    WHERE f.ElementID = a.StatusId) NOT IN ('Stängd', 'Closed', 'Avbruten', 'Cancelled', 'Löst', 'Resolved', 'Slutförd', 'Completed', 'Misslyckades', 'Failed') -- status ni inte vill se
    AND 
    (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as c
    WHERE c.ElementID = a.TierId) = 'Helpdesk' -- grupp
    ORDER BY 'ID','TITEL','STATUS','TILLDELAD','PRIORITET','BERÖRD ANVÄNDARE','KATEGORI','SUPPORTGRUPP', 'SKAPAD DATUM'

Answers

  • Service_Desk2Service_Desk2 Member IT Monkey ✭
    Answer ✓
    We ended up with this query:

    SELECT DISTINCT
          a.WorkItemId AS 'ID',
      a.Title AS 'TITEL',
      (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as b
    WHERE b.ElementID = a.StatusId) AS 'STATUS',
          a.AssignedUser AS 'TILLDELAD',
      (CASE 
    WHEN LEN(a.PriorityId) < 2
    THEN
    a.PriorityId 
    ELSE
    (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as e
    WHERE e.ElementID = a.PriorityId)
    END) AS 'PRIORITET',  
          a.AffectedUser AS 'BERÖRD ANVÄNDARE',
       (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as c
    WHERE c.ElementID = a.TierId) AS 'KATEGORI',
    (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as d
    WHERE d.ElementID = a.CategoryId) AS 'SUPPORTGRUPP',
      a.Created AS 'SKAPAD DATUM'
    FROM 
    [CiresonPortal].[dbo].[WorkItem] AS a
    WHERE (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as f
    WHERE f.ElementID = a.StatusId) NOT IN ('Stängd', 'Closed', 'Avbruten', 'Cancelled', 'Löst', 'Resolved', 'Slutförd', 'Completed', 'Misslyckades', 'Failed') -- status ni inte vill se
    AND 
    (SELECT MAX(DisplayString)
    FROM [CiresonPortal].[dbo].[DisplayString] as c
    WHERE c.ElementID = a.TierId) = 'Helpdesk' -- grupp
    ORDER BY 'ID','TITEL','STATUS','TILLDELAD','PRIORITET','BERÖRD ANVÄNDARE','KATEGORI','SUPPORTGRUPP', 'SKAPAD DATUM'
Sign In or Register to comment.