I had a request to give a list of users and their workgroups.
I don’t believe you can run a report in CIC for that but sql query can help
The department is IT SQL and every groups have ITSQL keyword in it.
For example, user “Bob Smith” in IT SQL department is in EOQV_ITSQL and EOSV_ITSQL_Expert
This query will list every users in IT SQL department with a maximum of 4 workgroups
WITH
cte_AddRN AS (
SELECT mt.UserId, mt.WorkGroup,
RN = ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY mt.WorkGroup)
FROM CIC_PROD.dbo.UserWorkgroups mt (nolock) where workgroup like '%%ITSQL%%'
)
SELECT arn.UserId
,I.FirstName
,I.LastName
,WorkGroup_1 = MAX(CASE WHEN arn.RN = 1 THEN arn.WorkGroup END)
,WorkGroup_2 = MAX(CASE WHEN arn.RN = 2 THEN arn.WorkGroup END)
,WorkGroup_3 = MAX(CASE WHEN arn.RN = 3 THEN arn.WorkGroup END)
,WorkGroup_4 = MAX(CASE WHEN arn.RN = 4 THEN arn.WorkGroup END)
FROM cte_AddRN arn
inner join CIC_PROD.dbo.Individual (nolock) I on I.ICUserID = arn.UserId
inner join CIC_PROD.dbo.UserWorkgroups (nolock) w on w.UserId = arn.UserId
GROUP BY arn.UserId
,I.FirstName
,I.LastName