Clarity System Variables available for NSQL coding

[Chris Flynn]

Hi Folks,

Just wondering if there is anything similar to the ${gel_objectInstanceId} that we use in Gel Scripts that we could use in a NSQL Query? Something that we could use to autofilter records based on the internal project id. We are building a portlet to display in a tab under the project story board page and we want it to autofilter based on the project that you are in. 

I know we can do it by creating a parameter and passing it to the filter but that has to be manually done. We would like to do it without having the user to worry about it.

Any ideas would be greatly appreciated.

[Praneeth Desur]

Hi Chris,

You can add below statement in WHERE clause in your NSQL query to filter by investment id.

AND (INV.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@)

Note: INV is the alias for inv_investments table.

Regards,

------------------------------
Praneeth Desur
Clarity PPM Specialist
Toyota Finance Australia
------------------------------

[Chris Flynn]

This worked perfectly. Thanks so much.

 

 

Chris Flynn | PMO Lead, Project Management Office - Corporate Services and Projects

Office of the Chief Information Officer
Executive Council
Government of Newfoundland & Labrador
P.O. Box 8700,  40 Higgins  Line,   St. John's  NL  A1B 4J6

709 729-4446 (t)  | 709 729-6767  (f)  | [e-mail]

 

 

[David Morton]

True "built-in" parameters are available in NSQL - see the the "Parameters" and specifically the "Built-in parameters" section of the documentation under "Clarity PPM Studio NSQL Queries"- here is a link ; http://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/business-management/clarity-project-and-portfolio-management-ppm-on-premise/15-8/reference/clarity-ppm-studio-development/clarity-ppm-studio-nsql-queries.html#concept.dita_4b7caade105b1a94c7a793b35d85d249be66d49a_UserDefinedNSQLConstructs

But what Praneeth has (correctly) guided you to is detailed under the "XPATH (XML Parameter) Construct" details (in the same documentation section) which is a neat trick that allows us to reference things on the relevant page's URL ; and in your case as long as you are on a page that has the project id in the URL (i.e. in the Project Dashboard) then this will work OK. But pay attention to the documentation where it says "This construct can only be used on a specific portlet instance type, not the General portlet instance type. This construct expects the internal ID value to come from the URL that is displaying the page that contains the portlet." as that is telling you that it only works on these sort of pages, not where you have just put a bespoke portlet on a bespoke portlet page (what the documentation means by "General portlet type") - but there are ways of getting "parameters" onto the URL of bespoke portlet pages though, this is an often discussed technique on these forums, search for "drill down portlets" and you should find threads. (if you don't find these and this is something you want to exploit, post in here again and someone will dig out the threads).

[Chris Flynn]

Thanks. This is working perfectly.

 

 

Chris Flynn | PMO Lead, Project Management Office - Corporate Services and Projects

Office of the Chief Information Officer
Executive Council
Government of Newfoundland & Labrador
P.O. Box 8700,  40 Higgins  Line,   St. John's  NL  A1B 4J6

709 729-4446 (t)  | 709 729-6767  (f)  | [e-mail]