Customizations
Ryan_Lane
Ryan_Lane
·Posted in Customizations

SQL Queries To Simulate Analyst Views

Hello all,
I've created several SQL queries that simulate another user's grid views for My Work, My Requests, Team Work and Team Requests and uploaded them to github to share: https://github.com/ryanlanegit/CiresonPortal/tree/master/SQL/Views

They make use of the ServiceManagement database's stored procedures spGet_GridWorkItems, spGet_MyRequests and spGet_TeamRequests.  So far these have really helped me when troubleshooting issues from the user's point of view for user roles/permissions and general queue issues.

Options

Each script should be begin with a Analyst Query section and a View Options section:
-- Analyst Query
DECLARE @P_UserNameQuery nvarchar(256) = 'ryanl%';
DECLARE @P_UserDomain nvarchar(256) = 'DOMAIN';
-- View Options
DECLARE @P_IncludeTeamItems bit = 'true';
DECLARE @P_ShowActivities bit = 'true';
DECLARE @P_ShowInactiveItems bit = 'true';
  • Modifying the username and domain values in Analyst Query to find the user you'd like to view. Note: I prefer username vs. the BaseId GUID in determining the user to make the query a bit easier to use off the cuff.
  • Modifying the values under View Options corresponds to (un)checking the options in the related grid on the portal:
 

Any suggestions for other views or improvements would be much appreciated!
7
2 replies