Portlet XPATH XML Construct

Document ID : KB000055224
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:
I created two user-defined portlets that I placed on the Project Dashboard page.
The two portlets do not return a result set based on the Project for which I am viewing the Dashboard page.
The NSQL Query statement uses the user-supplied parameter construct in the WHERE clause. Is this the correct way to implement additional portlets on the standard Project Dashboard Page or any other page?

Here is an example of my NSQL Query statement:
SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.ID:PID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.UNIQUE_NAME:PNAME@,
@SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):TEAM_COUNT:AGG@
FROM SRM_PROJECTS P,
PRTEAM T
WHERE P.ID = @WHERE:PARAM:USER_DEF:INTEGER:ID@
AND P.ID = T.PRPROJECTID
AND @FILTER@
GROUP BY P.ID, P.UNIQUE_NAME
HAVING @HAVING_FILTER@


Solution:    
The NSQL Query Statement does not have the proper construct in the WHERE clause.
There is another kind of construct that can be used in the WHERE clause that will allow the portlet to retrieve a name-value pair from the XML page URL where you are placing your user-defined portlet. This is called an XPATH or XML Parameter Construct.

This construct can only be used on a specific portlet instance type; not on the "General" portlet instance type. This construct is expecting the internal ID value to come from the URL that is displaying the page that contains the portlet and therefore, the portlet must be created as a specific portlet instance type, such as the "Project" object portlet instance type to be place on one of the pages from the project object. If you create a "General" portlet and place the "General" Portlet on a general page, there will not be a specific internal ID value in the URL that displays the page containing the portlet and therefore data will not be displayed as you might expect.

For this particular scenario where the portlet is placed on the Project Dashboard, change the NSQL Query as follows:

NOTE: 'id' is the name of the parameter that appears in the Project Dashboard URL that we want to use for this particular query to filter the data appropriately.

SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.ID:PID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:P.UNIQUE_NAME:PNAME@,
@SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):TEAM_COUNT:AGG@
FROM SRM_PROJECTS P,
PRTEAM T
WHERE P.ID = @where:param:xml:string:/data/id/@value@
AND P.ID = T.PRPROJECTID
AND @FILTER@
GROUP BY P.ID, P.UNIQUE_NAME
HAVING @HAVING_FILTER@

Create a portlet with a "portlet instance type" set to "Project" using the NSQL Query.
Place this portlet on a Tab within the Project Default Layout.

When using this construct, be sure that you understand the name-value pair that you want to retrieve from the URL on the page where the portlet is placed. If you do not specify the correct named parameter, your portlet will not generate the expected result set.

This functionality is not currently supported for use on custom attributes that use a Dynamic Query Lookup. 

The Parameterized Lookup functions differently than the XPATH XML Construct. The application may have seeded, system-restricted lookups that contain XPATH XML constructs, but the use of these lookups are restricted and controlled by the application.