Multi Value Parameters

Hi,

Can anyone tell me how to pass a data for a multivalue parameter through the URL.

Thanks In Advance

Regards

Raja Annamalai S

To set a parameter named "Name1" to value1 and value 2, use this format:

http://localhost/reportserver?/MyReport&Name1=value1&Name1=value2

|||Thank you very much.

Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi Value Parameters

I have an issue using a Muiti Value Parameter. Upto a point it works
quite well.
The parameter is based on a field within the report & so when I preview
I get a list of values, and can select all, or some records. Thats the
bit that works.
However when I run the report it returns data for for 1 value only. I
have made sure that the filter on my report uses 'in' instead of '='.
One thing I would rater do thatn use the report filter is pass the
parameter directly to my SQL statement, does anyone know how this can
be done?
Thanks
PaddySo you're using a filter on the dataset (retrieve all rows first). This is
not very efficient, especially if you're dealing with tables with millions
of rows...
To do it on the data source side:
If your data source is SQL Server 2005, you can simply write your TSQL as
"...and myTable.myField IN (@.myParameter)"
If your data source is SQL Server 2000 or something else you could do either
of the following:
stored proc, pass the parameter to the stored proc as
=join(Parameters!myParameter.Value, ",")
Then write some code that splits your values out by "," and writes them to
an in-memory join table. Typically in the past, we've used a UDF to return
a table as well.
For a "straight query" (IOW TSQL, PLSQL, etc), as long as the data source
supports the IN clause, you could write dynamic SQL in the form:
="select ..... where myTable.myValue IN (" &
join(Parameters!myParameter.Value, ",") & ")"
If you go the dynamic sql path, write the query out first normally so you
don't have to fill in the field list, then change it to dynamic.
Clear as mud?
-Tim
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:1152180850.976447.110760@.75g2000cwc.googlegroups.com...
>I have an issue using a Muiti Value Parameter. Upto a point it works
> quite well.
> The parameter is based on a field within the report & so when I preview
> I get a list of values, and can select all, or some records. Thats the
> bit that works.
> However when I run the report it returns data for for 1 value only. I
> have made sure that the filter on my report uses 'in' instead of '='.
> One thing I would rater do thatn use the report filter is pass the
> parameter directly to my SQL statement, does anyone know how this can
> be done?
> Thanks
> Paddy
>

Multi- value parameter!

Hi every body!
I have two reports ,in first i have one multi value parameter .in
first report i select more than one value ,then in second report i
want to have these values becuse i have another multi value
parameter ,when i join these reports via navigation
in parameters (in first report) i put the values of multi value
parameter in fisrt report for value of multi value parameter in second
report,but in this case fore example i have it:
in first reprt-> navigation->parameters->
ParameterName ParameterVaue
EmployeeID =Parameters!EmployeeID.Value(0)
but in second report i need all of my selections in first report not
just first value
Can anybody help me?On Nov 4, 4:35 am, SHIMAR...@.gmail.com wrote:
> Hi every body!
> I have two reports ,in first i have one multi value parameter .in
> first report i select more than one value ,then in second report i
> want to have these values becuse i have another multi value
> parameter ,when i join these reports via navigation
> in parameters (in first report) i put the values of multi value
> parameter in fisrt report for value of multi value parameter in second
> report,but in this case fore example i have it:
> in first reprt-> navigation->parameters->
> ParameterName ParameterVaue
> EmployeeID =Parameters!EmployeeID.Value(0)
> but in second report i need all of my selections in first report not
> just first value
> Can anybody help me?
If I'm understanding you correctly, you should be able to use an
expression similar to the following to obtain the multi-select
parameter's values from the main report.
=Join(Parameters!EmployeeID.Value,",")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Multi value parameter use in "IN" expression.

