Clarity

  • Private Community
 View Only

  • 1.  Portlet Query

    Posted Sep 08, 2010 02:36 PM
    Hi All,

    I am new to this kind of troubleshooting in Clarity.

    How can I find out the query running behind a portlet?

    My issue is that a manager is not seeing a few resources timesheets. He goes to Personal > Timesheets, in there he can see a bunch of resources but there are a few missing.

    I thought about checking the query this portlet is using to understand what is missing because we never had that issue and now it shows up.

    My issue is, manager A had a manager B under it. Manager B had a few resources under it. Now, manager B is not working at the company anymore, but manager A is not able to check the former manager B resources timesheets.

    Thanks,

    Fabricio


  • 2.  RE: Portlet Query

    Posted Sep 08, 2010 03:47 PM
    Hi Fabricio,

    The code you are looking for isn't available for viewing. It's built in product code. I would recommend opening a case with support so one of our engineers can help you identify why this manager can't see all the timesheets he needs to. This is more than likely a permissions issue. Manager A will likely need to have permissions to the resources the former manager B had access to.

    One thing you can check is whether or not manager A has the "Timesheets - Approve All" global right. This right will give him access to view/approve/process all timesheets however he will still need specific resource rights granted to the folks he needs to manage.

    Hope this helps!
    /Kathy


  • 3.  RE: Portlet Query

    Posted Oct 04, 2010 07:48 PM
    Hi Fabricio-
    One option is to create a Portlet that uses an NSQL query to "List all portlets (Grid/Graph) that utilise a specific NSQL query".

    The NSQL you can use is as follows:
    SELECT @SELECT:DIM:USER_DEF:IMPLIED:PORLET:PORTLETS.PORTLET_ID||'-'||PORTLETS.DATA_PROVIDER_CODE:QID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.PORTLET_ID:PORTLET_ID@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.PORTLET_NAME:PORTLET_NAME@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.data_provider_code:data_provider_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.dal_type:dal_type@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.portlet_code:portlet_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.source:source@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.category_id:category_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.category_name:category_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.portlet_type_name:portlet_type_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.is_active :is_active@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.is_available:is_available@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.portlet_type_code:portlet_type_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.instance_type:instance_type@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.provider_id:provider_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.is_provider_link:is_provider_link@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:PORLET:PORTLETS.description:description@
    FROM
    (
    select distinct
    dp.portlet_id
    , nls.name portlet_name
    , dp.data_provider_code
    , dp.dal_type
    , cp.portlet_code
    , cp.source
    , cp.category_id
    , cat.name as category_name
    , lpt.name as portlet_type_name
    , cp.is_active
    , cp.is_available
    , cp.portlet_type_code
    , cp.instance_type
    , case when dp.cnq_id is null then dp.cnq_id else obj_id end as provider_id
    , case when dp.cnq_id is null and obj_code is null then 0 else 1 end as is_provider_link
    , nls.description
    from (
    select
    cg.id
    , cg.portlet_id
    , case when cg.dal_type = 'nsql' then cgnq.query_code else dal_code end as data_provider_code
    , cg.dal_type
    , cg.dal_id
    , cg.dal_code
    , cgnq.id cnq_id -- NB! fiddle
    , cgnq.query_code
    , o.id as obj_id
    , o.code as obj_code
    from clarity.cmn_graphs cg
    left join clarity.cmn_nsql_queries cnq on dal_id = cnq.id
    left join clarity.cmn_gg_nsql_queries cgnq on cnq.id = cgnq.cmn_nsql_queries_id
    left join clarity.odf_objects o on cg.dal_code = o.code
    WHERE ( @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NOT NULL AND cgnq.query_code LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:q_text@ || '%')
    OR @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NULL
    union all
    select
    cg.id
    , cg.portlet_id
    , case when cg.dal_type = 'nsql' then cgnq.query_code else dal_code end as data_provider_code
    , cg.dal_type
    , cg.dal_id
    , cg.dal_code
    , cgnq.id cnq_id -- NB! fiddle
    , cgnq.query_code
    , o.id as obj_id
    , o.code as obj_code
    from clarity.cmn_grids cg
    left join clarity.cmn_nsql_queries cnq on dal_id = cnq.id
    left join clarity.cmn_gg_nsql_queries cgnq on cnq.id = cgnq.cmn_nsql_queries_id
    left join clarity.odf_objects o on cg.dal_code = o.code
    WHERE ( @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NOT NULL AND cgnq.query_code LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:q_text@ || '%')
    OR @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NULL
    ) dp
    join clarity.cmn_portlets cp on dp.portlet_id = cp.id
    join clarity.cmn_captions_nls nls on cp.id = nls.pk_id and nls.table_name = 'CMN_PORTLETS' and language_code = 'en'
    join (select * from clarity.cmn_lookups_v where lookup_type = 'PORTLET_TYPE' and language_code = 'en' ) lpt on lpt.lookup_code = cp.portlet_type_code
    join (select * from clarity.cmn_lookups_v where lookup_type = 'CMN_CATEGORIES' and language_code = 'en' and lookup_code != 'framework') cat on cat.id = cp.category_id
    ) portlets
    WHERE @FILTER@