Technique sought for linking an NSQL parameter to a portlet or portlet pa

[Anon Anon]

I am trying to find clear guidance on the process to follow for creating a portlet which contains a parameterised lookup. For example I would like to create a portlet which could appear on the Clarity Overview page and list all risks assigned to whoever had logged in (much as the "Action Items" and "My Projects" portlets do). Similarly I would like to have portlets appearing on the Projects Dashboard page to be limited to the ID of the particular project in question. The manual makes reference to user-supplied and built-in parameters and explains how they can be included in NSQL queries. However I have been able to find no further information explaining how to link to these these parameters in a portlet or a portlet page.

Any help gratefully appreciated.
I am using Clarity 8.1 sp3

[Anon Anon]

I don't quite follow what you mean by "how to link to these these parameters in a portlet or a portlet page"?    But the system provides "built-ins" - see the section in the "Studio" manual that says;  -----Built-in parameters, which automatically take their values at run-time based on
the current user settings or system context. Built-in parameters are specified
using the following syntax:@SELECT:PARAM:PARAM_IDENTIFIER[:ALIAS]@
or,
@WHERE:PARAM:PARAM_IDENTIFIER@
Where:
PARAM_IDENTIFIER is one of the following:
 USER_ID
 USER_NAME
 LANGUAGE
 LOCALE-----  which is just saying that you include the construct @WHERE:PARAM:USER_ID@ to pick up the logged on user id (etc)      But for the idenitifying the "current project" on the "Project Dashboard" page, you just use the construct;  @WHERE:PARAM:XML:INTEGER:/data/ProjectId/@value@  (which picks up the "ProjectId=" bit from the URL on the page)  If you look at the stock portlets that are on the Project Dashboard page you can see how that is used.    Much more interesting is the fact that you can use that technique for yourself though (i.e. you are not limited to the ProjectId one) - by building "linkable portlet pages", which lets you start to put things into the URL which you then can read using that :XML: construct in portlets that you put onto that portlet page. (You can reference any of the parameters on the URL in NSQL using this)  ---  Finally (!) there is a stock portlet called "Risks Assigned to Me" (this might be in one of the accelerator packs though) that does what one of your examples wants!    Good luck!  David MortonCapgemini

[Anon Anon]

Here's how it works:  1) You define Link Parameters in your portlet pages. These parameters will then appear in the URL when you access the portlet page.2) When you build a link to that portlet page (from another portlet), the same parameters will appear in the link definitions when you link to this portlet page. Choose values from the source portlet (the one you are linking from) to pass into the portlet page you are linking to. For example, if you want to pass the project ID into the destination portlet page, you must have project ID in the source portlet (that you are linking from).3) When you write your NSQL queries for the destination portlets, you can read the values of your parameters from the URL of the portlet page. This will filter the results of the NSQL query based on the parameters you pass in.   Creating link parameters:   1) Create a portlet page2) Create new link parameters for the variables you want to pass in. Make a note of the exact names you choose--these will need to appear in the  NSQL queries of the portlets that will appear on the page.   Creating NSQL queries based on the portlet page parameters:   1) Create an NSQL query for a portlet you would like to appear on the destination portlet page.2) In your where clause, include the following: @WHERE:PARAM:XML:INTEGER:/data/ parameterName /@value@    For example, if you create a link parameter called "resourceID" on the portlet page, you could have: WHERE SRM_RESOURCES.ID = @WHERE:PARAM:XML:INTEGER:/data/ resourceID /@value@ in your NSQL. This will filter the results of the portlet to only show records where the resource id = the resource id that appears in the URL of the portlet page.    The name of the parameter has to match the name you defined in the link parameters of the portlet page.    Be careful of the type (integer in this example). You may need to change that based on the type of parameter you are passing in.      Creating links to pass in to the portlet page   1) Edit the NSQL for the source portlet (the one you are linking from)2) Add a link to this NSQL, selecting the portlet page you created above.3) When you select your portlet page in the link definition, you will see the parameter you defined. Choose a value from the source portlet NSQL to pass in to the parameter. For example, if you are passing in SRM_RESOURCES.ID into a parameter called resourceID, you will need to return SRM_RESOURCES.ID in the source portlet NSQL, then pass it in to the resourceID parameter when you define the link from the source portlet NSQL.4) Edit the source portlet. Add the link you defined in the source portlet NSQL to a column in the source portlet.  Now when you click on the link in the source portlet, it will navigate to the portlet page and pass the link parameter to the URL in the destination portlet page. The NSQL for the portlets on the destination page will pick up the parameter from the URL and use it to filter the results of the NSQL query.  

