Clarity PPM1

Expand all | Collapse all

Clarity Tuesday Tip: Queries to find out who has personalized their view

  • 1.  Clarity Tuesday Tip: Queries to find out who has personalized their view

     
    Posted 11-26-2014 02:09 PM

    It is possible to find out which users have personalized their views using a query

     

    This query returns all user-personalized views in the system :

     

    SELECT
    users.user_name,
    grid.dal_code,
    grid.portlet_id,
    att.id,
    views.object_code,
    views.code,
    views.partition_code,
    users.user_name,
    views.view_type,
    views.principal_type,
    att.label,
    grid.code,
    grid.dal_type,
    grid.default_sort_col_code,
    grid.sort_col_code_2,
    grid.auto_expand,
    portlet.portlet_code,
    nls.name views_name
    FROM cmn_grids grid
    LEFT JOIN odf_views views ON grid.id = views.id
    LEFT JOIN odf_view_attributes att ON grid.id = att.view_id
    LEFT JOIN cmn_portlets portlet ON grid.portlet_id = portlet.id
    INNER JOIN cmn_sec_users users ON users.id = grid.principal_id
    INNER JOIN cmn_captions_nls nls ON nls.pk_id = grid.id
    WHERE grid.principal_type = 'USER'
    AND users.id = grid.principal_id
    AND nls.language_code IN ('en', null)
    AND users.id IN
    (SELECT DISTINCT principal_id FROM cmn_grids WHERE principal_type ='USER')
    GROUP BY
    users.user_name,
    grid.dal_code,
    grid.portlet_id,
    att.id,
    views.object_code,
    views.code,
    views.partition_code,
    users.user_name,
    views.view_type,
    views.principal_type,
    att.label,
    grid.code,
    grid.dal_type,
    grid.default_sort_col_code,
    grid.sort_col_code_2,
    grid.auto_expand,
    portlet.portlet_code,
    nls.name
    ORDER BY users.user_name


    Similar queries can be constructed to find the details on different levels. For example this Query returns all users that have personalized their Project List view:

     

    SELECT user_name,
       first_name,
       last_name
    FROM cmn_sec_users
    WHERE id IN (  SELECT DISTINCT principal_id
                   FROM cmn_grids
                   WHERE principal_type ='USER'
                      AND code='projmgr.projectList'
       )
    ORDER BY user_name

     

    Happy Thanksgiving to all!



  • 2.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 11-26-2014 03:41 PM

    Happy Thanksgiving to you and thank you for sharing this tip with the community Nora!

    Nora Tobon wrote:

     

    It is possible to find out which users have personalized their views using a query

     

    This query returns all user-personalized views in the system :

     

    SELECT
    users.user_name,
    grid.dal_code,
    grid.portlet_id,
    att.id,
    views.object_code,
    views.code,
    views.partition_code,
    users.user_name,
    views.view_type,
    views.principal_type,
    att.label,
    grid.code,
    grid.dal_type,
    grid.default_sort_col_code,
    grid.sort_col_code_2,
    grid.auto_expand,
    portlet.portlet_code,
    nls.name views_name
    FROM cmn_grids grid
    LEFT JOIN odf_views views ON grid.id = views.id
    LEFT JOIN odf_view_attributes att ON grid.id = att.view_id
    LEFT JOIN cmn_portlets portlet ON grid.portlet_id = portlet.id
    INNER JOIN cmn_sec_users users ON users.id = grid.principal_id
    INNER JOIN cmn_captions_nls nls ON nls.pk_id = grid.id
    WHERE grid.principal_type = 'USER'
    AND users.id = grid.principal_id
    AND nls.language_code IN ('en', null)
    AND users.id IN
    (SELECT DISTINCT principal_id FROM cmn_grids WHERE principal_type ='USER')
    GROUP BY
    users.user_name,
    grid.dal_code,
    grid.portlet_id,
    att.id,
    views.object_code,
    views.code,
    views.partition_code,
    users.user_name,
    views.view_type,
    views.principal_type,
    att.label,
    grid.code,
    grid.dal_type,
    grid.default_sort_col_code,
    grid.sort_col_code_2,
    grid.auto_expand,
    portlet.portlet_code,
    nls.name
    ORDER BY users.user_name


    Similar queries can be constructed to find the details on different levels. For example this Query returns all users that have personalized their Project List view:

     

    SELECT user_name,
       first_name,
       last_name
    FROM cmn_sec_users
    WHERE id IN (  SELECT DISTINCT principal_id
                   FROM cmn_grids
                   WHERE principal_type ='USER'
                      AND code='projmgr.projectList'
       )
    ORDER BY user_name

     

    Happy Thanksgiving to all!



  • 3.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 11-26-2014 03:51 PM

    Thank you the same.

    That will be handy when you upgrade and want to notify those to save their configurations before the upgrade overwrites them..



  • 4.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 02-12-2015 04:05 PM

    So how do users save their personalized views (or configurations), and then to restore them after an upgrade?



  • 5.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 04-02-2015 04:06 PM

    If it just a basic user with no XOG or db access then manual documentation with screenshots is the only way I am aware of.

    The admins can XOG out views and with db access you can ge many of the details fro mthe db.



  • 6.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 11-26-2014 11:44 PM

    Thanks Nora for sharing this useful info

     

    NJ



  • 7.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 02-12-2015 09:46 AM

    This is a great tip, thank you!



  • 8.  Re: Clarity Tuesday Tip: Queries to find out who has personalized their view

    Posted 09-22-2015 03:01 AM

    Thanks for sharing!