SSRS Data type error

swaggrK
swaggrK used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russell FoxDatabase Developer
Top Expert 2014

Commented:
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

Author

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

Database Developer
Top Expert 2014
Commented:
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
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
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))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial