Finding the manager using BPS Groups

0

Hi Przemysław,

if I haven't missed anything, would it be enaugh to select a user from the project group, who also belongs to the managers group?
Assuming, that you can indicate in the workflow a group for a given project
eg. expenses belongs to Project A -> search in Project A group

If so, it can be done based on the CacheOrganizationStructure and CacheOrganizationStructureGroupRelation table.

So we can use queries like this, first to return users from a group:

SELECT usr.COS_BpsID, usr.COS_DisplayName

FROM CacheOrganizationStructure gr
join CacheOrganizationStructureGroupRelations rel ON gr.COS_ID = rel.COSGR_GroupID
join CacheOrganizationStructure usr ON rel.COSGR_UserID = usr.COS_ID

WHERE gr.COS_BpsID = 'Project A' AND usr.COS_IsActive = 1

I use it as Table-valued function, and Scalar function to determine if user belongs to group:

return (
select
case when exists (
select *
from [dbo].[CacheOrganizationStructure] u
join [dbo].[CacheOrganizationStructureGroupRelations] gr
on gr.COSGR_UserID = u.COS_ID
join [dbo].[CacheOrganizationStructure] g
on g.COS_ID = gr.COSGR_GroupID
where u.cos_bpsid = 'User login' and g.COS_BpsID = 'Managers group id'
)
then 1
else 0
end
)

combining them, we can return users from the Project A group, who also belongs to the Managers group:

SELECT COS_BpsID
from dbo.getGroupUsers('Project A group id')
WHERE dbo.IsUserInGroup(COS_BpsID,'Managers group id')

Just an example to give some idea :)

Hi, thank you very much for a clue. I think i've even managed to simplify the solution to:
SELECT usr.COS_BpsID, usr.COS_DisplayName

FROM CacheOrganizationStructure gr
join CacheOrganizationStructureGroupRelations rel ON gr.COS_ID = rel.COSGR_GroupID
join CacheOrganizationStructure usr ON rel.COSGR_UserID = usr.COS_ID

WHERE gr.COS_BpsID = 'Project A ID' AND usr.COS_IsActive = 1
INTERSECT
SELECT usr.COS_BpsID, usr.COS_DisplayName

FROM CacheOrganizationStructure gr
join CacheOrganizationStructureGroupRelations rel ON gr.COS_ID = rel.COSGR_GroupID
join CacheOrganizationStructure usr ON rel.COSGR_UserID = usr.COS_ID

WHERE gr.COS_BpsID = 'Managers Group ID' AND usr.COS_IsActive = 1

but another problem arise, I cannot create the data source, I've got an error like in attached picture.

I've managed to work around the issue. I filter AD users list using directReports, Manager and Department attribute.