Crystal Report stored procedure with subreport

See more:

VB

hello again experts! can anyone tell me how to retrieve data from database using stored procedure for my main report and adding a subreport to my main report.. my code below is my stored procedure to my main report:

-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[EndOfDayReport] 
	-- Add the parameters for the stored procedure here
	@dtStartDate DATETIME,
	@dtEndDate DATETIME,
	@sShiftNumber nvarchar(50)
	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT    transactiondetails.foilionum
			  ,GuestTrans.hours
			  ,GuestTrans.NightsConsumed
			  ,transactionType = 
			  case transactiondetails.transactionType
			  when 'CREDIT CARD' then 'CC'
			  when 'DEBIT CARD' then 'DC'
			  when 'CASH' then 'CASH'
			  when 'GIFT CHECK' then 'GC'
			  when 'CHECK' then 'CHECK'
			  when 'MULTPLE PAYMENT' then 'MP'
			  when 'FOC' then 'FOC'
			  when 'A/R SEND BILL' then 'SEND BILL'
			  when 'A/R CHARGE TO' then 'CHARGE TO'
			  when 'HOUSE USE' then 'HOUSE USE'
			  when 'DD' then 'DD'
			  end
			  ,GuestTrans.roomnum
			  ,GuestTrans.rate
			  ,discounttype=
			  case GuestTrans.discounttype
			  when 'EUROMILES' then 'EURO'
			  when 'Cash Discount' then 'CD'
			  when 'Senior Citizen' then 'SCD'
			  end
			  ,GuestTrans.discountedBy
			  ,GuestTrans.checkin
			  ,GuestTrans.checkout
			  ,GuestTrans.pax
			  ,GuestTrans.company
			  ,GuestTrans.AmountRefund
			  ,roomtype = 
			  case GuestTrans.roomtype
				when 'Suite 1' then 'Suite 1'
				when 'Suite 2' then 'Suite 2'
				when 'Premium' then 'Premium'
				when 'Deluxe' then 'Deluxe'
				--when 'Euro Suite 2' then 'ES2'
				--when 'Euro Suite Twin' then 'ESTB'
				--when 'PStudio' then 'PSUT'
				--when 'PStandard' then 'PSTA'
				--when 'PEuro Suite 2' then 'PES2'
			END
			  
			  ,sum(transactiondetails.discount) as [discount]
			  ,paymentType =
			   case GuestTrans.paymentType
			  when 'CREDIT CARD' then 'CC'
			  when 'DEBIT CARD' then 'DC'
			  when 'CASH' then 'CASH'
			  when 'GIFT CHECK' then 'GC'
			  when 'CHECK' then 'CHECK'
			  when 'MULTPLE PAYMENT' then 'MP'

			  end
			  ,industry=
			  case GuestTrans.industry
			  when 'CORPORATE ACCOUNT' then 'CORP'
			  when 'TRAVEL AGENCY' then 'TA'
			  when 'INDIVIDUAL WALK IN' then 'WI'
			  when 'ONLINE BOOKING' then 'OB'
			  when 'GOVERNMENT' then 'GOVT'
			  
			  end
			  
			  ,GuestTrans.currency
			  , GuestTrans.overtime
			  ,GuestTrans.shift
			  ,GuestTrans.checkoutShift
			  ,transactiondetails.username
			  ,transactiondetails.supervisor
			  ,transactiondetails.checkInStat
			  ,transactiondetails.checkOutStat
			  ,(SELECT SUM(total) FROM  foodordertbl 
				where folioid = transactiondetails.foilionum
				and date BETWEEN @dtStartDate AND @dtEndDate
				) as [foodOrdered]
				--,(select count(roomtype) from ListOfCheckout where shift=@sShiftNumber and checkoutdate between  @dtStartDate AND @dtEndDate) as roomcount
			  ,GuestTrans.remainingbal,
			  sum(transactiondetails.breakfast)as [Breakfast],
			  --SUM(transactiondetails.overtime) AS [total_overtime],
			  sum(transactiondetails.misc) as [total_misc],
			  sum(transactiondetails.extend) as [total_extend],
			  sum(transactiondetails.roomamount) as [total_room],
			  sum(transactiondetails.extend) + sum(transactiondetails.misc) + sum(transactiondetails.breakfast) + sum(transactiondetails.roomamount) AS [Grand_total]
			  
FROM          GuestTrans FULL OUTER JOIN
              transactiondetails ON GuestTrans.foilionum = transactiondetails.foilionum full outer join ListOfCheckout on GuestTrans.foilionum=ListOfCheckout.foilionum
WHERE        (transactiondetails.transactiondatetime BETWEEN @dtStartDate AND @dtEndDate AND transactiondetails.checkInStat=1 AND transactiondetails.shift=@sShiftNumber)

GROUP BY GuestTrans.roomnum, 
		 GuestTrans.overtime, 
		 GuestTrans.roomtype,
		 GuestTrans.hours,
		 GuestTrans.paymentType,
		 GuestTrans.industry,
		 GuestTrans.discount,
		 GuestTrans.remainingbal,
		 GuestTrans.currency,
		 GuestTrans.discounttype,
		 GuestTrans.rate,
		 GuestTrans.pax,
		 GuestTrans.checkin,
		GuestTrans.checkout,
		GuestTrans.company,
		 GuestTrans.discountedBy,
		 transactiondetails.foilionum,
		transactiondetails.checkInStat,
		transactiondetails.checkOutStat ,
		transactiondetails.transactionType,
		transactiondetails.username,
		GuestTrans.NightsConsumed,
		transactiondetails.supervisor,
		GuestTrans.AmountRefund,
		GuestTrans.shift,
		GuestTrans.checkoutShift
ORDER BY GuestTrans.roomnum

END

on my subreport, i linked my parameter named @sShiftNumber. upon previewing my report i can see the changes that i want, but when it comes to actual, it says that "Failed to retrieve data in database". what is wrong with my stored procedure?