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:
ALTER PROCEDURE [dbo].[EndOfDayReport]
@dtStartDate DATETIME,
@dtEndDate DATETIME,
@sShiftNumber nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
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'
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]
,GuestTrans.remainingbal,
sum(transactiondetails.breakfast)as [Breakfast],
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?