How to create Microsoft Report (.rdlc) with multiple datasources in ASP.NET.

Saturday, November 02, 2013 Sourav Mondal rdlc | Report

Introduction

In the previous article, I have explained how to use Microsoft report (rdlc) in MVC, but lots of viewers asked for show some demonstration for show report with multiple datasources. So, today I will show you how to create Microsoft Report (.rdlc) with multiple data sources 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 table and insert data for show in report

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

In this example, I have used two tables as below

Table 1

Table 2

Step-4: 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 > enter Model Namespace > Finish.

Step-5: Add Dataset.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select Dataset under data > Enter Dataset name > Add.
Here you can add Datatable in 2 ways.
1. By Dragging Table from solution explorer to dataset.
2. Right Click on Dataset > Add Datatable. ( Than add columns by Right Click on Data table > Add > Column.)

[ View video for inner details steps ]

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

Add report folder to your project
Right Click on report folder > Add > New item > Select Report under Reporing > Enter report file name > Add.
Here we also have to add Datasource for our report.
Under report data Click on New > Dataset > Enter Dataset name > Select Datasource > Select Dataset > Ok.
(Repeat this step twice for add 2 data sources, that we have already created.)
Now Design your Report looks.
Here I have added 2 table for show data of 2 Datasource.
For Create Table --> Right Click on Report Body > Insert > Table.
(Repeat this step twice for add 2 table)

Here we have to do 1 more thing, that is have set DataSetname Name for this 2 table.
For Set DataSetname --> Select table > Go to Properties > Set DataSetname.
(Repeat this step twice for add 2 table)

My Design Looks like this....

[ View video for inner details steps ]

Step-7: Add a Web page for Show Report in Report Viewer.

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.

Drag Control ScriptManager and ReportViewer to your page.

Html Code

<h2>
    Microsoft Report using multiple datasource in ASP.NET
</h2>
<div>
    <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana"
        Font-Size="8pt" InteractiveDeviceInfos="(Collection)"
        WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Width="857px">
        <LocalReport ReportPath="Report\TopTenList.rdlc">
        </LocalReport>
    </rsweb:ReportViewer>
</div>

Write Below code in page_load event

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

}

And here is the function

private void PopulateReportData()
{
    List<EverestList> allEverest = new List<EverestList>();
    List<RiverList> allRiver = new List<RiverList>();
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        allEverest = dc.EverestLists.ToList();
        allRiver = dc.RiverLists.ToList();
    }
    ReportViewer1.LocalReport.DataSources.Clear();
    ReportDataSource rd1 = new ReportDataSource("DSEverest", allEverest);
    ReportDataSource rd2 = new ReportDataSource("DSRiver", allRiver);
    ReportViewer1.LocalReport.DataSources.Add(rd1);
    ReportViewer1.LocalReport.DataSources.Add(rd2);
    ReportViewer1.LocalReport.Refresh();
}