Sunday, October 16, 2016

How to create sub report using RDLC report

In this article I am going to explain how to create sub report using rdlc report.


Description:
I want to create sub report using RDLC.

Implementation:
I have created 2 tables [districtpopulation ,StatePopulation] and insert some dummy records.

How to create sub report using RDLC report


After that create store procedure to fetch data from database.

Create proc GetStatePopulation
as begin

Select * from StatePopulation
End



Create proc GetDistrictPopulation
(
@stateid int
)
as begin

Select * from districtpopulation where Stateid=@stateid
end



Follow the given steps to create sub reports

Step 1: Add new website.

Step 2: Now add dataset to website. Right click on website >>Add >> Add new item >> dataset.

Step 3: after that add connection. Drag and drop the store procedure to dataset and set foreign key relation.
How to create sub report using RDLC report

Step 4: Now add report to website. I have added two reports (Main report and sub report). Now add the dataset for main report.

How to create sub report using RDLC report


Add dataset for sup report and add the parameter.

How to create sub report using RDLC report


Step 5: Now add the webform to website. Drag and drop the script manager and reportviewer to webform. Set the main report path for local report.

How to create sub report using RDLC report


HTML Markup:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="793px">
            <LocalReport ReportPath="Mainreport.rdlc">
                <DataSources>
                    <rsweb:ReportDataSource DataSourceId="ObjectDataSource1" Name="DataSet1" />
                </DataSources>
            </LocalReport>
        </rsweb:ReportViewer>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetData" TypeName="DataSetTableAdapters.GetStatePopulationTableAdapter"></asp:ObjectDataSource>
    </div>
    </form>
</body>
</html>

Now write the code in code file.

Add the namespace

C#.net code
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Reporting.WebForms;

VB.net code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.Reporting.WebForms


C#.net code

   SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabaseConnectionString"].ToString()); 

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            Bindreport();
        }
    }

    public void Bindreport()
    {
        DataTable dt = new System.Data.DataTable();
        SqlDataAdapter adp = new SqlDataAdapter("GetStatePopulation", con);
        adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        adp.Fill(dt);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportDataSource reportdatasource = new ReportDataSource("DataSet1", dt);
        ReportViewer1.LocalReport.DataSources.Add(reportdatasource);
        ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(SubreportProcessing);
    }

    void SubreportProcessing(object sender, SubreportProcessingEventArgs e)
    {
        int stateid = int.Parse(e.Parameters["stateid"].Values[0].ToString());
        DataTable dt = new System.Data.DataTable();
        SqlDataAdapter adp = new SqlDataAdapter("GetDistrictPopulation", con);
        adp.SelectCommand.CommandType = CommandType.StoredProcedure;
        adp.SelectCommand.Parameters.AddWithValue("@stateid",stateid);
        adp.Fill(dt);
        ReportDataSource dts = new ReportDataSource("DataSet1", dt);
        e.DataSources.Add(dts);
    }

VB.net code:

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("TestDatabaseConnectionString").ToString())

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Bindreport()
        End If
    End Sub

    Public Sub Bindreport()
        Dim dt As DataTable = New System.Data.DataTable()
        Dim adp As New SqlDataAdapter("GetStatePopulation", con)
        adp.SelectCommand.CommandType = CommandType.StoredProcedure
        adp.Fill(dt)
        ReportViewer1.LocalReport.DataSources.Clear()
        Dim reportdatasource As New ReportDataSource("DataSet1", dt)
        ReportViewer1.LocalReport.DataSources.Add(reportdatasource)
        AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SubreportProcessing
    End Sub
   
Private Sub SubreportProcessing(sender As Object, e As SubreportProcessingEventArgs)
        Dim stateid As Integer = Integer.Parse(e.Parameters("stateid").Values(0).ToString())
        Dim dt As DataTable = New System.Data.DataTable()
        Dim adp As New SqlDataAdapter("GetDistrictPopulation", con)
        adp.SelectCommand.CommandType = CommandType.StoredProcedure
        adp.SelectCommand.Parameters.AddWithValue("@stateid", stateid)
        adp.Fill(dt)
        Dim dts As New ReportDataSource("DataSet1", dt)
        e.DataSources.Add(dts)
    End Sub

Build the website and run.

  

2 comments:

  1. Thankyou so much, after days looking for subreporting, this is the only step by step manual that explains it

    ReplyDelete