View Only
  • 1.  Boolean parameters in NSQL filter?

    Posted May 20, 2015 09:33 AM

    I'm trying to create a portlet page that has two portlets side by side:

    1. An NSQL-based Pie Chart portlet. We added a Yes/No boolean attribute to the Project object and this is showing the count of those values in a pie.
    2. Standard Object-based portlet on the Project object that displays some project info, including the attribute used in the chart


    Invidually, I can add the needed filters (Active yes/no, new attribute yes/no, project phase, etc) to both portlets and it works fine, but I couldn't figure out how to make a boolean parameter for the NSQL one. Instead I was able to just use an integer param and point it to a Yes/No lookup.


    I then decided to try making a portlet filter to use for the whole page instead of filtering on each portlet separately, but I can't get it to work for the Yes/No fields since on the NSQL portlet its an Integer, but on the object portlet its a Boolean


    Right now, the paramters are formated as

    AND @WHERE:PARAM:USER_DEF:INTEGER:PROJ.is_active:is_active@
    AND @WHERE:PARAM:USER_DEF:INTEGER:PROJ.orh_discretion:is_disc@

    I tried both BOOLEAN and BOOL but neither would work

    AND @WHERE:PARAM:USER_DEF:BOOLEAN:PROJ.is_active:is_active@
    AND @WHERE:PARAM:USER_DEF:BOOL:PROJ.orh_discretion:is_disc@


    Is there a way to make this work?

  • 2.  Re: Boolean parameters in NSQL filter?

    Posted May 20, 2015 09:41 AM

    Check this -


    NSQL Boolean Parameter



  • 3.  Re: Boolean parameters in NSQL filter?

    Posted May 20, 2015 09:57 AM

    I saw that, but I'm not looking for a checkbox. I'd like it to function as it does with the object portlet (dropdown with Yes/No/All) which does work well with the lookup, its just that the underlying type difference (int on the chart, bool in the object portlet) doesn't allow me to make a page filter that can be mapped down to the two portlets, atleast I can't figure out how

  • 4.  Re: Boolean parameters in NSQL filter?
    Best Answer

    Posted May 20, 2015 10:22 AM



    works for me in the select-list of a NSQL portlet (not a user-parameter as you have it)


    i.e. return the boolean as a value in the query, which you can then add to the filter section, rather than trying to code it in as a user-parameter.




    I have also found I sometimes need to cast booleans to integers in MSSQL NSQL to get stuff to work, resulting in very silly looking code like this;


    CASE my_bool WHEN 1 THEN 1 ELSE 0 END my_bool


    (never understood why I needed to do that!)

  • 5.  Re: Boolean parameters in NSQL filter?

    Posted May 20, 2015 12:10 PM

    Whelp, guess I should have tried that from the other thread, I just dismissed it since it wasn't exactly the same thing I was trying to do. D'oh


    Seems good so far, still have to get some more testing/validation, but some quick testing with different selections and it appears to be adding up. Thanks to both!

  • 6.  Re: Boolean parameters in NSQL filter?

    Posted May 20, 2015 12:17 PM

    Just realised I really just repeated what I wrote in that other thread ~7 years ago...


    But to expand a little, despite what old-me said in that other thread, I would still implement the filter as a drop-down (Yes/No/All) rather than a checkbox since it is functionally much more flexible like that. And so if you aren't relying on the BOOLEAN datatype, you can just convert the BOOLEAN to a numeric (in the NSQL) and then a user-parameter should also work OK.

  • 7.  Re: Boolean parameters in NSQL filter?

    Posted May 20, 2015 12:34 PM

    Agreed, thats basically what I had to start, it wasn't until I wanted the page filter to apply to both portlets that it got messy. Oh well, live and learn!