SQLTeam.com | Weblogs | Forums

SSRS Report Error


#1

Hi, I am new to SSRS i am building my report below is the stored proc i used and when i created my parameters, i keep getting an error converting nvarchar datatype to datetime when i run my report. CAN ANYONE PLEASE HELP?
Error below

  • Error occurred during local processing
    An Error occurred during report processing
    Query execution failed to dataset 'Loangauges'
    error converting data type nvarchar to datetime*

--

create PROCEDURE [dbo].[proc_LoanGauges] --EXEC dbo.proc_LoanGauges

@ReportDate Datetime,
@LoanAmount varchar(max),
@LoanPurpose varchar(max),
@PropertyUsage varchar(max),
@Demographics2 varchar(max)	

AS
	BEGIN

		DECLARE		@CurrentMonth INT,
					@CurrentYear  INT,
					@PriorMonth INT,
					@PriorYear  INT,
					@CurrentQuarter INT,
					@MTD INT,
					@QTD INT,
					@MonthlyPrior6mnthAvg INT,
					@MonthlyPrior6mnthAvgExcludeCurrMonth INT,
					@LastFullMonth INT,
					@LastFullQuarter INT
					--,@ReportDate Datetime = '4/4/2018'
					
		SET			@CurrentMonth = DATEPART(MM,@ReportDate)
		SET			@CurrentQuarter = DATEPART(QQ,@ReportDate)
		SET			@CurrentYear = DATEPART(YY,@ReportDate)
		SET			@PriorMonth = DATEPART(MM,DateAdd(MM,-1,@ReportDate))
		SET			@PriorYear = (SELECT CASE WHEN DATEPART(MM,DateAdd(MM,-1,@ReportDate)) = 1 THEN DATEPART(YY,DATEADD(YY,-1,@ReportDate)) ELSE @CurrentYear END)
					--SELECT @CurrentMonth,@CurrentQuarter,@CurrentYear,@PriorMonth,@PriorYear
					
		DECLARE		--@ReportDate datetime = '4/4/2018',
					@DayOfWeek varchar(100),
					@BeginOfWeek Datetime,
					@BeginOfMonth Datetime,
					@BeginOfYear Datetime

		SET			@DayOfWeek = (SELECT DATENAME(dw,@ReportDate))
		SET			@BeginOfWeek =	(
										SELECT		CASE	WHEN @DayOfWeek = 'Sunday'		THEN DATEADD(dd,0,@ReportDate)
															WHEN @DayOfWeek = 'Monday'		THEN DATEADD(dd,-1,@ReportDate)
															WHEN @DayOfWeek = 'Tuesday'		THEN DATEADD(dd,-2,@ReportDate)
															WHEN @DayOfWeek = 'Wednesday'	THEN DATEADD(dd,-3,@ReportDate)
															WHEN @DayOfWeek = 'Thursday'	THEN DATEADD(dd,-4,@ReportDate)
															WHEN @DayOfWeek = 'Friday'		THEN DATEADD(dd,-5,@ReportDate)
															WHEN @DayOfWeek = 'Saturday'	THEN DATEADD(dd,-6,@ReportDate) END BeginOfWeek
									)

		SET			@BeginOfMonth = (
										SELECT		CONVERT(DATETIME,CONVERT(VARCHAR(100),DATEPART(MM,@ReportDate)) + '/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate)))
									)

		SET			@BeginOfYear = (
										SELECT		'1/1/' + CONVERT(VARCHAR(100),DATEPART(YY,@ReportDate))
									)
				SELECT		l.*,p.[Property Usage],b.[Marital Status],b.Sex,b.Race,DATEDIFF(YY,b.[Date of Birth],@ReportDate) Age,
					CASE WHEN LoanDate >= @BeginOfWeek	AND LoanDate <= @ReportDate THEN		'WeekToDate'
					     WHEN LoanDate >= @BeginOfMonth AND LoanDate <= @ReportDate  THEN		'MonthToDate'
						 WHEN LoanDate >= @BeginOfYear  AND LoanDate <= @ReportDate THEN		'YearToDate' ELSE NULL END ToDate,
					CASE WHEN LoanDate >= @BeginOfWeek	AND LoanDate <= @ReportDate THEN		1
					     WHEN LoanDate >= @BeginOfMonth AND LoanDate <= @ReportDate  THEN		2
						 WHEN LoanDate >= @BeginOfYear  AND LoanDate <= @ReportDate THEN		3 ELSE NULL END ToDateOrder,
					CASE WHEN LoanAmount <= 100000 THEN 'Less Than $100k'
						 WHEN LoanAmount between 100000 AND 200000 THEN '$100k to $200k'
						 WHEN LoanAmount > 200000 THEN 'More Than $200k' END LoanAmountGroup,
					CASE WHEN LoanAmount <= 100000 THEN 1
						 WHEN LoanAmount between 100000 AND 200000 THEN 2
						 WHEN LoanAmount > 200000 THEN 3 END LoanAmountGroupOrder,
					CASE WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) < 25 THEN '<=38'
						 WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) between 25 and 35 THEN '39-40'
						 WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) between 36 and 45 THEN '41-42'	
						 WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) > 45 THEN '42+' END AgeGroup,
					CASE WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) < 25 THEN 1
						 WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) between 25 and 35 THEN 2
						 WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) between 36 and 45 THEN 3
						 WHEN DATEDIFF(YY,b.[Date of Birth],@ReportDate) > 45 THEN 4 END AgeGroupOrder	
		INTO		#Financials	  
		FROM dbo.[Dim_Borrower*] b
		Inner Join Fact_Financials f
		On b.Borrower_Key = f.Borrower_Key
		Inner Join dbo.[Dim_Loan*] l
		On l.Loan_Key = f.Loan_Key
		Inner Join dbo.[Dim_Property*] p 
		on p.Property_ID = l.Property_ID
		
		SELECT		*
		INTO		#FinancialsII
		FROM		#Financials
		WHERE		LoanAmountGroup IN (SELECT items FROM dbo.Split(@LoanAmount,','))
		AND			[Purpose of Loan] IN (SELECT items FROM dbo.Split(@LoanPurpose,','))
		AND			[Property Usage] IN (SELECT items FROM dbo.Split(@PropertyUsage,','))
		AND			(AgeGroup IN (SELECT items FROM dbo.Split(@Demographics2,','))
			OR		 [Marital Status] IN (SELECT items FROM dbo.Split(@Demographics2,','))
			OR		 Race IN (SELECT items FROM dbo.Split(@Demographics2,','))
			OR		 Sex IN (SELECT items FROM dbo.Split(@Demographics2,',')))
			
			SET			@MTD =	(
								SELECT		COUNT(*)  
								FROM		#FinancialsII
								WHERE		DATEPART(MM,LoanDate) = @CurrentMonth
								AND			DATEPART(YY,LoanDate) = @CurrentYear
								AND			LoanDate <= @ReportDate
								)

			SET			@MonthlyPrior6mnthAvg = 
								(
								SELECT		AVG(LoanCnt)
								FROM	(
										SELECT		TOP 6 DATEPART(MM,LoanDate) LoanMonth,DATEPART(YY,LoanDate) LoanYear,COUNT(*) LoanCnt
										FROM		#FinancialsII
										WHERE		CONVERT(VARCHAR(25),DATEPART(MM,LoanDate)) + CONVERT(VARCHAR(25),DATEPART(YY,LoanDate)) <> CONVERT(VARCHAR(25),@CurrentMonth) +  CONVERT(VARCHAR(25),@CurrentYear)
										AND		LoanDate <= @ReportDate	
										GROUP BY	DATEPART(MM,LoanDate),DATEPART(YY,LoanDate)	
										ORDER BY	DATEPART(YY,LoanDate) DESC,DATEPART(MM,LoanDate) DESC		
										) a
								)

			SET			@LastFullMonth = 
								(
								SELECT		AVG(LoanCnt)
								FROM	(
										SELECT		TOP 1 DATEPART(MM,LoanDate) LoanMonth,DATEPART(YY,LoanDate) LoanYear,COUNT(*) LoanCnt
										FROM		#FinancialsII
										WHERE		CONVERT(VARCHAR(25),DATEPART(MM,LoanDate)) + CONVERT(VARCHAR(25),DATEPART(YY,LoanDate)) <> CONVERT(VARCHAR(25),@CurrentMonth) +  CONVERT(VARCHAR(25),@CurrentYear)
										AND			LoanDate <= @ReportDate	
										GROUP BY	DATEPART(MM,LoanDate),DATEPART(YY,LoanDate)	
										ORDER BY	DATEPART(YY,LoanDate) DESC,DATEPART(MM,LoanDate) DESC		
										) a
								)

			SET			@MonthlyPrior6mnthAvgExcludeCurrMonth = 
								(
								SELECT		AVG(LoanCnt)
								FROM	(
										SELECT		TOP 6 DATEPART(MM,LoanDate) LoanMonth,DATEPART(YY,LoanDate) LoanYear,COUNT(*) LoanCnt
										FROM		#FinancialsII
										WHERE		(CONVERT(VARCHAR(25),DATEPART(MM,LoanDate)) + CONVERT(VARCHAR(25),DATEPART(YY,LoanDate)) <> CONVERT(VARCHAR(25),@CurrentMonth) +  CONVERT(VARCHAR(25),@CurrentYear)
										AND			CONVERT(VARCHAR(25),DATEPART(MM,LoanDate)) + CONVERT(VARCHAR(25),DATEPART(YY,LoanDate)) <> CONVERT(VARCHAR(25),@PriorMonth) +  CONVERT(VARCHAR(25),@PriorYear))
										AND			LoanDate <= @ReportDate	
										GROUP BY	DATEPART(MM,LoanDate),DATEPART(YY,LoanDate)	
										ORDER BY	DATEPART(YY,LoanDate) DESC,DATEPART(MM,LoanDate) DESC
										) a
								)	

			SET			@QTD = 	
								(						
								SELECT		LoanCnt
								FROM		
										(		
										SELECT		TOP 1 DATEPART(QQ,LoanDate) LoanQuarter,DATEPART(YY,LoanDate) LoanYear,COUNT(*) LoanCnt
										FROM		#FinancialsII
										WHERE		CONVERT(VARCHAR(25),DATEPART(QQ,LoanDate)) + CONVERT(VARCHAR(25),DATEPART(YY,LoanDate)) = CONVERT(VARCHAR(25),@CurrentQuarter) +  CONVERT(VARCHAR(25),@CurrentYear)
										AND			LoanDate <= @ReportDate	
										GROUP BY	DATEPART(QQ,LoanDate),DATEPART(YY,LoanDate)	
										ORDER BY	DATEPART(YY,LoanDate) DESC,DATEPART(QQ,LoanDate) DESC	
										)	a
								)
																	
			SET			@LastFullQuarter = 	
								(						
								SELECT		LoanCnt
								FROM		
										(		
										SELECT		TOP 1 DATEPART(QQ,LoanDate) LoanQuarter,DATEPART(YY,LoanDate) LoanYear,COUNT(*) LoanCnt
										FROM		#FinancialsII
										WHERE		CONVERT(VARCHAR(25),DATEPART(QQ,LoanDate)) + CONVERT(VARCHAR(25),DATEPART(YY,LoanDate)) <> CONVERT(VARCHAR(25),@CurrentQuarter) +  CONVERT(VARCHAR(25),@CurrentYear)
										AND		LoanDate <= @ReportDate	
										GROUP BY	DATEPART(QQ,LoanDate),DATEPART(YY,LoanDate)	
										ORDER BY	DATEPART(YY,LoanDate) DESC,DATEPART(QQ,LoanDate) DESC	
										)	a
								)	
								
											
			SELECT		@MTD MTD,@MonthlyPrior6mnthAvg MonthlyPrior6mnthAvg,((@MTD *1.00)/@MonthlyPrior6mnthAvg) * 100 MTDvsMonthlyPrior6mnthAvg_PerInc,
						@LastFullMonth LastFullMonth,@MonthlyPrior6mnthAvgExcludeCurrMonth MonthlyPrior6mnthAvgExcludeCurrMonth,((@LastFullMonth *1.00)/@MonthlyPrior6mnthAvgExcludeCurrMonth) * 100 LastFullMonthvsMonthlyPrior6mnthExcludeCurrMonth_Avg_PerInc,
						@QTD QTD,@LastFullQuarter LastFullQuarter,((@QTD *1.00)/@LastFullQuarter) * 100 QTDvsLastFullQuarter
						
				
		
END

#2

Is the stored procedure executing without error in SSMS?


#3

Yes

Cyril

Tanlaka

MSBI Developer

Zoom_ID : 411 051 0761