Clarity

Say you have a calculated field which has color mapping for ranges.

You then put the field in an NSQL portlet.

How would you filter with that?

If it were in the content then you could  use Case statement and get the value (if the calculated values are not already calculated in a view).

A quick search for "parameter range" and "case parameter" did not give anything which I could have picked as useful.

Is it possible to build a @WHERE:PARAM:USER_DEF:INTEGER:my_caluclated_field... statement to work with the ranges?

Just using the field and selecting Value Range displays only All in the filter drop down.

And selecting color display all the colors in the system while only Green, Yellow and Red are in use.

if I understand you, I think you could use a @WHERE:PARAM:USER_DEF parameter and then "code" into the NSQL what that parameter "means";

WHERE 

(

( @WHERE:PARAM:USER_DEF:INTEGER:my_parameter@ = 1

AND my_calculated_range BETWEEN 0 AND 100 )

OR

( @WHERE:PARAM:USER_DEF:INTEGER:my_parameter@ = 2

AND my_calculated_range BETWEEN 100 AND 200 )

OR

( @WHERE:PARAM:USER_DEF:INTEGER:my_parameter@ = 3

AND my_calculated_range BETWEEN 200 AND 300 )

... etc

)

Thanks Dave_3.0 for the answer to my question.

I see how to make it work that way

.

In the mean time I came up with a workaround with an auxiliary field in the main SQL.

CASE

WHEN (SR.COP_OVERALL_STATUS > -1 and SR.COP_OVERALL_STATUS < 1) THEN '0'

WHEN (SR.COP_OVERALL_STATUS > 0 and SR.COP_OVERALL_STATUS < 16) THEN '1'

WHEN (SR.COP_OVERALL_STATUS > 15 and SR.COP_OVERALL_STATUS < 49) THEN '2'

WHEN (SR.COP_OVERALL_STATUS > 48 and SR.COP_OVERALL_STATUS < 148) THEN '3'

ELSE ''

END O_STATUS_F

That allows to use the simple parameter condition.

^ yeah thats basically the same answer, just structured a little differently (using CASE is good) (my 'my_parameter' is your '0','1','2','3')

Agreed, that was just easier for a non-techie to figure.

It turned out that my solution only worked because I had the SQL inside a  wrapper.

When I tried the same without a wrapper in the NSQL select it did not work.

Had to build the complexity in the parameter. :-(

So something like

AND    (@WHERE:PARAM:USER_DEF:STRING:z_OVERALL_STATUS@ is null OR @WHERE:PARAM:USER_DEF:STRING:z_OVERALL_STATUS@ = (CASE
 WHEN (SR.COP_OVERALL_STATUS > -1 and SR.COP_OVERALL_STATUS < 1) THEN '0'
WHEN (SR.COP_OVERALL_STATUS > 0 and SR.COP_OVERALL_STATUS < 16) THEN '1'
WHEN (SR.COP_OVERALL_STATUS > 15 and SR.COP_OVERALL_STATUS < 49) THEN '2'
WHEN (SR.COP_OVERALL_STATUS > 48 and SR.COP_OVERALL_STATUS < 148) THEN '3'
ELSE ''
END))

That was a good start of the journey.

Now I need to figure out how to make that multiselect and display icons in the filter dropdown.

Multi-select "WHERE:PARAM" filters are possible, but under a limited set of circumstances (you have to code the NSQL in a very specific way) - I don't think you'll be able to do that with your NSQL how it is then ; I can't recall the subtleties of it offhand though (it was pretty complicated ; RobE and myself had a long discussion about it all on the boards a few years ago).

Not sure I've ever got icons in a filter section though (for normal or parameter fields) ; is that even possible?  

Just recalled that the STRING_LIST construct came in at some point ; that allows mullti-select user-parameter values, but again usage is imited (and fiddly NSQL I think, never used it myself)

@WHERE:PARAM:USER_DEF:STRING_LIST:parameter_id@