VB.NET Crystal Reports from multiple tables

The focus of this tutorial is to generate Crystal Reports utilizing multiple tables. Specifically, we will be working with three tables: ordermaster, orderdetails, and product. By establishing connections between these tables using their corresponding related fields, we will create comprehensive reports encompassing data from all three tables - Click here to see Database Structure .

Through this approach, we can use the relational structure of the tables to extract relevant information and consolidate it into a cohesive and insightful report. The utilization of multiple tables allows for a more comprehensive analysis, as it enables us to incorporate data from various aspects of the business process.

By following the steps outlined in this tutorial, you will gain the expertise to create Crystal Reports that effectively utilize the data contained within these interconnected tables, thus providing a comprehensive and informative snapshot of your business operations.

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 .

After proceeding, you will encounter the SQL Server authentication screen. Here, you are required to select your SQL Server name, enter your user ID and password, and choose the appropriate database name. Once done, click "Next" to proceed.

Subsequently, you will be presented with the OLE DB Property values screen. It is recommended to leave the default settings as they are and proceed by clicking "Finish."

At this stage, you will observe your server name listed under the OLEDB Connection section. From this list, select the desired database name, in this case, "Crystaldb." Then, click on the "Tables" option, enabling you to view all the tables contained within the database.

To ensure comprehensive reporting from the three tables, namely OrderMaster, OrderDetails, and Product, select all the tables from the table list and transfer them to the right-side list box. This step is necessary as we will be generating reports using data from these three tables.

In the next step, we will establish relationships between the selected tables. This involves connecting the relevant fields from each table. Although not necessary, it is recommended to arrange the tables within the visible area of the list for ease of reference.

To create a relation, simply select the field from one table that corresponds to the related field in another table, and drag it to the respective related field in the target table. This action establishes the connection between the tables based on the shared data.

Once the relationships have been successfully established, the screen will resemble the following picture, depicting a visual representation of the table relationships within the Crystal Reports environment.

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.

Proceed by clicking the "Finish" button, as we have completed the necessary steps within the wizard. This will lead you to the Crystal Reports designer window, where you can begin customizing the report layout according to your specific requirements.

In the designer window, you have the flexibility to arrange the fields in a manner that best suits your desired view of the report. To rearrange the fields, simply drag and reposition the field objects within the screen. This allows you to achieve an optimal visual presentation of the report data.

Should you need to make further edits to a specific field, right-click on the corresponding field object and select "Edit Text Object." This grants you the ability to modify the text or other properties associated with the field.

The following picture provides a sample view of the designer window, showcasing the arrangement of fields after rearrangement has been performed.

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.

Put the following source code in the button click event

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.