rickyProgrammer rickyProgrammer - 1 month ago 11
ASP.NET (C#) Question

asp.net - how to pass a formulated column from SQL stored procedure / query to Crystal Report

Here's the detailed explanation:

I am passing a SQL statement from asp.net to crystal report though sql command. It is a lot easier when the columns in the table are already defined, when the columns are customized or formulated, what I do is create a formula in the formula field in crystal report and it works well. The only problem I am facing is the column called GCS. This column uses case statement to find the existence of data in another table, if there is a data, it will create a computation, otherwise, it will perform another computation. Please see the sql statement

string dstrQuery = @"select b.name , convert(varchar(10), a.date, 101) as Date,
datename(weekday, a.date) as Day,
convert(varchar,cast(SUM(a.surcharge ) as money),1) AS Surcharge,
convert(varchar,cast(SUM(a.senior) as money),1) as senior,
convert(varchar,cast((((SUM(a.senior))/0.20)*0.80) as money),1) as VATExemptSales,
convert(varchar,cast(SUM(a.other) as money),1) as Other,
convert(varchar,cast(SUM(a.refund) as money),1)as Refund,
convert(varchar,cast(SUM(a.void) as money),1) as Void,

CASE WHEN a.date IN (select Z.date from discount z where z.date between '"+ txtdatefrom.Text +"' and '"+ txtdateto.Text +"' and z.tenantcode = '" + hdnRP.Value + "')
THEN (convert(varchar,cast(((sum(a.cash) + SUM(a.charge) + SUM(a.gift)) - (SUM(a.surcharge))) as money),1) )
ELSE convert(varchar,cast(((sum(a.cash) + SUM(a.charge) + SUM(a.gift)+ SUM(a.other)) - (SUM(a.surcharge))) as money),1)
END as GSC,

convert(varchar,cast(SUM(a.cash ) as money),1) as Cash,
convert(varchar,cast(SUM(a.charge ) as money),1) as Charge,
convert(varchar,cast(SUM(a.gift ) as money),1) as Gift

from TENANT b inner join LOCATION c on b.location=c.location inner join DAILY a on a.tenantcode=b.tenantcode

where (a.date between '" + txtdatefrom.Text + "' and '" + txtdateto.Text + "') and (a.tenantcode = '" + hdnRP.Value + "') GROUP BY a.tenantcode, b.name, a.date order by a.date ";


Session["dstrQuery"] = dstrQuery; // the sql query to pass
Session["DateFrom"] = txtdatefrom.Text; // starting date textbox
Session["DateTo"] = txtdateto.Text; // ending date textbox
Session["hdnSearchedLoc"] = hdnLoc.Value; // hidden field of the Location (user searched)
Session["hdnSearchedRP"] = hdnRP.Value; //hidden field of the Tenant's name (use searched)



  • Using session to load the data needed in crystal report



report page .aspx.cs

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
ReportDocument report = new ReportDocument();

protected void Page_Load(object sender, EventArgs e)
{
con.Open();

string dstrQuery = "";

txtdatefrom.Text = Session["DateFrom"].ToString();
txtdateto.Text = Session["DateTo"].ToString();
hdnSearchedLoc.Value = Session["hdnSearchedLoc"].ToString();
hdnSearchedRP.Value = Session["hdnSearchedRP"].ToString();

dstrQuery = Session["dstrQuery"].ToString();




SqlCommand cmd = new SqlCommand(dstrQuery, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable ds = new DataTable();
da.Fill(ds);
report.Load(Server.MapPath("DailySalesTenderReport.rpt"));
report.SetDataSource(ds);
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.DataBind();

report.SetParameterValue("datefrom", txtdatefrom.Text);
report.SetParameterValue("dateto", txtdateto.Text);


con.Close();

}


What can I do? Can formula field be still able to cater my need on the GSC Column? How about SQL expression field? Please help. Thanks in advance

UPDATE:

For a more detailed explantion, My goal is to show a monthly sales reporting of all the tenants in one location, showing the tenants name, and other description, also includes a FORMULATED COLUMNS like Current and Previous Month overall sales, the percentage diffrence of two months name INC/DEC column.

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 -- PARA DI MAG DIVIDE BY ZERO ERROR, DO THIS SAME SA SALES/SQM kung ayaw ng "null" display
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


And using that SP, I was able to achieve my desired output in gridview. Here's a sample output, i juts needed to erase some part for confidentiality purposes.

enter image description here

FOR THE REPORTING FEATURE, I would just like to use the same stored procedure sing crystal report, but setting parameters is giving me trouble. Here's what I have 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();

}


Of course in the .rpt file, I am setting the datasource to the STORED PROCEDURE, while doing this, I am stopped by the asking of CR for me to enter values

enter image description here

struggle is, when i Dont input anything, there is no column created in crystal report, and leave me with no option but to input a predefined value which supposed to be not because values should be coming from User's options. and TO NOTE THAT TWO MONTHS COLUMN SHOULD BE CHANGING OR DYNAMIC.

Answer

Change your Report Datasource From the table you are using to ttx like below

  • Step 1 : Open your DailySalesTenderReport.rpt in your crystal Report .

- Step 2 : Note Table type in your case it will be "Table" not view. Click the "create new connection below."

  • Step 3 : Select "Field Definition option "

  • Step 4 : A window like the one below will pop up. Key in your column and select the type and click add button. Below is how i add the columns upto the 'GSC'.

  • Step 5 : When you finish click the window close it will prompt a message like below. Click yes and save the file with name you like

    • Step 6 : After you save your file. Click the finish button like below.

  • Step 7 : Finally . Select your newly created file , selec your existing source on top. and then click update. Save your report again and it should work fine.

Changing the Report datasource to ttx should work for you. Let me know if there is any issue.

Update

Alternatively, you can simply achieve this with stored procedure, kind of more reliable and easy to debug. JUst change your datasource to SQL stored procedure, but first create the procedure

   CREATE PROCEDURE [dbo].[Myprocedure]
    @datefrom DATETIME= null, -- This may string defending on what you intended.
    @dateTo DATETIME = NULL,
    @hdnRP nvarchar(50), --Size or dataype depend on you.

    AS
      begin

if  (@dateTo = '' or  Len(@dateTo) = 0) AND (@dateFrom = '' or  Len(@dateFrom) = 0) AND (@hdnRP = '' or  Len(@hdnRP ) = 0) )
  begin
    select   b.name , convert(varchar(10), a.date, 101) as Date, 
                         datename(weekday, a.date) as Day,
                         convert(varchar,cast(SUM(a.surcharge ) as money),1) AS Surcharge, 
                         convert(varchar,cast(SUM(a.senior)  as money),1) as senior, 
                         convert(varchar,cast((((SUM(a.senior))/0.20)*0.80)  as money),1) as VATExemptSales,
                         convert(varchar,cast(SUM(a.other)  as money),1) as Other, 
                         convert(varchar,cast(SUM(a.refund)  as money),1)as Refund, 
                         convert(varchar,cast(SUM(a.void)  as money),1) as Void, 

                         CASE WHEN a.date IN (select Z.date from discount z) 
                          THEN (convert(varchar,cast(((sum(a.cash) + SUM(a.charge) + SUM(a.gift)) - (SUM(a.surcharge))) as money),1) )
                          ELSE convert(varchar,cast(((sum(a.cash) + SUM(a.charge) + SUM(a.gift)+ SUM(a.other)) - (SUM(a.surcharge))) as money),1) 
                          END as GSC, 

                        convert(varchar,cast(SUM(a.cash ) as money),1) as Cash,  
                        convert(varchar,cast(SUM(a.charge ) as money),1) as Charge, 
                        convert(varchar,cast(SUM(a.gift ) as money),1) as Gift

                        from  TENANT b inner join LOCATION c on b.location=c.location inner join DAILY a on a.tenantcode=b.tenantcode   

                    GROUP BY a.tenantcode, b.name, a.date order by  a.date "

