VB.

NET Crystal Reports Related Contents

* * * * * * * * * * * * * * * * * * * * Sample Database and tables for Crystal Reports tutorials Step by Step help for creating a simple Crystal Reports in VB.NET VB.NET Crystal Reports from multiple tables VB.NET Crystal Reports String parameter VB.NET Crystal Reports Integer parameter VB.NET Crystal Reports Date parameter VB.NET Crystal Report Load Dynamically VB.NET Crystal Reports Formula Fields VB.NET Crystal Reports Summary Fields VB.NET Crystal Reports Export to PDF VB.NET Crystal Reports Export to Excel Email a Crystal Reports from VB.NET Crystal Reports Without Database Crystal Report from SQL Query String Dynamic Crystal Reports from SQL Query String Crystal Reports from XML File Create a Subreport in Crystal Reports Create a Subreport in Crystal Reports with Link How to deploy Crystal Reports on Clinet Machine How to create Crystal Reports installer using Merge Modules

* Sample Database for running Crystal Reports tutorials

In the following section you can see , how to create a sample Database and Tables and data for running Crystal Reports Tutorials . All examples in the VB.NET Crystal Reports Tutorials are based on the following database . First we have to create a database . Give the database name as "crystaldb" Create a DataBase "crystaldb" In the crystaldb database , create three tables OrderMaster , OrderDetails , Product . OrderMaster OrderMaster_id OrderMaster_date OrderMaster_customer OrderMaster_createduser OrderDetails OrderDetails_id OrderDetails_masterid OrderDetails_productid

OrderDetails_qty Product Product_id Product_name Product_price The following picture shows the relations of each table :

SQL command for creation tables are follows : CREATE TABLE [dbo].[OrderMaster] ( [OrderMaster_id] [int] NOT NULL , [OrderMaster_date] [datetime] NULL , [OrderMaster_customername] [varchar] (50), [OrderMaster_createduser] [varchar] (50) ) ON [PRIMARY] CREATE TABLE [dbo].[OrderDetails] ( [OrderDetails_id] [int] NOT NULL , [OrderDetails_masterid] [int] NULL , [OrderDetails_productid] [int] NULL , [OrderDetails_qty] [int] NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Product] (

[Product_id] [int] NOT NULL , [Product_name] [varchar] (50) , [Product_price] [numeric](18, 0) NULL ) ON [PRIMARY] Enter data to the tables :

Order Master Table Data

Order Details Table Data

Product Table Data

* VB.NET Crystal Reports for Beginners

Start your first VB.NET Crystal Reports . All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . Open Visual Studio .NET and select a new Visual Basic .NET Project.

Create a new Crystal Reports for Product table from the above database crystalDB. The Product Table has three fields (Product_id,Product_name,Product_price) and we are showing the whole table data in the Crystal Reports. From main menu in Visual Studio select PROJECT-->Add New Item . Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.

Select Report type from Crystal Reports gallery.

Accept the default settings and click OK. Next step is to select the appropriate connection to your database. Here we are going to select OLEDB connection for SQL Server

Select OLE DB (ADO) from Create New Connection .

Select Microsoft OLE DB Provider for SQL Server .

Next screen is the SQL Server authentication screen . Select your Sql Server name , enter userid , password and select your Database Name . Click next , Then the screen shows OLE DB Property values , leave it as it is , and click finish. Then you will get your Server name under OLEDB Connection from there select database name (Crystaldb) and click the tables , then you can see all your tables from your database. From the tables list select Product table to the right side list .

Click Next Button Select all fields from Product table to the right side list .

Click Finish Button. Then you can see the Crystal Reports designer window . You can arrange the design according your requirements. Your screen look like the following picture.

Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Reports Viewer control . Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form.

Select Form's source code view and put the code on top Imports CrystalDecisions.CrystalReports.Engine Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Reports is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. After you run the source code you will get the report like this.

Hope this tutorial help you to create your first Crystal Reports.

* VB.NET Crystal Reports from multiple tables

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . Here we are going to do is to generate Crystal Reports from multiple tables. Here we have three table (ordermaster , orderdetails amd product ) and we are generating report from these three tables by connecting each tables with their related fields. Open Visual Studio .NET and select a new Visual Basic .NET Project.

