Show sum of columns in RDLC Report using C# and VB.Net in ASP.Net

Last Reply one year ago By AnandM

Posted one year ago

Hi,

In my Database i have orderIncome table.

OrderIncome:(Fields)

1.OrderDate

2.OrderNo

3.PaymentMethod

4.Amount

5.OrderClosingDate

 

PaymentMethod have following types:

1.Cash,Online,Cheque,Foc

 

How i want separately display sum(cash),Sum(online),etc.. using orderclosingdate range in RDLC report.

 

 

Posted one year ago

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.

RDLC (Local SSRS) Report ASP.Net Example using DataSet or DataTable in C# VB.Net and Visual Studio 2010

Create RDLC Report using Stored Procedure in ASP.Net with C# and VB.Net

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html