end

ELSE 
 begin

   select   b.name , convert(varchar(10), a.date, 101) as Date, 
                         datename(weekday, a.date) as Day,
                         convert(varchar,cast(SUM(a.surcharge ) as money),1) AS Surcharge, 
                         convert(varchar,cast(SUM(a.senior)  as money),1) as senior, 
                         convert(varchar,cast((((SUM(a.senior))/0.20)*0.80)  as money),1) as VATExemptSales,
                         convert(varchar,cast(SUM(a.other)  as money),1) as Other, 
                         convert(varchar,cast(SUM(a.refund)  as money),1)as Refund, 
                         convert(varchar,cast(SUM(a.void)  as money),1) as Void, 

                         CASE WHEN a.date IN (select Z.date from discount z where z.date between            @datefrom  and @dateTo  and z.tenantcode = @hdnRP) 
                          THEN (convert(varchar,cast(((sum(a.cash) + SUM(a.charge) + SUM(a.gift)) - (SUM(a.surcharge))) as money),1) )
                          ELSE convert(varchar,cast(((sum(a.cash) + SUM(a.charge) + SUM(a.gift)+ SUM(a.other)) - (SUM(a.surcharge))) as money),1) 
                          END as GSC, 

                        convert(varchar,cast(SUM(a.cash ) as money),1) as Cash,  
                        convert(varchar,cast(SUM(a.charge ) as money),1) as Charge, 
                        convert(varchar,cast(SUM(a.gift ) as money),1) as Gift

                        from  TENANT b inner join LOCATION c on b.location=c.location inner join DAILY a on a.tenantcode=b.tenantcode   

                       where (a.date between @dateFrom and @dateTo)  and (a.tenantcode = @hdnRP) GROUP BY a.tenantcode, b.name, a.date order by  a.date "


  END    
