Hi vail,
I have created a sample which full fill your requirement you need to modify the code according to your need.
I have taken a reference from below articles for achieving the task.
SQL
CREATE PROCEDURE SummaryDetails
AS
BEGIN
DECLARE @OrderIncome AS TABLE (OrderDate DATETIME,OrderNo INT,PaymentMethod VARCHAR(50),Amount FLOAT,OrderClosingDate DATETIME)
INSERT INTO @OrderIncome VALUES(GETDATE(),1,'Online',2538.3,'09/14/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),2,'Cash',45225,'06/25/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),3,'Cheque',7585,'01/24/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),4,'Online',78855,'07/30/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),5,'Cheque',4545,'02/15/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),6,'Cash',24554,'09/06/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),7,'Online',4575,'03/22/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),8,'Cheque',9886,'07/11/2017')
INSERT INTO @OrderIncome VALUES(GETDATE(),9,'Online',7878,'10/14/2017')
DECLARE @Cash VARCHAR(50)
DECLARE @Online VARCHAR(50)
DECLARE @Cheque VARCHAR(50)
SET @Cash = (SELECT SUM(Amount) FROM @OrderIncome WHERE PaymentMethod = 'Cash' AND OrderClosingDate BETWEEN '01/24/2017' AND '09/14/2017')
SET @Online = (SELECT SUM(Amount) FROM @OrderIncome WHERE PaymentMethod = 'Online' AND OrderClosingDate BETWEEN '01/24/2017' AND '09/14/2017')
SET @Cheque = (SELECT SUM(Amount) FROM @OrderIncome WHERE PaymentMethod = 'Cheque' AND OrderClosingDate BETWEEN '01/24/2017' AND '09/14/2017')
SELECT @Cash 'Cash',@Online 'Online',@Cheque 'Cheque'
END
HTML
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager runat="server" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server">
</rsweb:ReportViewer>
</div>
</form>
</body>
</html>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
Summary dsCustomers = GetData();
ReportDataSource datasource = new ReportDataSource("Summary", dsCustomers.Tables[0]);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
}
}
private Summary GetData()
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand("SummaryDetails");
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
sda.SelectCommand = cmd;
using (Summary dsCustomers = new Summary())
{
sda.Fill(dsCustomers, "DataTable1");
return dsCustomers;
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
ReportViewer1.ProcessingMode = ProcessingMode.Local
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")
Dim dsCustomers As Summary = GetData()
Dim datasource As New ReportDataSource("Summary", dsCustomers.Tables(0))
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(datasource)
End If
End Sub
Private Function GetData() As Summary
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As New SqlCommand("SummaryDetails")
Using con As New SqlConnection(conString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
sda.SelectCommand = cmd
Using dsCustomers As New Summary()
sda.Fill(dsCustomers, "DataTable1")
Return dsCustomers
End Using
End Using
End Using
End Function
ScreenShot
