asked on

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.

Russell Fox

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

swaggrK

ASKER

@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

ASKER CERTIFIED SOLUTION

Russell Fox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nakul Vachhrajani

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