Wednesday, October 14, 2009

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 ^^
Share:

4 comments:

  1. Which version of Crystal Reports were you using by then? Because we used Crystal Reports 8 and reports like the one you created on your post worked all right, but now we've migrated to Crystal Reports XI, and subreports linked by TTX file fields don't work anymore. It only works when you put some database table together in the link, but if you use only TTX fields to link with the main report, it won't work.

    ReplyDelete
  2. I used Crystal Report 8.5. I suppose the best version for VB 6.0 is Crystal Report 8.5 ^^

    ReplyDelete
  3. Steven , You're the one who said the right thing

    ReplyDelete

You may be intersted in

Related Posts

Updating Table Containing Xml Column via LinkedServer

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone. There...

About Me

My photo
Is an ordinary man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing.

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Leave a Message