Avatar of swaggrK
swaggrK
 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.
* Stored Proc* T-SQLSSRSMicrosoft Server OSMicrosoft SQL Server

Avatar of undefined
Last Comment
Nakul Vachhrajani

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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))
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck