Clarity

  • 1.  Clarity Lookup attribute

    Posted Sep 15, 2016 06:38 AM
      |   view attached

    Hi,

    I am new to CA PPM module ,working on a report.  We are using Clarity v12.1 Service Pack 2.

    The report is retrieving fine. The requirement is that we want the lookup value to be displayed instead of the Lookup code.

    Steps taken:-

    1. Already created Lookup for the column and is activated.

    2. In the Look up properties- Parent  window, display attribute is taken as Name.

     

    What else I need to do to get the Display Value instead of the Code. Can anyone guide me as to how to accomplish this.

    PFA

    Thanks

    Attachment(s)



  • 2.  Re: Clarity Lookup attribute

    Posted Sep 15, 2016 09:20 AM

    Hi (and welcome).

     

    What do you mean by "a report"?

     

    --

     

    The lookup is setup right, but the lookup settings only really affects how the lookup is displayed against an Object in Clarity, once you get away from the object itself then you find you have to do a lot of the work yourself. For example if you are building a NSQL based portlet (or a SQL based report) then you have to pull the lookup display values from the database itself ; CMN_LOOKUPS is the table but theres also a useful view CMN_LOOKUPS_V that sorts out the language-specific label too)



  • 3.  Re: Clarity Lookup attribute

    Posted Sep 15, 2016 09:37 AM

    Hi David,

    Thanks for the reply.

     

    Report here means - NSQL based portlet.

    So my understanding from your mail is that , we cannot use the static lookup values in our NSQL based Portlet.

    If so then do I need to make the changes in the NSQL QUERY to get the display value?

    Correct me if I am wrong.

     

    Also let me know when can I use the static lookup values.

     

     

    Thanks



  • 4.  Re: Clarity Lookup attribute

    Posted Sep 15, 2016 10:56 AM

    In your NSQL query attribute settings you should then associate the right lookup with the column that you return in your NSQL then experiment with the display setting s in the portlet itself - that might give you enough functionality?

     

    If you need to return values outside the stock portlet/query functionality then you would use the table/view I've indicated.



  • 5.  Re: Clarity Lookup attribute

    Posted Sep 15, 2016 11:11 AM

    Hi David,

    I had made changes to the portlet NSQL.

     

    Below are the Changes-

     

    @select:metric:user_def:implied:coalesce(kulportfoliocat.Lookupname,''):Port_lookup:AGG@

    ……

    …..

    FROM

    (SELECT

    v1.name Lookupname

    FROM

    clarity.cmn_lookups_v v1

    left join clarity.odf_ca_project v2 ON v1.lookup_code = v2.deb_portfoliocat

    WHERE

    v1.lookup_type = 'DEB_PORTFOLIOCAT' and v1.language_code = @WHERE:PARAM:LANGUAGE@ and lookup_code = v2.deb_portfoliocat)

    kulportfoliocat

    Where

    …….

    …….

     

     

    This query is taking long to execute. Is this the correct query ?

     

    Thanks

    Kul



  • 6.  Re: Clarity Lookup attribute

    Posted Sep 15, 2016 12:22 PM

    I don't know what your portlet is trying to show, so its hard to say whether your query is right or wrong.

     

    Have you associated your lookup with the query attribute and seen how that behaves?



  • 7.  Re: Clarity Lookup attribute

    Posted Sep 16, 2016 10:01 AM

    Thanks David for your help ☺

    Will try your solution steps and  hopefully it will work.