Click here to Skip to main content
15,942,964 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
-- =============================================
-- 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?
Posted
Updated 8-Jul-14 21:36pm
v2
Comments
Dilan Shaminda 9-Jul-14 4:20am    
"when it comes to actual" means? and can you post your coding part for binding data for your sub-report?
Chester Costa 9-Jul-14 4:46am    
i've done it already sir,i just removed the part where i add another full join using the third table.then that's it. i just used the subreport linking with my parameter on main report.
Dilan Shaminda 9-Jul-14 5:23am    
Nice :-) happy coding...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900