[Anon Anon]

HI, this is very helpful but I have a problem related to a lookup that we've created on a portlet.   Basically, the lookup is for a portlet we've created called cumulative variance.   The lookup we need is supposed to list all the baselines of the current project that the portlet is on, however.. we've only been able to get it to list all the baselines for all projects.   The following nsql seems to work fine for this:  select
@SELECT:pb.id:id@,
@SELECT:pb.name:name@,
@SELECT:pb.project_id:project@,
@SELECT:ii.name:project_name@,
@SELECT:ii.code:project_code@,
@SELECT:CASE is_current WHEN 1 THEN '*' ELSE ' ' END:is_current@
from prj_baselines pb
INNER JOIN inv_investments ii
ON pb.project_id = ii.id
where ii.is_active=1      However, based on the posts above I added the following line hoping that it woulf further filter down the results and give me only the baselines associated with the project that the portlet is running:    and pb.project_id = @WHERE:PARAM:XML:INTEGER:/data/ProjectID/@value@  It does not do this however and the result back is nothing, no results.   Anyone have any ideas? am I building the parameter correctly or is there some reason that the lookup within the portlet doesn't know what project_id it is in?   Thanks!  

[Anon Anon]

afelston,  can i suggest that you read through this series of posts in case this helps  Try the dummy lookup approach that i've listed as the last post, to confirm / see that the XML identifier that you are trying to use, is in fact  populated by Clarity on the page that you are trying to use it?  I've found that the available ids from Clarity vary / change with the pages that you are on.  Dynamic NSQL and URL ids  samos

[Anon Anon]

