Dynamically Creating Crystal Reports

Problem

You need to define a DataTable at runtime and bind it to a Crystal Report.

Solution

Create a DataAdapter and use it to fill a DataTable with a subset of records (specified by a range of OrderID values, from the Orders table joined to Order Details records from the Northwinds sample database demonstrated in the following example). Create a new report document and set its data source to the DataTable . To display the report, set the source of the report view to the report document.

The C# code is shown in Example 7-32.

Example 7-32. File: CrystalReportsForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.SqlClient;

private CrystalDecisions.Windows.Forms.CrystalReportViewer crv;

// . . . 

// Get the user entered OrderID range.
int orderIdFrom, orderIdTo;
try
{
 orderIdFrom = Convert.ToInt32(orderIdFromTextBox.Text);
 orderIdTo = Convert.ToInt32(orderIdToTextBox.Text);
}
catch (Exception ex)
{
 MessageBox.Show(ex.Message, "Dynamic Crystal Reports",
 MessageBoxButtons.OK, MessageBoxIcon.Error);
 return;
}

Cursor.Current = Cursors.WaitCursor;

// Create a DataAdapter and fill the table.
String sqlText = "SELECT * FROM Orders " +
 "JOIN [Order Details] Order_Details ON Orders.OrderID = " +
 "Order_Details.OrderID " +
 "WHERE Orders.OrderID BETWEEN " + orderIdFrom + " AND " + orderIdTo;
SqlDataAdapter da = new SqlDataAdapter(sqlText,
 ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable dt = new DataTable( );
da.Fill(dt);

// Create a new ReportDocument.
ReportDocument cr = new ReportDocument( );
// Load the report.
cr.Load(ConfigurationSettings.AppSettings["Project_Directory"] +
 @"Chapter 07OrderWithDetailsCrystalReport.rpt");
// Set the data source for the report.
cr.SetDataSource(dt);

// Set the report document for the report view.
crv.ReportSource = cr;

Cursor.Current = Cursors.Default;

Discussion

Follow these steps to use a DataTable created at runtime as the data source for a Crystal Report:

1. Using the Crystal Report Designer in Visual Studio.NET, design and create the Crystal Report .RPT file. Link the report to the database in the designer to get the fields for the report as would normally be done. For more information about using the Crystal Report Designer, see the MSDN Library (you might have to change the filter in MSDN to "(no filter)" from ".NET Framework").
2. In the application, use a DataAdapter to fill a DataTable with the data required by the report.
3. Create a new ReportDocument object:

   ReportDocument cr = new ReportDocument( );
   

   The ReportDocument class represents a report and contains methods and properties including those used define, format, and load the report.

4. Use the Load( ) method of the ReportDocument to load the report defined in step 1:

   cr.Load(ConfigurationSettings.AppSettings["Project_Directory"] +
    @"Chapter 07OrderWithDetailsCrystalReport.rpt");
   

5. Use the SetDataSource( ) method of the ReportDocument to pass the data source to the report engine:

   cr.SetDataSource(dt);
   

6. Set the ReportSource property of the CrystalReportViewer to the ReportDocument to display the report in the viewer:

   crv.ReportSource = cr;
   

The CrystalReportViewer class provides methods, properties, and events that allow control of viewer appearance and functionality.