We help IT Professionals succeed at work.

SSRS Data type error

107 Views
Last Modified: 2018-12-03
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

Russell FoxData Engineer
CERTIFIED EXPERT
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

Data Engineer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

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))
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.