Introduction
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, I am not planning to get into the details of Crystal Reports. Every single kid can open Crystal 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. I 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 toCrystalReportViewer 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 http://www.dotnetking.com/contactus.aspx)
• 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.

1. Manually i.e. from a blank document
2. Using Standard Report Expert
3. From an existing report

Using Pull Method

Creating Crystal Reports Manually.

We would use the following steps to implement Crystal Reports using the Pull Model:

1. Create the .rpt file (from scratch) and set the necessary database connections using the Crystal Report Designer interface.

2. Place a CrystalReportViewer control from the toolbox on the .aspx page and set its properties to point to the .rpt file that we created in the previous step.

3. Call the databind method from your code behind page.

I. Steps to create the report i.e. the .rpt file

1) Add a new Crystal Report to the web form by right clicking on the "Solution Explorer", selecting "Add" --> "Add New Item" --> "Crystal Report".

2) On the "Crystal Report Gallery" pop up, select the "As a Blank Report" radio button and click "ok".

3) This should open up the Report File in the Crystal Report Designer.

4) Right click on the "Details Section" of the report, and select "Database" -> "Add/Remove Database".

5) In the "Database Expert" pop up window, expand the "OLE DB (ADO)" option by clicking the "+" sign, which should bring up another "OLE DB (ADO)" pop up.

6) In the "OLE DB (ADO)" pop up, Select "Microsoft OLE DB Provider for SQL Server" and click Next.

7) Specify the connection information.

8) Click "Next" and then click "Finish"

9) Now you should be able to see the Database Expert showing the table that have been selected

10) Expand the "Pubs" database, expand the "Tables", select the "Stores" table and click on ">" to include it into the "Selected Tables" section.

Note: If you add more than one table in the database Expert and the added tables have matching fields, when you click the OK button after adding the tables, the links between the added tables is displayed under the Links tab. You can remove the link by clicking the Clear Links button.

11) Now the Field Explorer should show you the selected table and its fields under the "Database Fields" section, in the left window.

12) Drag and drop the required fields into the "Details" section of the report. The field names would automatically appear in the "Page Header" section of the report. If you want to modify the header text then right click on the text of the "Page Header" section, select "Edit Text Object" option and edit it.

13) Save it and we are through.

II. Creating a Crystal Report Viewer Control

1) Drag and drop the "Crystal Report Viewer>" from the web forms tool box on to the .aspx page

2) Open the properties window for the Crystal Report Viewer control.

3) Click on the [...] next to the "Data Binding" Property and bring up the data binding pop-up window

4) Select "Report Source".

5) Select the "Custom Binding Expression" radio button, on the right side bottom of the window and specify the sample .rpt filename and path as shown in the fig.

6) You should be able to see the Crystal Report Viewer showing you a preview of actual report file using some dummy data and this completes the inserting of the Crystal Report Viewer controls and setting its properties.

Note: In the previous example, the CrystalReportViewer control was able to directly load the actual data during design time itself as the report was saved with the data. In this case, it will not display the data during design time as it not saved with the data - instead it will show up with dummy data during design time and will fetch the proper data only at run time.

7) Call the Databind method on the Page Load Event of the Code Behind file (.aspx.vb). Build and run your .aspx page. The output would look like this.

Using a PUSH model

1. Create a Dataset during design time.

2. Create the .rpt file (from scratch) and make it point to the Dataset that we created in the previous step.

3. Place a CrystalReportViewer control on the .aspx page and set its properties to point to the .rpt file that we created in the previous step.

4. In your code behind page, write the subroutine to make the connections to the database and populate the dataset that we created previously in step one.

5. Call the Databind method from your code behind page.

I. Creating a Dataset during Design Time to Define the Fields of the Reports

1) Right click on "Solution Explorer", select "Add" --> select "Add New Item" --> Select "DataSet"

2) Drag and drop the "Stores" table (within the PUBS database) from the "SQL Server" Item under "Server Explorer".

3) This should create a definition of the "Stores" table within the Dataset

The .xsd file created this way contains only the field definitions without any data in it. It is up to the developer to create the connection to the database, populate the dataset and feed it to the Crystal Report.

II. Creating the .rpt File

4) Create the report file using the steps mentioned previously. The only difference here is that instead of connecting to the Database thru Crystal Report to get to the Table, we would be using our DataSet that we just created.

5) After creating the .rpt file, right click on the "Details" section of the Report file, select "Add/Remove Database"

6) In the "Database Expert" window, expand "Project Data" (instead of "OLE DB" that was selected in the case of the PULL Model), expand "ADO.NET DataSet", "DataSet1", and select the "Stores"table.

7) Include the "Stores" table into the "Selected Tables" section by clicking on ">" and then Click "ok"

8) Follow the remaining steps to create the report layout as mentioned previously in the PULL Model to complete the .rpt Report file creation

III. Creating a CrystalReportViewer Control

9) Follow the steps mentioned previously in the PULL Model to create a Crystal Report Viewer control and set its properties.

