ASP.NET &Crystal Report:从带有临时表的存储过程加载数据源 [英] ASP.NET & Crystal Report: load datasource from a stored procedure with temp tables
问题描述
我使用了一个存储过程来实现我想要的输出，以显示一个商场/位置的租户的月度销售报告.存储过程包含多个临时表(由于现有数据库结构/架构的限制)来完成我需要完成的工作.该过程在gridview中确实成功.
I have used a stored procedure to achieve my desired output in displaying the monhtly sales report of tenants on one mall / location. The stored procedure contains multiple temp tables (due to the limitation of the existing database structure / schema) to accomplish what I need to accomplish. The procedure was indeed successful in gridview.
这是存储过程
USE [DATABASENAME]
GO
ALTER PROCEDURE [dbo].[spName]
// parameters
@Location int, // the location number
@CurrentMonthStart date ,
@MonthCurrent varchar(20),
@MonthPrevious varchar(20)
AS
BEGIN
//Using the CurrentMonthStart data, I formulated the other essential variable needed for the query to run
declare @PreviousMonthStart date
declare @PreviousMonthEnd date
declare @CurrentMonthEnd date
declare @query varchar (8000)
set @PreviousMonthStart = convert(varchar(10), DATEADD(m,-1, @CurrentMonthStart) , 101)
set @PreviousMonthEnd = convert(varchar(10), DATEADD(d,-1, @CurrentMonthStart) , 101)
set @CurrentMonthEnd = convert(varchar(10), DATEADD(d, -1, DATEADD(m,1, @CurrentMonthStart)) , 101)
// i used temp table several times
select b.tenantcode, b.date , SUM(a.other)as discount
INTO #NewDiscountTable
from DAILY a INNER JOIN DISCOUNT b on a.date = b.date and a.tenantcode = b.tenantcode
WHERE b.date between @PreviousMonthStart AND @CurrentMonthEnd and A.location = @Location
group by b.date, b.tenantcode
order by b.tenantcode
select tenantcode , SUM(discount) as Approved_Disc
into #NewDiscountTableFinal
from #NewDiscountTable
where date between @PreviousMonthStart AND @PreviousMonthEnd
group by tenantcode
select tenantcode , SUM(discount) as Approved_Disc2
into #NewDiscountTableFinal2
from #NewDiscountTable
where date between @CurrentMonthStart AND @CurrentMonthEnd
group by tenantcode
select b.sqm as 'FLOOR AREA/SQM', b.name as 'RETAIL PARTNERS' ,
convert(varchar(10), a.date, 101) as Date, datename(weekday, a.date) as Day,
((sum(a.cash) + SUM(a.charge) + SUM(a.gift)+ SUM(a.other)) - (SUM(a.surcharge))) as GSC,
a.location , a.tenantcode
into #NewDailySalesTenderTable
from TENANT b inner join LOCATION c on b.location=c.location inner join DAILY a on a.tenantcode=b.tenantcode
where a.location = @Location and b.status > 1 and
a.date BETWEEN @PreviousMonthStart and @CurrentMonthEnd
GROUP BY b.name, a.date , a.location , a.tenantcode , b.sqm
order by b.name, A.DATE
select [FLOOR AREA/SQM], [RETAIL PARTNERS] , Tenantcode, SUM(GSC) as GSCwithOtherDisc
into #NewDailySalesTenderTableFinal
from #NewDailySalesTenderTable
where date BETWEEN @PreviousMonthStart and @PreviousMonthEnd
GROUP BY [Retail Partners], tenantcode, [FLOOR AREA/SQM]
ORDER BY [Retail Partners]
select [FLOOR AREA/SQM], [RETAIL PARTNERS] , Tenantcode, SUM(GSC) as GSCwithOtherDisc2
into #NewDailySalesTenderTableFinal2
from #NewDailySalesTenderTable
where date between @CurrentMonthStart AND @CurrentMonthEnd
GROUP BY [Retail Partners], tenantcode, [FLOOR AREA/SQM]
ORDER BY [Retail Partners]
select A.[FLOOR AREA/SQM] , a.[Retail Partners],
case when a.tenantcode in (select d.tenantcode from #NewDiscountTableFinal2 d ) -- case within case kasi pag 0 yung divisor may error
then Round(((c.GSCwithOtherDisc2 - d.Approved_Disc2 )/(case when a.[FLOOR AREA/SQM] = 0 then null else (a.[FLOOR AREA/SQM]) end) ),0)
else Round(((c.GSCwithOtherDisc2 - 0)/ (case when a.[FLOOR AREA/SQM] = 0 then null else (a.[FLOOR AREA/SQM]) end) ),0)
end as 'SALES/SQM',
case when a.tenantcode in (select d.tenantcode from #NewDiscountTableFinal2 d )
then Round((c.GSCwithOtherDisc2 - d.Approved_Disc2 ),0)
else Round((c.GSCwithOtherDisc2 - 0),0)
end as CurrentMonth,
case when a.tenantcode in (select b.tenantcode from #NewDiscountTableFinal b )
then Round((a.GSCwithOtherDisc - b.Approved_Disc ),0)
else Round((a.GSCwithOtherDisc - 0),0)
end as PreviousMonth
--case when a.tenantcode in (select b.tenantcode from #NewDiscountTableFinal b )
--then Round((((((c.GSCwithOtherDisc2 - d.Approved_Disc2 )- (a.GSCwithOtherDisc - b.Approved_Disc )) / (a.GSCwithOtherDisc - b.Approved_Disc )) * 100)),0)
--else Round((C.GSCwithOtherDisc2 - 0),0)
--end as '%INC/DEC'
into #FinalResult
FROM #NewDailySalesTenderTableFinal a left join #NewDiscountTableFinal b on a.tenantcode = b.tenantcode join
#NewDailySalesTenderTableFinal2 c on a.tenantcode = c.tenantcode left join #NewDiscountTableFinal2 d on c.tenantcode = d.tenantcode
set @query = 'select [Retail Partners],[FLOOR AREA/SQM], ' +
'replace(convert(varchar,cast(([SALES/SQM]) as money),1), ''.00'','''') as ''SALES/SQM'',' +
'replace(convert(varchar,cast((CurrentMonth) as money),1), ''.00'','''') as ' + @MonthCurrent +
',replace(convert(varchar,cast((PreviousMonth) as money),1), ''.00'','''') as ' + @MonthPrevious +
',case when PreviousMonth = 0
then ''N/A''' +
'else replace(convert(varchar,cast(Round((((CurrentMonth-PreviousMonth)/ PreviousMonth ) *100),0) as money),1), ''.00'','''')
end as ''%INC/DEC'' ' +
'from #FinalResult '
execute(@query)
END
我已经找到问题的根源存储过程的最后一部分是冲突的根源.我将参数 - @MonthCurrent 和 @MonthPrevious 设为查询的生成列的部分，这些列基于用户选择的内容.我通过注释掉这些部分来隔离问题，看看它是否会在 Crystal Report 中运行，并且确实如此.
I HAVE FOUND OUT WHERE THE PROBLEM IS COMING FROM This last part from the stored procedure is the source of the conflict. The part where I made the PARAMETERS - @MonthCurrent and @MonthPrevious the generated column of the query, those columns are based on what the user has selected. I isolated the problem by commenting out those part to see if it will run in Crystal Report and it did.
问题是:如何在 Crystal REPORT 中加入这些列?
set @query = 'select [Retail Partners],[FLOOR AREA/SQM], ' +
'replace(convert(varchar,cast(([SALES/SQM]) as money),1), ''.00'','''') as ''SALES/SQM'',' +
'replace(convert(varchar,cast((CurrentMonth) as money),1), ''.00'','''') as ' + @MonthCurrent +
',replace(convert(varchar,cast((PreviousMonth) as money),1), ''.00'','''') as ' + @MonthPrevious +
',case when PreviousMonth = 0
then ''N/A''' +
'else replace(convert(varchar,cast(Round((((CurrentMonth-PreviousMonth)/ PreviousMonth ) *100),0) as money),1), ''.00'','''')
end as ''%INC/DEC'' ' +
'from #FinalResult '
示例截图，在gridview中成功运行SP
现在，我还想合并相同的存储过程，这次在 Crystal Report 中填充数据，使用上述 SP 作为我的数据源.这是我的开始
protected void Page_Load(object sender, EventArgs e)
{
con.Open();
//I pass the values of parameter from the page to the print page using Session
Label1.Text = Session["Location"].ToString();
int Loc = Convert.ToInt32(Label1.Text);
Label2.Text = Session["CurrentMonthStart"].ToString();
DateTime dt = Convert.ToDateTime(Label2.Text);
Label3.Text = Session["MonthCurrent"].ToString();
Label4.Text = Session["MonthPrevious"].ToString();
report.Load(Server.MapPath("MonthlySalesReport.rpt"));
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.ReuseParameterValuesOnRefresh = true;
CrystalReportViewer1.DataBind();
report.SetParameterValue(0, Loc);
report.SetParameterValue(1, dt);
report.SetParameterValue(2, Label3.Text);
report.SetParameterValue(3, Label4.Text);
con.Close();
}
这是由存储的pro中所述问题引起的错误
This is the error the is caused by the stated problem in the stored pro
在结果集中找不到一个或多个字段.使用验证数据库更新报告.文件 MonthlySalesReport {0E90B4CE-8D1A-4712-BE05-9C1DC8CC9ADB}.rpt 中的错误:找不到行集列.
推荐答案
CR 只能读取查询返回的内容.您可以在此处和这里.所有这些人都有和你一样的问题 ricky.阅读并看看它是否有帮助.如果没有，请不要浪费时间寻找解决方法.如果我是你.我创建了一个新表 dbo.MyTempTable 并在调用过程时刷新它.
CR is only able to read what is returned by your query. You can see similar issues Here and Here. All these people have similar issue like you ricky. Read and see if it can help. If it doesn't, don't waste your time find a work around. If i were you. I had create a new table dbo.MyTempTable and refresh it when ever the procedure is called like.
DELETE FROM dbo.MyTempTable
INSERT INTO dbo.myTempTable(Col1, Col 2, Col3, Col4 , Col5, Col6 ..)
select [Retail Partners],[FLOOR AREA/SQM], ' +
'replace(convert(varchar,cast(([SALES/SQM]) as money),1), ''.00'','''') as ''SALES/SQM'',' +
'replace(convert(varchar,cast((CurrentMonth) as money),1), ''.00'','''') as ' + @MonthCurrent +
',replace(convert(varchar,cast((PreviousMonth) as money),1), ''.00'','''') as ' + @MonthPrevious +
',case when PreviousMonth = 0
then ''N/A''' +
'else replace(convert(varchar,cast(Round((((CurrentMonth-PreviousMonth)/ PreviousMonth ) *100),0) as money),1), ''.00'','''')
end as ''%INC/DEC'' ' +
'from #FinalResult '
在上面你总是清空临时表.然后用您从查询中返回的内容填充临时表.
In the above you always empty the temptable. Then populate the temp table with what ever you return from your query.
然后继续创建另一个过程或简单地查看.喜欢
Then proceed and create another procedure or simply view. like
CREATE PROCEDURE dbo.MyTempTableProcedure
AS
BEGIN
SELECT * FROM myTempTable
END
转到您的报告并将数据源更新为 MyTempTableProcedure.这是四处走走.在您的后端 c# 中.在打印结果之前先执行该过程.你不会有这个问题，因为你已经用 gridview 做了.
Go to your report and update the datasource to MyTempTableProcedure. This is walk around. In your backend c#. Execute the procedure first before printing the result. You will not have problem with this because you already did with gridview.
Label1.Text = Session["Location"].ToString();
int Loc = Convert.ToInt32(Label1.Text);
Label2.Text = Session["CurrentMonthStart"].ToString();
DateTime dt = Convert.ToDateTime(Label2.Text);
Label3.Text = Session["MonthCurrent"].ToString();
Label4.Text = Session["MonthPrevious"].ToString();
//EXECCUTE the procedure here
using(SqlCommand cmd = new SqlCommand("spName",con)
{
cmd.CommandType = CommandType .StoredProcedure;
cmd.Parameters.AddwithValue("@Location", LOC);
cmd.Parameters.AddwithValue("@CurrentMonthStart ", Label3.Text);
cmd.Parameters.AddwithValue("@date ", dt);
cmd.Parameters.AddwithValue("@CurrentMonthStart ", Label4.Text);
cmd.ExecuteNonQuery();
}
report.Load(Server.MapPath("MonthlySalesReport.rpt"));
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.ReuseParameterValuesOnRefresh = true;
con.Close();
这应该是四处走走.这就是我的想法.希望有帮助.
This should be a walk around. This is what i am thinking though. Hope it helps.
这篇关于ASP.NET &Crystal Report:从带有临时表的存储过程加载数据源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
