Home Analytics

Can a view with the token [me] be promoted to the portal?

Paul_DalkinPaul_Dalkin Member IT Monkey ✭
edited December 2017 in Analytics
HI All

I'm trying to promote a view from the console to the portal and it appears that it doesn't seem to populate. The main aspect of this is use of the [me] token in a WorkItem CreatedBy view (to give analysts - especially first line - an insight into stuff they've created but not been assigned to when it was Completed or Resolved). But alas nothing comes through when the console produces results. We don't have Analystics installed yet but it's in the roadmap.

Best Answers

  • Paul_DalkinPaul_Dalkin Member IT Monkey ✭
    Answer ✓
    Fab - I'd best look into how to do this in the KB!
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Answer ✓
    Here is an example that will pull all open incidents assigned to the logged on user

    Select * From
    (SELECT wi.WorkItemId as [Id], 
    wi.Title,
    dsstatus.DisplayString as [Status], 
    dssupport.DisplayString as [SupportGroup], 
    wi.PriorityId as [Priority],
    Case When wi.AssignedUser IS Null Then 'UnAssigned'
    Else wi.AssignedUser
    End as [AssignedTo], 
    wi.AffectedUser, 
    wi.Created as [Created], 
    wi.LastModified as [LastModified]
    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
    WHERE wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 
    AND wi.StatusId NOT IN('2b8830b6-59f0-f574-9c2a-f4b4682f1681','bd0ae7c4-3315-2eb3-7933-82dfc482dbaf')
    AND AssignedUserId = @UserId
    ) Incidents
    Order By [Status], [Id] DESC

Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    I do not belive Cireson support a promoted view with [me]. but with 7+ (or maybe 8 double check the release notes) 
    You can build a SQL dashboard table widget with the @UserId and make is a dashboard page.
  • Paul_DalkinPaul_Dalkin Member IT Monkey ✭
    Hi thanks both for this - WRT sesiging an SQL widget, presumably Analysics would need to be installed to call it into the dashboard?
  • Paul_DalkinPaul_Dalkin Member IT Monkey ✭
    Answer ✓
    Fab - I'd best look into how to do this in the KB!
  • Paul_DalkinPaul_Dalkin Member IT Monkey ✭
    edited December 2017
    Hi I've tried to use the simple ServiceManagement DB SQL Query from the admin settings list: "work items completed" in the hope that amending the line 'AND (a.UserId = @UserId OR @IsScoped = 0)LINE ' to 'AND (a.UserId = @UserId=[me])'  will work.

    Being a complete novice to these SQL statements, some guidance from you guys would be most welcome.

    BWT it didn't work  :s
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Answer ✓
    Here is an example that will pull all open incidents assigned to the logged on user

    Select * From
    (SELECT wi.WorkItemId as [Id], 
    wi.Title,
    dsstatus.DisplayString as [Status], 
    dssupport.DisplayString as [SupportGroup], 
    wi.PriorityId as [Priority],
    Case When wi.AssignedUser IS Null Then 'UnAssigned'
    Else wi.AssignedUser
    End as [AssignedTo], 
    wi.AffectedUser, 
    wi.Created as [Created], 
    wi.LastModified as [LastModified]
    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
    WHERE wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 
    AND wi.StatusId NOT IN('2b8830b6-59f0-f574-9c2a-f4b4682f1681','bd0ae7c4-3315-2eb3-7933-82dfc482dbaf')
    AND AssignedUserId = @UserId
    ) Incidents
    Order By [Status], [Id] DESC
Sign In or Register to comment.