Multi-value parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 25 May 2006 20:01 GMT

I want to make certain parameters in my report multi-valued; however, for
some of those parameters, possible values include commas, as such:

Value 1: a,b,c
Value 2: 1,2,3

Reporting Services uses a comma as the delimiter, so the parameter list that
gets passed into the stored procedure is "a,b,c,1,2,3". This obviously won't
work.

Here's my question: can I change the delimiter to something other than a
comma? Failing that, is there a work-around for this problem?

Thanks,
Mark

Reply to this Message

Yitzhak Khabinsky - 26 May 2006 07:02 GMT

It is possible.
Let's say your multivalued parameter is called MultiValuedList.
Define a second parameter and assign the following expression to it:
=join(Parameters!MultiValuedList.Value,"~")
By doing thar tilde will be your delimiter.
Pass your second parameter to a stored procedure.

Don't forget to check on prompt as Hidden for that second parameter.

Yitzhak Khabinsky

Reply to this Message

Mark - 30 May 2006 18:00 GMT

This works, except that I have 2+ cascading multi-value parameters, where the
selected items from one parameter are fed into the next parameter as input.
Here is the problem that arises from that scenario:

Suppose I have two multi-value report parameters, A and B, both of which are
populated by stored procedures. The selected items from A act as the input
for B. Heeding Yitzhak's advice, I add a parameter between A and B--let's
call it X. X merely combines the selected parameters from A into a string
(with a custom delimiter) so that they can be fed into B.

Ideally, after the values for A are selected by the user, X should then be
auto-populated, after which B should be populated by the database based on
the input of X. Unfortunately, Reporting Services apparently tries to
populate B before constructing X, causing an error.

Hopefully this makes sense. Is there anybody who can help with this problem?

> It is possible.
> Let's say your multivalued parameter is called MultiValuedList.
[quoted text clipped - 6 lines]
>
> Yitzhak Khabinsky

Reply to this Message

Bruce L-C [MVP] - 30 May 2006 18:06 GMT

Make sure that X shows up between A and B in the order of parameters.

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

> This works, except that I have 2+ cascading multi-value parameters, where
> the
[quoted text clipped - 27 lines]
>>
>> Yitzhak Khabinsky

Reply to this Message

Mark - 30 May 2006 18:36 GMT

Bruce,

I did have X between A and B, but I still got the error. (The error said B
was missing the parameter X). I've double-checked spelling and I didn't see
any typos. It might be that I'm misinterpreting what the problem is, but I
haven't found any evidence toward that end myself.

> Make sure that X shows up between A and B in the order of parameters.
>
[quoted text clipped - 29 lines]
> >>
> >> Yitzhak Khabinsky

Reply to this Message

Bruce L-C [MVP] - 30 May 2006 18:39 GMT

Different issue I believe. Parameters are case sensitive. Make sure you have
the parameter name correct.

Signature

Bruce Loehle-Conger
MVP SQL Server Reporting Services

> Bruce,
>
[quoted text clipped - 44 lines]
>> >>
>> >> Yitzhak Khabinsky

Reply to this Message

Amarnath - 26 May 2006 07:34 GMT

You can use =Join(Parameters!Report_Parameter_0.Value,".") or any other thing
to join.

Amarnath

> I want to make certain parameters in my report multi-valued; however, for
> some of those parameters, possible values include commas, as such:
[quoted text clipped - 11 lines]
> Thanks,
> Mark

Reply to this Message