| Author |
Topic |
|
rwlopez
Yak Posting Veteran
80 Posts |
 Posted - 2006-12-14 : 17:23:37
|
| I am having a problem with a using a Multi-Valued parameter in a report. I have researched the site and learned how it can be done by converting a CSV string to a table using and function and then referencing the function in the WHERE clause. I am able to make this work in SQL for example.DECLARE @TheCSV as varchar(1000)SET @TheCSV = '16,17'EXECUTE dbo.StoredProcedure @TheCSVHowever when a create a multivalued parameter named TheCSV in a report and try to pass the values selected by the user to the SP I always get a mesage saying. Must declare the scalar variable @TheCSV. The SQL for the report calls the SP in the same way as the above example the only difference is that @TheCSV is SET by the user rather than being hard coded. I have created many reports that use parameters and stored procedures but I can't get the multi-valued parameters in a Reporting Services report to work. Has anyone had this problem before? |
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-12-14 : 17:28:28
|
| I forgot to mention that the report works when I only select one value. It is when I select more than one value that I have a problem. |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-14 : 17:43:07
|
| If you click the Dataset properties button (... next to the Dataset drop down on the Data tab) and click the Parameters tab, what do you see listed there? |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-12-14 : 17:49:08
|
| @TheCSV =Parameters!TheCSV.Value |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-14 : 18:02:38
|
| That looks right, what about the code for your stored proc? |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-12-14 : 18:19:42
|
| I'll give you the relevent part of the code:CREATE PROCEDURE dbo.StoredProcedure --Report Parameters@TheCSV as varchar(1000)ASBEGIN SET NOCOUNT ON;SELECT ...FROM ...WHERE ...AND FieldName IN (Select IntValue from dbo.CsvToInt(@TheCSV))And here is the SP the WHERE clause is calling. I got the code from this site. What I can't figure out is why can I manually set the value, SET @TheCSV = '1,2' and it works but I can't get the report parameter to do the same thing. If I have a parameter with a list the goes 1 through 5 and I select 1 & 2 shouldn't this return a value of '1, 2'?set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER Function [dbo].[CsvToInt] ( @Array varchar(1000)) returns @IntTable table (IntValue int)ASbegin declare @separator char(1) set @separator = ',' declare @separator_position int declare @array_value varchar(1000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) Insert @IntTable Values (Cast(@array_value as int)) select @array = stuff(@array, 1, @separator_position, '') end returnend |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-12-14 : 19:25:23
|
| I created a blank report and added a text box that captures the data from the multi valued parameter. The parameter does not store the data in a format such as '1, 2' Instead the data is stored ad an array I can view the data in the array in a text box like this =Parameters!TheCSV.Value(0) & ", " & Parameters!TheCSV.Value(1), however how can I have a stored procedure recieve a parameter that is in the format '1, 2'. I guess I need to capture the array from the parameter and convert it to a CSV string, but I am not sure what is the best way to accomplish this. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-14 : 19:36:06
|
| Your Dataset type is Stored Procedure and you have selected the stored procedure from the list of stored procedures right? |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-12-14 : 19:44:40
|
| No, that is what it was. I hadn't changed the setting. Thank you so much for you assistance. It is working now. |
|
|
|
|
Multi-Valued Parameters