I am being forced to use an ODBC source for SSRS report builder, source is Impala SQL, which doesn't support the use of parameters. So I had to use a string builder function to get parameters into my script. This is working well, however the ask was to allow for multiple values in the text parameter. I am not sure how to get those values into a syntax that will allow the script to execute correctly, i.e. each value wrapped single quotes with a comma separation.
Currently working, single value parameter code: ...member_id = ' "&Parameters!MemberSearch.Value()&"'...
Original dataset has 17+million records and runs out of memory when attempting to use the filters instead of parameters.
Any help is greatly appreciated.
JOIN can be used to convert an array into a string.
="SELECT * FROM TABLE
WHERE member_id IN ('" & JOIN(Parameters!MemberSearch.Value, "', '") & "')"
Values of
Bill Mark Tom
would be converted to
Bill', 'Mark', 'Tom
The second argument ("', '") of the JOIN will put a closing single quote, comma, and an opening single quote between each value. The single quotes in the expression text will be the opening and closing quotes needed for the string.
I don't know Impala but with most DBMSs you'd need to change the EQUALS to IN for multi-values.