Hi
While writing my latest report i have been trying to use the result of a
multi value parameter as the value for a "IN" expression in my SQL query,
this does not seem to work, the multi value parameter shows that it has
values in it but the "IN" expression wont use them to filter the SQL query.
I have since found that the way i am using the Parameter in the SQL is
correct but i am now unsure of how the parameter should be set up.
Can anyone help?
Steve D'IN' should have multiple values seperated with a comma. I hope you know
that. So you need to use =join() to build the multi value parameters into
something like this (1,2,3,4) then pass this value into the query it should
work.
Amarnath
"Steve Dearman" wrote:
> Hi
> While writing my latest report i have been trying to use the result of a
> multi value parameter as the value for a "IN" expression in my SQL query,
> this does not seem to work, the multi value parameter shows that it has
> values in it but the "IN" expression wont use them to filter the SQL query.
> I have since found that the way i am using the Parameter in the SQL is
> correct but i am now unsure of how the parameter should be set up.
> Can anyone help?
> Steve D
>
>|||When I use a multi-value parameter I just do this:
select somefield from sometable where anotherfield in (@.Param)
Where @.Param maps to a multi-value parameter. There is no need for anything
fancy unless passing the parameter to a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:56A2A663-692A-460A-8D64-4FD1F1B0B80C@.microsoft.com...
> 'IN' should have multiple values seperated with a comma. I hope you know
> that. So you need to use =join() to build the multi value parameters into
> something like this (1,2,3,4) then pass this value into the query it
> should
> work.
> Amarnath
>
> "Steve Dearman" wrote:
>> Hi
>> While writing my latest report i have been trying to use the result of a
>> multi value parameter as the value for a "IN" expression in my SQL query,
>> this does not seem to work, the multi value parameter shows that it has
>> values in it but the "IN" expression wont use them to filter the SQL
>> query.
>> I have since found that the way i am using the Parameter in the SQL is
>> correct but i am now unsure of how the parameter should be set up.
>> Can anyone help?
>> Steve D
>>

Multi Value Parameter Passing in OLEDB or ODBC

Hi Robert
We are integrating Sql server 2005 with sybase through ODBC.We are using
named parameters in the query...query which will fetch data from sybase
database.Now named parameters are those parameters which we specify with
'@.'.i.e. @.emp_id.
As we are conneting sybase from sql server 2005 through ODBC...but ODBC
doesnt support named parameter...so we are using unnamed parameters (?)
that is for e.g.
Select emp_id,emp_name from t_employee where emp_id = ?
but this is single value unnamed paramater...do u have idea how to pass
multivalue unnamed parameter which i can use with "In" clause..
Select emp_id,emp_name from t_employee where emp_id in ()
if i use ? along with "In" clause...it throws an error saying "Multivalue
Parameter is not supported in Data Extension"
Based on your last thread , we try to get something regarding creation of Custom Data Extension, but unfortunately we are not able to find much more on this particular topic.
We are able to find some readymain Custom Data Extension code from following link
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
but unfortunately we are not able to find more from that link also.
Can u plz help us regarding this, as we are passing through very crucial problem.
We will really very glad, if you can help us to solve this problem.
Thanks in advance :).

Take your sql and replace the ? with a valid emp_id and then run the query:

Select emp_id,emp_name from t_employee where emp_id = 2056

Goto report parameters and delete all of the query parameters the sql was using. Then create a report parameter named EmpID. Set in to multivalue. You then can change your sql to the following:

="Select emp_id,emp_name from t_employee where emp_id in (" & Parameters!EmpID.Value & ")"

You have to follow the process as described. Also, if EmpId is a string then you need to surround your values with single quotes. ie. When you select your drop down to choose the EmpID's to run for you should see" '20034' and '23456' etc. This will then pass a comma delimited string value to the SQL.

Multi value parameter not working