From main menu in Visual Studio select PROJECT-->Add New Item . Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.

Select Report type from Crystal Reports gallery.

Accept the default settings and click OK. Next step is to select the appropriate connection to your database. Here we are going to select OLEDB connection for SQL Server

Select OLE DB (ADO) from Create New Connection .

Select Microsoft OLE DB Provider for SQL Server .

Next screen is the SQL Server authentication screen . Select your Sql Server name , enter userid , password and select your Database Name . Click next , Then the screen shows OLE DB Property values , leave it as it is , and click finish. Then you will get your Server name under OLEDB Connection from there select database name (Crystaldb) and click the tables , then you can see all your tables from your database. Select all table from the table list to right side list box, because we are creating report from three tables ( OrderMaster, OrderDetails, Product) .

The next step is to make relation between these selected tables. Here we are connecting the related fields from each table. For that we arrange the tables in visible area in the list (this is not necessary ) and select the field we are going to make relation and drag to the related field of the other table. After made the relation the screen is look like the following picture .

Next step is to select the fields from the tables . Here we are selecting only Customername , orderdate from ordermastertable , Productname from product table and quantity from order details.

Click the Finish button because now we are not using other functionalities of this wizard. After that you will get the Crystal Reports designer window . You can arrange the fields in the designer window according to your requirement to view the report . For rearranging you can drag the field

object in the screen . For editing right click the field object and select Edit Text Object. The following picture shows the sample of designer window after rearrange the field.

Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Reports Viewer control . Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form.

Select Form's source code view and put the code on top

Imports CrystalDecisions.CrystalReports.Engine Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. After you run the source code you will get the report like this.

Here we connected three tables related field and get the result . If you have any comments please contact the email address in our contact page.

* VB.NET Crystal Reports String parameter

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . Here we are passing a String parameter from vb.net to Crystal Reports . For that , from vb.net program we pass a customer name as parameter and show all the order of that customer to the Crystal Reports. In the previous tutorial we saw how to generate a Crystal Reports from multiple tables. Here is the continuation of that tutorial , the only different is that we are passing a Customer Name as a String parameter and get the report of that particular Customer only . Before starting to this tutorial just take a look at the previous tutorial ( Crystal Report from multiple tables ). In the previous section we are getting the report of all orders from all customers , that is the all order placed by all customers , here is only one customer. Hope you went through previous tutorial , if not click here ( Crystal Report from multiple tables ). Next step is to create a string parameter in Crystal report. Select the Field Explorer from CrystalReport Menu.

Then you can see Field Explorer in the Left hand side. Select Parameter Field from Field Explorer and right Click.

Fill the appropriate name for Name and Promting text fields

After creating the parameter field , we have to create the selection formula for the Crystal Reports . For creating selection formula , Right click on Crystal Reports designer window , select REPORT -> SELECTION FORMULA -> RECORD .

Then you can see the record Selection Formula Editor. This for entering the selection formula. For that you have to select the fields from above fields and make the formula . First you have to select OrderMaster.OrderMaster_customername from Report Field and select the comparison operator and select the parameter field. Double click each field then it will be selected. Form the following picture you can understand the selection fields.

After the creation of selection formula close that screen . Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Reports Viewer control . Select the default form (Form1.vb) you created in VB.NET and drag a Textbox , button and CrystalReportViewer control to your form.

Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click

Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") Dim Dim Dim Dim crParameterFieldDefinitions As ParameterFieldDefinitions crParameterFieldDefinition As ParameterFieldDefinition crParameterValues As New ParameterValues crParameterDiscreteValue As New ParameterDiscreteValue

crParameterDiscreteValue.Value = TextBox1.Text crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields crParameterFieldDefinition = _ crParameterFieldDefinitions.Item("Customername") crParameterValues = crParameterFieldDefinition.CurrentValues crParameterValues.Clear() crParameterValues.Add(crParameterDiscreteValue) crParameterFieldDefinition.ApplyCurrentValues(crParameterValues) CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. Now you can run the program . Enter a Customer Name(enter any existing customer from Ordermaster table) and click the button , then your report is look like the following picture.

Here we get the result of the Customer4's order details.

