Clarity

Can you pass a Page filter field to your NSQL query to filter your portlet based on what you select in your page filter?

Yes.

Filter-portlet on the page. in the page properties you map the fields on that filter-portlet to the fields on your NSQL-portlet. Fields have to be the same datatype (so if its a lookup field in your NSQL-portlet then the field in the filter-portlet has to be the same lookup)

It all works OK once you see the places you need to do "stuff".

Would you be able to point me to some examples of the NSQL code and where you setup the page properties that show how you map the fields on that filter-portlet to the fields

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)[phone]  | 709 729-6767  (f)   | chrisflynn@gov.nl.ca<mailto:chrisflynn@gov.nl.ca>

There is nothing "special" about the NSQL code at all - its exactly the same as a "normal" portlet (in fact it doesn't have to be a NSQL based portlet, will work with a object portlet too).

So for the fields in your portlet that you would normally put onto the "List Filter Section / Layout" - don't do that at all, just leave the "List Filter Section" empty* - but those fields are the ones that you would map in the page layout to the fields in your filter-portlet. The mapping is on the page properties / page filters tab (add the filter-portlet to the page obviously!) and then theres a little properties icon next to the filter-portlet name - click that ("Filter Mappings") and thats where the mapping is set up.

(* - i.e we don't combine filter-portlet fields with normal-portlet fields - we only have the filter-portlet fields)

I think I am understanding most of what you are saying. The only thing I am unsure of is what do I put in where I currently have the static fiscal year to now. See highlighted below on the last line. I want this to be based on the on the page filter I created. So if I just use the page filter field I created in the page filter which is nl_fis_yr, will it recognize what that field is in my NSQL code. Can I write the following: where CR.NL_OCIO_FIS_YR = nl_fis_yr ?

Currently I have the fiscal year statically assigned: @SELECT:METRIC:USER_DEF:IMPLIED:(SELECT SUM(nlaprcpy)  FROM ODF_CA_NL_FIN_PROJ CR where CR.NL_OCIO_FIS_YR = 'nl_2016_2017'

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)[phone]  | 709 729-6767  (f)   | chrisflynn@gov.nl.ca<mailto:chrisflynn@gov.nl.ca>

There should not be any changes in the NSQL Query at all. Here's a really simple example, just so the concept is clear.

* If the NSQL Query is like this:

select

@select:dim:user_def:implied:query0:id:id@,
@select:dim_prop:user_def:implied:query0:full_name:full_name@,
@select:dim_prop:user_def:implied:query0:unique_name:unique_name@

from srm_resources

where @filter@

You will get three fields when you couple this query to a portlet: id, full_name and unique_name.

* Now, just go to Administration -> Studio -> Portlets. Click on New -> Filter Portlet. Fill out the information in the first page. In the second page, click on "Add" and create a "String" field called "full_name".

* Go to the page where you have displayed the portlet created out of the query above, and click on "Personalize". Click on "Page Filters -> Add". Select the "Filter Portlet" created above and Add.

* Here's the most important part. Click on the icon displayed in the second column, of the "Personalize -> Page Filters" page. Once you are in, select "full_name" in the "Mapping Field".

That's it. You have mapped an ordinary portlet's field, to a filter portlet's field. Your filter portlet will now have access to search a column in the ordinary portlet.

Jeevan has given you a nice worked example - but for your specific case what I think you should be doing is;

(assuming you are trying to parameterise the passing of what you have hard-coded as 'nl_2016_2017' currently in your where CR.NL_OCIO_FIS_YR = 'nl_2016_2017' ( sorry I'm not seeing any highlighting?) )

Firstly you need to change that hard coding into a parameter - would look like this;

where CR.NL_OCIO_FIS_YR = @WHERE:PARAM:USER_DEF:STRING:my_fiscal_year@

That would create in your query attributes an attribute called param_my_fiscal_year

You would (I assume) associate that parameter attribute in the query with a lookup that delivered fiscal year values [ you don't have to do this if you are relying on users typing in the right value though, but I would think a lookup is a better solution ]

Up to now this is all still a "normal portlet" though ; and you would "normally" put that param_my_fiscal_year attribute on the "List Filter Section / Layout" of the portlet using the query. But if we are going to use a page filter then don't do that.

Instead you make sure that the nl_fis_yr field in your page filter is defined using the same lookup as above (if you used one that is).

Now put everything on a page - page filter and portlet ; and in the page properties / page filters tab you should see the little properties icon next to the filter-portlet name ("Filter Mappings") and thats where the mapping is set up - and because you have the same lookup then the mapping will only allow you to select the param_my_fiscal_year field in the portlet to be mapped to the nl_fis_yr field in the page filter (which is a bit redundant in this case, but if you had more fields to map of the same datatype(s) you can see why you have to do this explicitly).

--

Hopefully this and Jeevan's example makes enough sense?

I think this is what I was looking for. I will give a try and report back.

You guys really know your stuff.

Thanks a million.

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)[phone]  | 709 729-6767  (f)   | chrisflynn@gov.nl.ca<mailto:chrisflynn@gov.nl.ca>

I have the following command. What would I do in this case.

@SELECT:METRIC:USER_DEF:IMPLIED:(SELECT SUM(NL_APP_BUD) FROM ODF_CA_NL_FIN_PROJ CR where CR.NL_OCIO_FIS_YR = 'nl_2016_2017'  AND P.ID = CR.ODF_PARENT_ID)+(SELECT SUM(NL_APP_BUD) FROM ODF_CA_NL_CAP_ACC_FIN_PROJ CP where CP.NL_OCIO_FIS_YR = 'nl_2016_2017' AND P.ID = CP.ODF_PARENT_ID):TOT_APP_BUD@,

Like you stated, I need to parameterize the hard coded fiscal year of ‘nl_2016_2017 in this code.

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)[phone]  | 709 729-6767  (f)   | chrisflynn@gov.nl.ca<mailto:chrisflynn@gov.nl.ca>

I would never put any complicated logic into the @SELECT part of the NSQL statement, leave it in the SQL part.

So instead of;

SELECT
@SELECT:something_complicated@,
@SELECT:something_else_complicated@
From
my_tables
WHERE...

I would code it as;

SELECT
@SELECT:column1
@SELECT:column2
FROM
(
SELECT something_complicated AS column1,
something_else_complicated AS column2
from my_tables
WHERE...
)

(obviously bogus syntax, trying to illustrate structure not syntax here)

that way you can put your @WHERE parameter into the SQL part of the code OK.