I'm creating a report using RDLC and Visual Basic. I used 2 datatables to display information in the report which is one for main report(DTT) and another one for sub report(TTT). I have 2 dataset in RDLC, which are TFDataset and TSDataset. My report consist of 1 production order(PO) as primary key with multiple task in database. In a form, I created a Datagridview(DGW), for displaying data from database with checkboxes, and a ReportViewer, to display the report. The flow system is, the DGW shows list of PO, so user can tick multiple checkboxes from the DGW and print out those selected PO into a report. The selected PO are used to retrieve task using SQL and fill into the datatables. The PO are stored in DTT and task are stored in TTT. I used FOR EACH loop to run SQL for each check box value. DTT used for main report displays multiple PO correctly with proper page break but TTT seems to display a list of stacked task from other PO because the loop just added the task into the same datatable on each loop. Hence, the problem is how to separate the task according to its own PO?
Private Sub GenerateReport()
For Each row As DataGridViewRow In DataGridView1.Rows
Dim isSelected As Boolean = Convert.ToBoolean(row.Cells("checkbox").Value)
If isSelected Then
Dim opt As QrCodeEncodingOptions = New QrCodeEncodingOptions
opt.DisableECI = True
opt.CharacterSet = "UTF-8"
opt.Width = 250
opt.Height = 250
Dim writer As New BarcodeWriter()
writer.Format = BarcodeFormat.QR_CODE
writer.Options = opt
Dim qr = New BarcodeWriter()
qr.Options = opt
qr.Format = BarcodeFormat.QR_CODE
Dim Result = New Bitmap(qr.Write(imdata.Rows(row.Index)(1).ToString))
Dim stream As New IO.MemoryStream
Result.Save(stream, Imaging.ImageFormat.Bmp)
connect.Open()
Dim insert As New SqlCommand("update POTB set t_qrcd = @t_qrcd, t_pfno = @t_pfno, t_dtpr = @t_dtpr where t_pdno = '" + imdata.Rows(row.Index)(0).ToString + "'", connect)
insert.Parameters.Clear()
insert.Parameters.Add("@t_pfno", SqlDbType.NVarChar).Value = Upf
insert.Parameters.Add("@t_qrcd", SqlDbType.Image).Value = stream.ToArray()
insert.Parameters.Add("@t_dtpr", SqlDbType.DateTime).Value = Date.Now
insert.ExecuteNonQuery()
Dim command As New SqlCommand("select t_pdno, t_item , t_dsca, t_qrdr, FORMAT(t_rdld,'dd/MM/yyyy') as t_rdld, t_opro, t_mark, t_qrcd, t_repr, t_pfno, FORMAT(t_dtpr,'dd/MM/yyyy hh:mm:ss tt') as t_dtpr FROM POTB
where t_pdno = '" + imdata.Rows(row.Index)(1).ToString + "'", connect)
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(DTT)
Dim rds As New ReportDataSource("TFDataSet", DTT)
Dim task As New SqlCommand(" select ttirou1022201.t_tano, ttirou0032201.t_dsca, ttirou1022201.t_opno from ttisfc0012201
join ttcibd0012201 on ttcibd0012201.t_item = ttisfc0012201.t_mitm
join ttirou1022201 on ttirou1022201.t_mitm = ttisfc0012201.t_mitm and ttirou1022201.t_opro = ttisfc0012201.t_opro
join ttirou0032201 on ttirou0032201.t_tano = ttirou1022201.t_tano
where ttisfc0012201.t_pdno = '" + imdata.Rows(row.Index)(1).ToString + "' and ttisfc0012201.t_mitm = '" + imdata.Rows(row.Index)(2).ToString + "'
order by ttirou1022201.t_opno asc", connect)
Dim taskap As New SqlDataAdapter(task)
taskap.Fill(TTT)
ReportViewer1.LocalReport.DataSources.Add(rds)
connect.Close()
End If
Next
list.Add(TTT)
AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SetSubDataSource
ReportViewer1.RefreshReport()
End Sub
You need to pass the Main Report PO ID as a Parameter to the sub report. This way each Instance of the sub report is filtered for its parent PO ID. Take a look at this https://social.msdn.microsoft.com/Forums/sqlserver/en-US/45c500d9-6de1-473e-b118-d023fc5ba988/ssrs-subreport-parameter-passing-issue
