If you have to create a report in Crystal Reports from multiple data sources or provide multiple views of the data, you may find sticky situations because CR does not allow you to have multiple Detail sections in a single report. For example, say you have to display data from two tables on the same report page. Now say, you create two Detail sections and bind each Detail section with a table, you will find second table records repeating within the first table records.
For example, if I want to generate a report that looks like Figure 1.
Report Header
- Page Header
o Detail - Data from Customers table
o Detail - Data from Orders table
- Page Footer
Report Footer
Figure 1.
To do this, you will have to create two details sections on the same level, not recursive.
The work around for this is using sub reports. What we do is to create two sub reports, one for Customers and one for Orders. These sub reports have nothing but a single Detail section.
Now we create two Page Header sections on the main report and put a sub report on each Page Header sections. The new report looks like Figure 2.
Report Header
- Page Header 1
o Sub Report 1
- Page Header 2
o Sub Report 2
o Detail Section (Hide it)
- Page Footer
Report Footer
Figure 2.
In the above format in Figure 2, we treat each sub report as a separate report. Now you can bind each sub report with a separate DataSet or DataTable.
For example, if we have data coming in a DataSet from two tables-Customers and Orders, the following code binds each DataTable to a separate sub report:
Dim report As New DynamicReport
report.SetDataSource(ds)
report.Subreports.Item("SubReport1").SetDataSource(ds.Tables("Customers"))
report.Subreports.Item("SubReport2").SetDataSource(ds.Tables("Orders"))
CrystalReportViewer1.ReportSource = report
CrystalReportViewer1.DataBind()
Similarly, your data can be coming from multiple data sources; you can bind as many as sub reports with different data sources.
Note: In the above code, DynamicReport is the class name of crystal report. For example, if you have a crystal report called "MyReport.rpt" and you add this report to the VS.NET project, you will see a class "MyReport.cs" or "MyReport.vb" depending on the language you choose. Make sure you change DynamicReport with your class name "MyReport".
Summary
In this article, we learned how to create multiple sub reports and bind them with multiple data source using DataSet. Using the same approach, you can get data from multiple data sources in a single report.