How to identify collaboration managers on projects?

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

We have the need to know what users are collaboration managers and which projects are involved; we spent significant time going one by one to be aware about that.

STEPS TO REPRODUCE:
1. Create a project (Automatically project creator is: Collaboration Manager)
2. Go to Team Tab > Participant and add other users and then Mark it as collaboration manager
3. Now the project will have couple users that are collaboration manager, how to identify it for all our projects?

Environment

All CA PPM releases

Resolution

The following query can be useful to get the desired information to know all projects where specific user is collaboration manager (just need to change last filter to the appropriate user name):

We have the need to know what users are collaboration managers and which projects are involved; we spent significant time going one by one to be aware about that.

Find all Projects where a user/s is Collaboration Manager

-- Get list of all projects where admin is Collaboration Manager

Select
CPPV.USER_ID USER_ID, 
CPPV.USER_NAME USERNAME, 
CPPV.PROJECT_ID PROJECT_ID, 
INV.CODE PRJ_CODE, 
INV.NAME PRJ_NAME 
from CLB_PROJECT_PARTICIPANTS_V CPPV 
LEFT OUTER JOIN INV_INVESTMENTS INV 
ON (INV.ID = CPPV.PROJECT_ID) 
WHERE CPPV.IS_PROJECT_MANAGER = 1 --1 if is Collaboration Manager
AND CPPV.USER_NAME =  'admin'

To find for more than one user, we can evaluate change the last filter as below

-- Get list of all projects where admin, user2, user3, userx is Collaboration Manager

AND CPPV.USER_NAME IN  ('admin', 'user2', 'user3', 'userx') 

Find all Users on a Project along with Collaboration Manager status

Select
CPPV.USER_ID USER_ID,
CPPV.USER_NAME USERNAME,
CPPV.PROJECT_ID PROJECT_ID,
CPPV.is_project_manager ,
INV.CODE PRJ_CODE,
INV.NAME PRJ_NAME
from CLB_PROJECT_PARTICIPANTS_V CPPV
LEFT OUTER JOIN INV_INVESTMENTS INV
ON (INV.ID = CPPV.PROJECT_ID)
where INV.NAME like '%PROJECT_NAME%'; -- Need to replace the PROJECT_NAME