Code Behind Page Modifications: 10) Call this subroutine in your page load event:

  Sub BindReport()
    Dim myConnection As New SqlClient.SqlConnection()
      myConnection.ConnectionString = "server= (local)\NetSDK;database=pubs;Trusted_Connection=yes"
    Dim MyCommand As New SqlClient.SqlCommand()
      MyCommand.Connection = myConnection
      MyCommand.CommandText = "Select * from Stores"
      MyCommand.CommandType = CommandType.Text
    Dim MyDA As New SqlClient.SqlDataAdapter()
      MyDA.SelectCommand = MyCommand
    Dim myDS As New Dataset1()
    'This is our DataSet created at Design Time    
      MyDA.Fill(myDS, "Stores")
    'You have to use the same name as that of your Dataset that you created during design time
    Dim oRpt As New CrystalReport1()
      ' This is the Crystal Report file created at Design Time
      oRpt.SetDataSource(myDS)
    ' Set the SetDataSource property of the Report to the Dataset
      CrystalReportViewer1.ReportSource = oRpt
    ' Set the Crystal Report Viewer's property to the oRpt Report object that we created
  End Sub

Note: In the above code, you would notice that the object oRpt is an instance of the "Strongly Typed" Report file. If we were to use an "UnTyped" Report then we would have to use a ReportDocument object and manually load the report file into it.

Enhancing Crystal Reports

Accessing filtered data through Crystal reports

Perform the following steps for the same.

1. Generate a dataset that contains data according to your selection criteria, say "where (cost>1000)".

2. Create the Crystal Report manually. It would look like this.

3. Right Click Group Name fields in the field Explorer window and select insert Group from the shortcut menu. Select the relevant field name from the first list box as shown.

The group name field is created, since the data needs to be grouped on the basis of the cat id say.

4. A plus sign is added in front of Group Name Filed in the field explorer window. The Group Name Field needs to be added to the Group Header section of the Crystal Report. Notice this is done automatically.

5. Right Click the running total field and select new. Fill the required values through > and the drop down list.

6. Since the count of number of categories is to be displayed for the total categories, drag RTotal0 to the footer of the report.

Create a formula

Suppose if the report required some Calculations too. Perform the following steps:

1. Right Click the formula Fields in the field explorer window and select new. Enter any relevant name, say percentage.

2. A formula can be created by using the three panes in the dialog box. The first pane contains all the crystal report fields, the second contains all the functions, such as Avg, Sin, Sum etc and the third contains operators such as arithmetic, conversion and comparison operators.

3. Double click any relevant field name from the forst pane, say there's some field like advance from some CustOrder table. Then expand Arithmetic from the third pane and double click Divide operator.

4. Double click another field name from the first which you want to use as divisor of the first field name already selected say it is CustOrder.Cost.

5. Double Click the Multiply from third pane and the type 100.

6. The formula would appear as {CustOrder.Advance}/{ CustOrder.Cost} * 100.

7. Save the formula and close Formula Editor:@Percentage dialog box.

8. Insert the percentage formula field in the details pane.

9. Host the Crystal report.

Exporting Crystal reports

When using Crystal Reports in a Web Form, the CrystalReportViewer control does not have the export or the print buttons unlike the one in Windows Form. Although, we can achieve export and print functionality through coding. If we export to PDF format, Acrobat can handle the printing for us, as well as saving a copy of the report.

You can opt to export your report file into one of the following formats:

• PDF (Portable Document Format)

• DOC (MS Word Document)

• XLS (MS Excel Spreadsheet)

• HTML (Hyper Text Markup Language - 3.2 or 4.0 compliant)

• RTF (Rich Text Format)

To accomplish this you could place a button on your page to trigger the export functionality.

When using Crystal Reports in ASP.NET in a Web Form, the CrystalReportViewer control does not have the export or the print buttons like the one in Windows Form. We can still achieve some degree of export and print functionality by writing our own code to handle exporting. If we export to PDF format, Acrobat can be used to handle the printing for us and saving a copy of the report.

Exporting a Report File Created using the PULL Model

Here the Crystal Report takes care of connecting to the database and fetching the required records, so you would only have to use the below given code in the Event of the button.

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesButton1.Click
    Dim myReport As CrystalReport1 = New CrystalReport1()
    'Note : we are creating an instance of the strongly-typed Crystal Report file here.
 
    Dim DiskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = NewCrystalDecisions.Shared.DiskFileDestinationOptions
      myReport.ExportOptions.ExportDestinationType = CrystalDecisions.[Shared].ExportDestinationType.DiskFile
    ' You also have the option to export the report to other sources
    ' like Microsoft Exchange, MAPI, etc.    
 
      myReport.ExportOptions.ExportFormatType = CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat
    'Here we are exporting the report to a .pdf format.  You can
    ' also choose any of the other formats specified above. 
 
      DiskOpts.DiskFileName = "c:\Output.pdf"
    'If you do not specify the exact path here (i.e. including
    ' the drive and Directory),
    'then you would find your output file landing up in the
    'c:\WinNT\System32 directory - atleast in case of a
    ' Windows 2000 System
      myReport.ExportOptions.DestinationOptions = DiskOpts
    'The Reports Export Options does not have a filename property
    'that can be directly set. Instead, you will have to use
    'the DiskFileDestinationOptions object and set its DiskFileName
    'property to the file name (including the path) of your  choice.
    'Then you would set the Report Export Options
    'DestinationOptions property to point to the
    'DiskFileDestinationOption object. 
 
      myReport.Export()
    'This statement exports the report based on the previously set properties.
 
  End Sub

refer

http://www.beansoftware.com/ASP.NET-Tutorials/Using-Crystal-Reports.aspx