ASP.NET水晶报表：从临时表的存储过程负载数据源(ASP.NET & Crystal Repor

我使用了一个存储过程来实现我想要的输出在一个商场/位置显示租户的monhtly销售报告。该存储过程包含多个临时表（由于现有的数据库结构/架构的限制）来完成我需要完成。该过程确实是成功的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查询的生成列，这些列是基于用户的选择。我注释掉那些部分，看看它是否会在水晶报表运行，它没有分离的问题。

问题是：如何可以加入水晶报表的那些列？

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

现在，我想也纳入同一个存储过程，这次使用上述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();

    }

这是错误的，通过在存储亲了上述问题引起的

一个或多个字段不能在结果集中被发现。 使用验证数据库更新报告。 在文件MonthlySalesReport错误{0E90B4CE-8D1A-4712-BE05-9C1DC8CC9ADB}。RPT：行集列无法找到。

CR只能够读什么是您的查询返回。你可以看到类似的问题在这里和这里。所有这些人都有类似的问题，像你这样的瑞奇。阅读，看看它是否可以提供帮助。如果没有，不要浪费你的时间四处找工作。如果我是你。我不得不创建一个新表dbo.MyTempTable并刷新它，当曾经的过程被称为等。

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。然后填充你从你的查询返回什么都临时表。

然后继续并创建另一个程序或干脆视图。喜欢

CREATE PROCEDURE dbo.MyTempTableProcedure

AS 
  BEGIN 
     SELECT * FROM myTempTable
  END

转到您的报告中并更新数据源到MyTempTableProcedure。这是走动。在后端的C＃。打印结果之前先执行该过程。你不会有问题，这是因为你已经与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();

这应该是周围散步。这就是我想，虽然。希望能帮助到你。