Multiple selection in a paramter?

I need a report (VS2005) that will select multiple records, such as:
Select * from CrimCase where Docket in (?). There is already code to
return a string of Dockets (pretty complex) so it would be easier to
just pass that along than to try to duplicate the code in SQL.
I tried setting the Report Parameter to Multi-Value but that doesn't
work. I have no idea how to pass in the parameter but this does work:
Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
When I replace the string with ? and get prompted for a parameter, it
does not work.
Any suggestions appreciated.On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
> I need a report (VS2005) that will select multiple records, such as:
> Select * from CrimCase where Docket in (?). There is already code to
> return a string of Dockets (pretty complex) so it would be easier to
> just pass that along than to try to duplicate the code in SQL.
> I tried setting the Report Parameter to Multi-Value but that doesn't
> work. I have no idea how to pass in the parameter but this does work:
> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
> When I replace the string with ? and get prompted for a parameter, it
> does not work.
> Any suggestions appreciated.
If you are referring to linking a multi-value parameter to a stored
procedure, you will want to select the Data tab >> select Edit
Selected Dataset [...] >> select the Parameters tab >> set Parameter
Name = @.Docket and set Parameter Value to an expression similar to
this: =Join(Parameters!Docket.Value, ","). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Wed, 20 Feb 2008 18:51:47 -0800 (PST), EMartinez
<emartinez.pr1@.gmail.com> wrote:
>On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
>> I need a report (VS2005) that will select multiple records, such as:
>> Select * from CrimCase where Docket in (?). There is already code to
>> return a string of Dockets (pretty complex) so it would be easier to
>> just pass that along than to try to duplicate the code in SQL.
>> I tried setting the Report Parameter to Multi-Value but that doesn't
>> work. I have no idea how to pass in the parameter but this does work:
>> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
>> When I replace the string with ? and get prompted for a parameter, it
>> does not work.
>> Any suggestions appreciated.
>
>If you are referring to linking a multi-value parameter to a stored
>procedure, you will want to select the Data tab >> select Edit
>Selected Dataset [...] >> select the Parameters tab >> set Parameter
>Name = @.Docket and set Parameter Value to an expression similar to
>this: =Join(Parameters!Docket.Value, ","). Hope this helps.
>
Not exactly, since it isn't going to a stored procedure; the SQL is
text. But, I didn't know about that parameter tab, nor that I could
put in a more complex expression. I'm not sure how to interact with
the SSRS engine. I really want the query to end up constructing OR
statements - ie, Select * from LawCases where Docket = 1112222 or
docket = 444232 or docket = 777333, extending the query depending on
the actual number of docket numbers passed in the one parameter.
I think a more acceptable way is to just create a temporary table,
putting in the cases that I want reported on, and then call SSRS,
taking all the cases in that table.
I'm intrigued by what I can do with the code though. Is it possible to
write code in the Custom Code section that will actually construct the
query on the fly, or is that code only for calling once the query has
returned and the records are being processed?|||First, it looks like you are using ODBC (hence the ? in your query). No
problem, just that when you map query parameters to report parameters it is
order dependent (i.e. the order your ? come in your query).
The following query will work for you:
Select * from LawCases where Docket in (?)
Then in layout, Report Menu-> Report Parameters set this parameter as
multi-value.
For testing, put in the appropriate values in available values. Do not put
quotes (single or double). Make sure the data type of the parameter is
string.
Put this in for available values:
Label Value
Case1 2006NY031095
Case2 2006NY024091
After you get this working then add whatever parameters you need to have
another dataset that creates this list of dockets. For instance, add your
date range and the other dataset uses the data range to return the dockets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:912rr3hcfe43pt8ri4fm580tanniajmhd3@.4ax.com...
> On Wed, 20 Feb 2008 18:51:47 -0800 (PST), EMartinez
> <emartinez.pr1@.gmail.com> wrote:
>>On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
>> I need a report (VS2005) that will select multiple records, such as:
>> Select * from CrimCase where Docket in (?). There is already code to
>> return a string of Dockets (pretty complex) so it would be easier to
>> just pass that along than to try to duplicate the code in SQL.
>> I tried setting the Report Parameter to Multi-Value but that doesn't
>> work. I have no idea how to pass in the parameter but this does work:
>> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
>> When I replace the string with ? and get prompted for a parameter, it
>> does not work.
>> Any suggestions appreciated.
>>
>>If you are referring to linking a multi-value parameter to a stored
>>procedure, you will want to select the Data tab >> select Edit
>>Selected Dataset [...] >> select the Parameters tab >> set Parameter
>>Name = @.Docket and set Parameter Value to an expression similar to
>>this: =Join(Parameters!Docket.Value, ","). Hope this helps.
> Not exactly, since it isn't going to a stored procedure; the SQL is
> text. But, I didn't know about that parameter tab, nor that I could
> put in a more complex expression. I'm not sure how to interact with
> the SSRS engine. I really want the query to end up constructing OR
> statements - ie, Select * from LawCases where Docket = 1112222 or
> docket = 444232 or docket = 777333, extending the query depending on
> the actual number of docket numbers passed in the one parameter.
> I think a more acceptable way is to just create a temporary table,
> putting in the cases that I want reported on, and then call SSRS,
> taking all the cases in that table.
> I'm intrigued by what I can do with the code though. Is it possible to
> write code in the Custom Code section that will actually construct the
> query on the fly, or is that code only for calling once the query has
> returned and the records are being processed?