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.

simple_vb.net_crystal_report_1.GIF

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.

simple_vb.net_crystal_report_2.GIF

Select Report type from Crystal Reports gallery.

simple_vb.net_crystal_report_3.GIF

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 .

simple_vb.net_crystal_report_4.GIF

Select Microsoft OLE DB Provider for SQL Server .

simple_vb.net_crystal_report_5.GIF

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) .

vb.net_crystal_report_from_multiple_table_6.GIF

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 .

vb.net_crystal_report_from_multiple_table_7.GIF

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.

vb.net_crystal_report_from_multiple_table_8.GIF

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.

vb.net_crystal_report_from_multiple_table_9.GIF

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.

simple_vb.net_crystal_report_9.GIF

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.

vb.net_crystal_report_from_multiple_table_10.GIF


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 - Related Contents



.