Hi rickabbam,
Referring the below link i have created an example. Please refer the below example and correct your code.
- https://blogs.msdn.microsoft.com/sqlforum/2011/01/02/walkthrough-add-a-subreport-in-local-report-in-reportviewer/
For this sample I have used of NorthWind database that you can download using the link given below.
Download Northwind Database
For the example i have added two DataSet with the name Customers and Orders and two Reports with name CustomerReport and OrdersReport and configured as in the above article.
Design of DataSet and the reports.
HTML
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="600">
</rsweb:ReportViewer>
</form>
Namespaces
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ReportViewer1.ProcessingMode = ProcessingMode.Local;
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/CustomerReport.rdlc");
Customers dsCustomers = GetCustomers("SELECT TOP 5 CustomerID,ContactName,City,Country FROM Customers");
ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
ReportViewer1.LocalReport.DataSources.Clear();
ReportViewer1.LocalReport.DataSources.Add(datasource);
ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(SetSubDataSource);
this.ReportViewer1.LocalReport.Refresh();
}
}
public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
{
Orders dsOrders = GetOrders("SELECT TOP 5 OrderID,ShipName,ShipCity,ShipCountry FROM Orders WHERE CustomerID = '" + ((ReportParameterInfo)e.Parameters[0]).Values[0] + "'");
ReportDataSource datasource = new ReportDataSource("Orders", dsOrders.Tables[0]);
e.DataSources.Add(datasource);
}
private Customers GetCustomers(string query)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (Customers dsCustomers = new Customers())
{
sda.Fill(dsCustomers, "DataTable1");
return dsCustomers;
}
}
}
}
private Orders GetOrders(string query)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (Orders dsOrders = new Orders())
{
sda.Fill(dsOrders, "DataTable1");
return dsOrders;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
ReportViewer1.ProcessingMode = ProcessingMode.Local
ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/CustomerReport.rdlc")
Dim dsCustomers As Customers = GetCustomers("SELECT TOP 5 CustomerID,ContactName,City,Country FROM Customers")
Dim datasource As ReportDataSource = New ReportDataSource("Customers", dsCustomers.Tables(0))
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(datasource)
AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SetSubDataSource
Me.ReportViewer1.LocalReport.Refresh()
End If
End Sub
Public Sub SetSubDataSource(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)
Dim dsOrders As Orders = GetOrders("SELECT TOP 5 OrderID,ShipName,ShipCity,ShipCountry FROM Orders WHERE CustomerID = '" & (CType(e.Parameters(0), ReportParameterInfo)).Values(0) & "'")
Dim datasource As ReportDataSource = New ReportDataSource("Orders", dsOrders.Tables(0))
e.DataSources.Add(datasource)
End Sub
Private Function GetCustomers(ByVal query As String) As Customers
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dsCustomers As Customers = New Customers()
sda.Fill(dsCustomers, "DataTable1")
Return dsCustomers
End Using
End Using
End Using
End Function
Private Function GetOrders(ByVal query As String) As Orders
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dsOrders As Orders = New Orders()
sda.Fill(dsOrders, "DataTable1")
Return dsOrders
End Using
End Using
End Using
End Function
Screenshot