I have installed MS sql server 2005 developer edition beta2.
In report designer,i have given a parameter to a multi value and when see in
preview i am able to see the multi valued parameter.
but when i run it and see it in reportserver,even though i select multi
values,while displaying the results its taking only the last value selected.
please help me in this regard.I think that with Beta 2 there were some problems with multi-value. It is
working in the release version. If you have MSDN you can download it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sirisha" <sirisha@.discussions.microsoft.com> wrote in message
news:D7D10346-8D31-4810-A287-AD6D1D41030B@.microsoft.com...
>I have installed MS sql server 2005 developer edition beta2.
> In report designer,i have given a parameter to a multi value and when see
> in
> preview i am able to see the multi valued parameter.
> but when i run it and see it in reportserver,even though i select multi
> values,while displaying the results its taking only the last value
> selected.
> please help me in this regard.
>sql

Multi value parameter issue?

How do I make a mvp optional? I don't want to always have to select a
value for it.
Thanks in advanceAs far as I can see, you just can't make it optional.
An alternative to your users could be to (select all). That shouldn't be
that much of a problem, as I assume your report stored procedure already
accepts multiple values.
"gte401e" wrote:
> How do I make a mvp optional? I don't want to always have to select a
> value for it.
> Thanks in advance
>

Multi value parameter in db2 query

hi,

i'm trying to perform a query against a db2 database like this:

SELECT ... FROM ... WHERE (field IN (?))

Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:

An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension

But when i type the query like this

SELECT ... FROM ... WHERE (field IN ('value1','value2'))

it executes flawlessly.

I am using the IBM ole db driver for db2 if that matters

Can anyone help me?

IBM's Ole DB driver only supports parameterized queries with single valued parameters. See e.g. this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81223&SiteID=1

SSRS 2005 contains specific functionality to perform query rewrite for multi-valued query parameters in the cases of SQL Server, Oracle 9 or later, and SSAS / XML/A-based data sources.

For all other cases you would need a custom data extension implementation to provide the query rewrite functionality.

-- Robert

Multi value parameter help needed