* VB.NET Crystal Reports Integer parameter

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we are passing an Integer Parameter to Crystal Reports from VB.NET. In the previous tutorial , we saw passing a string parameter to Crystal Reports from VB.NET and get the result. This is very similar to that tutorial , so here showing only the change information part only. So please take a look at the complete part of passing a string parameter to Crystal Reports from VB.NET. When we pass an Integer parameter , we have to create a new Integer parameter in the Parameter Fields of Field Explorer. Then we will get the following screen and fill the fields like in the picture .

After creating the parameter field , we have to create the selection formula for the Crystal Reports . For creating selection formula , Right click on Crystal Reports designer window , select REPORT -> SELECTION FORMULA -> RECORD . Then you can see the record Selection Formula Editor. This for entering the selection formula. For that you have to select the fields from above lists and make the formula . Here we made the formula like select the records from Product table whose value is greater than the input value. For that first we select the table field that is Product_price from Product table and then we select the comparison operator and finally we select our Parameter we created earlier. Double click each field then it will automatically selected

After the creation of selection formula close that screen . Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Reports Viewer control . Select the default form (Form1.vb) you created in VB.NET and drag a Textbox , button and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") Dim Dim Dim Dim crParameterFieldDefinitions As ParameterFieldDefinitions crParameterFieldDefinition As ParameterFieldDefinition crParameterValues As New ParameterValues crParameterDiscreteValue As New ParameterDiscreteValue

crParameterDiscreteValue.Value = Convert.ToInt32(TextBox1.Text) crParameterFieldDefinitions = _ cryRpt.DataDefinition.ParameterFields crParameterFieldDefinition = _ crParameterFieldDefinitions.Item("Price") crParameterValues = crParameterFieldDefinition.CurrentValues crParameterValues.Clear() crParameterValues.Add(crParameterDiscreteValue) crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. Now you can run the program . Enter any price , then you can see the report of the Product list whose price is greater than or equal to the entered price.

Here we got the result of Product list whose price is grater than 50.

* VB.NET Crystal Reports Date parameter

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we are passing a date variable to Crystal Reports from VB.NET. In the previous tutorials , we saw passing a string parameter to Crystal Reports , integer parameter to Crystal report from VB.NET and get the result. This tutorial is very similar to the above two tutorials , so please take look into the two tutorials before we start this one. When we pass a Date parameter, we have to create a new date parameter in the Parameter Fields of Field Explorer. Then we will get the following screen and fill the fields like in the picture .

After creating the parameter field , we have to create the selection formula for the Crystal Reports . For creating selection formula , Right click on Crystal Reports designer window , select REPORT -> SELECTION FORMULA -> RECORD . Then you can see the record Selection Formula Editor. This for entering the selection formula. For that you have to select the fields from above lists and make the formula . Here we are making the formula like , select all records details from the tables whose order date is greater than the input date parameter. For doing this you have to select Ordermaster.orderdate , comparison operator and parameter date field from selection list of Formula Editor and make the formula.

After the creation of selection formula close that screen . Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Report Viewer control . Select the default form (Form1.vb) you created in VB.NET and drag a Textbox , button and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") Dim Dim Dim Dim crParameterFieldDefinitions As ParameterFieldDefinitions crParameterFieldDefinition As ParameterFieldDefinition crParameterValues As New ParameterValues crParameterDiscreteValue As New ParameterDiscreteValue

crParameterDiscreteValue.Value = TextBox1.Text crParameterFieldDefinitions = _ cryRpt.DataDefinition.ParameterFields crParameterFieldDefinition = _ crParameterFieldDefinitions.Item("Orderdate") crParameterValues = crParameterFieldDefinition.CurrentValues crParameterValues.Clear() crParameterValues.Add(crParameterDiscreteValue) crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. Now you can run the program . Enter any date , then you can see the report whose order date is greater than or equal to the entered date.

Here we got the result of orders whose date is greater than the entered date

