SSRS, error message of query execution failed

Double-click on the data set named GGMExport, and in the Properties dialog that pops up, query tab and if the Query type says...

Also paste whatever parameters were passed if there were any.

Also whatever data source is behind used in the data set, test it to make sure it connects correctly.

Since the data set execution  failed, and there's not any code in this question, there's currently not enough information here to provide an answer.

Thanks for your reply Jim. Please see the sp code below. I have added "grant execute on sp_rept_TitleLoan_VP_GrossProfit to public
go" in the SP which I think it will give privs to public. If I am wrong, where can I set up privs to public?
Thanks.

USE [SMRLoanAppLive]
GO

/****** Object:  StoredProcedure [dbo].[sp_rept_GGMExport]    Script Date: 11/04/2015 08:23:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

If exists (select 1 from sys.procedures where name ='sp_rept_GGMExport')
drop procedure dbo.sp_rept_GGMExport
go 
/******************************************************************
Created by:		Byron Chan
Created on:		8/24/2015
Description:	
Testing:		exec sp_rept_GGMExport 
	
	 
	if exists (select 1 from sys.objects where name = 'tblGGMExport' and type = 'U')
		drop table tblGGMExport
	else
		Create table tblGGMExport (LoanID int,ContractString varchar(20),LoanAmountFinanced money, LoanDate datetime, LoanStoreID int, LoanStoreState varchar(20)) 
	go
	insert into tblGGMExport(LoanID, ContractString, LoanAmountFinanced, LoanDate, LoanStoreID, LoanStoreState)
	select loanid, contractstring, LoanAmountFinanced, LoanDate, LoanStoreID, st.StoreState
	from tblloans ln join tblStores st on ln.LoanStoreID = st.StoreID
	where LoanStatusID not in (2,8)
	and exists (select * from tblStores_tblStoreTypes sst where st.StoreID = sst.StoreID and sst.StoreTypeID = 1)
--*******************************************************************/


create procedure [dbo].[sp_rept_GGMExport]
	
as
Begin