Hi
I have a problem my database table has my parameter value
UserId integer
I need to set up my report to accept more than one id at a time (ex.
2, 44, 5). I went through the report parameters in reporting services
and set the parameter to accept multi value. When I tried to run the
report I was allowed to put in multi values with no problem, but it is
returning an error saying that it cannot convert data type nvarchar to
int. Any ideas?
thanks for the help
twoI'm guessing that your multi-value parameter has a lable and a value.
You probably set the parameter up wrong in Report Parameters. You
have a select with (label, value) pairs (apple, 2), (orange, 44),
(peach, 5). When you select apple, orange and peach you are expecting
2, 44, and 5, but you have probably set it up to pass in apple,
orange, peach. Which leads to the conversion problem you are seeing.
Are the options coming from a datasource or did you just type them
into the report parameters section?|||On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> I'm guessing that your multi-value parameter has a lable and a value.
> You probably set the parameter up wrong in Report Parameters. You
> have a select with (label, value) pairs (apple, 2), (orange, 44),
> (peach, 5). When you select apple, orange and peach you are expecting
> 2, 44, and 5, but you have probably set it up to pass in apple,
> orange, peach. Which leads to the conversion problem you are seeing.
> Are the options coming from a datasource or did you just type them
> into the report parameters section?
I am using a stored procedure that requires only one paramter which is
the id (int). The options for the parameters are not coming from a
datasource, Its pulling from my datasource that id (int) is a
parameter. So there is no label since the available values section is
set to non-queried.|||On Oct 5, 10:10 am, Twobridge <Twobri...@.gmail.com> wrote:
> On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> > I'm guessing that your multi-value parameter has a lable and a value.
> > You probably set the parameter up wrong in Report Parameters. You
> > have a select with (label, value) pairs (apple, 2), (orange, 44),
> > (peach, 5). When you select apple, orange and peach you are expecting
> > 2, 44, and 5, but you have probably set it up to pass in apple,
> > orange, peach. Which leads to the conversion problem you are seeing.
> > Are the options coming from a datasource or did you just type them
> > into the report parameters section?
> I am using a stored procedure that requires only one paramter which is
> the id (int). The options for the parameters are not coming from a
> datasource, Its pulling from my datasource that id (int) is a
> parameter. So there is no label since the available values section is
> set to non-queried.
I am thinking my problem isnt the parameter setting but my stored
procedure on the sqlserver. It is set up to accept a integer as it
parameter not an array of int. And to be honest i have no idea how to
set a stored procedure up to accept an array of integers.|||I have never used multi-select as a textbox that I put values in. I always
put in the values to select from, either a manual list I put in for the
parameter or a query. To make sure multi-select is working for you put in a
list of parameter values. This will make sure the issue is not your textbox
input.
The second issue I see is that you are trying to pass an multi-select list
to a stored procedure of type int. What the multi-select does is allow you
to multi-select from an option list. If you put in 1,12,34 into a listbox
this is by definition a string, it is not integers.
OK, now on to your stored procedure. There is absolutely no way to pass
multiple integers into a singe integer parameter. You are trying to get RS
to do something impossible. A way to look at this, if you cannot do it from
SQL Management Studio where you invoke the SP yourself, then there is no way
RS can do it either.
Also, for another problem what you want cannot be done without special code
in a stored procedure. If you have a query this would work:
select * from sometable where somefield in (@.id)
In this case the data type of id can be int.
Now with your stored procedure with a parameter of type int called id, it
only accepts a single integer.
Below is a post of mine from previous that describes the issue with
multi-parameters and stored procedures. What the problem is and how to get
around it:
>>>>>
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Twobridge" <Twobridge@.gmail.com> wrote in message
news:1191597037.153027.72250@.w3g2000hsg.googlegroups.com...
> On Oct 5, 9:30 am, jer...@.gmail.com wrote:
>> I'm guessing that your multi-value parameter has a lable and a value.
>> You probably set the parameter up wrong in Report Parameters. You
>> have a select with (label, value) pairs (apple, 2), (orange, 44),
>> (peach, 5). When you select apple, orange and peach you are expecting
>> 2, 44, and 5, but you have probably set it up to pass in apple,
>> orange, peach. Which leads to the conversion problem you are seeing.
>> Are the options coming from a datasource or did you just type them
>> into the report parameters section?
> I am using a stored procedure that requires only one paramter which is
> the id (int). The options for the parameters are not coming from a
> datasource, Its pulling from my datasource that id (int) is a
> parameter. So there is no label since the available values section is
> set to non-queried.
>|||On Oct 5, 10:41 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never used multi-select as a textbox that I put values in. I always
> put in the values to select from, either a manual list I put in for the
> parameter or a query. To make sure multi-select is working for you put in a
> list of parameter values. This will make sure the issue is not your textbox
> input.
> The second issue I see is that you are trying to pass an multi-select list
> to a stored procedure of type int. What the multi-select does is allow you
> to multi-select from an option list. If you put in 1,12,34 into a listbox
> this is by definition a string, it is not integers.
> OK, now on to your stored procedure. There is absolutely no way to pass
> multiple integers into a singe integer parameter. You are trying to get RS
> to do something impossible. A way to look at this, if you cannot do it from
> SQL Management Studio where you invoke the SP yourself, then there is no way
> RS can do it either.
> Also, for another problem what you want cannot be done without special code
> in a stored procedure. If you have a query this would work:
> select * from sometable where somefield in (@.id)
> In this case the data type of id can be int.
> Now with your stored procedure with a parameter of type int called id, it
> only accepts a single integer.
> Below is a post of mine from previous that describes the issue with
> multi-parameters and stored procedures. What the problem is and how to get
> around it:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Twobridge" <Twobri...@.gmail.com> wrote in message
> news:1191597037.153027.72250@.w3g2000hsg.googlegroups.com...
>
> > On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> >> I'm guessing that your multi-value parameter has a lable and a value.
> >> You probably set the parameter up wrong in Report Parameters. You
> >> have a select with (label, value) pairs (apple, 2), (orange, 44),
> >> (peach, 5). When you select apple, orange and peach you are expecting
> >> 2, 44, and 5, but you have probably set it up to pass in apple,
> >> orange, peach. Which leads to the conversion problem you are seeing.
> >> Are the options coming from a datasource or did you just type them
> >> into the report parameters section?
> > I am using a stored procedure that requires only one paramter which is
> > the id (int). The options for the parameters are not coming from a
> > datasource, Its pulling from my datasource that id (int) is a
> > parameter. So there is no label since the available values section is
> > set to non-queried.- Hide quoted text -
> - Show quoted text -
Thanks everyone who helped me out. Bruce you were correct that it was
more to do with my stored procedure that RS. I found a really good
example of a function that basically took in my string of numbers and
was able to break up the string into my needed id's . Here is a link
if anyone needs it.
http://www.sommarskog.se/arrays-in-sql-2005.html

