MultiValue Parameter

Hello,

I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

Thank you,

Itzhak

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

|||

Thank you, this will solve my issue.

I put the following expression and it works well !!

=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))

Thanks,

Itzhak

multiple selection listbox as data control?

Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc.

i have the listbox as the control that is connected to the datasource (the sql used for it isselect * from cars_table where color =@.color

this works fine when one item in the listbox is selected, but when multiples are selected it does not work

what format does the=@.colorhave to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it working

does anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to work

thanks in advance!

You will have to iterate through the selections and dynamically build your SQL

foreach (ListItem itemin ListBox1.Items)
{
if(item.Selected)
 {
sql += " Or color = '" + item.Text +"'";
 }
}

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?