ASP.NET& Crystal Report：从具有临时表的存储过程加载数据源 [英] ASP.NET & Crystal Report: load datasource from a stored procedure with temp tables
问题描述
我使用一个存储过程来实现我想要的输出，显示一个商场/位置的租户的monhtly销售报告。存储过程包含多个临时表（由于现有数据库结构/模式的限制）来完成我需要完成的任务。该程序在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.
以下是存储过程：
Here's the stored procedure
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
我发现问题来自
存储过程的最后一部分是冲突的根源。我在其中我做了PARAMETERS - @MonthCurrent和@MonthPrevious生成的查询的列的那些部分，这些列是基于用户已选择的。我通过注释这些部分，以查看它是否将运行在水晶报表中，它确实是孤立的问题。
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.
问题是：如何连接这些列在水晶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：找不到行集列。
One or more fields could not be found in the result set. Use Verify Database to update the report. Error in File MonthlySalesReport {0E90B4CE-8D1A-4712-BE05-9C1DC8CC9ADB}.rpt: The rowset column could not be found.
推荐答案
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屋！