Multi Value Parameter and Filtering

Hi All
I am trying to filter a table on my report based on a multi-value parameter,
I have tried several methods to no avail. When I use the following I do not
get an error but I do not get data showing on the report.
Expression is set to =Fields!Account.Value
Operator is set to In
Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
Can anyone help this newbie style question
RegardsI think you should be able to leave out the Join function.
Set the value as: =Parameters!Select_Individual_Account.Value
I do that in one of my reports and it works fine. Just make sure there is
not a (0) at the end of the paramerter.
"Are friends electric?" wrote:
> Hi All
> I am trying to filter a table on my report based on a multi-value parameter,
> I have tried several methods to no avail. When I use the following I do not
> get an error but I do not get data showing on the report.
> Expression is set to =Fields!Account.Value
> Operator is set to In
> Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> Can anyone help this newbie style question
> Regards
>
>|||Thanks for helping Matt
I now get the following error
The filter expression for the table cannot be performed,
cannot compare data of type system.string and system.object
any ideas
Thanks
Steve
"Matt M" wrote:
> I think you should be able to leave out the Join function.
> Set the value as: =Parameters!Select_Individual_Account.Value
> I do that in one of my reports and it works fine. Just make sure there is
> not a (0) at the end of the paramerter.
> "Are friends electric?" wrote:
> > Hi All
> >
> > I am trying to filter a table on my report based on a multi-value parameter,
> >
> > I have tried several methods to no avail. When I use the following I do not
> > get an error but I do not get data showing on the report.
> >
> > Expression is set to =Fields!Account.Value
> > Operator is set to In
> > Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> >
> > Can anyone help this newbie style question
> >
> > Regards
> >
> >
> >
> >

Multi value Parameter

Hi,

In my report i have a multi valued parameter, when i view my report in the Web application with Report Viewer, The multi valued parameter is displaying in light color.Can we able to change this.

Thanks in advance

Mahima,

You can go to your Reporting Service\ReportManager\Style directory, this is were your "CSS" reporting service stylesheets are located. I believe the file you looking for is RSWebParts.css

Ham

|||

Hi,

Thanks for the reply.Can we able to change the width and Height of the multi valued parameter.Now it is of fixed size and displaying the horizantal and vertical scroll bars.

Thanks

multi value parameter

hi,

i read from this forum that to pass array to report is using multi value parameter . my problem now is

1. can i pass multi dimension array.

2. how do i show the multi value parameter in a table . for example i have a multi value parameter that may contain 10 or 20 array . how do i dynamic it show in table.

rgds,

charles

One way to do this is to use separate parameters for separate dimensions of the array. Would that work for you?

Another possibility might be to put the multiple dimensions of each row of the array into a delimited string and parse out the values for display (use the Split function in your expressions).

As far as how you should them in a table... you can probably kludge this too...

But if you want to show them in a table, why not just create a dataset from the array instead of trying to pass a param? ?

Maybe I'm missing your problem...

multi value parameter

