Hi All, In SSRS, I am having a report parameter, Country where user can select multiple countries. The codes of the selected countries are passed in a parameter (@CNTRY_CD) of a stored procedure. Later this @CNTRY_CD is passed in where clause with IN statement as Select selection list From Table name Where some statement AND LOCDIM.CNTRY_CD IN (@CNTRY_CD) Now, I need to do something in SSRS so that the values should pass in the parameter @CNTRY_CD in the following format Country1,Country2,Country3,Country4,------ Is this possible in SSRS? If yes then please help me. Thanks

Hai You can do all this in your stored procedure, have you tried that? (Using Dynamic Query)

You can also do that in SSRS using the following expression: =JOIN(Parameters!CTRY_ID.Value, ', ') The ', ' part of this is used as the separator in the output on the report. Note that when using this function, the (0) that would normally follow a parameter output is not required. Good luck,

NigelWest, The function =JOIN(Parameters!CTRY_ID.Value, ', ') will add only a comma between multiple selection. What I need is that a function that add a ' before the value selected then ' followed by a comma. i.e. 'country1','country2',......... Thnaks

Try this expression = Chr(39) & Replace(JOIN(Parameters!CTRY_ID.Value, ', '),",",Chr(39) & "," & Chr(39)) & Chr(39) this will solve your issue.

You can use split function in SP to solve this. For the function try Google it.. -------------------- Senthil

Hi All, In SSRS,you need to pass the multi valued parameters in the report. You need to write the dynamic Stored procedures. ----Please follow some examples below. Step 1: For integer values /************************************************************ EXEC USP_CRMLEAD '1,2,3' PASSING THE MULTIVALUED PARAMETER RECORDS ***************************************************************/ ALTER PROCEDURE USP_CRMLEAD (@CRMLEAD_ID VARCHAR(100)) AS BEGIN DECLARE @PMAINQUERY NVARCHAR(MAX) DECLARE @PWHERECONDITION NVARCHAR(MAX) SET @PMAINQUERY='SELECT '+'crmlead_id,Display_Name,Company,Lead_Source ,ISNULL(Address1,'''')+''''+ISNULL(Address2,'''')as address,city,state,Country from CrmLead' SET @PWHERECONDITION=' WHERE CRMLEAD_ID IN ('+ @CRMLEAD_ID +')' SET @PMAINQUERY=@PMAINQUERY+''+@PWHERECONDITION PRINT @PMAINQUERY EXEC SP_EXECUTESQL @PMAINQUERY END GO Step 2: For Varchar datatype /******************************************************************** SELECT * FROM CRMLEAD EXEC USP_CRMLEADVARCHARTYPE 'Web,Purchased List' *****************************************************************/ CREATE PROCEDURE USP_CRMLEADVARCHARTYPE(@LEADSOURCE NVARCHAR(200)) AS BEGIN DECLARE @PMAINQUERY NVARCHAR(MAX) DECLARE @PWHERECONDITION NVARCHAR(MAX) ---FIND THE LENGHT OF THE LEADSOURCE IF( LEN(@LEADSOURCE) >0 AND CHARINDEX(@LEADSOURCE, ',')=1) BEGIN SET @LEADSOURCE = '@LEADSOURCE' END ELSE BEGIN IF(LEN(@LEADSOURCE) >0 ) BEGIN SET @LEADSOURCE = '''' + REPLACE(@LEADSOURCE, ',', ''',''') + '''' END ELSE BEGIN SET @LEADSOURCE = 'c' END END SET @PMAINQUERY='SELECT '+'crmlead_id,Display_Name,Company,Lead_Source ,ISNULL(Address1,'''')+''''+ISNULL(Address2,'''')as address,city,state,Country from CrmLead' SET @PWHERECONDITION='WHERE LEAD_SOURCE IN ('+@LEADSOURCE+')' SET @PMAINQUERY=@PMAINQUERY+' '+@PWHERECONDITION PRINT @PMAINQUERY EXEC SP_EXECUTESQL @PMAINQUERY END

It's funny I was also doing a country list and I came up with this, which works. SELECT @CountryList = COALESCE( @CountryList + ',', '') Then in the where clause I used this select blah blah from whatever where CHARINDEX( ',' + sc.country_code + ',', ',' + @CountryList + ',') > 0