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.

1.    Launch Visual Studio 2008.

2.    Select File -> New Web Site from the menu.

3.    Choose ASP.NET from the list of templates.  You can select File or HTTP from the Location drop down list.  For this sample I'll use File.

4.    Set the Web Site Name to SubReportSample.  The path should be "..\Visual Studio 2008\WebSites\SubReportSample"

5.    Select Visual C# from the Language drop down list and then click OK.

Visual Studio will create a new web site and add a Default.aspx page and web.config file.  Now you need to add a Crystal Report file to your site.  To do this, follow these steps:

6.    Right click on the web site in your Solution Explorer.

7.    Select Add New Item… from the pop-up menu.

8.    Click on Crystal Report from the templates.  If you don't see Crystal Report as an option then you need to run the Visual Studio 2008 installation and select the option to install Crystal Reports.  It comes free with Visual Studio 2008.

9.    Change the name of the report to SubReport.rpt.  The Language drop down should already be set to Visual C# but if it isn't do so.

10. Click the Add button.

Visual Studio will add the Crystal Report file to your web site and bring up the Crystal Reports Gallery dialog.  You may be prompted to register your product if this is the first time you're using Crystal Reports.  You can skip registering the product but it will keep prompting you every time you open a report so it's worth taking the extra minute or two to register.  Once you get to the Crystal Reports Gallery dialog you should select the "As a blank report" option and click OK.

Your report will be displayed in Visual Studio and you're ready to start building your report.

The next step in designing this report is to connect to the AdventureWorksLT database.  These steps assume you have already downloaded and run the install for the AdventureWorks database.

1.    Right click on the Database Fields node in the Field Explorer.  If you do not see the Field Explorer window select Crystal Reports -> Field Explorer from the menu.  Select Database Expert… from the pop-up menu.

2.    This report is going to connect directly to the database using OLE DB.  To connect to the database click the plus sign next to the Create New Connection node.

3.    Click the plus sign next to OLE DB (ADO).  This will display the OLE DB dialog which allows you to select your AdventureWorksLT database.

4.    Select SQL Native Client from the list of providers and click Next.

5.    Enter the server name where the SQL Server database is installed.

6.    You can either use integrated security or a SQL Login and password to connect to the database.  This depends on how you setup your database when you installed it.  I created a SQL Login called "aspalliance" and gave it a password of "aspalliance" and added the Login to the db_owner role in the AdventureWorksLT database.  Enter the User Id and Password.

7.    Now click on the Database drop down list.  You should see AdventureWorksLT as one of the options.  If you do not then the SQL Login does not have access to the database.  You'll have to use SQL Server Management Studio to add the aspalliance Login and add them to the db_owner role for the AdventureWorksLT database.

8.    Once you have selected AdventureWorksLT for the database click Next and then click Finish.

Your SQL Server should appear under the OLE DB (ADO) node and the AdventureWorksLT database should be listed under the server name.

9.    Click the plus sign next to AdventureWorksLT and you'll see the list of schemas defined in the database.  If you click the plus sign next to a schema name you'll see two nodes, one for Tables and one for Views.  Clicking the plus sign next to either will display the list of tables or views in that schema.

10. The next step is to select the appropriate tables and views for the report and move them to the Selected Tables list in the dialog box. Expand the SalesLT schema and expand the Tables.  Click on the SalesOrderHeader table and then click the > button to move this table to the Selected Tables list.

11. Click the OK buton.

You should now see the report in Visual Studio 2008.  Click the plus sign next to the Database Fields node in the Field Explorer.  You see the SalesOrderHeader table.  Click the plus sign next to the table and you'll see the fields in that table.  These are now available to be used in the report.

This report will display each SalesOrderHeader record and list the SalesOrderDetail records associated with the SalesOrderID.  The report will be grouped by the SalesOrderID field and the subreport will retrieve the corresponding records from the AdventureWorks.SalesOrderDetail table.  This step will add a group to the report by SalesOrderID.

1.    From the main menu select Crystal Reports -> Insert -> Group.

2.    Choose SalesOrderID from the drop down list.

3.    Click the OK button.

