This is the seventh article of a series that uses ASP.NET
and Crystal Reports to build reports using the Adventure Works Sample
Database. Before reading this article it would be helpful to have read Part
1, Part
2, Part
3, Part
4, Part
5, or Part
6, but it is not required. This article shows you how to create a report
using two different databases. In some situations you are faced with trying to
merge data on a single report from two different data sources and a single SQL
Statement cannot accomplish the query needed for your results. Crystal Reports
has a feature called Subreports that makes the task of creating a report based
on two databases trivial. This article builds a web page to display the report
to the user using the Crystal Report Viewer control and shows how to connect
the two databases to the report. You can download the code here.
Before you begin, you will need to have installed Visual
Studio 2008 with Crystal Reports for .NET. The samples are written in Visual
Studio 2008 but they will work with Visual Studio 2005 also. You also need to
download the AdventureWorks sample database from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=34032
for SQL Server 2008. Download and install the
SQL2008.AdventureWorks_All_Databases.x86.msi file. If you do not have SQL
Server 2008 you can use SQL Server 2005 but you'll need to download the 2005
AdventureWorks samples. You will use the AdventureWorks and AdventureWorksLT
databases to build this report.
The goal of this article is to create a web page that looks
like the following image.
This report uses the SalesOrderHeader table from the
AdventureWorksLT database and the SalesOrderDetail from the AdventureWorks
database. Normally you wouldn't store header and detail information in different
databases, but this article shows you the concept of mixing data from two
different databases so you can apply the pattern to your own solutions.
The AdventureWorksLT database is a less normalized version
of the AdventureWorks database and also contains less data. The
"main" report is based on the AdventureWorksLT database while the subreport
is based on the AdventureWorks database.
