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):

‘All’-entity within a Multi-value parameter

In addition to my previous post, an ‘All’-entity is very handy when it comes to some reports.
In my example, I’ll continue using the Sales example from my previous post.

Normally when you have a parameter with just one value, it’s easy: just use a NULL-value (click).
But when it comes to Multi-value parameters, it’s a bit more tricky.

I’ve added an ‘(All)’-entity to my selection of the SalesTerritory table:

In combination with my MultiValue function, these SQL statements will do it:


SQL code: TotalSales example.doc

Results:

SET @TerritoryList = ‘-1’ will return:

Note: The ‘(All)’-entity cannot be used in combination with other entities.
‘-1,2,4’ will not work, because TerritoryID ‘-1’ is not an existing ID.

Multi-value parameter function

Some reports use Multi-value parameters in combination with the Report Item ‘List’.
For example, in order to print a sales overview per location/territory.

If you’re using a parameter called TerritoryID and you wish to use a Multi-value list, you need some function that splits the parameter value (for example: ‘2,4,6,8’) into separate ID’s.

Well, there is an easy function for it:

SQL code: fn_MultiValue.doc

A simple example:

The results: