CODEWITHSUNDEEP
sqlreporting-servicesimpalareportbuilder
TrevB
TrevB

Reputation: 40

SSRS Converting MultiValue Text Paramater into a String in SQL Syntax

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.

Upvotes: 0

Views: 2233

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10880

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.

Upvotes: 1

Related Questions