Dynamic Query with Multi-valued parameter

I'm having problems getting a dynamic query with a multi-valued parameter. I
have a report parameter, defined as multi-value, called deployment_id. I
want to have a dynamic query built with it (because I am eventually going to
also send in a parameter for the WHERE clause).
This works:
SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name
FROM o_dpl_communication C INNER JOIN
o_dpl_recipient R ON C.recipient_id = R.recipient_id
WHERE C.deployment_id IN (@.deployment_id)
This does not:
="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
R.last_name " &
"FROM o_dpl_communication C INNER JOIN " &
"o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
"WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
Anyone know how I can fix this dynamic query? Thanks!
StephanieRead the thread Dynamic Conditional report parameters. I show how to do this
... kindof. Because you need single quotes this is not easy (unless your
parameter list is integer, in which case it is quite easy). Anyway, read the
thread and see if it helps you.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||try to use:
Join(Parameters!deployment_id.Value, ", ")
the .value return an array, the Join keyword convert the array into a string
with a comma as a separator
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> I'm having problems getting a dynamic query with a multi-valued parameter.
> I
> have a report parameter, defined as multi-value, called deployment_id. I
> want to have a dynamic query built with it (because I am eventually going
> to
> also send in a parameter for the WHERE clause).
> This works:
> SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name
> FROM o_dpl_communication C INNER JOIN
> o_dpl_recipient R ON C.recipient_id = R.recipient_id
> WHERE C.deployment_id IN (@.deployment_id)
> This does not:
> ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> R.last_name " &
> "FROM o_dpl_communication C INNER JOIN " &
> "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> Anyone know how I can fix this dynamic query? Thanks!
> Stephanie|||First of all, are you imbedding code directly in the report? that is not
good, since you have to redeploy the whole report should the code need to be
changed. Make a stored procedure instead and pass the list in as a
varchar(255).
Once inside the sproc, all you have to do is parse through the list in a
while loop and create output into a temp table that you can then filter or
output any way you want.
this is way more efficent on the sql server, and will make life easier in
the long run!
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||That worked perfectly, thanks very much!
Stephanie
"Jeje" wrote:
> try to use:
> Join(Parameters!deployment_id.Value, ", ")
> the .value return an array, the Join keyword convert the array into a string
> with a comma as a separator
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>|||Carl,
Thanks for the reply. I knew I could use a stored proc but I needed this to
work on multiple databases. I'm sending in a report parameter of the
database name that I want to report on and I don't want the stored proc to be
in each of the databases.
Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
Stephanie
"Carl Henthorn" wrote:
> First of all, are you imbedding code directly in the report? that is not
> good, since you have to redeploy the whole report should the code need to be
> changed. Make a stored procedure instead and pass the list in as a
> varchar(255).
> Once inside the sproc, all you have to do is parse through the list in a
> while loop and create output into a temp table that you can then filter or
> output any way you want.
> this is way more efficent on the sql server, and will make life easier in
> the long run!
> "Jeje" wrote:
> > try to use:
> > Join(Parameters!deployment_id.Value, ", ")
> >
> > the .value return an array, the Join keyword convert the array into a string
> > with a comma as a separator
> >
> >
> > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > I
> > > have a report parameter, defined as multi-value, called deployment_id. I
> > > want to have a dynamic query built with it (because I am eventually going
> > > to
> > > also send in a parameter for the WHERE clause).
> > >
> > > This works:
> > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name
> > > FROM o_dpl_communication C INNER JOIN
> > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > WHERE C.deployment_id IN (@.deployment_id)
> > >
> > > This does not:
> > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > R.last_name " &
> > > "FROM o_dpl_communication C INNER JOIN " &
> > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > >
> > > Anyone know how I can fix this dynamic query? Thanks!
> > >
> > > Stephanie
> >
> >|||Thanks, Bruce. That's just what I was looking for. And I appreciated seeing
the additional information on the parameters, ie. Value(0), Count, etc.
Stephanie
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||thats cool. In that particular case, i would place the dynamic sql inside the
loop and passed in the value. I am glad you have a working solution! :-)
"Stephanie" wrote:
> Carl,
> Thanks for the reply. I knew I could use a stored proc but I needed this to
> work on multiple databases. I'm sending in a report parameter of the
> database name that I want to report on and I don't want the stored proc to be
> in each of the databases.
> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
> Stephanie
>
> "Carl Henthorn" wrote:
> > First of all, are you imbedding code directly in the report? that is not
> > good, since you have to redeploy the whole report should the code need to be
> > changed. Make a stored procedure instead and pass the list in as a
> > varchar(255).
> > Once inside the sproc, all you have to do is parse through the list in a
> > while loop and create output into a temp table that you can then filter or
> > output any way you want.
> > this is way more efficent on the sql server, and will make life easier in
> > the long run!
> >
> > "Jeje" wrote:
> >
> > > try to use:
> > > Join(Parameters!deployment_id.Value, ", ")
> > >
> > > the .value return an array, the Join keyword convert the array into a string
> > > with a comma as a separator
> > >
> > >
> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > > > I'm having problems getting a dynamic query with a multi-valued parameter.
> > > > I
> > > > have a report parameter, defined as multi-value, called deployment_id. I
> > > > want to have a dynamic query built with it (because I am eventually going
> > > > to
> > > > also send in a parameter for the WHERE clause).
> > > >
> > > > This works:
> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name
> > > > FROM o_dpl_communication C INNER JOIN
> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > > > WHERE C.deployment_id IN (@.deployment_id)
> > > >
> > > > This does not:
> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > > > R.last_name " &
> > > > "FROM o_dpl_communication C INNER JOIN " &
> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> > > >
> > > > Anyone know how I can fix this dynamic query? Thanks!
> > > >
> > > > Stephanie
> > >
> > >|||I'm pretty sure it only works because the data type of the field is integer.
If it had been string it would not have worked.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
> thats cool. In that particular case, i would place the dynamic sql inside
> the
> loop and passed in the value. I am glad you have a working solution! :-)
> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed this
>> to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then filter
>> > or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into a
>> > > string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am eventually
>> > > > going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >|||sure
in case of an array of string the code is different
something like:
"'" & Join(Parameters!deployment_id.Value, "', '") & "'"
single quote ' added in the join clause + single quote ' added before and
after the Join clause produce:
'toto', 'tata', 'tutu'
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
> I'm pretty sure it only works because the data type of the field is
> integer. If it had been string it would not have worked.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql inside
>> the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code need
>> > to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list in
>> > a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life easier
>> > in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>|||Good point.
Bruce
"Jeje" <willgart@.hotmail.com> wrote in message
news:4ED05EA4-A55B-480A-BFBB-6B863080B026@.microsoft.com...
> sure
> in case of an array of string the code is different
> something like:
> "'" & Join(Parameters!deployment_id.Value, "', '") & "'"
> single quote ' added in the join clause + single quote ' added before and
> after the Join clause produce:
> 'toto', 'tata', 'tutu'
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OR2lzWlkHHA.5024@.TK2MSFTNGP06.phx.gbl...
>> I'm pretty sure it only works because the data type of the field is
>> integer. If it had been string it would not have worked.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:F370A919-A103-499B-8C25-312F9CD42BB7@.microsoft.com...
>> thats cool. In that particular case, i would place the dynamic sql
>> inside the
>> loop and passed in the value. I am glad you have a working solution! :-)
>> "Stephanie" wrote:
>> Carl,
>> Thanks for the reply. I knew I could use a stored proc but I needed
>> this to
>> work on multiple databases. I'm sending in a report parameter of the
>> database name that I want to report on and I don't want the stored proc
>> to be
>> in each of the databases.
>> Anyway, Jeje's answer worked perfectly. Again, thanks for the time.
>> Stephanie
>>
>> "Carl Henthorn" wrote:
>> > First of all, are you imbedding code directly in the report? that is
>> > not
>> > good, since you have to redeploy the whole report should the code
>> > need to be
>> > changed. Make a stored procedure instead and pass the list in as a
>> > varchar(255).
>> > Once inside the sproc, all you have to do is parse through the list
>> > in a
>> > while loop and create output into a temp table that you can then
>> > filter or
>> > output any way you want.
>> > this is way more efficent on the sql server, and will make life
>> > easier in
>> > the long run!
>> >
>> > "Jeje" wrote:
>> >
>> > > try to use:
>> > > Join(Parameters!deployment_id.Value, ", ")
>> > >
>> > > the .value return an array, the Join keyword convert the array into
>> > > a string
>> > > with a comma as a separator
>> > >
>> > >
>> > > "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> > > news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > > > I'm having problems getting a dynamic query with a multi-valued
>> > > > parameter.
>> > > > I
>> > > > have a report parameter, defined as multi-value, called
>> > > > deployment_id. I
>> > > > want to have a dynamic query built with it (because I am
>> > > > eventually going
>> > > > to
>> > > > also send in a parameter for the WHERE clause).
>> > > >
>> > > > This works:
>> > > > SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name
>> > > > FROM o_dpl_communication C INNER JOIN
>> > > > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > > > WHERE C.deployment_id IN (@.deployment_id)
>> > > >
>> > > > This does not:
>> > > > ="SELECT C.recipient_id, R.customer_id, R.delivery_email,
>> > > > R.first_name,
>> > > > R.last_name " &
>> > > > "FROM o_dpl_communication C INNER JOIN " &
>> > > > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > > > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value &
>> > > > ")"
>> > > >
>> > > > Anyone know how I can fix this dynamic query? Thanks!
>> > > >
>> > > > Stephanie
>> > >
>> > >
>>
>|||Bruce, cannot locate the thread, believe that info will be very helpful to me.
"Bruce L-C [MVP]" wrote:
> Read the thread Dynamic Conditional report parameters. I show how to do this
> ... kindof. Because you need single quotes this is not easy (unless your
> parameter list is integer, in which case it is quite easy). Anyway, read the
> thread and see if it helps you.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
> > I'm having problems getting a dynamic query with a multi-valued parameter.
> > I
> > have a report parameter, defined as multi-value, called deployment_id. I
> > want to have a dynamic query built with it (because I am eventually going
> > to
> > also send in a parameter for the WHERE clause).
> >
> > This works:
> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name
> > FROM o_dpl_communication C INNER JOIN
> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
> > WHERE C.deployment_id IN (@.deployment_id)
> >
> > This does not:
> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
> > R.last_name " &
> > "FROM o_dpl_communication C INNER JOIN " &
> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
> >
> > Anyone know how I can fix this dynamic query? Thanks!
> >
> > Stephanie
>
>|||Go to Google, click on groups from the down arrow so you search groups then
search on this:
Dynamic Conditional report parameters
You will see it then.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Z-Will" <Z-Will@.discussions.microsoft.com> wrote in message
news:3EFFC67E-1254-4B64-90C7-264C3C43C36B@.microsoft.com...
> Bruce, cannot locate the thread, believe that info will be very helpful to
> me.
> "Bruce L-C [MVP]" wrote:
>> Read the thread Dynamic Conditional report parameters. I show how to do
>> this
>> ... kindof. Because you need single quotes this is not easy (unless your
>> parameter list is integer, in which case it is quite easy). Anyway, read
>> the
>> thread and see if it helps you.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
>> news:06596D25-9887-48F1-B512-CC5CDDF1E0FE@.microsoft.com...
>> > I'm having problems getting a dynamic query with a multi-valued
>> > parameter.
>> > I
>> > have a report parameter, defined as multi-value, called deployment_id.
>> > I
>> > want to have a dynamic query built with it (because I am eventually
>> > going
>> > to
>> > also send in a parameter for the WHERE clause).
>> >
>> > This works:
>> > SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name
>> > FROM o_dpl_communication C INNER JOIN
>> > o_dpl_recipient R ON C.recipient_id = R.recipient_id
>> > WHERE C.deployment_id IN (@.deployment_id)
>> >
>> > This does not:
>> > ="SELECT C.recipient_id, R.customer_id, R.delivery_email, R.first_name,
>> > R.last_name " &
>> > "FROM o_dpl_communication C INNER JOIN " &
>> > "o_dpl_recipient R ON C.recipient_id = R.recipient_id " &
>> > "WHERE C.deployment_id IN (" & Parameters!deployment_id.Value & ")"
>> >
>> > Anyone know how I can fix this dynamic query? Thanks!
>> >
>> > Stephanie
>>