My subreport is displaying all the result in each cell in the Main Report

Question

I do have RDLC files named MainReport.rdlc and SubReport.rdlc.

Subreport.rdlc (Obviously, act as my subreport).

I was able to display the details of my subreport in my Main Table. but the problem is that the subreport displays all the data on each row in my subreport

What I want is this:

But what is happening is this:

The dataset query on MainReport

Select DISTINCT tblMain.ID_Num, tblMain.fldDate,Scope.Description,tblMain.Accomplishment,Rating.fldQuality, Rating.fldQuantity, Rating.fldAccuracy from tblMain 
JOIN tblScope as SCOPE on  tblMain.Scope = Scope.Id
LEFT JOIN tblRatings as Rating on tblMain.RatingRefCode = Rating.ScopeCode 
where tblMain.ID_Num = @RefNo AND fldDate between @Start and @End
Group By tblMain.ID_Num, tblMain.fldDate,Scope.Description,tblMain.Accomplishment,Rating.fldQuality, Rating.fldQuantity, Rating.fldAccuracy

Here is the dataset query on Subreport

Select DISTINCT tblMain.ID_Num, tblMain.fldDate,Scope.Description,tblMain.Accomplishment from tblMain 
JOIN tblScope as SCOPE on  tblMain.Scope = Scope.Id
LEFT JOIN tblRatings as Rating on tblMain.RatingRefCode = Rating.ScopeCode 
where tblMain.ID_Num = @RefNo AND fldDate between @Start and @End
Group By tblMain.ID_Num, tblMain.fldDate,Scope.Description,tblMain.Accomplishment

Answer 1

It seems that you didn't set subreport parameters.

In MainReport.rdlc:

• right click on subreport > Subreport Properties > Parameters
• set a new parameter with name ScopeParameter and value equal to Scope field in your dataset (e.g. [ScopeId])

In Subreport.rdlc:

• set a new parameter with name ScopeParameter
• in your Tablix or Tablix Group define a new filter with [ScopeId] = @ScopeParameter

With large amount of data it seems faster using SubreportProcessingHandler event to filter data in your subreport instead of setting a filter in Tablix or Tablix Group.

Private Sub SubreportProcessingHandler(sender As Object, e As SubreportProcessingEventArgs)

    Dim intScope As Integer = CInt(e.Parameters.Item("ScopeParameter").Values(0))

    Dim dvYourDataView As New DataView(Me.dsYourDataset.Tables(0))
    dvYourDataView.RowFilter = "ScopeId = " & intIdScope

    e.DataSources.Add(New ReportDataSource("YourReportDataSourceName", dvYourDataView.ToTable("YourReportDataSourceName")))

End Sub