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 and Subreports

Subreports in Crystal Reports

This article provides step by step instruction on how to create and use a subreport in a Crystal Report on a WebForm (.aspx file). The main report will be based on a data set of Customers from the AdventureWorks database. The subreport dataset will be based on these same customers orders: Order No, Order Date, Total Amounts. Using subreports in Crystal Reports is very easy to do compared to subreports in SSRS as there are only a few steps to link the reports together - no report parameter is required and no query parameter is required which makes the processing time less than an SSRS report using a Subreport, but not less than an SSRS Composite report using grouping.

Creating the Data Sources for the Reports

The data sources for the parent\master report and the child\subreport are Views that are created in SSMS on SQL Server 2012 in the AdventureWorks database.

CREATE VIEW [dbo].[vw_Customers]
AS
SELECT Sales.CustomerAddress.CustomerID, Person.Contact.FirstName + CASE WHEN len(Person.Contact.MiddleName) > 0 THEN ' ' + Person.Contact.MiddleName ELSE '' END + ' ' + Person.Contact.LastName AS CustomerName,
Person.Address.AddressLine1 + CASE WHEN len(Person.Address.AddressLine2) > 0 THEN ' ' + Person.Address.AddressLine2 ELSE '' END AS AddressLn1,
Person.Address.City + ', ' + Person.StateProvince.Name + ' ' + Person.Address.PostalCode AS AddressLn2
FROM Sales.Customer
INNER JOIN Sales.CustomerAddress ON Sales.Customer.CustomerID = Sales.CustomerAddress.CustomerID
INNER JOIN Person.Address ON Sales.CustomerAddress.AddressID = Person.Address.AddressID AND Sales.CustomerAddress.AddressID = Person.Address.AddressID
INNER JOIN Sales.Individual ON Sales.Customer.CustomerID = Sales.Individual.CustomerID
INNER JOIN Person.Contact ON Sales.Individual.ContactID = Person.Contact.ContactID AND Sales.Individual.ContactID = Person.Contact.ContactID AND Sales.Individual.ContactID = Person.Contact.ContactID
INNER JOIN Person.StateProvince ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID

And create code for the 2nd View, dbo.vw_CustomerOrders

CREATE VIEW [dbo].[vw_CustomersOrders]
AS
SELECT Sales.Customer.CustomerID, Sales.SalesOrderHeader.SalesOrderNumber,
Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt, Sales.SalesOrderHeader.Freight, Sales.SalesOrderHeader.OrderDate
FROM Sales.Customer
INNER JOIN Sales.SalesOrderHeader ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID

Create and set up the Crystal Reports

Open up Visual Studio to create a new website and then Add a New Item and select Crystal Report and change the name to Customers.

Adding new Crystal Report

Select As a Blank Report from the box that will pop up after you clicked Add in the previous dialog box.

Setting Crystal Reports to use a blank report

In the Field Explorer, Right Click on the Database Expert and click the Plus Sign next to the OLE DB (ADO) folder under the Create New Connection folder. A pop up will appear allowing you to set up a SQL Native Client connection. Just select your SQL Native Client version, click Next to complete the SQL Server name and Database Name.

Using OLE DB as the DataSource

You can then select the database name and click on the View name and add it as the Data Source of the Report.

Getting the DataSource

Right click on the body of the report and select Group Expert. Open the View and select CustomerID to Group By. Click OK to close.

Click in the Field Explorer to drag and drop the CustomerName into the Group Section next to "Group #1 Name" and Right click and click Insert TextObject to add a Text Box that you can manually type in "Customer ID:". This will help the End User understand what they are looking at in the report.

Main Report Database Fields

View of the Group Section with each textbox object changed to a Bold Font.

Formatting Group #1

Create and add a 2nd Crystal Report to the project called CustomerOrders.rpt as a Blank Report. Go to the Database Expert and the data Source should be available under OLE ADO. Just select the View vw_CustomerOrders as the source. Add each field by dragging and dropping it in the Details section of the Report from the Field Explorer under Database Fields.

Database Fields for Customer Orders

The view of the report. As you drag and drop the fields, the Page Header section is populated with a TextObject of the same name with an Underline. I eliminated the white space between the Report Header and Footer as I will not be using them in the report as a subreport.

Formatting the Subreport

Right click the Order Date field and select Format Object to change the format of the Date and Time from System Default Short Format (which includes the time) to by the 03/01/1999 format. It will look cleaner than the default on the report.

Formatting the Date

In the Customers.rpt report, Right click on the body of the report and select Insert, Subreport. Hover over the Details section (a box will appear under the cursor) and place the box as far to the left inside of the Details Section.

Inserting the Subreport

A pop up dialog box will appear. Select Choose an Existing report option and browse to the location of the CustomerOrders.rpt. Click on the Link tab.

Setting the Subreport

Select the CustomerID field from the Report fields and click the Arrow to add it to the Fields to link to Box. The Subreport parameter field to use will be automatically set for you.

Linking the Subreport to the Main report

I added a Text Object to the Page Header and changed the Font size and made it bold. I also added the Special Field of Print Time to the Header and formatted it as well. I put a line in at the top of the Group Section to separate the Customers visually once the report renders.

Subreport Imported

Add a Web Page With a Crystal ReportViewer and ReportSource

Add a new item of a Web Form to the project.

Adding the WebForm

From the Toolbox, add the Crystal ReportViewer to the WebForm and then select the ReportSource and select the Customers.rpt report.

Crystal ReportViewer and ReportSource

Set the webForm as the Start Page by Right clicking on it. Run the project and view the report.

View in webpage of Crystal Report

If you still needed to make changes to the subreport, you can, just remember to right click on it in the Main report and select Re-import subreport to get the updated version.

Re-import of Subreport