End 

After you create your procedure, go to your .rpt report , open and change the data source to SQL data source, navigate through and select your procedure , then update just like with ttx but here Sql data source. When you update, Your columns will come under the report and under the parameter , u would have have three parameters. @datFrom, @datTo, @hdnRP . Modify your page load you will not be needing the dataset.

  ReportDocument report = new ReportDocument();
  report.SetParameterValue(0, Loc);
  report.SetParameterValue(1, Label2.Text);
  report.SetParameterValue(2, Label3.Text);
  report.SetParameterValue(3, Label4.Text);
  crViewer.ReportSource = report; //crViewver is your crystal report viewer name.

I suppose you know how to use your parameters also. Hope this, works.

Update

I think i have seen something similar. Its bcos of the complexity of ur procedure. There are similar questions on how to supressed this here and here

Update

If your store-procedure does what you want. You dont have to change datasource each time. Actually i forgot since during ttx . You can just use Dataset and some trick i normally do. Create and empty database table which you dont need. Add your report columns in the table just like ttx file and set your report source to the emty table which you will never use. And then from the source code. Pass Dataset as your report source example.

If your report has three columns . Name, user name and password. Then create SQL table with columns name , user name, password. Use your table just for column names. Set the report datasource to the empty table. Make sure its empty.

SqlConnection conn = new SqlConnection("Your Connection String");
SqlCommand comm = new SqlCommand("Your Store procedure name", conn); //you can use string query 
comm.CommandType = CommandType.StoredProcedure; //Or Text when you use sql like when u use ttx b4.

SqlDataAdapter da = new SqlDataAdapter(comm);

da.SelectCommand.CommandTimeout =0;
DataSet ds = new DataSet("Your dataset name");
da.Fill(ds);

if(ds.Tables[0].Rows.Count > 0)
{
   ReportDocument doc = new ReportDocument(); //Your report Doc instance
   doc.Load("Report FIle path");

   //Below is important . Here you overwrite the datasource
   doc.Database.Tables[0].SetDataSource(ds.Tables[0]); //Set your report datasource,
   report.SetParameterValue(0, Loc); //Set your parameters; //You can manually add the parameters in   your report.

   crViewer.ReportSource = doc; //Set your Report Viewer source .  And you are good to go. 

}

You can change This report Source how ever you like from query or store procedure as per as the columns are the same. Just override the datasource and you are done. Hope it helps.

Comments