* VB.NET Crystal Reports Load Dynamically

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this section we are passing User ID , Password , Server Name and Database Name dynamically to Crystal Reports from vb.net . SITUATIONS : In many situations this is useful , for example if you develop a database project in a test server and later you have to migrate it , in such situations you have to give these information dynamically to Crystal Reports. In this program we are using our earlier program and pass the values dynamically to Crystal Reports. Before we start this section take a look at the step by step Crystal Report in VB.NET . In the step by step Crystal Report we created a report selecting all data from the Product table . There is no chance to change the server on runtime in that case because it is a static report . Here we are passing Server Name , UserID and Password dynamically to the Crystal Reports. Here we use Crystal Reports ConnectionInfo . Dim crConnectionInfo As New ConnectionInfo , and pass these arguments to ConnectionInfo Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument Dim crtableLogoninfos As New TableLogOnInfos Dim crtableLogoninfo As New TableLogOnInfo Dim crConnectionInfo As New ConnectionInfo Dim CrTables As Tables Dim CrTable As Table cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") With crConnectionInfo .ServerName = "YOUR SERVER NAME" .DatabaseName = "YOUR DATABASE NAME" .UserID = "YOUR DATABASE USERNAME" .Password = "YOUR DATABASE PASSWORD" End With CrTables = cryRpt.Database.Tables For Each CrTable In CrTables

Next

crtableLogoninfo = CrTable.LogOnInfo crtableLogoninfo.ConnectionInfo = crConnectionInfo CrTable.ApplyLogOnInfo(crtableLogoninfo)

CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. You have to replace the source code values of "YOUR SERVER NAME" , "YOUR DATABASE NAME" , "YOUR DATABASE USERNAME" , "YOUR DATABASE PASSWORD" with your correct values . When you run this program you will get the following screen.

* VB.NET Crystal Reports Formula Fields

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we are adding a Formula Field in existing Crystal Reports . SITUATIONS : If you have a Crystal Reports with Qty and Price , you need an additional field in your Crystal Reports for the Total of QTY X PRICE . In this situation you have to use the Formula Field in Crystal Reports. In this tutorial we are showing the all orders with qty and price and the total of each row , that means each in each row we are showing the total of qty and price. Before starting this tutorial. Create a new Crystal Reports with fields CustomerName , Order Date , Product Name and Product Price . If you do not know how to create this report , just look the previous tutorial Crystal Report from multiple tables . In that report selecting only four fields , here we need one more field Prodcut>Price . After you create the Crystal Reports you screen is look like the following picture :

Next is to create the a Formula Field for showing the total of Qty and Price . Right Click Formula Field in the Field Explorer and click New. Then you will get an Input Message Box , type Total in textbox and click Use Editor

Now you can see Formula Editor screen . Now you can enter which formula you want . Here we want the result of Qty X Price . For that we select OrderDetails.Qty , the multiplication operator and Product.Price . Double click each field for selection.

Now you can see Total Under the Formula Field . Drag the field in to the Crystal Reports where ever you want .

Now the designing part is over . Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt

CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. When you run this program you will get the following screen.

* VB.NET Crystal Reports Summary Fields

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we are taking the sum of field value of Total . This is the continuation of the previous tutorial Crystal Report Formula Field . So before we start this tutorial , take a look at the previous tutorial Crystal Report Formula Field. Here we are taking the grand total of the Total field . The Total field is a Formula field is the result of qty X price . In the Crystal Reports designer view right click on the Report Footer , just below the Total field and select Insert -> Summary .

Then you will get a screen , select the Total from the combo box and Sum from next Combo Box , and summary location Grand Total (Report Footer) . Click Ok button

Now you can see @Total is just below the Total field in the report Footer.

Now the designing part is over . Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Put the following source code in the button click event
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. When you run this program you will get the following screen.

* VB.NET Crystal Reports Export to PDF

Exporting from Crystal Reports to PDF format , we are using Crystal Reportss CrExportOptions . Also we have to set PdfRtfWordFormatOptions and ExportFormatType.PortableDocFormat . In the following example you can see how to export a Crystal Reports as a PDF format file. All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we are using our earlier program step by step Crystal Report for pull data from database to Crystal Reports . Before we start this section take a look at the step by step Crystal Report in VB.NET . In the step by step Crystal Report we pull all data from Product table , here now we are exporting that data to a PDF format file. Select the default form (Form1.vb) you created in VB.NET and drag two buttons (Button1, Button2 ) and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Put the following source code in the button click events
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Dim cryRpt As New ReportDocument Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Try Dim CrExportOptions As ExportOptions Dim CrDiskFileDestinationOptions As New _ DiskFileDestinationOptions() Dim CrFormatTypeOptions As New PdfRtfWordFormatOptions() CrDiskFileDestinationOptions.DiskFileName = _ "c:\crystalExport.pdf" CrExportOptions = cryRpt.ExportOptions With CrExportOptions .ExportDestinationType = ExportDestinationType.DiskFile .ExportFormatType = ExportFormatType.PortableDocFormat .DestinationOptions = CrDiskFileDestinationOptions .FormatOptions = CrFormatTypeOptions End With cryRpt.Export() Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. When you run this program you will get the PDF file (crystalExport.pdf) in your computer's C:

