ASP.NET & Crystal Report: load datasource from a stored procedure with temp tables
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
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.
QUESTION IS: How can join those columns in 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 '
Sample screenshot, successfully run the SP in gridview
Now, I would like to incorporate also the same stored procedure, this time filling data in Crystal Report, using the said SP as my datasource. Here's what have I started
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
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 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
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.