Hi satabeach,
Make use of ReportParameter to set Total Sales, Total Discount, Final Price in the report.
Refer below article to use Parameter.
Check this example. Now please take its reference and correct your code.
Report design and the parameters with dataset
HTML
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<rsweb:ReportViewer ID="ReportViewer1" runat="server">
</rsweb:ReportViewer>
</form>
</body>
</html>
Namespaces
C#
using System.Data;
using System.Data.OleDb;
using System.Linq;
using Microsoft.Reporting.WebForms;
VB.Net
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.Reporting.WebForms
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Sales.accdb;Persist Security Info = False; ";
OleDbConnection con = new OleDbConnection(str);
OleDbCommand cmd = new OleDbCommand("SELECT Item,SRate,Qty,TotalPrice,SalesID,Discount FROM Sales", con);
OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
oda.Fill(dt);
decimal totalSales = Convert.ToDecimal(dt.Compute("SUM(TotalPrice)", string.Empty));
DataTable res = dt.AsEnumerable().GroupBy(r => r["SalesID"]).Select(g => g.First()).CopyToDataTable();
decimal totalDiscount = Convert.ToDecimal(res.Compute("SUM(Discount)", string.Empty));
decimal finalPrice = totalSales + totalDiscount;
Sales dsSales = new Sales();
foreach (DataRow dr in dt.Rows)
{
dsSales.Tables[0].Rows.Add(dr["Item"].ToString(), dr["SRate"].ToString(), dr["Qty"], dr["TotalPrice"]);
}
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
ReportDataSource datasource = new ReportDataSource("Sales", dsSales.Tables[0]);
ReportParameterCollection reportparameter = new ReportParameterCollection();
reportparameter.Add(new ReportParameter("TotalSales", totalSales.ToString()));
reportparameter.Add(new ReportParameter("TotalDiscount", "-" + totalDiscount.ToString()));
reportparameter.Add(new ReportParameter("FinalPrice", finalPrice.ToString()));
ReportViewer1.LocalReport.SetParameters(reportparameter);
ReportViewer1.LocalReport.Refresh();
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Sales.accdb;Persist Security Info = False; "
Dim con As OleDbConnection = New OleDbConnection(str)
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Item,SRate,Qty,TotalPrice,SalesID,Discount FROM Sales", con)
Dim oda As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
oda.Fill(dt)
Dim totalSales As Decimal = Convert.ToDecimal(dt.Compute("SUM(TotalPrice)", String.Empty))
Dim res As DataTable = dt.AsEnumerable().GroupBy(Function(r) r("SalesID")).[Select](Function(g) g.First()).CopyToDataTable()
Dim totalDiscount As Decimal = Convert.ToDecimal(res.Compute("SUM(Discount)", String.Empty))
Dim finalPrice As Decimal = totalSales + totalDiscount
Dim dsSales As Sales = New Sales()
For Each dr As DataRow In dt.Rows
dsSales.Tables(0).Rows.Add(dr("Item").ToString(), dr("SRate").ToString(), dr("Qty"), dr("TotalPrice"))
Next
ReportViewer1.ProcessingMode = ProcessingMode.Local
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")
Dim datasource As ReportDataSource = New ReportDataSource("Sales", dsSales.Tables(0))
Dim reportparameter As ReportParameterCollection = New ReportParameterCollection()
reportparameter.Add(New ReportParameter("TotalSales", totalSales.ToString()))
reportparameter.Add(New ReportParameter("TotalDiscount", "-" + totalDiscount.ToString()))
reportparameter.Add(New ReportParameter("FinalPrice", finalPrice.ToString()))
ReportViewer1.LocalReport.SetParameters(reportparameter)
ReportViewer1.LocalReport.Refresh()
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(datasource)
End If
End Sub
Screenshot