I've been reading the forums regarding this but don't seem to understand
exactly how this is supposed to work. I have parameters list build from
query. Report Parameter is set to multi value. The SP that the report uses
has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
report and choose 'select all' I get no results though if I select just one,
it works fine. Selecting more than one also returns no result.
What can I possibly be doing wrong?SQL 2005, SP2
"brian" wrote:
> I've been reading the forums regarding this but don't seem to understand
> exactly how this is supposed to work. I have parameters list build from
> query. Report Parameter is set to multi value. The SP that the report uses
> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
> report and choose 'select all' I get no results though if I select just one,
> it works fine. Selecting more than one also returns no result.
> What can I possibly be doing wrong?|||You answered yourself but unfortunately you answered incorrectly. Here is my
stock answer for this:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
One other option is to create dynamic SQL in your stored procedure as well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"brian" <brian@.discussions.microsoft.com> wrote in message
news:F270F0D4-6551-444A-9711-E5B0FE938063@.microsoft.com...
> SQL 2005, SP2
> "brian" wrote:
>> I've been reading the forums regarding this but don't seem to understand
>> exactly how this is supposed to work. I have parameters list build from
>> query. Report Parameter is set to multi value. The SP that the report
>> uses
>> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
>> report and choose 'select all' I get no results though if I select just
>> one,
>> it works fine. Selecting more than one also returns no result.
>> What can I possibly be doing wrong?|||Way over my head; I have more reading to do. Thanks Bruce.
"Bruce L-C [MVP]" wrote:
> You answered yourself but unfortunately you answered incorrectly. Here is my
> stock answer for this:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> One other option is to create dynamic SQL in your stored procedure as well.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:F270F0D4-6551-444A-9711-E5B0FE938063@.microsoft.com...
> > SQL 2005, SP2
> >
> > "brian" wrote:
> >
> >> I've been reading the forums regarding this but don't seem to understand
> >> exactly how this is supposed to work. I have parameters list build from
> >> query. Report Parameter is set to multi value. The SP that the report
> >> uses
> >> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
> >> report and choose 'select all' I get no results though if I select just
> >> one,
> >> it works fine. Selecting more than one also returns no result.
> >>
> >> What can I possibly be doing wrong?
>
>

Multi value parameter

In our project we are using reporting service web service calls to get report
paramaeters and report data.
We are passing an array of parameter values to webservice method
GetReportData() as an input parameter to render the output bytes.This
parameter values array consists of each controls's selected value.
Now in case of multivalue parameters, how to pass these multivalues to
webservice method GetReportData()?
Suppose for the control if we select the multiple values, how to send them
to array along with the other control's values to parametervalues collection.
please give suggestions on these.
Thanx
sriramI got the answer for this multivalue parameter
We have to send the multivalue parmaeters with same parameter name but with
different selected values in to parameter values array as below:
Here Foo is a multivalue parameter
<ParameterValues>
<ParameterValue>
<Name>Foo</Name>
<Value>1</Value>
</ParameterValue>
<ParameterValue>
<Name>Foo</Name>
<Value>2</Value>
</ParameterValue>
<ParameterValue>
<Name>Bar</Name>
<Value>NorthWest</Value>
</ParameterValue>
</ParameterValues>
"SRIRAM" wrote:
> In our project we are using reporting service web service calls to get report
> paramaeters and report data.
> We are passing an array of parameter values to webservice method
> GetReportData() as an input parameter to render the output bytes.This
> parameter values array consists of each controls's selected value.
> Now in case of multivalue parameters, how to pass these multivalues to
> webservice method GetReportData()?
> Suppose for the control if we select the multiple values, how to send them
> to array along with the other control's values to parametervalues collection.
> please give suggestions on these.
> Thanx
> sriramsql

Multi Value Integer Parameter

I am trying to create a report with a multi value integer parameter. I have tried

="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Parameters!StatusCode.Value + ")"

I get an error stating

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'ProjectRequestStatus'.

Error during processing of the CommandText expression of dataset 'ProjectRequestStatus'.

Has anybody worked with integers?

Thanks,

Fred

Why not just :

SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (@.StatusCode)

That should work fine. Just set up you parameter as an integer, and multi value.

BobP

|||

If Bobp's solution doesn't work, you can try putting the Join function around your parameter value. It might not be passing the parameters correctly. Something like this:

Code Snippet

="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Join(Parameters!StatusCode.Value, ",") + ")"