* VB.NET Crystal Reports Export to Excel

Exporting from Crystal Reports to Excel format , we are using Crystal Reports CrExportOptions . Also we have to set ExcelFormatOptions and ExportFormatType.Excel . In the following example you can see how to export a Crystal Reports as a Excel format file. All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we are using our earlier program step by step Crystal Report pull data from database to Crystal Reports . Before we start this section take a look at the step by step Crystal Report in VB.NET . In the step by step Crystal Report we pull all data from Product table , here now we are exporting that data to a Excel format file. Select the default form (Form1.vb) you created in VB.NET and drag two buttons (Button1, Button2 ) and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Put the following source code in the button click events
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Dim cryRpt As New ReportDocument Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Try Dim CrExportOptions As ExportOptions Dim CrDiskFileDestinationOptions As New _ DiskFileDestinationOptions() Dim CrFormatTypeOptions As New ExcelFormatOptions CrDiskFileDestinationOptions.DiskFileName = _ "c:\crystalExport.xls" CrExportOptions = cryRpt.ExportOptions With CrExportOptions .ExportDestinationType = ExportDestinationType.DiskFile .ExportFormatType = ExportFormatType.Excel .DestinationOptions = CrDiskFileDestinationOptions .FormatOptions = CrFormatTypeOptions End With cryRpt.Export() Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. When you run this program you will get the Excel file (crystalExport.xls) in your computer's C:

* Email a Crystal Reports from VB.NET

For Email a Crystal Reports , we have to export the Crystal Reports in any of the File Format available in Crystal Reports and then Email it. All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . In this tutorial we use the tutorial Export Crystal Report to PDF file . So before we start this section please take a look at the tutorial Export Crystal Report to PDF file After export the Crystal Reports as a PDF file , the next step is to email that file . For that here we are using System.Web.Mail . We have to provide the necessary information to configuring SmtpMail client and send the exported file as attachment . Select the default form (Form1.vb) you created in VB.NET and drag two buttons (Button1, Button2 ) and CrystalReportViewer control to your form. Select Form's source code view and import the following : Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Web.Mail Put the following source code in the button click events
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Web.Mail Public Class Form1 Dim cryRpt As New ReportDocument Dim pdfFile As String = "c:\ProductReport1.pdf" Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Try Dim CrExportOptions As ExportOptions Dim CrDiskFileDestinationOptions As New _ DiskFileDestinationOptions() Dim CrFormatTypeOptions As New PdfRtfWordFormatOptions CrDiskFileDestinationOptions.DiskFileName = pdfFile CrExportOptions = cryRpt.ExportOptions With CrExportOptions .ExportDestinationType = ExportDestinationType.DiskFile .ExportFormatType = ExportFormatType.PortableDocFormat .DestinationOptions = CrDiskFileDestinationOptions .FormatOptions = CrFormatTypeOptions End With cryRpt.Export() Catch ex As Exception

MsgBox(ex.ToString) End Try sendMail() End Sub Private Sub sendMail() Try Dim Smtp As SmtpMail SmtpMail.SmtpServer.Insert(0, "your hostname") Dim Msg As MailMessage = New MailMessage Msg.To = "to address here" Msg.From = "from address here" Msg.Subject = "Crystal Report Attachment " Msg.Body = "Crystal Report Attachment " Msg.Attachments.Add(New MailAttachment(pdfFile)) Smtp.Send(Msg) Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

NOTES: cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here. Before you run this programme , you have to provide the necessary SMTP informations , that is your HOSTNAME , FROM ADDRESS and TO ADDRESS to the SMTP client.

* Crystal Reports Without Database

