Crystal Reports is the built-in report designing tool in Visual Studio .NET and it is fully integrated with windows and web applications. It is very easy to use and design the reports with it. To add a report to a visual studio .NET projects (windows or web) you just need to right-click on the project name and add new Items, and add the crystal report from the list. As a result Visual Studio adds a report to the project and opens the report designer to let you design and edit the reports.

 In this article, we are not planning to get into the details of Crystal Reports. Every single kid can open Crystal Report and play with it to design some simple reports (definitely you need to know a lot about crystal to design a professional report but currently that is not our main topic). There are lots of complains about crystal reports from the programmers that this feature is NOT working properly and we see even more complains when developers try it for the web application. We plan to give you and overview and a walkthrough code on how to use crystal reports in the web applications.

Data flow in .NET applications

To understand where the problems come from with this beautiful feature added to Visual Studio .NET we need to understand how the data is transferred from the data provider (SQL Server) to CrystalReportViewer Object. Imagine we are using ADO .NET to get a subset of data from SQL Server and store it in a DataSet. Then we conduct the data from dataset to CrystalReportViewer object to be displayed.

SQL Server -> SQLConnection -> SQLDataAdaptor -> DataSet -> CrystalReportDocument -> CrystalReportViewer -> ASP .NET web page

Hey, what's going on here!? Yes, in .NET application Crystal Report Document reads the data from dataset, NOT directly from data provider and this is the key point to Crystal Report integration with .NET. As a result to open Crystal Reports into a web page we need to follow these steps:

1. Populating DataSet in the same way that we do it in ADO .NET Disconnected scenario.
2. Creating the CrystalReportDocument Object and introducing populated DataSet as it's report source.
3. Redirecting the generated report to CrystalReportViewer web control that is provided by ASP .NET.

Let's do it

Ingredients:

1. Visual Studio .NET or Visual Studio 2003
2. SQL Server 7.0/2000 (If you use MSDE you need to restore Northwind Database, it is not included in MSDE)
3. Microsoft windows XP Professional or 2000 (If you have problems running this code on Windows 2003 server send contact us)

Recipe

1. Preparing SQL Server to run ASP .NET Application
Open the SQL Server Enterprise manager, Expand SQL Server Group -> Computer Name -> Security. Then right-click on logins and New Login... . On General tab click on ellipses in front of the name textbox. Select ASPNET and click on Add, the press OK to add ASPNET account to logins. Then click on Database Access tab and check Northwind in the list. Press OK and make sure if the <ComputerName>\ASPNET account ( add <ComputerName>\IIS_WPG in windows 2003 server instead) is added to logins list. You don't need to give any other permission. Public user has read and write permission to Northwind Database.

2. Creating a New ASP .NET web application
Run the visual Studio .NET and Create a new project. In the project types select Visual Basic Projects, and for the template select ASP .NET Web Application. Change the location to http://localhost/CrystalRepSample and click OK.

3. Connecting the application to Northwind Database in SQL Server and filling the DataSet with Northwind data.
- Open the server explorer (Ctl+Alt+S), right-click on Data Connections, and then Add Connection, for the server name type localhost or (local) or the SQL Server instance name. Select Use windows NT Integrated security and then select Northwind from Select the database on the server.
- In the server explorer expand the newly created connection to Northwind, expand the Tables and the drag the products table and drop it on the form. This creates an SQL ServerConnection object to Northwind as ServerConnection1, and the SQLDataAdaptor1 to pump the data to the DataSet that we are just about to create.
- Right-click on the SQLDataAdaptor1 and click on Generate DataSet... and then in the Generate Dataset dialog box just accept the defaults and click OK. The DataSet11 will be added to your form.
- Double-Click on the Webform1 that will open the webform in code view. In the page load event add the proper code to populate the dataset using SQLDataAdaptor1 as following:
     SqlDataAdapter1.Fill(DataSet11, "Products")
- Now we have a dataset with data that we can provide the data to report designer.

4. Creating the report with Crystal Designer
- In the project menu select Add New Item...
- In the templates box select Crystal Report, change the name to rpProducts.rpt and and click Open.
- To save the time just accept the Report Expert and standard report and press OK.
- In the Standard report expert dialog box in the available data sources expand project data. Expand ADO .NET DataSets and expand CrystalRepSample.DataSet1, you will find Products table in there. Select it and click insert table. Then click Next.
-  In the Fields tab just select some fields and click Add.
- Just click on Finish button and your report is ready.
- At the moment we have a report that reads data from an ADO .NET DataSet

5. Creating the web interface to present report
- Open the Webform1.aspx in design mode.
- Click on a white space on the form and the in the properties window set the pageLayout to FlowLayout. (This step is very important because we are using as Web Custom Control)
- Go to the toolbox and click on Web Forms then select CrystalReportViewer from the list and drag and drop it in the webform1.aspx. CrystalReportViewer1 will be added to the form.

6. Providing the data to report
- Open webform1.aspx in Code View and go to Page_Load sub.
- AFTER the line of code that you have already added, add the following code

Dim cr As New rpProducts() ' Creates the ReportDocument object
cr.SetDataSource(DataSet11) ' Defines the source of data for report which is DataSet11
CrystalReportViewer1.ReportSource = cr ' Makes the ReoprtViewer web control to know it's report source
CrystalReportViewer1.DataBind() ' You need to remind CrystalReportViewer1 that there is some data. Update the interface 

7. Building and testing the application
Just press Ctl +F5 to build and run the application