View Only
  • 1.  Collaboration Manager lists

    Posted Aug 15, 2012 11:12 AM
    I was wondering is there a way of viewing a list of Collaboration Manager on Clarity Version 12.1.1. We are have some issue where Collaboration Managers take up Managers Licence on Clarity and we want to restrict the amount of Collaboration Managers on Projects to free up some licence.

    Any Ideas on how I can get a list?


  • 2.  RE: Collaboration Manager lists
    Best Answer

    Posted Aug 15, 2012 11:26 AM
    Pretty sure that the application isn't going to give you that information without trawling around the projects...

    ... its not too complex (but a little non-intuitive) on the database though.

    For a project, there will be a record in the CLB_PROJECT_GROUP_KEYS table.

    Then the PROJECT_MANAGERS_GROUP_ID (which in this context means the Collaboration Manager) in that table identifies a "group of users" - so go to the CMN_SEC_USER_GROUPS table with that id and that will then give you the resources that are Collaboration Managers.

    Build a bit of SQL that pulls all that together and you should be able to get a list of all your Collaboration Managers across all your projects.

  • 3.  RE: Collaboration Manager lists

    Posted Aug 16, 2012 03:49 AM
    Thanks Dave I'll try that.


  • 4.  RE: Collaboration Manager lists

    Posted Aug 16, 2012 09:15 AM
    in our v12.06 (MS SQL) system there is a function: z_get_collab_mangrs which gives you a delimited list of CMs when you pass it a project's internal ID.

    It's not something I've developed so I'm guessing it's a custom thing that was built for us sometime in the past. Here's the code if you want it
    [size=3]CREATE FUNCTION [niku].[Z_GET_COLLAB_MANGRS] (@v_id_project NUMERIC)
    DECLARE c_coll_manas CURSOR FOR
    select srm.full_name 
    from clb_project_participants_v vp, srm_resources srm, prj_projects prj
    where vp.project_id=@v_id_project and vp.is_project_manager=1 
    and AND vp.project_id=prj.prid
    and vp.user_id<>prj.manager_id and vp.user_id<>1
    select srm.full_name
    from srm_resources srm ,prj_projects prj 
    where prj.prid=@v_id_project and prj.manager_id=srm.user_id
            DECLARE     @v_name_string   NVARCHAR(400),
    @v_col_man NVARCHAR (1000)
     SET @v_col_man= ''
      OPEN c_coll_manas
    FETCH NEXT FROM c_coll_manas INTO @v_name_string
      WHILE (@@FETCH_STATUS <> -1)
    SET @v_col_man = @v_col_man + @v_name_string + ' & '   
    FETCH NEXT FROM c_coll_manas INTO @v_name_string
    select @v_col_man = substring(@v_col_man,1,len(@v_col_man)-2)
       CLOSE c_coll_manas
       DEALLOCATE c_coll_manas
       RETURN @v_col_man

  • 5.  RE: Collaboration Manager lists

    Posted Aug 16, 2012 09:41 AM
    ^ ok that view "[font=Courier New]clb_project_participants_v[font]" that is used in your Z_ function pulls together some of the tables I was referencing earlier.... so we could use the view instead of the tables I suggested. In fact that view is probably what the application screen uses when it displays the participants page.

    (I'm always guilty of looking at the TABLES rather than the VIEWS in the datamodel - habit I guess :tongue - mind you looking at the definition of that view, it IS a bit wacky seems to call a function to determine which project member is a collaboration manager etc)

  • 6.  RE: Collaboration Manager lists

    Posted Aug 17, 2012 06:50 AM
    Thanks Owen,

    I'll give it a go. I tied Dave's suggesting and it works ok as well. however there are so many Projects ID's to go through.

    Thanks again

  • 7.  RE: Collaboration Manager lists

    Posted Aug 17, 2012 11:11 AM
    I wish I would have posted this topic months ago. It could have helped me out and saved me lots of time.

    I had the same issue because CA decided to audit my licenses. I argued that there needs to be an easy way to find out who has Coll. Mgr rights from about 2,000 projects. In short, thru a lot of manual work and then opening a ticket with support I was able to get it solved. I used the license portlet to sort thru who had Coll mgr rights and gave that list to CA. They developed a query they ran that gave me a list of the projects that those resources had coll mgr rights on. Overall was not a fun task but CA support made it less of a headache in the end.


  • 8.  RE: Collaboration Manager lists

    Posted Aug 17, 2012 12:02 PM
    Just to expand on the "license usage" a little bit - this is a slightly bigger topic than just "Collaboration Managers" since there are many other ways that a user can use up a license...

    There are 3 handy VIEWS on the database that drive the license portlets, but they don't quite go as far as telling us WHY a user is using a particular license...

    CMN_LIC_COUNT_V counts the licenses used
    CMN_LIC_USERS_V show us which users have which license type
    and those views are based upon CMN_LIC_RIGHT_V which maps the licence types to the underlying system access rights.

    But until you map those system rights to rights on specific instances or specific global rights (which the license portlets do not do for us), then finding out HOW a specific USER has that license is a bit tricky. The answer is in that CMN_LIC_RIGHT_V view but you still have to match the rights to the actual objects...

    In the "old" licensing model (originally in v7.5.1) I built a series of portlets that displayed all the information and allowed me to drill from licence to user and finally to the actual access rights on specific instances etc that the user had that contributed to that license (its this very last step that the stock screens - which did not appear until 7.5.2 I think - still (in v13) do not provide for us!).

  • 9.  RE: Collaboration Manager lists

    Posted Aug 20, 2012 10:23 AM
    Thanks Dave
    In the version we are using, 12.1.1, there is a Licences Information link on the Admin side that we can use to drill to the specific rights the "Manager" is using.
    We have identified a number of ways that generates a Managers Licence but Collaboration Manager is the key one for us. We have a number of Resources that are just Collaboration Managers with no responsibilities and we need to free these.

    Have some great suggestion from so we'll keep going until we get the list.

  • 10.  RE: Collaboration Manager lists

    Posted Aug 21, 2012 10:26 AM

    The below sql will show all active projects with the respective collaboration managers.


    (select code from inv_investments i where,
    (select name from inv_investments i where,
    (select user_name from cmn_sec_users c where,
    (select last_name || ' ' || first_name from cmn_sec_users c where
    and k.project_id in (select id from inv_investments where is_active=1)
    order by k.project_id,g.user_id

    you can hardcode and test for single project
    --and k.project_id = 5702084