Clarity

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?

Check this -

NSQL Boolean Parameter

https://communities.ca.com/message/2281435#2281435

NJ

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

@SELECT:DIM_PROP:USER_DEF:BOOLEAN:

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!)

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!

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.

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!