Error Converting Data Type Nvarchar To Int Reporting Services
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Error converting data type nvarchar to int SQL Server > SQL Server Reporting Services, Power View error converting data type nvarchar to int stored procedure Question 0 Sign in to vote I've got a very simple report (using Employees
Error Converting Data Type Nvarchar To Numeric.
table in Northwind) that I'm using an INT (EmployeesID) as the parameter. (Query Failed for Dataset1) it works fine if
Error Converting Data Type Nvarchar To Numeric. In Sql Server 2012
I choose only one item from the list - but I'm allowing Multiple values, but when I actuallychoose multiple values, I get the error that's in the subject line. MysProc to get the Employees
Error Converting Data Type Nvarchar To Numeric. In Sql Server 2008
from the ManagerID (which is actually the EmployeeID - list of employees).Here's the code: Select Emp.EmployeeID, emp.Firstname + ' ' + emp.Lastname as Name, emp.Extension, emp.reportsto,mgrs.FirstName + ' ' + mgrs.lastname as Manager from Employees emp inner join Employees mgrs on emp.ReportsTo=mgrs.EmployeeID Where emp.ReportsTo=@MgrID I don't see what it means by converting nvarchar to int -- ReportsTo field and the EmployeeID field are both int the error converting data type nvarchar to float. Listbox query Value field is EmployeeID and label field is the manager name. the only thing I can think of is that, when it runs the query, it's using the combination of the names as the search criteria, instead of the employeeID for each name. Can anyone see what might be the problem? Friday, November 19, 2010 5:30 PM Reply | Quote Answers 0 Sign in to vote Hi AugustWind, To solve this issue, check this http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/on how touse MultiValue Parameters with Stored Procedures in SSRS. thanks, Jerry Marked as answer by Jerry NeeModerator Friday, November 26, 2010 12:13 PM Monday, November 22, 2010 5:41 AM Reply | Quote Moderator All replies 0 Sign in to vote Hello, If you have mulitple values for a parameter, you can't compare it with =, you have to use the IN operator, and this works also with only one single value: Where emp.ReportsTo IN (@MgrID) Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de Friday, November 19, 2010 5:48 PM Reply | Quote 0 Sign in to vote Tried that - - it stil
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to error converting data type nvarchar to datetime. Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs error converting data type nvarchar to bigint. Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Error Converting Datatype nvarchar to int --SSRS ssrs multi value parameter Want to Advertise Here? Solved Error Converting Datatype nvarchar to int --SSRS Posted on 2010-02-04 MS SQL Server 2005 SSRS 2 Verified Solutions 6 Comments 2,437 Views Last Modified: 2012-05-08 Hi I am calling a https://social.msdn.microsoft.com/Forums/sqlserver/en-US/82bf531d-dd08-49ab-8bf2-56435a0427b0/error-converting-data-type-nvarchar-to-int?forum=sqlreportingservices stored procedure from SQL Server reporting services 2005 and the procedure is in SQL Server 2000. I am passing in a multi value to the stored procedure. On the reporting side I did this in the parameter =join(Parameters!ProdList.Value,",") I also handled the below code in my proc INSERT INTO #Products SELECT ProdID, ProDName FROM Product WHERE ProdID IN (select value from ufn_split(@ProdList,',')) ufn_split is a function which takes https://www.experts-exchange.com/questions/25116564/Error-Converting-Datatype-nvarchar-to-int-SSRS.html for example "1,2,3" and returns as table of 1 2 3, but inspite of doing research on net for handling multi-values I am still getting this ERROR Can anyone Please solve my issue and may be shed some direction.... 0 Question by:baylar Facebook Twitter LinkedIn Google Best Solution bybaylar Angelll: This part I kept it the way you typed it. Go to Solution 6 Comments LVL 10 Overall: Level 10 MS SQL Server 2005 3 SSRS 1 Message Expert Comment by:Rakesh Jaimini2010-02-04 in ufn_split function you are splitting values before storing them convert them into integer first 0 LVL 10 Overall: Level 10 MS SQL Server 2005 3 SSRS 1 Message Assisted Solution by:Rakesh Jaimini2010-02-04 or you can do like INSERT INTO #Products SELECT ProdID, ProDName FROM Product WHERE ProdID IN (select cast(value as int) from ufn_split(@ProdList,',')) 0 Message Author Comment by:baylar2010-02-04 Hi Rakesh I just did what you have said but same ERROR 0 Message Author Comment by:baylar2010-02-04 Hey It just worked..........I made the following changes: 1) I changed the command text from text to stored procedure and changed the below code EXEC dbo.Reporting_1 @StartDate, @EndDate, @ProdList to just the proc name: dbo.Reporting. and defined the parameters in the para
across our financial http://www.networksteve.com/enterprise/topic.php/SSRS_2005_-_I_cannot_run_this_report_-_Error_converting_datatype/?TopicId=45786&Posts=3 year. It is based on a Stored Procedure, which http://luckykanwar.blogspot.com/2012/12/ssrs-report-using-stored-procedure.html is as follows: ---Delete existing SP IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('CostCentres')) DROP Proc CostCentres go --- Create Stored proc --- Calculation of Cost Centre Report create proc [dbo].[CostCentres] @CompanyID nvarchar error converting (4), @Department nvarchar (2),@GLAccount int,@Date datetime AS begin set nocount on set dateformat dmy declare @month int, @year int, @day int ---Declare @Date datetime ---Declare @CompanyID nvarchar (4) ---Declare @Department nvarchar (2) ---Declare @GLAccount int --- set @Date = '31/03/2011' set @month = MONTH(@Date) set error converting data @year = YEAR(@Date) set @day = DAY(@Date) ---set @companyID='ansa' ---set @Department='FS' ---set @GLAccount=4010 if (@month<11) Begin SELECT ledgertrans.DATAAREAID, ledgertrans.DIMENSION AS Department,
ledgertable.ACCOUNTName, ledgertrans.DIMENSION2_ AS CostCentre,
cast('01'+' '+ CONVERT(varchar(3), ledgertrans. transDate, 100) +' ' + cast(datepart(yyyy,
ledgertrans.transdate) as varchar(4)) as datetime) as DayMonthYear,
sum(ledgertrans.AMOUNTMST) as Amount FROM LEDGERTRANS Inner join Ledgertable on ledgertrans.dataareaid=ledgertable.dataareaid and ledgertrans.accountnum=
ledgertable.accountnum WHERE (ledgertrans.DATAAREAID IN (@CompanyID)) AND (ledgertrans.ACCOUNTNUM IN
(@GLAccount)) AND (ledgertrans.DIMENSION in (@Department)) And (ledgertrans.TRANSDATE >= '01/11/'+cast(YEAR(@Date) -1 as varchar(4))) and
(ledgertrans.TRANSDATE <=(@Date)) Group by ledgertrans.dataareaid, ledgertrans.dimension , ledgertable.accountname,
ledgertrans.DIMENSION2_,cast('01'+' '+ CONVERT(varchar(3), ledgertrans.transDate, 100) +' ' +
cast(datepart(yyyy, ledgertrans.transdate) as varchar(4)) as datetime)
Order by ledgertrans.dimension, ledgertable.accountname, ledgertrans.DIMENSION2_,
cast('01'+' '+ CONVERT(varchar(3), ledgertrans.transDate, 100) +' ' +cast(datepart(yyyy,
ledgertrans.transdate) as varchar(4)) as datetime) End Else begin SELECT ledgertrans.DATAAREAID, ledgertrans.DIMENSION AS Department,
ledgertable.ACCOUNTName, ledgertrans.DIMENSION2_ AS CostCentre, cast('01'+' '+
CONVERT(varchar(3), ledgertrans.transDate, 100) +' ' +cast(datepart(yyyy, ledgertrans.transdate)
data source to fetch data from the SQL Server. After a few glitches I finally found a way to do this. Lets go through the entire process. What went wrong and how I solved it. I created a stored procedure named SalesValue with @Year as an input parameter. ----------------------------------------------------------------------------------------------------------- if OBJECT_ID('SalesValue') is not null drop proc SalesValue go create proc SalesValue (@Year nvarchar(100)) as SELECT SalesTerritoryCountry, SalesTerritoryGroup, [MonthNumberOfYear], CASE WHEN monthnumberofyear between 1 and 3 then 'Quarter 1' WHEN monthnumberofyear between 4 and 6 then 'Quarter 2' WHEN monthnumberofyear between 7 and 9 then 'Quarter 3' WHEN monthnumberofyear between 10 and 12 then 'Quarter 4' end as [Quarter], case when monthnumberofyear between 1 and 6 then 'Half Year 1' when monthnumberofyear between 7 and 12 then 'Half Year 2' end as [Half Year], CalendarYear, SUM(FactResellerSales.SalesAmount) AS SalesAmount, SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, SUM(FactResellerSales.SalesAmount) / SUM(FactResellerSales.OrderQuantity) AS AvgSale FROM FactResellerSales INNER JOIN DimSalesTerritory ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey WHERE CalendarYear in (@Year) GROUP BY [MonthNumberOfYear],DimSalesTerritory.SalesTerritoryGroup, SalesTerritoryCountry, CalendarYear ORDER BY DimSalesTerritory.SalesTerritoryGroup, CalendarYear,[MonthNumberOfYear] go ----------------------------------------------------------------------------------------------------- The stored procedure was created successfully. When I executed the above procedure with one