|||

When I try that, it errors out when I enter more than one value separated by commas when running the dataset. It works if I only enter one value.

The error is: Conversion failed when converting the nvarchar value '2, 3' to data type int.

When I preview the report I receive no errors but no data is displayed except in the page header and the first group header. (There is a 2nd and 3rd group) It does not matter whether I enter one or more values in the parameter. There is data in the Document Map which allows you to drill down to group 3 data.

Any ideas why there is no data?

Fred

|||

I just tried this solution by putting the join function in the expression. Like the solution above, I get the same results in the Preview. No data except in Group Header 1 and the Document Map allows me to drill down to Group 3.

I checked the Visibility flag in each cell and row and the whole table. All Hidden properties are False.

Fred

|||

Can you post the Parameter RDL snippet?

BobP

|||I found my problem. One of the groups did have the Visibility Hidden property set to True.

Multi value in Report Parameter using MSVS Report Designer.

Hi All,

I have a question, How to user the Multi value in Visual Studio Report Designer.

My dataset contains the following

Select empno,empname,basicpay,deptcd from employee where deptcd in (substring(@.gdept,1,4))

Here variable gdept is the report parameter. I am passing the value.
it works fine with single value.

( the source of gdept is a another dataset to list the deparment code+name,

Example

DEP1 - COMPUTER

DEP2 - ADMIN

DEP3 - FINANCE

)

when I preview, i have a combo box to select the department.

If I choose one deparment, it works fine.

If I choose more than one department or select all I have error message

"substring function requires 3 aruguments."

what is wrong is the query

Please advise.

Cheers,

Saleem

Thats nor possible within a single query. By choosing more than one Value it will evaluate to SUBSTR('A','B',1,4) which is not valid, because the signature of SUBSTRING Only allows 3 parameters. You will have to do that with splitting the values. I once wrote a function for that which will make those values Accessible for you as a table and which can be joined easily afterwards.

See more details on: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Multi value "Select All"

Hi all
I have a multi-value drop down list as my report's parameter.
Once the report is executed with the selected parameter, I am
displaying the selected parameter also as a part of the report's
results.
Is there a way I can display the word "All" in the report results when
the user selects "Select All" of the report parameter?
At present it shows a long list of concatenated values of the drop down
list.
Thanks heapsI might be misunderstanding you, but you can display the parameter
label that is selected: Parameter.label instead of parameter.value|||I am sorry if my question was not clear.
I tried Parameter.Label, but gives an error if the parameter is a Multi
Value type.
So, one has to use Join(Parameter.Label,",").
This displays a list of all the paramters that were selected.
What I want is, if the user selects "Select All" instead of showing the
list of parameters, i should be able to display the word "All".
Does this make sense?
Thanks

Multi Select type-in Parameter - To PYRO

Hi All

Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.

My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..

I'm using SSRS with SSAS cube all in BI all 2005

Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.

Thanks in advance

Gerhard Davids

Bump|||Hi Pyro,

I'm sad to say that I too tried more then once to do that but from soom reason I coud'nt.

The only way I manage was useing a query for the multi value parameter.

I don't want to say that its impasisble 'coz maybe some one will have an answer but its definitely not that easy.|||

Thanks Roy

I appreciate you letting me know. I'll post a solution here as
soon as I can find one.

Gerhard

|||Hi pyro.

I've just encounter this problem again :(

Is there any chance that you found a solution?

Thanks.|||

Hey Roy

No solution yet :S

But I have been thinking of one and the following is what i've come up with:

Create a Parameter that is non-queried so that it is type-able.
then create some form of custom code to split user input by 'comma' or
some form of seperation maby '; '. The next step would be to filter the table/matrix accordingley.
Using something like: =Fields!Company = Code.split(Paramters!typeMe.value)
{The code would then have a counter that builds up i guess}

I have yet to test this and the report may become slugish on
large queries, but this is pure creative thinking at the moment.

I most probabley find time to test this in the near future as complaints
are mounting ;P

G