Home› Analyst Portal

@UserId Use in Dashboard Query Syntax

Nick_FlintNick_Flint Customer Advanced IT Monkey ✭✭✭
in Analyst Portal

The following dashboard query works as expected:

(SELECT [SupportGroupEnumerationID]

 FROM [ServiceManagement].[dbo].[ALE_UserSupportGroups]

 WHERE [ServiceManagement].[dbo].[ALE_UserSupportGroups].Id='4e01e1a8-f5b8-acbf-036d-c6803dde3c5e')

4e01e1a8-f5b8-acbf-036d-c6803dde3c5e is the ID of the current user in the portal and is the BaseManagedEntityId of the user in the database.

ALE_UserSupportGroups is a custom view that lists all of the support groups the user is a part.


I'm trying to use the @UserId token to make it dynamic:

(SELECT [SupportGroupEnumerationID]

 FROM [ServiceManagement].[dbo].[ALE_UserSupportGroups]

 WHERE [ServiceManagement].[dbo].[ALE_UserSupportGroups].Id=@UserId)


but I don't get any results with that. I've tried '@UserId', {{@UserId}}, '{{@UserId}}'

Please tell me I'm missing something simple.

Best Answer

  • Nick_FlintNick_Flint Customer Advanced IT Monkey ✭✭✭
    Answer ✓

    @Shane_White I have the query working now. I think the final piece was that the @UserId token uses the GUID and not the alpha numeric username.

Answers

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @Nick_Flint

    I have tried this sample query and seems to work fine in the syntax you have:

    SELECT *

    From dbo.WorkItem

    Where AssignedUserId = @UserId


    Maybe change it to lowercase u, so userId and see if this works?

    Thanks,

    Shane

  • Nick_FlintNick_Flint Customer Advanced IT Monkey ✭✭✭

    This works:

    SELECT *

    From dbo.WorkItem

    Where AssignedUserId = @UserId

    But this doesn't:

    select *

    From dbo.WorkItem

    where id in (SELECT id

    From dbo.WorkItem

    Where AssignedUserId = @UserId

    )


    So my issue appears to be using @UserId in a subquery. Should that work (a bug?) or do I need to go back to the drawing board on my entire query?

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @Nick_Flint

    Interesting that query you sent also works for me.. What version of the portal are you running?

    Thanks,

    Shane

  • Nick_FlintNick_Flint Customer Advanced IT Monkey ✭✭✭

    @Shane_White My query is working in I.E. but not in Chrome. We're on 9.4.1. In the Chrome console I'm getting the following error message:

    image.png


  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @Nick_Flint

    I would probably suggest updating! I am on a newer version and using Chrome and seems to be working fine! 😁

    Thanks,

    Shane

  • Nick_FlintNick_Flint Customer Advanced IT Monkey ✭✭✭

    I upgraded my dev environments to 9.7.0.2016. Now I'm not seeing the Edit button on the dashboards in those environments. Is there a setting that needs to be set to grant edit access to the dashboards?

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @Nick_Flint

    Make sure you are logged in with a user that can edit dashboards and make sure your dashboard license is visible on the license page!

    Thanks,

    Shane

  • Nick_FlintNick_Flint Customer Advanced IT Monkey ✭✭✭
    Answer ✓

    @Shane_White I have the query working now. I think the final piece was that the @UserId token uses the GUID and not the alpha numeric username.

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @Nick_Flint

    Yes that is correct, it is the Id (guid) of the User not the username.

    Glad you got it working.

    Thanks,

    Shane

Sign In or Register to comment.