Clarity PPM

Expand all | Collapse all

Hierarchical Lookup similar to OBS

  • 1.  Hierarchical Lookup similar to OBS

    Posted 06-18-2009 03:40 AM
    Hi,  Can we have a lookup which is similar to the OBS hierarchical structure (with the complete path).  We must be able to select the OBS from a hierarchical structure and it should display the complete path after i select it.  Using 'OBS filter browse'  I do not get the complete path.Using "NBI_GET_OBS_FLAT_PATH_FCT" i get the complete path but the lookup is not hierarchical.  Is there a way where we can combine these both?    Thanks,Shruthi

  • 2.  Re: Hierarchical Lookup similar to OBS

    Posted 06-18-2009 07:06 AM
      |   view attached
    Shruthi,  i have craeted  an Lookup based on Nsql for this issue and working fine.Enclosed  an doc which have nsql,2 views sql (which i have created based on OBS Tables for our reporting) and the snapshots of lookup.we are using this for past 2 years.  Issues with Clarity OBS Browse  1. When an user clicks the OBS Browse it will show all the OBS and user has to select particular OBS and then select his untit / level.2.  When he select an obs which has 7 levels,that if he selects the 4th level ,it will show only the 4th level not the full Hierrachy.He do knows what he has selected.It is not at all user friendly.3. If he has the same name for different units imagine the confusion,he has re-do it again.4.Only the GUI is better.  Hope this one helps.  sundar


    OBS   345K 1 version

  • 3.  Re: Hierarchical Lookup similar to OBS

    Posted 04-27-2010 04:59 AM
    Hi Sundar,  I think this is great. Is this wrriten in ORACLE. we use SQL . Let me try this out

  • 4.  Re: Hierarchical Lookup similar to OBS

    Posted 04-28-2010 04:31 AM
    Hi Sundar,  Im getting an error message when I create this  ODF-0001: Unable to register dynamic query lookup: Could not retrieve or register the nsql::NSQL Syntax exception: NSQL_SYNTAX_ERROR_DB_ERROR, Info: [CA Clarity][SQLServer JDBC Driver][SQLServer]'DECODE' is not a recognized built-in function name. SQL Text: SELECT obs.obs_id obs_id, obs.obs_name obs_name, obs.RT_OBS RT_OBS FROM ( SELECT OBS_ID, DECODE(RT_OBS,NULL, 'Group/Business/Business Unit/Function/Team/Sub-Team/Sub-SubTeam','Group/Business/Business Unit/Function/Team/Sub-Team/Sub-SubTeam') obs_name ,RT_OBS FROM ( SELECT DISTINCT OBS_ID,SUBSTR(OL.OBS_PATH,1,LENGTH(OL.OBS_PATH)-1)RT_OBS,depth FROM UV_PRJ_RSM_OBS_DETAILS OD, UV_OBS_LEVELS OL WHERE OD.OBS_ID = OL.LEVEL1_ID AND OD.TABLE_NAME = 'SRM_RESOURCES' AND OD.TYPE_UNIQUE_NAME = 'RT_OBS' ) ORDER BY RT_OBS )obs .

  • 5.  Re: Hierarchical Lookup similar to OBS

    Posted 04-28-2010 04:59 AM
    Hi Karen...  "DECODE" is a Oracle construct; you appear to be on SQL*Server so the syntax is going to be a little different.  [In Oracle terms, DECODE is a IF/THEN/ELSE shorthand, so DECODE(column_a,value,1,'Yes',2,'No','Other') means IF column_a = 1 then "Yes", ELSE if column_a = 2 then "No", Else "Other"]  Hopefully someone SQL*Server literate (not me!) can give you the equivalent syntax!

  • 6.  Re: Hierarchical Lookup similar to OBS

    Posted 04-28-2010 09:11 AM
    Hi,  sorry not able to help u.sql server so long(7 yrs back...).More over i don't have sql server installed.i used this OBS Lookup in 7.5.x version.  From our Forums.  Taken some examples from net.  The equivalent of Oracle's DECODE function in SQL Server is the CASE function.

    CASE input_expression
          WHEN when_expression_1 THEN result_expression_1
      [ WHEN when_expression_2 THEN result_expression_2
          WHEN when_expression_n THEN result_expression_n ]
      [ ELSE else_result_expression ]


    DECODE can be conveniently simulated using the T-SQL CASE expression.

    SELECT Sport,
    CASE Sport
    WHEN 'Cricket' THEN 'England'
    WHEN 'Hockey' THEN 'India'
    WHEN 'Base Ball' THEN 'America'
    END AS 'Originating Country'
    FROM Sports    cheers,sundar

  • 7.  RE: Re: Hierarchical Lookup similar to OBS

    Posted 06-22-2010 07:12 PM
    Hi Sundar,

    I can't seem to find the attachments you are refering to? I have a similar needs. Please help.


  • 8.  RE: Re: Hierarchical Lookup similar to OBS

    Posted 06-23-2010 06:01 AM
    I dont see any attached document.. How to get it?

  • 9.  RE: Re: Hierarchical Lookup similar to OBS

    Posted 06-23-2010 01:55 PM
      |   view attached

    I have developed this in 7.5.2 to suit our own needs.we used it for our custom reports.
    The main reason for doing it is explained above Post.Issues with Clarity OBS Browse.

    I don't know whether will help you but i have enclosed it.

    CA Admin-->Where are the attachments gone ,is all the attachments in old ca forums is gone??????????????



  • 10.  Re: Hierarchical Lookup similar to OBS

    Posted 05-04-2015 11:04 AM



    we have got a problem with filtering in this lookupu in browse window. When I try to filter for RT_OBS field it always returns all values. Please have you got any idea how to solve this?


    Our Clarity version is 06 14




  • 11.  Re: Hierarchical Lookup similar to OBS

    Posted 05-05-2015 03:18 AM



    I found the solution, there is missing WHERE @FILTER@ in the lookup query. After adding this to query it works as I expect.