Use SSRS Subreport in ReportViewer in ASP.NET

Using SSRS Subreport in Local Report

Microsoft published an article on this topic. The title is "Walkthrough: Add a Subreport in local report in ReportViewer", and you may find this article from the following site.

Walkthrough to add a subreport in local report in reportviewer

If you follow the step listed in the article, it will work. However, we found the process can be simplified. Here is a screenshot that shows both the main report and address from subreport.

A Simple Way to Make Local Subreport Work in ASP.NET

Here we use Visual Studio 2005 and SSRS 2005 rdlc files to show how this can be done in a few steps. Suppose you have valid main report rdlc and subreport rdlc. If the subreport is not valid, there will be an error message "Subreport cannot be shown." We had the experience that the subreport is actually correct, but for some reason SSRS does not like the filter in the table because the same filter formula works for SSRS 2008 very well.

• The DB Connection is "DSN=Xtreme Sample Database 2005".
• The main report is "Employee Profile Subreport.rdlc", and the subreport is "Employee Profile Subreport_sub_EmployeeAddress.rdlc". Subreport must use the .rdlc extension, otherwise it cannot be found.

Steps to Show SSRS Subreport from ASP.NET

Here are the steps.

• Create a new ASP.NET website, name it "WebSubreport", choose C# as the language for the website project.
• Add a ReportViewer. SSRS ReportViewer control can be found from Data in the toolbox.
• Add the main report rdlc and subreport rdlc to the project, in our demo they are "Employee Profile Subreport.rdlc", and  "Employee Profile Subreport_sub_EmployeeAddress.rdlc"
• Add the following connection string to the Web.Config file. The connection must match your report and can retrieve data from your DB, please make sure the DB connection is correct.

 <connectionstrings>
    <add connectionstring="Dsn=Xtreme Sample Database 2005;
    dbq=C:\Program Files\Microsoft Visual Studio 8\Crystal Reports
    \Samples\en\Databases\xtreme.mdb;
    driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5"
    name="ConnectionString" providername="System.Data.Odbc">
    </add>
 </connectionstrings>

• Add the main report and data sources to the ReportViewer. "Dataset1" is the dataset name in both main report and subreport in "Default.aspx".

<LocalReport ReportPath="Employee Profile Subreport.rdlc" 
    EnableExternalImages=True EnableHyperlinks=True >
    <DataSources>
        <rsweb:ReportDataSource DataSourceId="SqlDataSource1" Name="DataSet1" />
        <rsweb:ReportDataSource DataSourceId="SqlDataSource2" Name="DataSet1" />
    </DataSources>
    </LocalReport>

• Now Add the datasets. The connection string is what we added in the Web.Config, and the "SelectCommand" for SqlDataSource1 is the CommandText from the main report, andthe "SelectCommand" for SqlDataSource1 is the CommandText from the subreport

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString=
    "<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
        SelectCommand="SELECT Employee.[Last Name], Employee.[First Name], 
        Employee.Extension, Employee.Position, Employee.[Birth Date], 
        Employee.[Home Phone], 
        Employee.[Employee ID], Employee_Addresses.Region, 
        Employee_Addresses.City, Employee.Notes, Employee.Photo 
        FROM   Employee Employee INNER JOIN [Employee Addresses] Employee_Addresses 
                ON Employee.[Employee ID]=Employee_Addresses.[Employee ID]">
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString=
    "<%$ ConnectionStrings:ConnectionString %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
        SelectCommand="SELECT Employee_Addresses.Address1, 
        Employee_Addresses.Address2, Employee_Addresses.City, 
        Employee_Addresses.Region, Employee_Addresses.Country, 
        Employee_Addresses.[Postal Code], 
        Employee_Addresses.[Employee ID] 
        FROM   [Employee Addresses] Employee_Addresses">
        </asp:SqlDataSource>

• Finally handle the subreport. This can be done by hooking up to the SubreportProcessingEventHandler. Here is the finished code in "Default.aspx.cs".

    protected void Page_Load(object sender, EventArgs e)
    {
        ReportViewer1.LocalReport.SubreportProcessing += 
        new SubreportProcessingEventHandler(SetSubDataSource);
        this.ReportViewer1.LocalReport.Refresh();
    }

    public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
    {
        e.DataSources.Add(new ReportDataSource("DataSet1", "SqlDataSource2"));
    }

Now it is ready to run. You can download the finished project here.