Usually Crystal Reports we are using to fetch data from database and show it as a report. Here we are going to generate a Crystal Reports without using a datbase . For generating a Crystal Reports without database , here we are using a Strongly Typed Dataset and a Data Table. The basics of Crystal Reports creation you can find in the previous section of this tutorial , before we start this tutorial you can take a look at the step by step Crystal Report. Generating a Strongly Typed DataSet Create a new VB.NET Project and accept the default settings. Create a new Dataset from Project Add New Item Dialogue Box.

Accept the default name DataSet1.xsd . Create a data table for DataSet1.xsd . Select DataSet1.xsd from Solution Explorer and right click . Select datatable from the menu. Then you will get a datatable in the Datast . Right click the datatable and select Add-Column .

Here we are making a two column Crystal Reports , so we need two column in the data table . Add and ID column and Name column in the Data Table.

Now the dataset part is over . Next step is to create a Crystal Reports from this Dataset . Before going to make Crystal Reports design please take a look at step by step Crystal Report for easy creation of Crystal Reports. Select a new Crystal Reports from Add New Item menu and accept the default settings. The next screen is to select appropriate data source . There you can find the Datatable1 from Project data ADO.NET Datasets , and select Datatable1 to the right side.

Click Next button and select ID and Name from the datatable1 to right side and click finish.

Now the Crystal Reports designer part is over . Next part is to create data for the Crystal Reports . For that we have to create a Data Table through programmatically and add data to dataset1. Select the default form (Form1.vb) you created in VB.NET and drag one button and CrystalReportViewer control to your form. Put the following source code in the button click events
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim ds As New DataSet1 Dim t As DataTable = ds.Tables.Add("Items") t.Columns.Add("id", Type.GetType("System.Int32")) t.Columns.Add("Item", Type.GetType("System.String")) Dim r As DataRow Dim i As Integer For i = 0 To 9 r = t.NewRow() r("id") = i r("Item") = "Item" & i t.Rows.Add(r) Next Dim objRpt As New CrystalReport1 objRpt.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = objRpt CrystalReportViewer1.Refresh() End Sub End Class

* Crystal Reports from SQL Query String

We can create Crystal Reports in VB.NET using SQL Query String . Here we create a Strongly Typed dataset for Crystal Reports design and create a connection object and execute the SQL Query String . All Crystal Report programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . The basics of Crystal Reports creation you can find in the previous section of this tutorial , before we start this tutorial you can take a look at the step by step Crystal Report. Generating a Strongly Typed DataSet In the previous section you can see a detailed tutorial about to create a strongly typed datset and its Crystal Reports design . After create the dataset and Crystal Reports design you should create a connection object and fetch the data from database. Select the default form (Form1.vb) you created in VB.NET and drag one button and CrystalReportViewer control to your form. Put the following source code in the button click events
Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim ds As New DataSet1 Dim cnn As SqlConnection Dim connectionString As String Dim sql As String connectionString = "data source=servername; _ initial catalog=crystaldb;user id=username;password=password;" cnn = New SqlConnection(connectionString) cnn.Open() sql = "SELECT Product_id,Product_name,Product_price FROM Product" Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet1 dscmd.Fill(ds, "Product") MsgBox(ds.Tables(1).Rows.Count) cnn.Close() Dim objRpt As New CrystalReport1 objRpt.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = objRpt CrystalReportViewer1.Refresh() End Sub End Class

NOTE : You have to provide the necessary databse information to Connection String.

* Crystal Reports from SQL Query String

In usual practice , Crystal Reports we are getting from pre defined columns. But we can make Crystal Reports from Dynamic column . Here we are going to do the dynamic Crystal Reports from SQL statements . That is we enter SQL in textbox and get the Crystal Reports according to the SQL statement.

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . Create a new VB.NET project and add a Strongly Typed Dataset . Before creating a Strongly Typed take a look at the detailed tutorial of create a strongly typed datset and add five column in the Datatable. Here we are limiting as five column , but you can add any number of column according to your requirements.

Next step is to create a Crystal Reports design from the Strongly Typed dataset.

Select all the column from dataset.

Select the default form(Form1.vb) and add a TextBox , Button and Crystal Reports Viewer .

