SSRS - Multi-Value Parameter with Stored Procedure

  • Hi,

    I googled this issue and I was not able to come up with any solution... not sure what I'm doing wrong. Whatever I do, either I get an error (different error based what I try) or only the first value is displayed. This is for SSRS 2005

    I'm using the Stored Procedure below and in my Dataset I only have the SP name and the Command type is SP. In the Dataset Parameters, the Name is @ClientID and the value is =JOIN(Parameters!ClientID.Value, ",")

    The Report Parameters has Multi-Value and Allow blank value selected... in the Available Values, From query is checked and I use a simple query that would pull the ClientID and the ClientName from another DataSet: SELECT DISTINCT KC.ClientID, KC.ClientCode + ' - ' + KC.ClientName as ClientName

    from CLIENTS KC

    I Found the Split Function in WHERE clause from some site (http://www.bidn.com/blogs/mikedavis/ssis/360/multi-value-parameters-from-stored-procedures-in-ssrs)

    ALTER PROCEDURE [dbo].[spr_ServerInfoByClients]

    @ClientID nvarchar(4)

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT KC.ClientID, KC.ClientCode, KC.ClientName, KS.ServerName, KS.ServerDesc, KS.ServerIPAddress, KS.ServerType

    FROM CLIENTS KC INNER JOIN SERVERS KS ON (KC.ClientID = KS.ClientID)

    WHERE

    KS.ClientID IN (SELECT Item FROM Split(@ClientID, ','))

    ORDER BY KS.ClientID

    END

    Thanks

  • Please disregard... i found the answer. I needed to change the character length to Max.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply