Introduction
This program shows how to dynamically load data from a database and application into the Crystal Report. By using this program, we can customize a Crystal Report to some limit at run time of the application like specifying which field (Columns of a particular table) should be displayed in the report.
Background
This problem arose because a group of students from SLIIT asked me how to dynamically generate a Crystal Report using C# 2.0 (.NET 2005). I tried to find a solution for this by searching many forums and sites, but unfortunately I couldn't find any solution for that. Some forums said that there is no way to create dynamic Crystal Reports using .NET 2005. Finally, I found a way to do that.
Using the Code
- Create a C# project or add a Form to your existing project.
Now you can add Checkboxes that correspond to columns of a particular table that should be displayed in the Crystal Report and CrystalReportViewer control to the form.
For this demonstration, I have created a database called db1.mdb (in bin\Debug) using Access and created a table called Customer
.
- Add a
DataSet
(*.xsd file) to your project using add -> New Items in solution explorer. After that, add a DataTable
to the DataSet
.
Add columns to DataTable
and name them Column1
, Column2
, and so on. The number of columns depends on how many columns should be displayed in the Crystal report.
- Add a Crystal Report into the project and using the Report Wizard, choose ADO.NET
DataSet
s of the Project data source as the data source of the Crystal Report and select Customer
data table of DataSet1
as the selected table of the Crystal Report. Then select fields to be displayed in your report. Then remove Column1
…, Column5
objects in Section 2 of the Crystal Report.
- Now add parameters called
col1
, col2
… col5
(the number of parameters should be equal to the number of columns displayed in the Crystal Report.) using Field Explorer.
-
Add the following method to your Form for Create SQL SELECT query and assign values to parameters of the Crystal Report according to user selected columns that should be displayed on your report.
private string CreateSelectQueryAndParameters()
{
ReportDocument reportDocument;
ParameterFields paramFields;
ParameterField paramField;
ParameterDiscreteValue paramDiscreteValue;
reportDocument = new ReportDocument();
paramFields = new ParameterFields();
string query = "SELECT ";
int columnNo = 0;
if (chbCode.Checked)
{
columnNo++;
query = query.Insert(query.Length, "Code as Column" +
columnNo.ToString());
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Customer Code";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
}
if (chbFirstName.Checked)
{
columnNo++;
if (query.Contains("Column"))
{
query = query.Insert(query.Length, ", ");
}
query = query.Insert(query.Length, "FirstName as Column" +
columnNo.ToString());
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "First Name";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
}
if (chbLastName.Checked)
{
columnNo++;
if (query.Contains("Column"))
{
query = query.Insert(query.Length, ", ");
}
query = query.Insert(query.Length, "LastName as Column" +
columnNo.ToString());
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Last Name";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
}
if (chbAddress.Checked)
{
columnNo++;
if (query.Contains("Column"))
{
query = query.Insert(query.Length, ", ");
}
query = query.Insert(query.Length, "Address as Column" +
columnNo.ToString());
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Address";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
}
if (chbPhone.Checked)
{
columnNo++;
if (query.Contains("Column"))
{
query = query.Insert(query.Length, ", ");
}
query = query.Insert(query.Length, "Phone as Column" +
columnNo.ToString());
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "Phone";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
}
for (int i = columnNo; i < 5; i++)
{
columnNo++;
paramField = new ParameterField();
paramField.Name = "col" + columnNo.ToString();
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
}
crystalReportViewer1.ParameterFieldInfo = paramFields;
query += " FROM Customer" ;
return query;
}
-
Add the following method to the button click event to display a report when the user presses the button:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Shared;
using CrystalDecisions.Windows.Forms;
namespace app5
{
public partial class Form1 : Form
{
CrystalReport1 objRpt;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
objRpt = new CrystalReport1();
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=|DataDirectory|\\db1.mdb";
string query = CreateSelectQueryAndParameters();
if (!query.Contains("Column"))
{
MessageBox.Show("No selection to display!");
return;
}
try
{
OleDbConnection Conn = new OleDbConnection(connString);
OleDbDataAdapter adepter =
new OleDbDataAdapter(query, connString);
DataSet1 Ds = new DataSet1();
adepter.Fill(Ds, "Customer");
objRpt.SetDataSource(Ds);
crystalReportViewer1.ReportSource = objRpt;
}
catch (OleDbException oleEx)
{
MessageBox.Show(oleEx.Message);
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message);
}
}
History
- 29th September, 2007: Initial post