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.
Advertisement
Answer
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.