I am writing an NSQL query for a portlet. I would like to be able to filter the query by my page filter I have setup. Can this be done?
"What do I put here?" - whatever you want! ;-)
So if you put @WHERE:PARAM:USER_DEF:INTEGER:myparameter@ then you get, in the attribute list for the query, a new attribute called param_myparameter
NB this is just "usual" parameters in queries ( lookup NSQL Queries › User-Defined NSQL Constructs › Parameters in the studio documentation for more info).
If the fields in your filter portlet have lookups associated with them, then you need to make sure that the new param_myparameter in the query has the same lookup associated to it, and/or is the same datatype as the field in the filter portlet ; then you can make the mapping in the page setup.
But we don't have to use user-parameters to make this work though ; alternatively in your NSQL if you return as a column/attribute your project id, then you could just map the filter-portlet;s project id field to that column (as long as the datatypes match like above).
Have you checked this ?
Also, check this:
Thanks for your response.
I already have filter portlets setup on my portlet page. I am doing a sum in my NSQL code and it is summing up my entire approved budget for every project in the table. I would like to sum up the approved budget for the project and fiscal year that I am filtering on. (Note: My budget has a list of line objects with different amounts and that is what I am summing) See my code below. I would like to add a parameter that picks up on the page filter and only sums up for the project number that I key into the page filter. I am not sure if I can reference my page filter of Project Number in my NSQL code.
FROM ODF_CA_PROJECT P
INNER JOIN ODF_CA_NL_FIN_PROJ CR ON CR.ODF_PARENT_ID = P.ID
INNER JOIN ODF_CA_NL_CAP_ACC_FIN_PROJ CP ON CP.ODF_PARENT_ID = P.ID
AND P.ID = @WHERE:PARAM:XML:INTEGER:/?.../@value@ ( I would like to be able to reference my project number page filter here.)
GROUP BY P.ID,P.NL_DTC
Can you check if this helps ?
I think NJ has pointed you at all the right sort of thing, but I'm not sure what you mean by "page filter"?
If you have a portlet-filter on your page (this is what I would think of as "page filter") then you just associate a field in the filter-portlet with a NSQL parameter field in your portlet. (one of the @WHERE:PARAM:USER_DEF fields)
Alternatively, if your page exists in in the context of an object (eg project dashboard page) and thats what you are referring to as "page filter" ; then the object's id should be in the URL ; and thats what you can pick up using the @WHERE:PARAM:XML: syntax).
Or do you mean something else? (screen shot maybe useful to explain)
I have two Object based Portlets that are being filtered by a portlet-filter. I would like to pass this portlet-filter on to a third NSQL based portlet on the same page that totals up the approved budget based on the portlet-filter placed on this page. I am using the overview page to place a tab on the general screen to access these portlets. How can I reference this portlet-filter in my NSQL code? I have attached a screen shot of my screen for your reference.
In the NSQL for your third portlet use a @WHERE:PARAM:USER_DEF field for your project id.
On the page layout (in the Filter Content Mappings which you find against the filter-portlet on the page setup), you associate the relevant field from the filter-portlet with that NSQL parameter.
I am using DTC as my project id. See my NSQL code below for the third portlet.
AND @WHERE:PARAM:USER_DEF***What do I put here?
GROUP BY P.NL_DTC, NL_APP_BUD, CP.NL_APP_BUD
Hi chrisflynn - Did Dave_3.0 's response help answer your question? If so please mark as Correct Answer. Thanks!
Retrieving data ...