Building Reports using ASP.NET and Crystal Reports - Part 7 Using Subreports to Create Advanced Reports
page 1 of 7
Published: 15 Dec 2009
Abstract
In this article, Vince examines the creation of a report that connects to two databases but displays the data on one report with he help of relevant screenshots and source code. Sub reports embed one report inside another while linking on a common field. They can be useful when you need to report data from more than one data source or when you need to run separate and distinct queries to retrieve data on a report.
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 44845/ 59

Introduction

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.


View Entire Article

User Comments

Title: Crystal Report   
Name: B.Shah
Date: 2010-09-21 6:43:53 AM
Comment:
Nice Article..Thanks
Title: Donor Database Administrator   
Name: Abdillahi Jibril
Date: 2010-01-27 11:00:37 AM
Comment:
I have medium to advanced level experience in Crystal Report, and your article helps me improve my skills to be better Crystal Report Developer.
Title: crystal reports   
Name: joohitha
Date: 2010-01-05 11:47:19 PM
Comment:
nice article

Tks vincey
Title: crystal reports tutor   
Name: lokesh
Date: 2009-12-28 5:02:03 AM
Comment:
Thanks vincey
very nice and very help full for crystal report beginners.
i will be at lokesh_bsc@yahoo.com

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-04-28 9:37:35 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search