Here we are going to pass the SQl statements to Crystal Reports at runtime . For that we parsing the SQL statement before we passing it to Crystal Reports. So we create a function for parsing SQL statements. Public Function procesSQL() As String Put the following vb.net source code in your form and run the program .
Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1 Dim objRpt As New CrystalReport1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cnn As SqlConnection Dim connectionString As String Dim sql As String connectionString = "data source=SERVERNAME; _ initial catalog=crystaldb;user id=sa;password=PASSWORD;" cnn = New SqlConnection(connectionString) cnn.Open() sql = procesSQL() Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet1 dscmd.Fill(ds, "Product") objRpt.SetDataSource(ds.Tables(1)) CrystalReportViewer1.ReportSource = objRpt CrystalReportViewer1.Refresh() End Sub Public Function procesSQL() As String Dim sql As String Dim inSql As String Dim firstPart As String Dim lastPart As String Dim selectStart As Integer Dim fromStart As Integer Dim fields As String() Dim i As Integer Dim MyText As TextObject inSql = TextBox1.Text inSql = inSql.ToUpper selectStart = inSql.IndexOf("SELECT") fromStart = inSql.IndexOf("FROM") selectStart = selectStart + 6 firstPart = inSql.Substring(selectStart, (fromStart - selectStart)) lastPart = inSql.Substring(fromStart, inSql.Length - fromStart) fields = firstPart.Split(",") firstPart = "" For i = 0 To fields.Length - 1 If i > 0 Then firstPart = firstPart & " , " _ & fields(i).ToString() & " AS COLUMN" & i + 1 MyText = CType(objRpt.ReportDefinition.ReportObjects("Text" _ & i + 1), TextObject) MyText.Text = fields(i).ToString() Else firstPart = firstPart & fields(i).ToString() & _ " AS COLUMN" & i + 1 MyText = CType(objRpt.ReportDefinition.ReportObjects("Text" & _ i + 1), TextObject)

MyText.Text = fields(i).ToString() End If Next sql = "SELECT " & firstPart & " " & lastPart Return sql End Function End Class

NOTE : You have to provide the necessary databse information to Connection String.

* Crystal Reports from XML File

In this section you can see , creating a Crystal Reports from XML file instead of database . This is very similer to creating Crystal Reports from database , the only difference is to sslect the data source as your XML file. Here we are creating an XML file name is Product is XML file the same structure of the Product table in the sample Database Structure. Download Product.XML

The basics of Crystal Reports creation provided in previous tutorials , if you dont have much knowledge in Crystal Reports , take a look at the tutorial step by step Crystal Report before start this section. The change happen only from previous report , when you select Data for Crsyatl Report , you have to select Create New Connection - Database Files and select the XML file you want to generate Crystal Reports (In this case you select the Product.xml ).

Select all the fields from Product and click finish button Now the designer part is over . Next step is to select the default form(Form1.vb) and add a Button and Crystal Reports Viewer to the Form. Put the following vb.net source code in your form and run the program .
Imports CrystalDecisions.CrystalReports.Engine Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cryRpt As New ReportDocument cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") CrystalReportViewer1.ReportSource = cryRpt CrystalReportViewer1.Refresh() End Sub End Class

cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt") The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here.

* Create a Subreport in Crystal Reports

Subreport in Crystal Reports means a Report within a Report . When we want to show some additional information about the data in the Crystal Reports , we use the subreports to show the details. Subreports we show within the main Crystal Reports and also we can show it as On-Demand report , that means we put an Hyper link in the row, and when user click that hyper link then will get the subreport. This section we are showing the subreport within the Crystal Reports . That is for each row there is a subreoprt for the details.

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure . Here we are create an order report based on three tables in the database and show a subreoprt for each row of the specified Product Name. Here we are using our earlier program Crystal Report from multiple tables to show the main Crystal Reports Data , so please refer Crystal Report from multiple tables before start this section . Create a Crystal Reports using three tables and select customername , date , product and qty . It will explain in detail the previous section Crystal Report from multiple tables . Next step is to create a subreport inside the main report. Here we are showing the Product details in each row of the specified product in the main row. After create the main report , right click on Crystal Reports designer window and select InsertSubreport.

