08 January 2008

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.

1 Comment:

Shubha said...

Hi,
We have Crystal Report XIR2 Designer. Is it possible to create dynamic query just with designer? If so can you please help me on how to create dynamic query on runtime.

Many Thanks.

 

© 2007 t!ps n tr!cks: Crystal Report : Populate with dynamic query



Template unik dari rohman


---[[ Skip to top ]]---