Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


Crystal Reports Multiple DataTables ASPX Website

Crystal Reports using ReportDocument & Subreport with 2 DataTables in a DataSet

This article shows the steps to creating a webpage with Crystal Report that has a Subreport embedded in it. The Default page will display a list of Vendors from the AdventureWorks2014 database in a DropDownList. Once selected the VendorID will be passed to a second ASPX page in the QueryString to be used in populating 2 DataTables in a DataSet that will be the DataSource for the Main Report and Subreport of the Crystal Report embedded in it.

Create a DataSet

Create a New WebSite project in Visual Studio and select ASP.Net Web Forms Site.

New WebSite Project

This project will use 2 SQL Views in the AdventureWorks2014 database: one to get the Vendors and one to get the PurchaseOrder data of the Vendors:

Vendors View -

CREATE View [dbo].[vw_PurchaseOrderVendorList]
as
 SELECT BusinessEntityID, Name as Vendor,
 AddressLine1 + ' ' + IsNull(AddressLine2,'') as Addr1,
 City + ', ' + StateProvinceName + ' ' + PostalCode as Addr2,
 p.PurchaseOrderID, p.OrderDate, p.SubTotal, p.TaxAmt, [Freight],p.TotalDue
 from [Purchasing].[vVendorWithAddresses] pv left outer join
 [Purchasing].[PurchaseOrderHeader] p on p.VendorID = pv.BusinessEntityID

Vendor PurchaseOrders View -

CREATE VIEW [dbo].[vw_ProductsOrders]
AS
 select pd.*,p.OrderDate, pp.Name as ProductName,
 ps.Name as SubCatName, pc.Name as Category,
 (pd.OrderQty * pd.UnitPrice) as OrderTotal, p.VendorID
 from [Purchasing].[PurchaseOrderHeader] p inner join
 [Purchasing].[PurchaseOrderDetail] pd on pd.PurchaseOrderID = p.PurchaseOrderID
 inner join [Production].[Product] pp on pp.ProductID = pd.ProductID
 left outer join [Production].[ProductSubcategory] ps on ps.ProductSubcategoryID =  pp.ProductSubcategoryID
 left outer join Production.ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID

Add a New Item and Select DataSet.

New Item DataSet

Right click on the DataSet and click Add, New TableAdapter.

DataSet TableAdapter

Set up the DataConnection to the tableAdapters to use the Views on SQL Server created above.

SQL Data Connection for DataSet

Choose the DataSource of SQL Server.

Microsoft SQL Server Data Source

Add the SQL connection to the DataSet.

SQL DataSet Connection

Select the New data connection just created to connect the TableAdapter to the View.

TableAdapter SQL Connection

Save the Connection String to the Application Config file.

App Config File SQL ConnectionString

Configure the TableAdapter by Selecting Use SQL Statements option and click Next.

TableAdapter Configuration Wizard

In the Enter a SQL Statement click on Query Builder to open a new window to build the Query (to add an ID parameter).

SQL Statement Window

Select fields from the View (if not already selected) and add a parameter named @id to the Filter section of the BusinessEntityID field as shown below. Click OK to Continue.

Query Builder Window to Add Parameter to Filter

Click Next in the SQL Statement window to continue.

SQL Statement Window with complete query including Where filter clause

Click Next in the Methods to Generate window to continue.

Choose Methods to Generate window

Repeat the process of to add another TableAdapter to the DataSet and connect to vw_ProductOrders view and add the @id field in the SQL Statement step for the VendorID

Data Set with TableAdapters

Create Crystal Report and a SubReport

Add a New Item of a Crystal Report to the Web Project and click through the windows to build a new report.

New Crystal Report Window

Select the ADO .NET DataSets from the ProjectData Data Source and select vw_PurchaseOrderVendorList.

Crystal Reports DataSet Source

Select the fields to include the report from the DataSet.

Crystal Reports DataSet fields

Repeat the prcess for an additional Crystal Report for the vw_ProductOrders (Purchase Order Details) DataSet TableAdapter which will be used as a SubReport in the PurchaseOrderVendorList Report.

Vendor List Main Crystal Report with SubReport

SubReport crVendorOrders.rpt report Design View which uses vw_ProductOrders ADO DataSet.

SubReport for Main Report using DataSet

Create a Web Page for the Crystal Report

Add a New WebPage to the Project called Vendors.aspx. Add the references to Crystal Decisions in the Vendors.aspx.cs webPage code.

Crystal Reports references in ASPX code

Add code above the Page_Load Method in the code for Vendors.aspx.cs to create a ReportDocument (crpt).
Add code in the Page_Load Method to pass the QueryString member VendorID to a local variable id.
Add additional code to instantiate a local copy of the DataSet Vendor and create a tableAdapter variable to instantiate and Fill the Data for the PurchaseOrderVendorList.
The code includes a call to instantiate a DataTable for the vw_PurchaseOrderVendorList and finally a call to the Fill Method - which contains both the DataTable and the Variable @id that the Method is expecting. Without the ID field, this code would throw an Exception.

TableAdapter, DataTable, and Fill Method for table 1

Add an additional tableAdapter, dataTable, and call to the Fill method of the 2nd TableAdapter for vw_ProductOrders.

TableAdapter, DataTable, and Fill Method for table 2 for SubReport

Below the code for the 2nd Table Adapter, add code to map the server Path to the Crystal Report (the Main Report containing the SubReport) and Set the DataSource for the Main Report (ds.Tables[0]) and the SubReport (ds.Tables[1]).
Set the Report Viewer ReportSource in the Vendors.aspx page to the ReportDocument crpt.

Mapping DataSet Tables to the Main Report and Subreports

A look at all of the code put together to add 2 TableAdapters and set the Crystal Report DataSource to each table from the DataSet.

Adding Data Adapters and Data Source to Project

Creating a DropDownList to select a Vendor and Display Vendors.aspx Page with Report

Modify the Default.aspx page to add a SqlDataSource, a DropDownList ordered by Name, and a Button to click to redirect to the Vendors.aspx page

Default.aspx SQLDataSource with DropDownList to select Vendor ID for QueryString

Add code to the Button to add a QueryString variable of VendorID to the Redirect so that the Vendors.aspx page may process the data for the Vendor Selected in the Default page.

Code for DropDownList to create Redirect to CrystalReports web page with QueryString variable

Test the Website

The Default.aspx page should look similar to the image below. Select a Vendor name from the list and press the Click for Report button.

Website with DropDownList for Testing

If successful, the redirect will pass the QueryString variable to the Vendors.aspx page and the report with the subreport data will appear.

Crystal Report with Subreport using 2 Table Adapters

As not all Vendors will have purchase Orders, it is a good idea to suppress a Blank Subreport in the Main report or add text to inform the user that there is No Data.

Right click on the subreport and click Format Object and select the subreport tab and select Suppress Blank Subreport.

Suppress Subreport in Crystal Reports

The Crystal Report will appear as shown below for Vendors without Purchase Orders having a blank subreport.

Main Report without Subreport