December 4, 2008 at 9:24 am
Hi friends,
i have a report where there are 5 parameter. One of them is multi valued parameter. Everything is ok. I can run it and it can handle multi value parameter along with other parameter. I have tried to work out in the stored proeceudre first and then put into RS. Now when i try to run dataset and enter all parameter then it works fine and gives the appropriate output but when i preview or deploy and check it then it doesnt work. It gives only one record (first record). There is enough data type and varchar(8000) so no question about first record as other record truncated.
Can you please advice?
thanks,
vijay
December 4, 2008 at 9:33 am
How are you handling the Multi-value parameter in the stored procedure? Here is a quote from BOL (bolding by me):
Writing Queries that Map to Multivalued Report Parameters
You can define a multivalued parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN statement to specify the parameter.
Could you post your code?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 9:39 am
Hi,
i am creating temporarty table and inserting the parameter into that table and then using that table into main query.
create table #TempTable (
slice varchar(8000))
declare @index1 int
declare @slice1 varchar(8000)
set @index1 = 1
if @trust is null
set @index1 = 0
while @index1 !=0
begin
set @index1 = charindex(',',@trust)
if @index1 !=0
set @slice1 = left(@trust,@index1 - 1)
else
set @slice1 = @trust
insert into #TempTable select @slice1
set @trust= right(@trust,len(@trust) - @index1)
if len(@trust) = 0
break
end
Now my stored procedure runs and when it comes to trust it will use above above.
where trust in (select slice from #temptable)
regards,
vijay
December 4, 2008 at 9:40 am
If it is a problem with the array or anythign then it shouldnt appear in dataset result in RS. But it appears in dataset.
December 5, 2008 at 2:21 am
Can any one help please?
December 5, 2008 at 5:26 am
You really have not supplied enough information for us to help. We would need to see the code from the report as well as the complete stored procedure. Without that we are shooting tossing darts in the dark, eventually one might hit the mark.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 5, 2008 at 8:33 am
I have found the solution. Basically i can assume that when RS passes the multi value parameter from RS to SP then it will pass it as array of very long string. When i select the parameter, it will select only parameter!value(0), that means only first record will appear.
I have some idea that it picks up only first record so there should be something where it passes only first record of the array. And it is the parameter.
so i have changed it to expression which says something like:
=Join(Parameters!MultiPara.Value(), ",")
Here it will pass all string of array to SP and i made one function which will then split array of string into one parameter by splitting at ",".
thanks for your help.
regards,
vijay
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply