Creating Sub Report using TTX in VB

Before creating sub report using TTX in VB, you should have already known how to create report using TTX. Otherwise, you may want to find out how to create report using TTX in my previous post.

Before going to the sub report, it's a good idea to share the code query in Visual Basic used to retrieve the data first.

Private Sub cmdPrint_Click()
   Dim rs As New ADODB.Recordset 'recordset for main report
   Dim rs1 As New ADODB.Recordset 'recordset for subreport
   Dim sql As String
   With CrystalReport1
       .Reset
       'Set data for main report
       sql = "select CustomerID, CompanyName, ContactName, ContactTitle, " & _
             "Address from Customers where Country = '" & cmbCountry.Text & "'"
       Set rs = New ADODB.Recordset
       Set rs = con.Execute(sql)
      
       .ReportFileName = App.Path & "/RptTest.rpt"
       .Destination = crptToWindow
       .SetTablePrivateData 0, 3, rs
       'End set data for main report
      
       'Set data for subreport
       .SubreportToChange = .GetNthSubreportName(0)
       sql = "select a.customerid, d.productname, c.quantity, c.unitprice  " & _
           "from customers a " & _
           "left join orders b " & _
           "on b.customerid = a.customerid " & _
           "left join orderdetails c " & _
           "on c.orderid = b.orderid " & _
           "left join products d " & _
           "on d.productid = c.productid " & _
           "order by a.customerid, d.productname"
       Set rs1 = New ADODB.Recordset
       Set rs1 = con.Execute(sql)
       .SetTablePrivateData 0, 3, rs1
       'End set data for subreport
      
       .WindowState = crptMaximized
       .WindowShowPrintBtn = True

       .Action = 1
   End With
End Sub

Suppose here's the data retrieved in the main report:


And here's the data retrieved in the sub report:


In this case, we would retrieve the data on the main report, while at the same time, show the orders made by each customer.

Note that there has to be one or more linking fields between the report and sub report in this case. In the data shown above, the linker is the field CustomerId.

Steps in creating the sub report:
1. Assume the main report is done, like the following picture (if you have not tried making the main report, you may want to refer to my previous post about making the main report):



2. To insert sub report, choose menu Insert -> Subreport, then a dialog box will be prompted, insert the desired sub report name, then click Report Expert.



3. Another dialog boxes will be prompted, we will have to create new TTX for the sub report. The steps are the same as those used in creating the TTX for the main report.


4. After finished creating the TTX, add the TTX to the report.


5. Then, go to tab Fields, Add the fields to be displayed on the report, then click OK.


6. And here's the report with the sub report embedded in it.


7. To Edit the view of sub report, double click on the report, and arrange it to get desired layout.


8. After done with the layout of the sub report, we have to go back to the main report to link the report and the sub report to get desired data grouped by certain field(s). In the data used above, the linker field is CustomerId. Right-click on the sub report, choose Change Subreport Links. As we're going to link the field CustomerId in the main report and the subreport, choose the field on the main report, then select the linking field in the sub report (Make sure the field linked on the lower right is the field which belongs to the sub report, not the main report). Click OK.


9. Now we're finished with the main and sub report.

After running the program, we will get a report which contains the data of the Customers and the order details of each customer as the picture shown below.



That's all folks... Good Luck ^^