Crystal Report : Populate with dynamic query

Last few days I was facing a problem of creating CrystalReport with dynamic query. More specifically, I wanted to change the query in runtime and populate the report with query-results. And I somehow managed to get rid of this. Still I’m not sure if it’s the best way, but it works.

Followings are the steps I found useful:

1. Firstly, create a crystal report with the database fields (which will be viewed) embedded in the report.
2. Then, create a ReportDocument object, say ‘rptDoc’. [required namespace ‘CrystalDecisions.CrystalReports.Engine’]
3. Load the crystal report within rptDoc.
4. Set its data source, with the DataSet / DataTable which contains the results of the query.
5. Finally, for viewing the report set the ReportSource property of CrystalReportViewer to this rptDoc object.

Sample Code:

// prepare the DataTable for the report
string connectionString = "Dsn=TestDB";
OdbcConnection conn = new OdbcConnection(connectionString);
conn.Open();
string query = "select * from person where personId <>; // dynamic query
OdbcCommand command = new OdbcCommand(query, conn);
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
conn.Close();

// populate the report with the created DataTable dt
string reportPatfh = "CrystalReport1.rpt"; // path of the crystal report
ReportDocument rptDoc = new ReportDocument();
rptDoc.Load(reportPatfh);
rptDoc.SetDataSource(dt);
crystalReportViewer1.ReportSource = rptDoc;

Notes:

Here only data are changed dynamically, not the format of the report.