The report will now have two sections added, GroupHeaderSection1 and GroupFooterSection1.  Crystal Reports will automatically add the "Group #1 Name" field to the header.  For this report, the SalesOrderID will print in the group header section.

You can click the Main Report Preview button at the bottom of the screen to view the SalesOrderID records that are in this database.  As you can see, there is much less data in the AdventureWorksLT database than there is in the AdventureWorks database.

The subreport will display the SalesOrderDetails records for a given SalesOrderID.  Again, this is for demonstration purposes only but you can apply the same concept to your reports when you have data in two different databases.  You can also apply the same concept if you have two separate queries that need to execute to produce data on a report.

1.    Right click on the white space in the Details section and select InsertàSubreport… from the pop-up menu.

2.    A square box will follow your mouse pointer until you left click in a section.  Left click in the Details section so the report is placed in the Details section.  The Insert Subreport dialog will appear.

3.    Select the "Create a subreport with the Report Wizard option and enter "SalesOrderDetail" for the New report name.  Click the Report Wizard… button.

4.    This will display the Standard Report Wizard dialog box.  The first step in the wizard is to choose a data source for the subreport.  Double click on the Make New Connection to create a new connection to the AdventureWorks database.

5.    Select SQL Native Client from the Provider list and click the Next button.

6.    Enter the server name, a user name and password, and select the AdventureWorks database from the database drop down list.

7.    Click the Next button and then the Finish button.

8.    Expand the AdventureWorks database to display the list of schemas.  Expand the Sales schema and the tables underneath it.

9.    Select the SalesOrderDetail table and click the > button to move the table to the Selected Tables list.

10. Expand the Production schema and the tables underneath it.

11. Select the Product table and click the > button to move the table to the Selected Tables list.  The SalesOrderDetail has a foreign key to the Product table.  The report will display the product name and number for each record in the SalesOrderDetail table rather than the foreign key.

12. Click the Next button.

13. Crystal Reports tries to determine the relationships between tables automatically and usually does a good job.  However, in this case it created two links where we only want one between the Product.ProductID and SalesOrderDetail.ProductID. The link between the rowguid fields between the two tables should be deleted.  To do this left click on the line between the two fields and press the Delete key.

14. Click the Next button.

15. The next screen asks you which fields you want on the report.  Click on the following fields and move them to the Fields To Display list by clicking on the > button. Product.ProductNumber, Product.ProductName, SalesOrderDetail.OrderQty, SalesOrderDetail.UnitPrice, and SalesOrderDetail.LineTotal.

16. Click the Next button.

17. The next screen asks you to group the records.  There is no grouping in the subreport since this is handled in the main report.  Simply click the Next button.

18. The next screen asks you for the selection criteria.  There are no selection criteria for this report.  This will be handled in the main report.  The SalesOrderID field will be passed to this report and only records for the specified ID will be displayed.  Click the Next button.

19. The report style will be standard so leave Standard selected and click the Finish button.

20. This will bring you back to the Insert Subreport dialog.  Click on the Link tab.

21. This screen allows you to define the field that will link the two reports together.  Select the SalesOrderHeader.SaleOrderId from the Available Fields list and click the > button.

22. A parameter field will be created in the subreport that will contain the SalesOrderID from the main report.  Click the OK button.

23. The SalesOrderDetail subreport will be displayed as a box in the details section.  Click on the box to show the window handles and drag the borders so they are the width of the page.

24. By default the subreport is shown with a box around it.  For this report we want to remove the box.  To do this, right click on the subreport and select Format Object from the pop-up menu.

25. The Format Editor dialog box will appear.  Click the Border tab and set the Left, Right, Top, and Bottom border to None and click the OK button.

26. Suppress Section1(Report Header), Section2(Page Header), GroupFooterSection1, Section1(Report Footer), and Section5(Page Footer).  You can hide the section by right clicking the gray bar and selecting Suppress from the pop-up menu.

27. Save the report.

28. Now you can preview the report by clicking on the Main Report Preview button.

Step 5: Format the Subreport

As you can see from the preview, the print date is being repeated in the sub report and there is extra space between each group.  This is because when the sub report was created Crystal included the print date in the report and also shows extra sections in the subreport.  You can modify the subreport to not show the print date and hide the extra sections by following these steps.

1.    Click the Main Report button.