Then you will get the subreport object , drag the object in the designer window at the down part of the details tab , just below the fields in the details tab. When you release the mouse you will get a dialogue box asking report name . Enter the report name you want and click the Report Wizard button. The wizard shows the table selection screen and select the table . Here in this case we are selecting the Product Table from the list and click next . Next screen is showing the table , from there select the fields you want to show the data and click finish. The you will get the subreport main screen again and select Link tab . The link tab is making relation with your main Report and subreport . Here we are linking the productname from main report to the subreport. For that select Product.Product_name from Available fields.

Accept the other settings as it is in the screen and click ok. Now you can see the subreport object in the screen , if you want to modify subreport design , double click on subreport object and you can design subreport.

Now the designer part is over . Next step is to select the default form(Form1.vb) and add a Button and Crystal Reports Viewer to the Form. Put the following vb.net source code in your form and run the program .
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim crp As New CrystalReport1 CrystalReportViewer1.ReportSource = crp CrystalReportViewer1.Refresh() End Sub End Class

* Create a Subreport in Crystal Reports with Link

In the previous section Subreport in Crystal Report is described how to insert a subreport in Crystal Reports . In that case the subreport is embedded within the main Crystal Reports. Here we are creating a subreport , and the subreport has only a link in the main Report , on-demand subreports . That is when the user click the link , then only the subreport display.

Here we are using our previous example Subreport in Crystal Report and make a link in the min Crystal Reports for on-demand subreport. Select the subreport object in the Crystal Reports and right click , then select Format Object .

Then you will get Format Editor . Select Subreport tab from Format Editor , you can find there a check box On-demand Subreport . You have to select that check box , then the subreport become as a link in your main Crystal Reports. If you want to change the title , you can change it in subreport name textbox. Finally click OK button.

Now the designing part is over and you are ready to generate subreport on-demand. Next step is to select the default form(Form1.vb) and add a Button and Crystal Report Viewer to the Form. Put the following vb.net source code in your form and run the program .
Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim crp As New CrystalReport1 CrystalReportViewer1.ReportSource = crp CrystalReportViewer1.Refresh() End Sub End Class

* How to deploy Crystal Reports on Clinet Machine

Crystal Reports for Visual Studio ships with your deployment projects that enable you to deploy Crystal Reports components and assemblies on the target machine. We can use different approaches to install Crystal Reports runtime files on the target machine . If you are using Visual Studio then during your setup and deployment you can add the CRRedist2005_x86.msi file to your setup file and distribute it as a single setup file . The installer can execute the setup file automatically with your .Net Project in a single click. You can find the CRRedist2005_x86.msi file in your system's C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports . Also you can distribute the CRRedist2005_x86.msi separately and install it on the target machine. The other way to install Crystal Reports on target machine is to create a setup file using Merge Modules and distribute it with your application or as a separate setup file. Click the following link to see how to make a setup file with Merge Modules. Crystal Reports installer using Merge Modules.

* How to create Crystal Reports installer using Merge Modules

Crystal Reports uses merge modules to ensure the correct report components and assemblies are installed with your deployment project. A merge module is a set of components that are merged with a Windows installer for applications that need them. The following steps are creating a setup file for Crystal Reports Client side installation using Merge Modules. Before creating a setup project, make sure that the following files exist in the \Program Files\Common Files\Merge Modules folder. CrystalReportsRedist2005_x86.msm Microsoft_VC80_ATL_x86.msm policy_8_0_Microsoft_VC80_ATL_x86.msm Steps to create Setup file. 1) Make sure the above mentioned files are in the \Program Files\Common Files\Merge Modules folder , If the files are not there you can download the file and copy it to the folder \Program Files\Common Files\Merge Modules . Download: https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/11688 2) Open your Visual Studio 3) Select "New Project" from File menu.

4) Select "Setup and Deployment" from "Other Project Types"

5) In the "Solution Explorer", select your setup project, right-click, and select Add and Merge Module from the menu.

6) Select CrystalReportsRedist2005_x86.msm to your setup project. The file available at \Program Files\Common Files\Merge Modules

Then you can see Microsoft_VC80_ATL_x86.msm and policy_8_0_Microsoft_VC80_ATL_x86.msm will be automatically added in the Detected Depencies section. 7) Build your project. Now you will get the setup file for distribution. Either you can add this setup file to you application installer or distribute it as a separate setup file.