问题描述
我使用了一个存储过程来实现我想要的输出在一个商场/位置显示租户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.
这里的存储过程
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
我发现问题出在哪里IS COMING FROM
这最后一部分从存储过程是冲突的来源。在那里我做了参数部分 - @MonthCurrent和@月previous查询的生成列，这些列是基于用户的选择。我孤立注释掉那些部分的问题，看它是否将在水晶报表运行，它没有。
I HAVE FOUND OUT WHERE THE PROBLEM IS COMING FROMThis 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.
的问题是：？在水晶报表如何加入这些列
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 '
样本屏幕截图成功运行SP在GridView控件
现在，我想也纳入同一个存储过程，这次使用该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();
}
这是错误的被规定的问题，存储亲造成
This is the error the is caused by the stated problem in the stored pro
一个或多个字段不能在结果集中找到。使用验证数据库更新报告。在文件MonthlySalesReport错误{0E90B4CE-8D1A-4712-BE05-9C1DC8CC9ADB} .RPT：行集列找不到的
推荐答案
CR只能够读出什么是您的查询返回。你可以看到类似的问题和<一个href=\"http://stackoverflow.com/questions/13464503/using-a-stored-procedure-containing-dynamic-sql-to-create-a-report-in-c-sharp-r?rq=1\">Here.所有这些人都有类似的问题，像你这样的瑞奇。阅读，看看它是否可以提供帮助。如果没有，不要浪费你的时间四处找工作。如果我是你。我不得不创建一个新表 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 '
在上面你总是清空不是Temptable。然后填充你从你的查询返回什么都临时表。
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和放大器;水晶报表：从临时表的存储过程数据源的负载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！