Generating a Multiple Table Crystal Report using VS 2005

In a previous article generating a Crystal Report using the Crystal Report items that comes with a default installation of Visual Studio 2005 was described. Whereas the previous article utilized data from a single table for report generation, the present article shows how you may obtain data from multiple tables for the report.

In this tutorial, as in the previous one, Crystal Reports and CrystalReportViewer objects will be used. Crystal Report may be created both for Windows and Web applications; the report created in this tutorial is for a Windows application.

Creating a Crystal Reports application

It is very easy to create a Crystal Report application. From the File menu item click on the New Project…drop-down item. This brings up the New Project window. Highlight Visual Basic under the Project Types, and under Visual Studio installed templates, choose the Crystal Report Application. This will add a default file name. Change it to something suitable. In this case this has been changed to MultiTable as shown in the next picture.

.

Now click on the button labeled OK. This creates the project files and also opens up,  in the tab, CrystalReport1.rpt as shown. This window can be the starting point for creating different kinds of reports such as Standard, Cross-Tab and Mail Label using the Report Expert. You may also begin with a blank report and build it step-by-step from scratch. It is also possible to modify an existing report to generate a new report with different formatting and even changed data.

In this tutorial a standard report will be created that uses data from multiple tables. Accepting the defaults, Using the Report Wizard and the Expert to produce a Standard report, click on the OK button.

The Standard Report Creation Wizard

This pops-up the Standard Report Creation Wizard as shown where you choose the data needed for the report. We will use the data from an MS Access database such as the one in the Northwind.mdb example database that is bundled with MS Access.

{mospagebreak title=Choosing a Data Connection}

Also we will be using the database files related to the Northwind.mdb. Click on the node named Database Files which opens up the Samples folder on the hard drive as shown.

Highlight the Northwind.mdb and click on the button labeled Open. This adds the Tables, Views and Stored Procedures to the Database Files node as shown. Whereas the MDB file has no categories such as Views and Stored Procedures, the Crystal Reports data window stuffs the queries, parametric queries and queries supporting the reports and forms into these objects.

{mospagebreak title=Choosing the Tables}

For this report we will be using two of the tables from this database. Click on the tables Customers and Orders and transfer them from the Available Data Sources area to the Selected Tables area as shown. You may use the > and >> arrows to make the transfer, or double click the item in the left to make the transfer to the other side.

Now click on the Next button which takes you to the next step of the wizard, namely Link, as shown in the next picture. The Customers table has only the Primary key (shown in green) and the Orders Table is related to four other tables. The program has identified an existing relationship in the database file between the two tables. In the absence of such a pre-existing link you may need to link them. There are several options to manipulate the Links in this window.

Establish a Link between Tables

Database search efficacy depends on the indexing of columns and clicking on the Index Legend… button shows the color map for the indexing that may be associated with the Link window shown.

Accept the default linking through the common columns CustomerID and click on the Next Button. This brings up the next step of the wizard shown where you need to choose the fields that are to be included in your report. The fields in the Available Fields area have columns from both of the joined tables.

{mospagebreak title=Choosing Fields to Include in the Report}

You may highlight and transfer the fields to the Fields to Display area as shown. Only these fields will be displayed if you do not make further changes at a later date. The choices made can be modified by returning the fields back to the Available Fields area if necessary.

Now click on the Next button. This opens up the next step of the report where you may be required to group your data based on some field.

Defining Groups

In this window you may also browse the data, or find a field by using the Browse Data… and Find Field… buttons. Highlighting the Customers.City in the available fields area, click on the button labeled Browse data…. This opens up a display list of all the cities as shown.

You may also preview the report by clicking on the MainReportPreview tab (at the bottom of Crystal Report1.rpt tab) as shown in the next picture.

You can review the grouped data on the left hand side of this report as shown and toggle the Grouping button to show the report with grouped data or ungrouped data.

In addition to grouping you may print the data (check how many pages will print before you hit this button!), or export the report into several forms by hitting the export button. The displayed report may be zoomed as well.

When you click on the design view of Form1 you will see an embedded CrystalReportViewer control and a CrystalReport11 (MultiTable.CrystalReport1) control in the control tray as shown. What you see in the CrystalReportViewer1 may not be completely meaningful in the design view, nor in the objects in the "Grouping area."

The CrystalReportViewer1 control has a large number of properties which can be changed. In fact there are links which may be activated to carry out the intended modifications as shown in the properties window of this control.

We will not be making any changes to the report viewer. Now click on the Build main menu item and run the form. The result is shown displayed in the next window. You can choose the city and the order date to find the other details as shown for the city "Bergamo" for the order date 12/14/1999. In this page you may also navigate from page to page, or look for a certain page.

If you want to make changes to the formatting of the report, you should go first to the Main Report after clicking on the CrystalReport1.rpt. You can then make changes to the report objects. If you want to make changes to the fields from the report, for example the OrderDate, you can click on the OrderDate in Section3 (Details) and in the corresponding property window turn off the minutes and seconds from the display by choosing the crNoMinute and crNoSecond values for the MinuteType and secondType properties. Also if you take out the city field being shown in every row, you can make the report more readable.

For formatting you can change the appropriate properties as well. Right clicking on an empty area in a section will bring up the context sensitive drop-down menu from which you can choose to carry out a variety of activities as shown.

After manipulating the fields and formatting the report elements, the report was modified as shown in the next picture. The Crystal Report Viewer control has all the necessary elements to create a visually attractive report, although the report shown may not be the most attractive one.

Summary

This tutorial shows you how to create a multi-table report using the Crystal Report objects called Crystal Report and CrystalReportViewer installed with the default installation of Visual Studio 2005. The wizard makes it very easy to create a report. Modifying the report requires knowledge of the earlier versions of Crystal Report software. The various objects needed for creating an attractive report are available either through property windows or context sensitive drop-down menus.

2 thoughts on “Generating a Multiple Table Crystal Report using VS 2005

  1. Follow the wizard and you are done. When multiple tables feed data to the report you need to take care of indicating the proper joins. That done well, your report is generated in no time. You could go and modify the report if you need to suit your requirements both in data and in formatting.
    Thanks for reading and look forward to your comments. Jay

  2. Hi,
    I have a big problem with this reporting issue. My report has tree parts. One of them is first part which may be repeated in details section but I have to more sections which should be appear at the end of the first detail part. I tried to show these two parts in the Report Footer but it did not work. I also tried to show it in the page footer and againd did not work. Then i understand that it must be on details section. But exact problem is that there are three queries to display in each parts. First query will return many records and these should be displayed first. The other two queries return only one record and these must be shown at the and of the report. Just one line bottom of the first part. When i put these two parts in details section then this one records in these two parts appear repetadly after all single record of the first query.How can i seperate these three queries. Is it possible to make a table inside report or any other solution?

[gp-comments width="770" linklove="off" ]