Clarity

Expand all | Collapse all

Project Hierarchy Portlet

  • 1.  Project Hierarchy Portlet

    Posted Jul 03, 2014 03:00 PM
      |   view attached

    Hi! A customer would like to see a list of Projects with their Sub Projects. Only those projects that have childs with some general information from the project properties including some custom fields.

     

    Has anyone done something like this? I've found a query for hierarchical projects and sub-project but it only shows name and id for the project and the sub project. It does show the data as I expect but the query is really too complex to me in order to modify it to bring also the custom fields.

     

    I'm attaching the query, sorry I don't have the link for the post where we found it.

     

    I really appreciate your help.

     

    Thanks!!

    Flo

    Attachment(s)



  • 2.  Re: Project Hierarchy Portlet

    Posted Jul 03, 2014 03:06 PM


  • 3.  Re: Project Hierarchy Portlet

    Posted Jul 03, 2014 04:11 PM

    Hi NJ, the query I need is very similar, but I don't need the tasks, I only need the project and sub-project hierarchy. We would like to see the column name, id, and some custom fields.

     

    Thanks!

    Flo



  • 4.  Re: Project Hierarchy Portlet

    Posted Jul 03, 2014 04:19 PM
    The hierarchical SQL code exists in Clarity, and it can be obtained:
    • In your database, enable SQL Trace
       UPDATE CMN_SEC_USERS
       SET SQLTRACE_ACTIVE = 15 -- Use NULL to disable.  "15" returns SQL Code and SQL Results
        WHERE ID =  5******  -- this the id corresponding to your name in CMN_SEC_USERS
    • Login to Clarity
    • Navigate to a project that has subprojects attached
    • Find log file in your app server's log directory and open it (If you have more than one app server, you'll need to now which server you are logged into Clarity on, as the log file will be produced on the app server that you are logged in through.  By adding security.caches after nu#action: in the URL and hitting enter, you'll see a screen that will report the name of the app server that you are logged into)
    • I typically delete the contents of the log file and then save it - gets rid of content I'm not interested in.
    • Click on the Hierarchy Tab
    • Open the log file and save it from your app server to your device
    • Rerun the UPDATE statement, using NULL to disable trace
    • Logout
    The SQL Code obtained by this method will give you the basis for a hierarchical project list.  You can modify it to include different attributes and to be run over more than one project and its children.  You'll also get to see how CA did it.  Any SQL you build should be checked by a DBA for optimization opportunities.
    Dale


  • 5.  Re: Project Hierarchy Portlet

    Posted Jul 04, 2014 03:12 PM

    Thanks Dale! I'll try that. Are you sure SQLTRACE_ACTIVE column exists in CMN_SEC_USERS? I'm in version 13.1.0

     

    Thanks!

    Flo



  • 6.  Re: Project Hierarchy Portlet

    Posted Jul 04, 2014 03:34 PM

    Nevermind! I found some intructions to enable SQL trace in version 13.x

     

    Thanks!

    Flo



  • 7.  Re: Project Hierarchy Portlet

    Posted Jul 04, 2014 04:56 PM


  • 8.  Re: Project Hierarchy Portlet

    Posted Jul 08, 2014 04:39 PM

    Were you able to get the SQL successfully for this?

     

    The NSQL that you attached is fairly spot-on for this kind of thing. If you expect to build a hierarchical portlet, it will be that complex, and can be more so if you add in staff and/or WBS.

     

    However, while not as "clean" you could also build a fairly straight-forward query that will show you what you need. You simply need to use joins based off of the parentID of the master to the sub.

     

    If you need assistance with this, let me know.

     

    thanks,

    Nathan



  • 9.  Re: Project Hierarchy Portlet

    Posted Jul 10, 2014 08:32 AM

    Hi Nathan,

     

    I have not been succesfull to create this query yet, and I'm about to give up. I'm very far of being an expert on SQL, so I think I could not succeed in writing such a complex query.

     

    If you can help me I'm more than thankful.

     

    Thanks and Regards!

    Flo



  • 10.  Re: Project Hierarchy Portlet

    Posted Jul 15, 2014 05:05 PM

    Here is one that I use.

     

    In the with clause:

     

    CHILD_INVESTMENTS is all investments that have a parent.

    INVEST_TREE pulls the top level investments and a recursive query to pull their children from CHILD_INVESTMENTS for each top level investment.

    INVEST_DISTINCT just makes sure we only have one row for each parent child relationship (Not sure this is really needed but habits are hard to break).

    INVEST_NSQL is the values we are going to use for our NSQL.  It is in this query you would join to your custom field tables.  I am pulling off fields in ODF_CA_PROJECT and CMN_LOOKUPS_V

     

    SELECT @SELECT:DIM:USER_DEF:IMPLIED:X:X.PROJECTID:PROJECTID@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.HG_HAS_CHILDREN:HG_HAS_CHILDREN@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PARENT_ID:PARENT_ID@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRNAME:PRNAME@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRDESC:PRDESC@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRFINISH:PRFINISH@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRSTART:PRSTART@ ,

      @SELECT:DIM_PROP:USER_DEF:BOOLEAN:X:X.ACTIVE:ACTIVE@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.LICYPHASE:LICYPHASE@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.COSTIND:COSTIND@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PERFIND:PERFIND@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.RISKIND:RISKIND@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.SCHEDIND:SCHEDIND@ ,

      @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.IPT:IPT@

    FROM

      ( WITH CHILD_INVESTMENTS AS

      ( SELECT DISTINCT inv.ID,

        inh.PARENT_ID,

        inh.CHILD_ID,

        inp.PARENT_ID AS HG_HAS_CHILDREN,

        inv.NAME

      FROM INV_INVESTMENTS inv

      LEFT OUTER JOIN INV_HIERARCHIES inh

      ON inv.ID          = inh.CHILD_ID

      AND inh.PARENT_ID IS NOT NULL

      LEFT OUTER JOIN INV_HIERARCHIES inp

      ON inv.ID = inp.PARENT_ID

      WHERE inv.IS_ACTIVE = 1

      ),

      INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) AS

      (SELECT ID,

        NAME,

        PARENT_ID,

        0  AS DEPTH,

        ID AS ROOT_ID,

        HG_HAS_CHILDREN

      FROM CHILD_INVESTMENTS

      WHERE PARENT_ID IS NULL

      AND NAME         = 'Enterprise Infrastructure'

      UNION ALL

      SELECT ci.ID,

        ci.NAME,

        ci.PARENT_ID,

        DEPTH + 1,

        ROOT_ID,

        ci.HG_HAS_CHILDREN

      FROM CHILD_INVESTMENTS ci

      INNER JOIN INVEST_TREE iv

      ON ci.PARENT_ID = iv.ID

      ),

      INVEST_DISTINCT AS

      ( SELECT DISTINCT ID,

        NAME,

        PARENT_ID,

        DEPTH,

        ROOT_ID,

        HG_HAS_CHILDREN

      FROM INVEST_TREE

      ),

      INVEST_NSQL AS

      (SELECT DISTINCT inv.ID PROJECTID ,

        iv.DEPTH ,

        iv.HG_HAS_CHILDREN ,

        iv.PARENT_ID ,

        inv.NAME PRNAME ,

        inv.description prdesc ,

        inv.SCHEDULE_FINISH PRFINISH ,

        inv.SCHEDULE_START PRSTART ,

        inv.IS_ACTIVE ACTIVE ,

        lkp0.NAME LICYPHASE ,

        odfp.MHS_COST_VAR*100 COSTIND ,

        odfp.MHS_PERFORMANCE_HLTH PERFIND ,

        odfp.MHS_RISKINDICATOR RISKIND ,

        odfp.MHS_SCHED_VAR*100 SCHEDIND ,

        lkp1.NAME IPT

      FROM INVEST_DISTINCT iv

      INNER JOIN INV_INVESTMENTS inv

      ON iv.ID = inv.id

      INNER JOIN ODF_CA_PROJECT odfp

      ON iv.ID = odfp.ID

      LEFT OUTER JOIN CMN_LOOKUPS_V lkp0

      ON lkp0.LOOKUP_CODE    = odfp.MHS_LIFECYCLE_PHASE

      AND lkp0.LANGUAGE_CODE = 'en'

      AND lkp0.LOOKUP_TYPE   = 'MHS_LIFECYCLE_PHASE'

      LEFT OUTER JOIN CMN_LOOKUPS_V lkp1

      ON lkp1.LOOKUP_CODE    = odfp.MHS_IPT

      AND lkp1.LANGUAGE_CODE = 'en'

      AND lkp1.LOOKUP_TYPE   = 'MHS_ASSCTD_IPT'

      )

    SELECT PROJECTID,

      HG_HAS_CHILDREN ,

      PARENT_ID ,

      PRNAME ,

      PRDESC ,

      PRFINISH ,

      PRSTART ,

      ACTIVE ,

      LICYPHASE ,

      COSTIND ,

      PERFIND ,

      RISKIND ,

      SCHEDIND ,

      IPT

    FROM INVEST_NSQL insql

      WHERE Depth = NVL(@WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@ , 1)

      AND @WHERE:SECURITY:PROJECT:PROJECTID@

    UNION

    SELECT PROJECTID,

      HG_HAS_CHILDREN ,

      PARENT_ID ,

      PRNAME ,

      PRDESC ,

      PRFINISH ,

      PRSTART ,

      ACTIVE ,

      LICYPHASE ,

      COSTIND ,

      PERFIND ,

      RISKIND ,

      SCHEDIND ,

      IPT

    FROM INVEST_NSQL insql

      WHERE PARENT_ID = @WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@

      AND @WHERE:SECURITY:PROJECT:PROJECTID@

    ORDER BY PRNAME

      ) X

    WHERE @FILTER@

     

    V/r,

    Gene



  • 11.  Re: Project Hierarchy Portlet

    Posted Jul 16, 2014 10:07 AM

    Hi Gene,

     

    Thank you! I will try your query and will be back with my feedback.

     

    Regards

    Flo



  • 12.  Re: Project Hierarchy Portlet

    Posted Jul 16, 2014 10:16 AM

    Does the 'with clause' work in SQL Server? Or is this query for oracle?

     

    Thanks!

    Flo



  • 13.  Re: Project Hierarchy Portlet

    Posted Jul 16, 2014 12:00 PM

    FloGoya wrote:

     

    Does the 'with clause' work in SQL Server? Or is this query for oracle?

     

    Thanks!

    Flo

    "WITH" does work in SQLServer, but unfortunately we can not "nest" it like we can in Oracle - in SQLServer it insists on being at the very 'outer' bit of the SQL statement - and since we are essentially wrapping a SQL statement inside the NSQL statement it just does not work as Clarity wants to 'own' that 'outer' bit.

     

    But...

     

    When I have had to translate an Oracle NSQL statement that contains lots of "WITH" bits then I have found that I can do it, by moving the "WITH" chunk of code down into the FROM clause - this all works, but makes the eventual code somewhat hard to read

     

    eg.

     

    instead of;

     

    WITH my_data_1 as ( SELECT foo FROM ... )

    , my_data_2 as ( SELECT bar FROM .... )

    SELECT

    A.***, B.yyy

    FROM my_data_1 A

    , my_data_2 B

    WHERE...

     

    you can code it (without any "WITH"s) like this;

     

    SELECT

    A.***, B.yyy

    FROM ( SELECT foo FROM ... ) A

    , ( SELECT bar FROM .... ) B

    WHERE...

     

    it becomes something of a mechanical search/replace problem then to convert from the complex Oracle NSQL to (even more) complex SQLServer NSQL - and this all gets REALLY unreadable where the table aliases are 'contained' within each other.



  • 14.  Re: Project Hierarchy Portlet

    Posted Jul 16, 2014 12:20 PM

    Good point Dave!

     

    I guess I hadn't thought about the query being a subquery of the NSQL query.  I guess the work around would be to place the CTE in a view and wrap the NSQL around the view.  Have you tried this before?

     

    V/r,

    Gene



  • 15.  Re: Project Hierarchy Portlet

    Posted Jul 16, 2014 12:30 PM

    (I always forget what those "WITH" things are called - CTE (common table expression) thats it!  )

     

    I've not ever had to push the logic down to a VIEW since I've often got some sort of user-supplied portlet parameter to cope with as well (which I could cope with in a VIEW but the problem starts to get exponentially more difficult to get my brain around), so I find that the search/replace process outlined above works for me (but I then try never to look at the ugly code it has created ever again and do all my subsequent debug/changes in the Oracle version of my code.)

     

    So I'm sure a VIEW might help, but only when you don't have to handle user-supplied parameters?



  • 16.  Re: Project Hierarchy Portlet

    Posted Jul 16, 2014 12:07 PM

    Yes this is an Oracle query.

     

    I just tested this one on MSSQL and it runs without an issue.  I removed the NSQL elements and ODF tables since my MSSQL instance isn't the same data structure.  You may have to remove the schema identifiers before the tables since my account is sa.

     

    WITH CHILD_INVESTMENTS AS

      ( SELECT DISTINCT inv.ID,

        inh.PARENT_ID,

        inh.CHILD_ID,

        inp.PARENT_ID AS HG_HAS_CHILDREN,

        inv.NAME

      FROM niku.INV_INVESTMENTS inv

      LEFT OUTER JOIN niku.INV_HIERARCHIES inh

      ON inv.ID          = inh.CHILD_ID

      AND inh.PARENT_ID IS NOT NULL

      LEFT OUTER JOIN niku.INV_HIERARCHIES inp

      ON inv.ID = inp.PARENT_ID

      WHERE inv.IS_ACTIVE = 1

      ),

      INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) AS

      (SELECT ID,

        NAME,

        PARENT_ID,

        0  AS DEPTH,

        ID AS ROOT_ID,

        HG_HAS_CHILDREN

      FROM CHILD_INVESTMENTS

      WHERE PARENT_ID IS NULL

      UNION ALL

      SELECT ci.ID,

        ci.NAME,

        ci.PARENT_ID,

        DEPTH + 1,

        ROOT_ID,

        ci.HG_HAS_CHILDREN

      FROM CHILD_INVESTMENTS ci

      INNER JOIN INVEST_TREE iv

      ON ci.PARENT_ID = iv.ID

      ),

      INVEST_DISTINCT AS

      ( SELECT DISTINCT ID,

        NAME,

        PARENT_ID,

        DEPTH,

        ROOT_ID,

        HG_HAS_CHILDREN

      FROM INVEST_TREE

      ),

      INVEST_NSQL AS

      (SELECT DISTINCT inv.ID PROJECTID ,

        iv.DEPTH ,

        iv.HG_HAS_CHILDREN ,

        iv.PARENT_ID ,

        inv.NAME PRNAME ,

        inv.description prdesc ,

        inv.SCHEDULE_FINISH PRFINISH ,

        inv.SCHEDULE_START PRSTART ,

        inv.IS_ACTIVE ACTIVE

      FROM INVEST_DISTINCT iv

      INNER JOIN niku.INV_INVESTMENTS inv

      ON iv.ID = inv.id

      )

    SELECT PROJECTID,

      HG_HAS_CHILDREN ,

      PARENT_ID ,

      PRNAME ,

      PRDESC ,

      PRFINISH ,

      PRSTART ,

      ACTIVE

    FROM INVEST_NSQL insql

      WHERE Depth = null

      UNION

    SELECT PROJECTID,

      HG_HAS_CHILDREN ,

      PARENT_ID ,

      PRNAME ,

      PRDESC ,

      PRFINISH ,

      PRSTART ,

      ACTIVE

    FROM INVEST_NSQL insql

      WHERE PARENT_ID = 1

    ORDER BY PRNAME

     

    V/r,

    Gene