2.    Right click on the subreport and select Edit Subreport from the pop-up menu.

3.    The SalesOrderDetail subreport will now be displayed and an extra button will appear next to the Main Report Preview button that allows you to toggle back and forth between each view.

4.    Click on the Print Date field in the sub report and press the delete key to remove it.

5.    Lasso all the column header fields and drag them to the top of the PageHeader section.

6.    Make the PageHeader height smaller by hovering your mouse over the top of the details gray bar until the pointer turns to a vertical bar.  Click and drag the bar to just below the column header text boxes.

7.    Right click on the ReportFooter2 gray bar and select Suppress from the pop-up menu.

8.    Right click on the LineTotal field in the Details section and select InsertàSummary… from the pop-up menu.

Leave the default settings and click the OK button.  This will add a total field to ReportFooterSection1(Report Footer a) and unhide the section.

9.    Click on the Main Report Preview button to display the report.

The report is now displayed correctly without the print date and the extra spacing for the sections. 

Now that you have built the report you can build a web page to display it.  I'll create a simple page that lets the user click a button to preview the report.  We'll do all of this in this Default.aspx page that is part of the web site we created.

1.    Open the Default.aspx page and view its HTML markup.  Add the following Register directive after the Page directive.

<%@ Register assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, 
  PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR"%>

This allows you to use the Crystal Reports Viewer control that comes with Visual Studio.

2.    Add the following code between the div tags.  

<asp:Button ID="btnPreview" runat="server" onclick="btnPreview_Click" 
  Text="Preview" />
<br />
<br />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
  AutoDataBind="true" />

3.    Open the code behind page and add the following using statements in the code behind.

//Custom using statements
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

4.    Add the following code to the Page_Load even handler.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        //Hide the crystal report viewer
        CrystalReportViewer1.Visible = false;         
    }
    else
    {
        //If this is a postback the rebind the report so the paging works.
        if (CrystalReportViewer1.Visible == true)
        {
            //Rebind the report.
            BindReport();
        }
    }
}

This will hide the Crystal Report Viewer Control when the user initially navigates to the page.  The postback check code is needed because if the user is paging through the report a postback event is fired and the report needs to be rebound to the Viewer control.

5.    Add the following code for the Preview button's click event.

protected void btnPreview_Click(object sender, EventArgs e)
{
    BindReport();
 
    CrystalReportViewer1.Visible = true;
}

6.    Now add the following custom methods.

public void BindReport()
{
  ReportDocument report = new ReportDocument();
  report.Load(Server.MapPath("SubReport.rpt"));
 
  SetTableLocation(report.Database.Tables, "YourServer", "AdventureWorksLT");
 
  SetTableLocation(report.Subreports[0].Database.Tables, "YourServer", 
                   "AdventureWorks");
 
  CrystalReportViewer1.ReportSource = report;                
}
 
private void SetTableLocation(Tables tables, string server, string database)
{
  ConnectionInfo connectionInfo = new ConnectionInfo();
 
  connectionInfo.ServerName = server;
  connectionInfo.DatabaseName = database;
  connectionInfo.UserID = "aspalliance";
  connectionInfo.Password = "aspalliance";
 
  foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
  {
      TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
      tableLogOnInfo.ConnectionInfo = connectionInfo;
      table.ApplyLogOnInfo(tableLogOnInfo);
  }
}

The first method creates an instance of the ReportDocument class.  This represents the report that you created earlier and allows you to manipulate it at runtime.  The SetTableLocation() method sets the table location for each table in the report.  Again, this assumes you've created an "aspalliance" SQL Login and have given it access to the database.  The ReportDocument class exposes the sub report as a collection.  You can have more than one subreport in a report but for this example we only have one so we have to set the location for the subreport with the index of 0.  The Crystal Report Viewer's source is then set to the report object. 

You can now run the project by pressing F5. Click the Preview button.

This article demonstrates how to use a subreport to retrieve data on the same report from two different databases.  You can expand on this sample by creating multiple subreports in a single report or use a subreport to run separate and distinct queries against the same database but display the data in a single report.  There are many instances where subreports can give you the flexibility you need when a simple SQL query cannot accomplish your goal.  Good luck on your project and happy coding.