Hi Samos,Ok.. I read the other post and I added the following   into my nsql for the lookup and clicked save:   select
@SELECT:pb.id:id@,
@SELECT:pb.name:name@,
@SELECT:pb.project_id:project@,
@SELECT:ii.name:project_name@,
@SELECT:ii.code:project_code@,
@SELECT:CASE is_current WHEN 1 THEN '*' ELSE ' ' END:is_current@
from prj_baselines pb
INNER JOIN inv_investments ii
ON pb.project_id = ii.id
where ii.is_active=1  
and ii.id = @WHERE:PARAM:USER_DEF:INTEGER:ProjectID@       /* note: I also tried adding the @BROWSE:ONLY ... :BROWSE:ONLY@ and also put IS NULL at the end, both gave me errors       Then I  copied the  following query  (I did not create this, it was created by a former employee)   that references the lookup:    SELECT @select:dim:user_def:implied:task:task_int_id:task_int_id@,
        @select:dim_prop:user_def:implied:task:task_sequence:task_sequence@,
        @select:dim_prop:user_def:implied:task:task_id:task_id@,
        @select:dim_prop:user_def:implied:task:task_name:task_name@,
        @select:dim_prop:user_def:implied:task:task_start:task_start@,
        @select:dim_prop:user_def:implied:task:task_finish:task_finish@,
                        @select:dim_prop:user_def:implied:task:BASE_START:BASE_START@,
                        @select:dim_prop:user_def:implied:task:BASE_FINISH:BASE_FINISH@,
        @select:dim_prop:user_def:implied:task:task_status:task_status@,
        @select:dim_prop:user_def:implied:task:project_int_id:project_int_id@,
        @select:dim_prop:user_def:implied:task:project_name:project_name@,
        @select:dim_prop:user_def:implied:task:hg_has_children:hg_has_children@,
        @select:metric:user_def:implied:base_usage:base_usage@,
        @select:metric:user_def:implied:act_usage:act_usage@,
        @select:metric:user_def:implied:etc_usage:etc_usage@,
        @select:metric:user_def:implied:eac_usage:eac_usage@,
        @select:metric:user_def:implied:cv_percent:cv_percent@
FROM
((SELECT v.task_id task_int_id,
v.task_sequence task_sequence,
v.task_code task_id,
v.task_name task_name,
t.prstart task_start,
@DBUSER@.cop_calc_finish_time_fct(t.prFinish) task_finish,
BASETASK.START_DATE BASE_START,
@DBUSER@.cop_calc_finish_time_fct(BASETASK.FINISH_DATE) BASE_FINISH,
v2.name task_status,
p.id project_int_id,
p.name project_name,
case
                    when (select count(*) from prtask where prtask.wbs_parseq = t.prwbssequence) > 0
                    then t.prwbssequence
                    else null
              end hg_has_children,
SUM(NVL(r.USAGE_SUM,0)) base_usage,
SUM(NVL(r.prActSum,0)) act_usage,
SUM(NVL(r.prEstSum,0)) etc_usage,
SUM((NVL(r.prActSum,0)+NVL(r.prEstSum,0))) eac_usage,
CASE WHEN SUM(r.USAGE_SUM) = 0
                    THEN 0
                    ELSE ((SUM((NVL(r.prActSum,0)+NVL(r.prEstSum,0))) - SUM(r.USAGE_SUM)) /
                          SUM(r.USAGE_SUM)) * 100 END cv_percent
FROM     cop_phase_rollup_v v
        INNER JOIN srm_projects p ON v.project_id = p.id
        INNER JOIN prtask t ON v.task_id = t.prid
        LEFT OUTER JOIN cmn_lookups_v v2 ON t.prstatus = v2.lookup_code
                          AND v2.lookup_type = 'prTaskStatus' AND v2.language_code = 'en'
        LEFT OUTER JOIN cop_proj_task_assign_v x ON v.project_id = x.project_id AND v.task_id = x.task_id
        LEFT OUTER JOIN PRJ_BASELINE_DETAILS BASETASK ON t.prid=BASETASK.OBJECT_ID
                          AND 'TASK' = BASETASK.OBJECT_TYPE
                          AND @WHERE:PARAM:USER_DEF:INTEGER:BASEREC_ID@ =BASETASK.BASELINE_ID
        LEFT OUTER JOIN PwC_Effort_Rollup_Vw r ON t.prid = r.id
                          AND     BASETASK.BASELINE_ID = r.baseline_id
              WHERE
                          (p.id = @WHERE:PARAM:XML:INTEGER:/data/id/@value@)
              AND     (@WHERE:SECURITY:PROJECT:p.id@)
              AND     NVL(@where:param:user_def:string:hg_row_id@, 0) =
                                (case when t.prwbslevel=1 then 0 else t.wbs_parseq END)
              GROUP BY v.task_id
            , v.task_sequence
            , v.task_code
            , v.task_name
            , t.prstart
            , t.prfinish
            , BASETASK.START_DATE
            ,BASETASK.FINISH_DATE
            , v2.name
            , p.id
            , p.name
            , t.prwbssequence
)
)
              WHERE     @FILTER@       Then I clicked on the attributes screen and then  clicke don param_baserec_id and selected it's properties, I then defined it as a lookup - numeric and selected the lookup I had definined earlier...  Now on one of the posts you state:   For lookups you should use a parametrized lookup - use:  @WHERE:PARAM:USER_DEF:INTEGER:ProjectID@  Then when you assign this lookup to an attribute, do not hit submit - hit save -   you will notice a new mapping field will show up in your page - one that will allow you to link your parameter (ProjectID in the above example)   to a field in the table where you're creating a new attribute.    By the way - this is available from Clarity r8 on, not previous versions...     I do not see any new mapping field that shows up on the page when I click save??   I am running version 8.1.0.03.4373.   Help! :) Thanks!     Aron Elston

[Anon Anon]

 I've not deeply read your post... but it seems like you are trying to associated a "parameterised lookup" with another lookup / NSQL query somehow?   That just does't make sense to me.  A "parameterised lookup" can be associated to an attribute on an OBJECT (and thats where you get all the "mapping" details) from.  --  Can you (simply) explain what it is that you are trying to do....... ?      Edit : perhaps you did explain it simply (my mistake):   Basically, the lookup is for a portlet we've created called cumulative variance. The lookup we need is supposed to list all the baselines of the current project that the portlet is on, however.. we've only been able to get it to list all the baselines for all projects. The following nsql seems to work fine for this:   But I don't think parameterised lookups are going to help you here at all!    You are trying to get a portlet on a page that already has a "project context" to be associated with that context.... and I don't think that it works like that.   Perhaps you can get what functionality you are after from the "page portlets" method?     Message Edited by Dave on 04-02-2010 02:36 PM [left]

[Anon Anon]

Basically what we are trying to do is this.  1.   On every project we have multiple cost plans, ones that have been created and then obviously the current baseline cost plan that is active for the project.2.   We   created a portlet that basically compares the current cost plan with a prior cost plan that had been created for the project.3.   In order for the user to select which cost plan to compare the current baseline we created a lookup for the user to select the cost plan from.  However, currently the best functionality we've been able to get from the lookup is that it gives the user a complete list of all the baselines for all the projects.   What we need is for it to only give baselines for the project that the portlet is on.   I tried created a system parameter, but that doesn't seem to work, and so I was trying the other method which is to create a user defined parameter, but that doesn't seem to work either.  I'm a little confused on why when I create a lookup on a portlet that is a page on a project why I can't give it a system parameterized lookup to tell the system to only return results which match that project.   Any help would be great, hope this make sense!   :smileysurprised:

[Anon Anon]

I think I understand your problem and I think I tried something similar (and it didn;t work).  Have you tried making the lookup in the portlet a drop-down (rather than a browse) - the drop down is "built" on the page with the project_id context (in the URL) / the browse start a new page (and I think loses the context).   This is just a wild-guess though.  Otherwise; do "Page Portlets" help any?   (would involve some redesign I think though)

[Anon Anon]

Hi Aron,  One thing I'd suggest: seperate the problems that you might be seeing from the PARAMs versus the core query. It looks like you've got the query working fine though.  As I understand it, you are putting a custom portlet on the project's dashboard tab - this gives the portlet the project context that Dave is talking about.This is also the way that you want to have a custom-lookup use the 'current' project identifier to provide the project-specific baseline versions as a user-selectable list, to then apply as a filter to the grid-portlets list of results.  I think that you are going to run into the same problems that we have run into in the past. That is with the ability to retrieve the @WHERE:PARAM's in the lookup-browse, as well as within the nsql query.  I tried what I've understood from what you are trying to do above, today on a 12.0.5 / MSSQL server, and found that I cannot retrieve the project's id for use within a Browse Lookup, but  I COULD retrieve the XML ID data value (which is the project database 5-million id) from within the NSQL of the portlet using @WHERE:PARAM:XML:INTEGER:/data/id/@value@  One thing that I think you should try as a debugging exercise, is to add one or more dummy columns to the nsql-select clause to display you the ids that are working. (such as attached)  As I said above, I see varied population of these parameter id's with varied pages throughout the system.  Maybe it's time for a case / erq for those ids to be populated by clarity on the pages you need / throughout the system?  PS: The block that you quoted me on, is actually Alexandre's  [ Edits: credit where credit is due :) ] Message Edited by samos on 08-02-2010 03:04 PM [left]

[Anon Anon]

Thanks very much to both of you for your replies - I had been getting very frustrated trying to get this information from the manuals which describe the parts but not the whole process. Will now do some experimentation along the lines you suggest.

[System]

Adding a note to push this forward after board consolidation.