Multi value parameter within SSRS dataset (Command type: text)

Today I wanted to use my Multi-value parameter function within a SSRS dataset (Command type: text).
Guess what…it didn’t work!

After literally an hour, I found the answer….it’s not possible at all.
Instead SSRS basically ‘chops’ the multi value string into something like: ‘aa’,’ab’,’…’
My function requires a single string instead of many small ones, for example: ‘aa,ab,…’

Now you’re thinking, what about the Join-function? As in:
=JOIN(@MultiValue,”,”)
Which returns a single string like aa,ab (no quotes!)

Or if you wish:
=”‘”+JOIN(@MultiValue,”,”)+”‘”
Which returns a single string like ‘aa,ab’ (with quotes using dynamic string-manipulation)

Too bad….the JOIN-function isn’t available within the Dataset (nor is EXEC() btw)

The answer is actually quite simple, don’t use statements like
‘X IN (SELECT * FROM dbo.fn_MultiValue(@MultiValue,’,’))’
Instead use the dataset Filter (or List if you use cascading parameters in your report):

Advertisements

One thought on “Multi value parameter within SSRS dataset (Command type: text)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s