Incorrect syntax near the keyword 'OR' when using ##OB_OPTIONSELECT_ALL macro

[Kirk_Sumpter]

We are on 18.0.1.49.

I have created a custom SQL data provider that works fine until I try to use the ##OB_OPTIONSELECT_ALL macro.  Once I implement the macro, I keep getting "Incorrect syntax near the keyword 'OR'" in diagnostics console.

I feel like I've implemented exactly as I've seen other examples and in the MRG:

Here is the data provider SQL:

SELECT hsi.rimlog.usernum
,hsi.useraccount.username
,hsi.rimlog.logdate
,hsi.rimlog.actionnum
,hsi.rimlog.messagetext
FROM hsi.rimlog
INNER JOIN hsi.useraccount ON hsi.rimlog.usernum = hsi.useraccount.usernum
where (hsi.rimlog.logdate between @{DATERANGE})
AND hsi.rimlog.actionnum in (##OB_OPTIONSELECT_ALL{(@{ACTIONNUMBER}, 0)})
AND hsi.rimlog.usernum in (@{USERNUMBER})

If i just do a generic parameter without the macro for actionnum it works fine (except doesn't implement the all option in the query obviously), and as you can see there are no other "OR" statements, so it seems like the error has to be coming from the implementation of the macro.  The SQL debug shows the macro being referenced, so I'm at a loss.

Any ideas?

[Kirk_Sumpter]

Hi Jason,

No need to apologize, I appreciate the help! 

With your suggestions above, I have two trains of thought:

1) Can you please clarify your statement: "we don't currently support using ##OB_OPTIONSELECT_ALL with a multi-selection of values"?  Based on what I thought you meant, I removed the "Allow Mulitiple Values" option for the ACTIONNUMBER parameter, but I still received the same error.  If that's not what your referencing, I'm a bit lost, but my solution may lie in there somewhere.

2) I implemented the workaround using a boolean parameter, but the issue I'm having with that is if I don't make the ACTIONNUMBER parameter a required input, I still get the same error pop up when trying to move past the parameter configuration screen.  To make it work I'm having to have the user select the checkbox for all, AND select an item in the action list box...which is obviously confusing and counter-intuitive.

[Jason_Knight]

1) By "multi-selection of values" I mean checking the "Allow Multiple Selection" box and using the "IN" operator.  If you've unchecked "Allow Multiple Selection" then you'll likely want to change "IN" to "=" in the query's clause about ACTIONNUMBER.  If you've changed it to "=" and still get the error, please reply with your current SQL query.

2) In my above workaround of putting "AND ((@{ALLACTIONS}=1) OR hsi.rimlog.actionnum in (@{ACTIONNUMBER}))" in the query, ACTIONNUMBER is still configured as "allow multiple selection" and "input required."  On my system, "input required" is automatically checked and grayed out when I check "allow multiple selection".  I don't see a way to have "allow multiple selection" without also having "input required."

[Kirk_Sumpter]

thanks Jason, appreciate your continued engagement on this:

1) by changing the SQL to '=' rather than in, this did work.  The fact this macro isn't supported for a multi-select list does take some shine off it, since it can only be used in an 'all or none' scenario, but at least this does work as currently intended.

2) The issue I have with the check box workaround is I don't want to make the select list required input...but that's the only way I can make it work.  When I try to configure the select list to NOT be required input, the configuration fails with an error.  Making the select list required means the user has to check the box for all, and then has to randomly select a value from the actions select list, which has no value or bearing on the actual query being run. 

To clarify, for this scenario, here is the applicable SQL line:

((@{ALLACTIONS}=1) OR hsi.rimlog.actionnum = (@{ACTIONNUMBER}))

In the parameter config for the ACTIONNUMBER, I want to NOT require input, nor allow multiple selections, but I keep getting the same error message as before when trying to move past the parameter config screen and both of those options are not selected.

If I do set the parameter to require input, it does 'work' (i.e. the SQL is generated and you can run the report), but from a user perspective it means an extra click on a random action to get past the validation of requiring an action to be selected (and probably very confusing as well).

[Jason_Knight]

2) Sorry, I'm confused.  You wrote "In the parameter config for the ACTIONNUMBER, I want to NOT require input, nor allow multiple selections, but I keep getting the same error message as before when trying to move past the parameter config screen and both of those options are not selected."  You want to not allow multiple selections?  If that's the case -- if you don't want to allow multiple selections -- then you don't need the ALLACTIONS parameter.  You can go back to using ##OB_OPTIONSELECT_ALL instead.