C# Crystal Reports from Multiple Tables

Here we are going to generate Crystal Reports from multiple tables in C#. Here we have three tables (ordermaster , orderdetails and product ) and we are generating a Crystal Report from these three tables by connecting each table with their related fields.

Select all table from the table list to right side list box, because we are creating report from three tables ( OrderMaster, OrderDetails, Product) . If you don't know up to this part of the tutorial , refer previous tutorial for up to selecting databese for Crystal reports.

The next step is to make relations 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 fields that we want to make relation and drag to the related field of the other selected tables. After made the relations with tables the screen is look like the following picture .

Next step is to select the fields from the selected tables ( OrderMaster, OrderDetails, Product) . Here we are selecting the fields Customername , orderdate from ordermastertable , Productname from product table and quantity from order details table. The field selection screen is look like the following picture .

After select the fields from tables, click the Finish button because now we are not using any other functionalities of the Crystal Reports 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 re-arranging fields in the designer window , you can drag the field object on 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 Crystal Reports in C# and view it in Crystal Reports Viewer control .

Select the default form (Form1.cs) you created in C# and drag a button and a CrystalReportViewer control to your form .

After you drag the CrystalReportViewer to your form , it will look like the following picture.

You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code.

using CrystalDecisions.CrystalReports.Engine;

using System;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ReportDocument cryRpt = new ReportDocument();
            cryRpt.Load(PUT CRYSTAL REPORT PATH HERE\\CrystalReport1.rpt");
            crystalReportViewer1.ReportSource = cryRpt;
            crystalReportViewer1.Refresh();
        }
    }
}

The Crystal Reports file path in your C# project files location, there you can see CrystalReport1.rpt . So give the full path name of Crystal Reports file like c:\projects\crystalreports\CrystalReport1.rpt

After you run the source code you will get the report like this.

C# Crystal Report Database

First we have to create a database named it as "crystaldb"

Create DataBase "crystaldb"

In the crystaldb database , create three tables

OrderMaster , OrderDetails , Product .

The Table Structure follows :

 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 relation of tables in crystaldb database :

SQL command for creating 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 some data to the tables :

From the following pictures you can see some data in the table for C# - Crystal Reports tutorial

Order Master Table Data

Order Details Table Data

Product Table Data

Create a Crystal Report from ADO.NET Dataset using VB.NET

Generating an Object for the DataSet

Object for ADO.NET is a collection of dataset classes created in memory.

To create a dataset object from Northwind database in SQL Server, using ADO.NET

DataSet Designer.

1. In the Solution Explorer, right-click the project name, point to Add, and click Add New

Item.

2. In the Categories area of the Add New Item dialog box, expand the folder and select

Data.

3. In the Templates area, select Dataset.

4. Accept the default name Dataset1.xsd.

This creates a new schema file that will be used to generate a strongly typed dataset. The

schema file will be displayed in ADO.NET Dataset designer.
5. In the Solutions Explorer, click on Dataset1.xsd file, if now already the active view.

6. From the Server Explore, on the right connect to SQL Server and drill down to Northwind Database.

7. Highlight the Table Customers (or stored procedure if desired) and drag and drop it on

the Interface of Dataset1.xsd. Dataset1.xsd should now be displayed in the Dataset tab

as under

This creates a dataset object and contains only a description of the database based on the

schema in Dataset1.xsd. It does not contain the actual data.

Connecting Report to an ADO.NET Dataset Object

From ADO.NET Dataset Object you can add tables to Crystal Report using Database Expert in

Crystal Report Designer.

To create a new report and connect it to Dataset object which contains description for

Customers table in Northwind database

1. In the Visual Studio .NET Solution Explorer, right-click your project to display the
2. Point to Add and click Add New Item.

3. In the Add New Item dialog box, select Crystal Report from the Templates area. ClickOpen.
 

4. Crystal Report Gallery will be displayed, as shown below

5. You can choose from any of the options provided in Crystal Report Gallery. But for the

purpose of this walkthrough choose As a Blank Report and click OK.

 

6. On File menu, click Save to save the report.

7. Right click in the Report Designer, point to Database, and click Add/Remove Database.

8. You’ll be presented with Database Expert wizard

9.In the Database Expert wizard, expand the Project Data folder, expand the ADO.NETfolder and select the dataset object as shown below

Datasets

10. If you now drill down Database Fields node, in the Field Explorer, you can view
11. Drag and drop the fields onto the report and format them as required.

Pushing data into DataSet object and binding report to Windows Forms Viewer

In order to display actual data in the report, you should fill the dataset object with the data before

you bind the report to Windows Forms Viewer. You should do this in the corresponding source file

for Windows Form.

1. Drag and drop CrystalReportViewer control on Form1 and set the DisplayGroupTreeproperty to False, as shown below

2. Accept the default name as CrystalReportViewer1.

3. Open Form1 code editor and add the following code on Load event of Form1.

Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) .Load

Dim

Dim

Dim

Dim

Dim

Try

MyCommand.Connection = myConnection

MyCommand.CommandText = "SELECT * FROM Customers"

MyCommand.CommandType = CommandType.Text

myDA.SelectCommand = MyCommand

myDA.Fill(myDS, "Customers")

rpt.SetDataSource(myDS)

CrystalReportViewer1.ReportSource = rpt

Catch

MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK,

MessageBoxIcon.Error)

rpt As New CrystalReport1() 'The report you created. myConnection As SqlConnection MyCommand As New SqlCommand() myDA As New SqlDataAdapter() myDS As New Dataset1() 'The DataSet you created. myConnection = & _"Initial Catalog=northwind;")New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" Excep As Exception

End Try

End Sub

Private Sub

Handles MyBase

Customers table and all its fields

shortcut menu.