0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

BEAUTIFUL!! This worked perfectly! I am too new to the site to give you an upvote apparently, but this was VERY much appreciated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.