function to return multi-selected values?

Is there a function to return the multi-selected parm values in a comma
delimited string? Using RS2005. dataset1 is the source of a parameter I'll
call STORE. I have this defined as a multi-select parm. Getting error
(cannot add multi value query parameter 'STORE' for dataset 'dataset2'
because it is not supported by the data extension) on trying to select parms
that are needed for my next dataset query unless I map the parameter for
dataset2 to an expression like this STORE = Parameters!STORE.Value(0) + "," +
Parameters!STORE.Value(1)
I cant do that in reality though - I was just seeing if it would work. I
need a function that I can map the parameter to which will feed to dataset2
all the STORE values chosen (similar to above). Why does it say that it is
not supported when I can type in comma delimited values in that parameter
field and run the report? Thanks in advanceIf I understand you have this...
Parameter 1, STORE, multi-select. Source is from query (dataset1) -
something like SELECT STORE_NAME FROM STORE.
dataset2 feeds the data in your report. It has to have a where clause
based on the values selected from the STORE parameter.
You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
are using a supported database: SQL Server or... i think... Oracle).
If you are using another dbms (we use sybase for some of our reports,
and we get that error) you have to be creative...
In that case, for dataset2, I have done the following.
="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
Join(Parameters!STORE.Value, "','") & "'"
There are single quotes in there to put them around each value in STORE
(assuming it is a string). If they are integers, you can lose the
single quotes.
Good luck,
Regards,
Dan|||Thanks Dan,
Actually I am using db2 version 8.1 (database lives on a unix box). You
understood correctly that I have dataset1 which provides the selections for
the parm STORE. The parm is then mapped to a query parm that is needed for
dataset2 which is a stored procedure (no sql to manipulate there). Any ideas
there? Again ... thanks
"Dan" wrote:
> If I understand you have this...
> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> something like SELECT STORE_NAME FROM STORE.
> dataset2 feeds the data in your report. It has to have a where clause
> based on the values selected from the STORE parameter.
> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> are using a supported database: SQL Server or... i think... Oracle).
> If you are using another dbms (we use sybase for some of our reports,
> and we get that error) you have to be creative...
> In that case, for dataset2, I have done the following.
> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> Join(Parameters!STORE.Value, "','") & "'"
> There are single quotes in there to put them around each value in STORE
> (assuming it is a string). If they are integers, you can lose the
> single quotes.
> Good luck,
> Regards,
> Dan
>|||When you pass a multi-select parameter to a stored procedure you are sending
a comma separated string of values. Why your stored procedure is having
trouble with it I don't know.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> Thanks Dan,
> Actually I am using db2 version 8.1 (database lives on a unix box). You
> understood correctly that I have dataset1 which provides the selections
> for
> the parm STORE. The parm is then mapped to a query parm that is needed
> for
> dataset2 which is a stored procedure (no sql to manipulate there). Any
> ideas
> there? Again ... thanks
> "Dan" wrote:
>> If I understand you have this...
>> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
>> something like SELECT STORE_NAME FROM STORE.
>> dataset2 feeds the data in your report. It has to have a where clause
>> based on the values selected from the STORE parameter.
>> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
>> are using a supported database: SQL Server or... i think... Oracle).
>> If you are using another dbms (we use sybase for some of our reports,
>> and we get that error) you have to be creative...
>> In that case, for dataset2, I have done the following.
>> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
>> Join(Parameters!STORE.Value, "','") & "'"
>> There are single quotes in there to put them around each value in STORE
>> (assuming it is a string). If they are integers, you can lose the
>> single quotes.
>> Good luck,
>> Regards,
>> Dan
>>|||I dont know either. If I take the multi-value off of that parm and type in 2
comma separated values then it works just fine. Something about the
multi-value it doesnt like or I am setting up wrong. This is db2 v8.1 ...
the stored proc works to accept comma-separated values when typed in ... just
not when selected as multi-value ... I cant see what I would be doing wrong?
"Bruce L-C [MVP]" wrote:
> When you pass a multi-select parameter to a stored procedure you are sending
> a comma separated string of values. Why your stored procedure is having
> trouble with it I don't know.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> > Thanks Dan,
> > Actually I am using db2 version 8.1 (database lives on a unix box). You
> > understood correctly that I have dataset1 which provides the selections
> > for
> > the parm STORE. The parm is then mapped to a query parm that is needed
> > for
> > dataset2 which is a stored procedure (no sql to manipulate there). Any
> > ideas
> > there? Again ... thanks
> >
> > "Dan" wrote:
> >
> >> If I understand you have this...
> >>
> >> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> >> something like SELECT STORE_NAME FROM STORE.
> >>
> >> dataset2 feeds the data in your report. It has to have a where clause
> >> based on the values selected from the STORE parameter.
> >>
> >> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> >> are using a supported database: SQL Server or... i think... Oracle).
> >>
> >> If you are using another dbms (we use sybase for some of our reports,
> >> and we get that error) you have to be creative...
> >>
> >> In that case, for dataset2, I have done the following.
> >>
> >> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> >> Join(Parameters!STORE.Value, "','") & "'"
> >>
> >> There are single quotes in there to put them around each value in STORE
> >> (assuming it is a string). If they are integers, you can lose the
> >> single quotes.
> >>
> >> Good luck,
> >>
> >> Regards,
> >>
> >> Dan
> >>
> >>
>
>|||Bruce may be better versed in some of this than I am, but from what I
understand, a comma separated list is only sent through to the dbms
through a data extension that supports multi-value parameters. I don't
believe db2/odbc does.
What does the text from your dataset 2 look like?
If you changed it to something like the following, it should work.
="exec storedprocname '" & Join(Parameters!STORE.Value, "','") & "'"