Crystal报表存储过程与子报表

本文介绍了Crystal报表存储过程与子报表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 再次问好专家！任何人都可以告诉我如何使用我的主报告的存储过程从数据库中检索数据并将子报告添加到我的主报告..我的代码是我的主报告存储过程： - =========== ================================== - 作者：< author ,, name> - 创建日期：< create> - 说明：< description ,,> - ===== ======================================== ALTER PROCEDURE [dbo]。[EndOfDayReport] - - 添加参数此处的存储过程 @ dtStartDate DATETIME ， @ dtEndDate DATETIME ， @ sShiftNumber nvarchar （ 50 ） AS BEGIN - 添加SET NOCOUNT ON以防止额外的结果集 - 干扰SELECT语句。 SET NOCOUNT ON ; - 在此处插入程序语句 SELECT transactiondetails.foilionum ，GuestTrans.hours ，GuestTrans.NightsConsumed ，transactionType = case transactiondetails.transactionType ' CREDIT CARD' 然后 ' CC' ' DEBIT CARD' 然后 ' DC' ' CASH' 然后 ' CASH' 何时 ' GIFT CHECK' 然后 ' GC' 当 ' CHECK' 然后 ' CHECK' ' MULTPLE PAYMENT' 然后 ' MP' ' FOC' 然后 ' FOC' ' A / R SEND BILL' 然后 ' SEND BILL' ' A / R CHARGE TO' 然后 ' 收费' ' HOUSE USE' 然后 ' HOUSE USE' ' DD' 然后 ' DD' end ，GuestTrans.roomnum ，GuestTrans.rate ，discounttype = case GuestTrans.discounttype ' EUROMILES' 然后 ' EURO' ' 现金折扣' 然后 ' CD' ' 老年人' 然后 ' SCD' end ，Gues tTrans.discountedBy ，GuestTrans.checkin ，GuestTrans.checkout ，GuestTrans.pax ，GuestTrans.company ，GuestTrans.AmountRefund ，roomtype = case GuestTrans.roomtype ' 套件1' 然后 ' Suite 1' ' Suite 2' 然后 ' Suite 2' ' Premium' 然后 ' Premium' 当 ' Deluxe' 然后 ' Deluxe' - 当'Euro Suite 2'然后'ES2' - 当'Euro Suite Twin'然后'ESTB' - 当'PStudio'然后'PSUT' - 当'PStandard'然后'PSTA' - 当'PEuro Suite 2'然后' PES2' END ，sum（transactiondetails.discount） as [discount] ，paymentType = case GuestTra ns.paymentType ' CREDIT CARD' ' CC' 当 ' DEBIT CARD' 然后 ' DC' 当 ' CASH' 然后 ' CASH' 当 ' GIFT CHECK' 然后 ' GC' 当 ' CHECK' 然后 ' 检查' 何时 ' MULTPLE PAYMENT' 然后 ' MP' end ，industry = case GuestTrans.industry when ' 公司帐户' 然后 ' CORP' ' TRAVEL AGENCY' then ' TA' 何时 ' INIVIDUAL WALK IN' ' WI' 何时 ' ONLINE BOOKING' ' OB' 何时 ' 政府' 然后 ' GOVT' end ，GuestTrans.currency ，GuestTrans.overtime ，GuestTrans.shift ，GuestTrans.checkoutShift ，transactiondetails.username ，transactiondetails.supervisor ，transactiondetails.checkInStat ，transactiondetails.checkOutStat ，（ SELECT SUM（总计） FROM foodordertbl 其中 folioid = transactiondetails.foilionum 和 date BETWEEN @ dtStartDate AND @ dtEndDate ） as [foodOrdered] - ，（选择计数） （roomtype）来自ListOfCheckout，其中shift = @ sShiftNumber和@dtStartDate与@dtEndDate之间的checkoutdate）作为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 完整 外部 加入 ListOfCheckout 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。小时， GuestTrans.paymentType， GuestTrans.industry， GuestTrans.discount， GuestTrans.remainingbal， GuestTrans.currency， GuestTrans.discounttype， Gues tTrans.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子报表，我链接了名为@sShiftNumber的参数。在预览我的报告时，我可以看到我想要的更改，但是当涉及实际时，它会说无法检索数据库中的数据。我的存储过程出了什么问题？解决方案 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)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON; -- Insert statements for procedure hereSELECT 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.roomtypewhen '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 foodordertblwhere folioid = transactiondetails.foilionumand 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.foilionumWHERE (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.checkoutShiftORDER BY GuestTrans.roomnumENDon 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? 解决方案 这篇关于Crystal报表存储过程与子报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！