SSRS Data type error

In my stored procedure I am setting the following parameters:
@site varchar(4),
@employeeid INT,
@CommissionLevelID INT


In SSRS, my parameter setting are:
CommissionLevelID is set to "integer" and I have checked "Allow multiple values"
employeeid is set to "integer" and I have checked "Allow multiple values"


But, I get an error message "Error converting data type nvarchar to int" when I select more than one employee.
It works fine when I select just one employee from the drop down.
swaggrKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
In your query, make sure it's using
WHERE COMMISSIONLEVELID IN(@CommissionLevelID)

Open in new window

and not
WHERE COMMISSIONLEVELID LIKE @CommissionLevelID

Open in new window

swaggrKAuthor Commented:
@Russell...yes, I am doing that. Here is the full stored proc


ALTER PROCEDURE [dbo].[TiersEmployee]
	@site varchar(4),
	@EffectiveDate varchar(25),
	@employeeid INT,
	@CommissionLevelID INT
AS
BEGIN


SELECT CommissionLevelID, [EffectiveDate], [LevelName], active, [siteid], [sitename], fname, lname, EmployeeID, [4] as [Count4], [5] as [Count5], [6] as [Count6], [7] as [Count7], [8] as [Count8], [9] as [Count9], [10] as [Count10] 
FROM   
(SELECT [LevelName], active, s.[siteid], s.[sitename], e.fname, e.lname, cla.EmployeeID, cla.CommissionLevelID, cld.AttendantCount, cld.Rate, cld.EffectiveDate
FROM [CommissionLevelAssignment] cla
INNER JOIN [CommissionLevelDetail] cld
ON cla.CommissionLevelID = cld.CommissionLevelID
INNER JOIN [CommissionLevel] cl
ON cld.[CommissionLevelID] = cl.[CommissionLevelID]
RIGHT OUTER JOIN [dbo].EMPLOYEES e
ON cla.EmployeeID = e.employeeid
INNER JOIN [SITES] s
ON s.[siteid] = cl.[siteid] 

) p  
PIVOT  
(  
MAX(Rate)  
FOR AttendantCount IN  
( [4], [5], [6], [7], [8], [9], [10])  
) AS pvt  
 WHERE
	CommissionLevelID IS NOT NULL 
	and siteid = @site 
	and EffectiveDate >= @EffectiveDate
	and employeeid IN (@employeeid) 
	and CommissionLevelID IN (@CommissionLevelID)
	ORDER BY [LevelName], [employeeid]

Open in new window

Russell FoxDatabase DeveloperCommented:
Try removing the proc, and instead use that query directly in SSRS. The problem is with passing a multi-value parameter outside of SSRS. The SSRS engine knows how to parse it, but the SQL query engine does not because the parameter is passed as a comma-separated list. SQL interprets that as a string.

You can also accept the parameter as a string parameter, and then use a SPLIT function to break it up in the proc.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/passing-multivalue-parameter-in-stored-procedure-ssrs?forum=sqlreportingservices

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The problem is in how you are passing the parameters. You want to pass a "list" of employees (e.g. a comma-separated list of integers).

A comma-separated list is a string and not an Integer. So, you need to accept a string of integers (e.g. @employeeId INT should actually be @employeeId NVARCHAR(255))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc

From novice to tech pro — start learning today.