Convert multivalued parameters to a string

I have read all the articles regarding this issue.
I would like to implent an "select all" function in the multivalued prompt.
The multivalued datavalues comes from a table. e.x.
select label,value from basetable
union
select 'All' as label,'%' as value
The user choice comes in the parameter @.BasePick
I have tried different SQL
1:
select id,data1,data2 from Trans t
inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
result: SQL gives an "comma error" when user pick several multivalue elements
2:
select id,data1,data2 from Trans t
where @.BasePick='%' or t.id in (@.BasePick)
result: SQL gives an "comma error" when user pick several multivalue elements
I guess that there are no way to convert an RS array to a string for use in
SQL. What is the best pracsis here.
--
Regards
Henrik JuelI presume you use RS2005.
the SP2 will provide the Select All option in a multivalue parameter.
to convert a multivalue to a string, use the Join code:
=Join(myparam.Value, ",")
returns a comma separated string.
But in your case, use 2 parameters in your query.
The first one use the BasePick multivalue parameter
the second one will use a formula (RS formula) like:
select id,data1,data2 from Trans t
where @.AllPick = '%' or t.id in (@.BasePick)
@.AllPick will receive the result of this formula:
=myparam.Value(0)
(to return only the first value from the multivalue)
"Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
>I have read all the articles regarding this issue.
> I would like to implent an "select all" function in the multivalued
> prompt.
> The multivalued datavalues comes from a table. e.x.
> select label,value from basetable
> union
> select 'All' as label,'%' as value
> The user choice comes in the parameter @.BasePick
> I have tried different SQL
> 1:
> select id,data1,data2 from Trans t
> inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> result: SQL gives an "comma error" when user pick several multivalue
> elements
> 2:
> select id,data1,data2 from Trans t
> where @.BasePick='%' or t.id in (@.BasePick)
> result: SQL gives an "comma error" when user pick several multivalue
> elements
> I guess that there are no way to convert an RS array to a string for use
> in
> SQL. What is the best pracsis here.
> --
> Regards
> Henrik Juel|||Hi all,
I having same problem with these multi value params. I am using that
"CharList" function in my SSRS 2005 report SP s.I have number of
Cascading multi value parameters in my reprots. And also these mv
params taking long time to refresh. If user selects 'All' giving an
"comma error". Can any body tell me which one is the better methodology
in performance wise. If you people solved this problem please let me
know..
Thanks,
Munny.
Jeje wrote:
> I presume you use RS2005.
> the SP2 will provide the Select All option in a multivalue parameter.
> to convert a multivalue to a string, use the Join code:
> =Join(myparam.Value, ",")
> returns a comma separated string.
> But in your case, use 2 parameters in your query.
> The first one use the BasePick multivalue parameter
> the second one will use a formula (RS formula) like:
> select id,data1,data2 from Trans t
> where @.AllPick = '%' or t.id in (@.BasePick)
> @.AllPick will receive the result of this formula:
> =myparam.Value(0)
> (to return only the first value from the multivalue)
>
> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
> >I have read all the articles regarding this issue.
> >
> > I would like to implent an "select all" function in the multivalued
> > prompt.
> > The multivalued datavalues comes from a table. e.x.
> >
> > select label,value from basetable
> > union
> > select 'All' as label,'%' as value
> >
> > The user choice comes in the parameter @.BasePick
> >
> > I have tried different SQL
> >
> > 1:
> > select id,data1,data2 from Trans t
> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > 2:
> > select id,data1,data2 from Trans t
> > where @.BasePick='%' or t.id in (@.BasePick)
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > I guess that there are no way to convert an RS array to a string for use
> > in
> > SQL. What is the best pracsis here.
> >
> > --
> > Regards
> > Henrik Juel|||Thx for a good reply.
Especially using two parameters was useful. I use an interal RS variable
@.Allpick that default is set to BasePick.value(0). Then I can use the
where @.AllPick = '%' or t.id in (@.BasePick)
Regards
Henrik Juel
"Jeje" wrote:
> I presume you use RS2005.
> the SP2 will provide the Select All option in a multivalue parameter.
> to convert a multivalue to a string, use the Join code:
> =Join(myparam.Value, ",")
> returns a comma separated string.
> But in your case, use 2 parameters in your query.
> The first one use the BasePick multivalue parameter
> the second one will use a formula (RS formula) like:
> select id,data1,data2 from Trans t
> where @.AllPick = '%' or t.id in (@.BasePick)
> @.AllPick will receive the result of this formula:
> =myparam.Value(0)
> (to return only the first value from the multivalue)
>
> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
> >I have read all the articles regarding this issue.
> >
> > I would like to implent an "select all" function in the multivalued
> > prompt.
> > The multivalued datavalues comes from a table. e.x.
> >
> > select label,value from basetable
> > union
> > select 'All' as label,'%' as value
> >
> > The user choice comes in the parameter @.BasePick
> >
> > I have tried different SQL
> >
> > 1:
> > select id,data1,data2 from Trans t
> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > 2:
> > select id,data1,data2 from Trans t
> > where @.BasePick='%' or t.id in (@.BasePick)
> >
> > result: SQL gives an "comma error" when user pick several multivalue
> > elements
> >
> > I guess that there are no way to convert an RS array to a string for use
> > in
> > SQL. What is the best pracsis here.
> >
> > --
> > Regards
> > Henrik Juel
>|||in fact, an internal variable is not required.
you can just map the @.AllPick query parameter to a formula instead-of
creating a new report parameter.
"Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
news:D42F6BDE-08C5-466F-9D79-6B9973B62E56@.microsoft.com...
> Thx for a good reply.
> Especially using two parameters was useful. I use an interal RS variable
> @.Allpick that default is set to BasePick.value(0). Then I can use the
> where @.AllPick = '%' or t.id in (@.BasePick)
>
> --
> Regards
> Henrik Juel
>
> "Jeje" wrote:
>> I presume you use RS2005.
>> the SP2 will provide the Select All option in a multivalue parameter.
>> to convert a multivalue to a string, use the Join code:
>> =Join(myparam.Value, ",")
>> returns a comma separated string.
>> But in your case, use 2 parameters in your query.
>> The first one use the BasePick multivalue parameter
>> the second one will use a formula (RS formula) like:
>> select id,data1,data2 from Trans t
>> where @.AllPick = '%' or t.id in (@.BasePick)
>> @.AllPick will receive the result of this formula:
>> =myparam.Value(0)
>> (to return only the first value from the multivalue)
>>
>> "Henrik Juel" <HenrikJuel@.newsgroup.nospam> wrote in message
>> news:3393164A-DFEC-45F9-9E1F-435C0892DA64@.microsoft.com...
>> >I have read all the articles regarding this issue.
>> >
>> > I would like to implent an "select all" function in the multivalued
>> > prompt.
>> > The multivalued datavalues comes from a table. e.x.
>> >
>> > select label,value from basetable
>> > union
>> > select 'All' as label,'%' as value
>> >
>> > The user choice comes in the parameter @.BasePick
>> >
>> > I have tried different SQL
>> >
>> > 1:
>> > select id,data1,data2 from Trans t
>> > inner join charlist_to_table(@.BasePick,Default) f on t.id = f.str
>> >
>> > result: SQL gives an "comma error" when user pick several multivalue
>> > elements
>> >
>> > 2:
>> > select id,data1,data2 from Trans t
>> > where @.BasePick='%' or t.id in (@.BasePick)
>> >
>> > result: SQL gives an "comma error" when user pick several multivalue
>> > elements
>> >
>> > I guess that there are no way to convert an RS array to a string for
>> > use
>> > in
>> > SQL. What is the best pracsis here.
>> >
>> > --
>> > Regards
>> > Henrik Juel