/*
	--delete yesterday's data for testing purpose.

	Declare @BeginDate datetime, @EndDate datetime
	Set @BeginDate=dbo.date(dateadd(d,-1,getdate()))
	Set @EndDate=DateAdd(ss,-1,DateAdd(d,1,@BeginDate))	
	delete tblGGMExport where LoanDate >= @BeginDate
*/
	set FMTONLY off

	declare @EndDate datetime = cast(convert(varchar(10),GETDATE(),121) as date)

	if object_ID('tempdb..#temp') > 0
		drop table #temp
	
	create table #temp(loanid int, OldLoanNumber int, contractstring varchar(100), 
						LoanAmountFinanced money , LoanDate datetime, customerid int, assetid int, 
						LoanStoreID int, StoreState varchar(20), EntryDate datetime)

	insert into #temp
	select loanid, OldLoanNumber, contractstring, LoanAmountFinanced, LoanDate, customerid, assetid, LoanStoreID, st.StoreState,
		cast(convert(varchar(10),GETDATE(),121) as date)
	from tblloans ln join tblStores st on ln.LoanStoreID = st.StoreID
	where LoanStatusID not in (2,8)
		and not exists (select * from tblGGMExport g where g.LoanID = ln.LoanID)
		and exists (select * from tblStores_tblStoreTypes sst where st.StoreID = sst.StoreID and sst.StoreTypeID = 1)
		and LoanDate < @EndDate

	Select StoreState,
		LoanID,
		ContractString,
		CustomerFirstName,
		CustomerLastName,
		LoanAmountFinanced,
		cast((LoanAmountFinanced/Case When BBValue=0 THEN 1 ELSE BBValue END) as decimal(10,4)) as LoanToValue,
		cast((LoanAmountFinanced/Case When Income=0 Then 1 Else Income END) as decimal(10,4)) as IncomeToLoan,
		NumberReferences,
		lengthOfResidency,
		lengthOfemployment,
		LoanRefinanceID,
		VIN,
		(
			Select COUNT(*) 
			from tblLoans
				inner Join tblAutomobiles on tblAutomobiles.AssetID=tblLoans.AssetID
			Where LoanID<>a.LoanID 
				and AutomobileVIN=VIN 
				and LoanStatusID not in (2,8)
		) as NumDuplicateVin,
		convert(varchar(10),LoanDate,101) as LoanDate
	from
	(
		Select 
			LoanID,
			LoanDate,
			ln.CustomerID,
			ContractString,
			LoanAmountFinanced,
			cust.CustomerFirstName,
			cust.CustomerLastName,
			ISNULL(ISNULL(AutomobileBlackBookRetailClean,AutomobileBlackBookLoanValue),AutomobileBlackBookValue) as BBValue,
			ISNull(CustomerMonthlyIncome,0) as Income,
			Case When CustomerReferenceName1 is not null then 1 Else 0 END + 
			Case When CustomerReferenceName2 is not null then 1 Else 0 END +
			Case When CustomerReferenceName3 is not null then 1 Else 0 END +
			Case When CustomerReferenceName4 is not null then 1 Else 0 END as NumberReferences,
			DateDiff(m,IsNull(CustomerEmploymentStartDate,LoanDate),GETDATE()) as LengthOfEmployment,
			DateDiff(m,IsNull(CustomerAddressFromDate,LoanDate),GETDATE())as LengthOfResidency,
			isNull(OldLoanNumber,0) as LoanRefinanceID,
			AutomobileVIN as VIN,
			StoreState
		from #temp ln
			inner Join tblCustomers	cust on cust.CustomerID=ln.CustomerID
			inner Join tblAutomobiles auto on auto.AssetID=ln.AssetID
		where ln.StoreState not in ('TX','OH')

		union
			
		Select 
			LoanID,
			LoanDate,
			ln.CustomerID,
			ContractString,
			LoanAmountFinanced,
			cust.applicantFirstName as CustomerFirstName,
			cust.applicantLastName as CustomerLastName,
			coalesce(AutomobileBlackBookRetailClean,AutomobileBlackBookLoanValue,AutomobileBlackBookValue) as BBValue,
			(ISNull(cust.applicantEmployerSalary,0) * PayfrequencyMultiplier) as Income,
			Case When cust.applicantReferenceName1 is not null then 1 Else 0 END + 
			Case When cust.applicantReferenceName2 is not null then 1 Else 0 END +
			Case When cust.applicantReferenceName3 is not null then 1 Else 0 END +
			Case When cust.applicantReferenceName4 is not null then 1 Else 0 END as NumberReferences,
			(IsNull(cust.applicantEmployerLengthYears,0) * 12) + (IsNull(cust.applicantEmployerLengthMonths,0)) as LengthOfEmployment,
			IsNull(cust.applicantMonthsAtResidence,0) as LengthOfResidency,
			isNull(OldLoanNumber,0) as LoanRefinanceID,
			AutomobileVIN as VIN,
			StoreState
		from #temp ln
			inner Join tblApplicants cust on cust.applicantLoanID=ln.LoanID
			inner Join tblAutomobiles on tblAutomobiles.AssetID=ln.AssetID
			left join tblPayfrequency pf on cust.applicantEmployerPayFrequency = pf.PayfrequencyType
		where ln.StoreState in ('TX','OH') and cust.applicantIsCoApplicant = 0
	) as a

	insert into tblGGMExport(LoanID, ContractString, LoanAmountFinanced, LoanDate, LoanStoreID, LoanStoreState, EntryDate)
	select LoanID, ContractString, LoanAmountFinanced, LoanDate, LoanStoreID, StoreState, EntryDate
	from #temp

	if object_ID('tempdb..#temp') > 0
		drop table #temp

end

GO

grant execute on sp_rept_TitleLoan_VP_GrossProfit to public
go

Select allOpen in new window

What happens when you execute the code without it being in a Stored Procedure, i.e. delete lines 1-37 and 151-156?

Thx, I tried it. The error msg showed "cannot read the next data row for the dataset dataset 1 (rserrorreadingnextdatarow).

So if the SP executes with an error message, then the SSRS can't use it, so you'll need to fix the SP first.

Double-click on the error message, watch the cursor jump to the offending line, and tell us which line it is.

Actually, it is a prvis issue. I fixed it. Thanks, Jim.
The report can run in the reporting manager. But, the data did not show. My manager showed me something like it below, to get the data yesterday. But I do not remember it exactly. I tried the code below, and it did not show the data. Is the code correct?

If exists (select 1 from sys.procedures where name ='sp_rept_GGMExport')
drop procedure dbo.sp_rept_GGMExport

Select allOpen in new window

Yes, as long as the default schema is dbo as it's not referenced in line 1.
Also, you'll want to watch that DROPping the proc doesn't also drop the security assignment, so your script should be drop - create - assign privs OR alter - assign privs.

Thank you, Jim

Thanks for the grade, good luck with your report.  -Jim

btw for SSRS questions you'll want to also add the MS SQL Server zone, as more experts monitor that one where some of them might not also be monitoring the SSRS zone.