Display record in RDLC Report like Bank statement using Stored Procedure with C# and VB.Net in ASP.Net

Last Reply on Aug 14, 2017 03:30 AM By AnandM

Posted on Aug 14, 2017 03:08 AM

Hi,

How to display the following datas in RDLC Report.

DECLARE @AccountBalance TABLE
( 
	TransactionType CHAR(1)
	,[Date] DATETIME
	,[Description] VARCHAR(100)
	,Amount DECIMAL(10,2)
	,Paymenttype VARCHAR(100)
	,Remarks VARCHAR(100)
)
INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:02:49.323','Credited',12000,'Cash','Done')
INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:04:50.113','Credited',1300.45,'Cheque','Done')
INSERT INTO @AccountBalance VALUES('D','07/03/2017 15:00:10.413','Debited',1500,'RTGS','Done')
INSERT INTO @AccountBalance VALUES('C','07/04/2017 18:02:20.223','Credited',25000,'NEFET','Done')
INSERT INTO @AccountBalance VALUES('D','07/04/2017 03:02:01.123','Debited',25000,'NEFET','Done')
SELECT *
		,(
		ISNULL((SELECT SUM(Amount) 
				FROM @AccountBalance a 
				WHERE a.[Date]<= AB.[Date] AND TransactionType = 'C'),0) - 
		ISNULL((SELECT SUM(Amount) 
				FROM @AccountBalance a 
				WHERE a.[Date]<= AB.[Date] AND TransactionType = 'D'),0) 
		) BALANCE 
FROM @AccountBalance AB
ORDER BY [Date]
Posted on Aug 14, 2017 03:30 AM

Hi vail,

I have created a sample which full fill your requirement by taking reference from below article and you need to modify the code according to your need.

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

Refer Below Sample Code.

SQL

CREATE PROCEDURE DisplayPassBookData
AS
BEGIN
	DECLARE @AccountBalance TABLE
	(
		TransactionType CHAR(1)
		,[Date] DATETIME
   		,[Description] VARCHAR(100)
   		,Amount DECIMAL(10,2)
   		,Paymenttype VARCHAR(100)
   		,Remarks VARCHAR(100)
	)
	INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:02:49.323','Credited',12000,'Cash','Done')
	INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:04:50.113','Credited',1300.45,'Cheque','Done')
	INSERT INTO @AccountBalance VALUES('D','07/03/2017 15:00:10.413','Debited',1500,'RTGS','Done')
	INSERT INTO @AccountBalance VALUES('C','07/04/2017 18:02:20.223','Credited',25000,'NEFET','Done')
	INSERT INTO @AccountBalance VALUES('D','07/04/2017 03:02:01.123','Debited',25000,'NEFET','Done')
	SELECT *
	        ,(
	        ISNULL((SELECT SUM(Amount)
	                FROM @AccountBalance a
	                WHERE a.[Date]<= AB.[Date] AND TransactionType = 'C'),0) -
	        ISNULL((SELECT SUM(Amount)
	                FROM @AccountBalance a
	                WHERE a.[Date]<= AB.[Date] AND TransactionType = 'D'),0)
	        ) BALANCE
	FROM @AccountBalance AB
	ORDER BY [Date]
END

HTML

<div>
    <asp:ScriptManager runat="server" />
    <rsweb:ReportViewer ID="ReportViewer1" Width="750px" runat="server">
    </rsweb:ReportViewer>
</div>

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ReportViewer1.ProcessingMode = ProcessingMode.Local;
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
        Transcations dsTranscations = GetData();
        ReportDataSource datasource = new ReportDataSource("DataSet1", dsTranscations.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
    }
}

private Transcations GetData()
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand("DisplayPassBookData");
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand = cmd;
            using (Transcations dsCustomers = new Transcations())
            {
                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 dsTranscations As Transcations = GetData()
        Dim datasource As New ReportDataSource("DataSet1", dsTranscations.Tables(0))
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(datasource)
    End If
End Sub

Private Function GetData() As Transcations
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As New SqlCommand("DisplayPassBookData")
    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 Transcations()
                sda.Fill(dsCustomers, "DataTable1")
                Return dsCustomers
            End Using
        End Using
    End Using
End Function

ScreenShot