How to Display Master/Detail Data from a Database using Microsoft Report (rdlc) in ASP.NET.

Introduction

In this post, I explain how to Display Master/Detail Data from a Database using Microsoft Report (rdlc) in ASP.NET.

Steps :

Step - 1: Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create 2 table for Master Details Record.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used table as below

Step-4: Create a Stored Procedure for fetch join data.

Right Click on Stored Procedure (in your database) > Add new Stoted procedure > Write following SQL Code And Save/Execute.

        CREATE PROCEDURE dbo.GetOrderDetails 
           AS
         SELECT        
          C.CID, 
          C.CustomerCode, 
          C.CustomerName, 
          C.ContactNo, 
          C.State, 
          C.City, 
          O.OrderID, 
          O.OrderNo, 
          O.OrderDate, 
          O.Quantity, 
          O.UnitPrice, 
          O.TotalAmount
         FROM            
          Customers AS C INNER JOIN
             OrderMaster AS O ON C.CID = O.CID
         RETURN

Step-5: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables and Select Stored Procedure> enter Model Namespace > Finish.

Step-6: Add a Dataset (this is required for our .rdlc file).

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select Dataset under data> Enter dataset name > Add.

Design your dataset as below...

Step-7: Add Report file(.rdlc) and Design your report.

Right Click on Solution Explorer > Add > New item > Select Report under Reporing > Enter report file name > Add.

Here (in this .rdlc) do following
1. First add Our Created Dataset.

Under report data Click on New > Dataset > Choose Data Source(existing created Dataset) > Select Available Dataset > OK.

2. Add a List Control to rdlc design page.

Under Toolbox > Report Item > Drag List to Design Page. Select List Control > Open Properties > Select Dataset Name.

3. Add Row Group for this List Control of rdlc page.

Select List Control > Under row group Click on Details > Group Properties > Click on Add (under general tab) > Select Group On > Ok.

4. Add Fields inside List Control for Show Master Data.
5. Add Table inside List Control for Show Details Data.

Final Design Will be Like This...

Step-8: Add a Webpage and Design for show Master Details Record using Report Viwer.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

              <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

            <asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">
    
            </asp:Content>
            <asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">
                <h3>Master Details Report using Microsoft Report in ASP.NET</h3>
                <div>
                    <rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="1000px" Height="800px" Font-Names="Verdana" Font-Size="8pt" WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt">
                        <LocalReport ReportPath="rpt_MasterDetailsOrder.rdlc" />
                    </rsweb:ReportViewer>
                </div>
            </asp:Content>

Step-9: Write code in page_load event for fetch Master data from database and bind to report viwer.

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

and here is the function

            
            private void PopulateReport()
            {
                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    var v = (from a in dc.GetOrderDetails()
                             select a);
                    ReportDataSource rd = new ReportDataSource("dsMasterDetailsOrder", v.ToList());
                    ReportViewer1.LocalReport.DataSources.Add(rd);
                    ReportViewer1.LocalReport.Refresh();
                }
            }