Clarity

  • 1.  Hierarchy portlet

    Posted Jul 07, 2015 09:02 AM


    Hi Team,

     

    Hierarchical portlet is used for object and sub object relation. I have a requirement where I have to show list of other timesheet approvers if I am not able to approve. I would pass resource id as parameter. We are using Timesheet OBS. So I can have more than 10 other approvers for timesheet. Whether It is possible to show in Hierarchical way

     

     

    Say

     

    Resource_name resource_email Primary_approver

                   Primary approver1

                   Primary approver 2

                   so on...

     

    If I my Timesheet obs is A with manager as B then my other approvers will be c,d,e who are there in timesheet obs A.

     

    So I can pass the resource id in first query block to get resource primary approver and obs parent id. How can I pass the parent id obs in the second union block to get other approvers?

    Right now I have concatenated all other approvers to show in single line.

     

     

    Thanks,

     

    Arun



  • 2.  Re: Hierarchy portlet

    Posted Jul 07, 2015 12:52 PM

    Arun,

     

    This is the format for our code:

     

    NSQL SELECT

    FROM

      (SELECT Approvers

      UNION

      SELECT Resources

      UNION

      SELECT Export to Excel)

     

    I don't have authorization to include the whole code.

     

    Our portlet needs some improvements, too.  For instance,

    • do not forget that TIMESHEETS objects only contains records for created timesheets
      • if a timesheet is missing (not created) then the query will return nothing
      • if you want missing timesheets, you need to include something in your query to look at the resources timesheet start/end dates and compare to build a result of timesheets that should exist but are not found in TIMESHEETS object - these then needed to be included via UNION
    • the Excel portion of the code may result in records grouped by approver
      • this may not work well for the user if they want to build pivot tables, graphs, etc.
      • may be better to have Approver appear as a column value, rather than a grouping value

     

    Dale



  • 3.  Re: Hierarchy portlet

    Posted Jul 08, 2015 12:33 PM

    Hi Dale,

     

    Thanks the reply. I am planning to show in hierarchical portlet. When I pass the group id(hg_row_id) in the below 2nd union I am not getting the tree structure. Is hierarchical portlet is limited to only object relationship?



  • 4.  Re: Hierarchy portlet

    Posted Jul 08, 2015 12:39 PM

    ^ no you can build a hierarchical portlet on "anything" - the trick is all in the NSQL though ; its not simple but once you understand how it works then it makes sense.

     

    You have to do a lot of coding to get it it to work though - its an advanced technique and your NSQL will end up being pretty complicated!



  • 5.  Re: Hierarchy portlet

    Posted Jul 08, 2015 12:45 PM

    Hi Dave,

    Thanks for the reply. I have created a nsql which in which from first union all i will get the group id. And in the second union all i am passing that id . Though i get results in sql developer. But when I construct to nsql it is not happening.

    Could you give any example?



  • 6.  Re: Hierarchy portlet

    Posted Jul 08, 2015 12:52 PM

    As I said,  its complicated and not easily explained ; best way is to build something simple yourself so you understand how the hierarchy bit works and then apply that to your real portlet.

     

    The Studio Developer guide details the requirements in terms of how you need to write the NSQL - the trick is all in how you populate the hg_has_children variable as this gets "passed" to the "next" query as the hg_row_id.

     

    Theres some sample NSQL here ; RE: Re: Hierarchical query for a hierarchical Portlet



  • 7.  Re: Hierarchy portlet

    Posted Jul 08, 2015 02:43 PM

    May I also bring to your attention (collectively) the hg_all_rows construct should you wish to leverage Export to Excel from any hierarchical grid portlets.

     

    It is some additional effort up front, but could be much easier than trying to refactor it in later.

     

    A quick search pulled up this prior reference for it (which still applies despite the version difference): Hierarchical Portlets & Exporting to Excel in v12.06

     

     

    The attachment in this thread may also be of value: Export to Excel Portlet Hierarchy - Clarity



  • 8.  Re: Hierarchy portlet

    Posted Jul 08, 2015 12:57 PM

    Arun,

     

    In our setup:

     

    NSQL SELECT

    FROM

      (SELECT Approvers, UserID hg_has_children

      UNION

      SELECT Resources, NULL hg_has_children

      UNION

      SELECT Export to Excel), NULL hg_has_children

     

    I'm not sure what you mean "in below second union" - if you meant to attach your code, its not displayed.  In our code we are passing id in first section of unions (approver section) and second section (resource section) is NULL - the approver has children, our resources do not have children.

     

    Perhaps this is the difference that is causing it not to work for you?

     

    Dale



  • 9.  Re: Hierarchy portlet

    Posted Jul 09, 2015 04:00 AM

    Hi ,

     

    We want to identify a program linked to many master programs . So i created the query and i want to show in hierarchical portlet.

     

     

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:hgquery.project_id:project_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.inv_code:inv_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.inv_name:inv_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:sum(hgquery.master_prog):master_prog@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.hg_has_children:hg_has_children@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.last_updated_date:last_updated_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.last_updated_by:last_updated_by@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:OBJ:hgquery.last_updated_name:last_updated_name@

    from
        (    
         SELECT DISTINCT
         i.id project_id,
         i.code inv_code,
         i.name inv_name,
         (select count(*)
                FROM
                        inv_hierarchies, inv_investments inv_p, inv_investments inv_c
                WHERE
                        inv_p.ID = inv_hierarchies.parent_id  AND
                        inv_c.ID = inv_hierarchies.child_id
                        AND inv_hierarchies.PARENT_id IN (SELECT distinct PRID FROM INV_PROJECTS WHERE IS_PROGRAM = 1)
                        AND inv_hierarchies.CHILD_ID=I.ID)  master_prog,
                        i.id hg_has_children,
                        null last_updated_date,
                        null last_updated_by,
                        null last_updated_name,
                        project_type.name project_type

             FROM INV_INVESTMENTS i inner join odf_ca_project odf on odf.id=i.id
             LEFT OUTER JOIN INV_PROJECTS P ON P.Prid = i.id
           
         where @where:param:user_def:string:hg_row_id@ is null
         AND i.is_active = 1                

         AND ( 
                ( i.odf_object_code = 'project'
                  AND P.Is_Template = 0
          AND P.is_Program = 1
                )        
             ) GROUP BY i.id, i.code, i.name
            
             union
            
             SELECT
                inv_hierarchies.parent_id project_id,INV_P.CODE inv_code,INV_P.NAME inv_name,
               0 master_prog,null hg_has_children,
               to_char(inv_hierarchies.last_updated_date,'DD/MM/YYYY')last_updated_date,
              (select unique_name from srm_Resources a where a.user_id=inv_hierarchies.last_updated_by)last_updated_by,
                (select full_name from srm_Resources a where a.user_id=inv_hierarchies.last_updated_by)last_updated_name
        
              FROM
                        inv_hierarchies left outer join
    inv_investments inv_p on inv_p.ID = inv_hierarchies.parent_id
    left outer join inv_investments inv_c on inv_c.ID = inv_hierarchies.child_id
    left outer join odf_ca_project odf on  odf.id=inv_p.id

      WHERE
                       
                 
                        INV_P.IS_ACTIVE=1 AND INV_C.IS_ACTIVE=1
                        AND inv_hierarchies.PARENT_id IN (SELECT distinct PRID FROM INV_PROJECTS WHERE IS_PROGRAM = 1 and Is_Template = 0
    )
                        and inv_c.ID=@where:param:user_def:string:hg_row_id@
                        and odf.id=inv_p.id
                       

    )hgquery where @filter@  GROUP BY  hgquery.project_id, hgquery.inv_code,hgquery.inv_name, hgquery.hg_has_children, hgquery.last_updated_date, hgquery.last_updated_by, hgquery.last_updated_name HAVING( SUM(master_prog)>=1 )

     

    Though I get the expected results when running from oracle. But I am not getting tree structure in clarity? Is there anything wrong ?



  • 10.  Re: Hierarchy portlet

    Broadcom Employee
    Posted Jul 30, 2015 01:03 PM

    This KB article has a sample as well

     

    Document ID:  TEC530394

    User-defined Hierarchical Portlets do not include the children rows when the data is Exported to Excel.