Simple Procedure to Develop Query Based Crystal Reports

• Download source files - 26 Kb

Introduction

Most of the time, the developers use simple way to design crystal reports using direct table link, in this way the report shows all the data present in the table and if we need to display some of the data from that table we need to pass our sql query to report to show the specified data only. There are two common methods to achive the same: dataset or recordset and in both the cases we set them as datasource of our report and before this data source is populated using our queries, here I am going to use recordset.

Procedure and Code

Here I am going to design two reports from the Territories and Region table of northwind database of SQL Server.

First add a crsytal report to your project and create that using wizar. Select Oledb(ADO) as datasource abd select oledb provider for SQL Server with proper user, password etc. Then add tables, views etc from where you want to display the data and then select fileds etc and complete the report design.
Now add a CrsytalReportViwer and copy the following code with proper changes.

VB

Dim Conn As New ADODB.Connection()
Dim report As New SimpleReport()
report = New SimpleReport()
Conn.ConnectionString = "Provider=sqloledb; Data Source=mydatasource; Initial Catalog=northwind;User Id=myuserid;Password=mypassword"
Conn.ConnectionTimeout = 10
Conn.Open()
Dim rs As ADODB.Recordset
rs = New ADODB.Recordset()
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Dim sql As String = "SELECT * FROM Territories"
rs.Open(sql, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1)
CrystalReportViewer1.ReportSource = report
report.SetDataSource(rs)
CrystalReportViewer1.DataBind()
CrystalReportViewer1.RefreshReport()
rs.Close()
rs = Nothing

C#

ADODB.Connection Conn = new ADODB.Connection();
SimpleReport report = new SimpleReport(); 
report = new SimpleReport(); 
Conn.ConnectionString = "Provider=sqloledb; Data Source=mydatasource; Initial Catalog=northwind;User Id=myuserid;Password=mypassword"; 
Conn.ConnectionTimeout = 10; 
Conn.Open(); 
ADODB.Recordset rs; 
rs = new ADODB.Recordset(); 
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient; 
string sql = "SELECT * FROM Territories"; 
rs.Open(sql, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1); 
CrystalReportViewer1.ReportSource = report; 
report.SetDataSource(rs); 
CrystalReportViewer1.DataBind(); 
CrystalReportViewer1.RefreshReport(); 
rs.Close(); 
rs = null;

The above code describe the mechanisam to design a report using a single table query and if you want to pick data from different tables, you will design the report using those tables and at the same time you must have to pass the recordset or dataser accordingly using sql query and using the query you will mention the field name according to those present in the report , regardless from whichever table you get the data, other will it will never display any data.

Summary

I admit this is not the simplest method to populate reports data but whenever you want to controls things you must have to do something extra. Using this process you can pass any kind of information to your report and populate them well accroding to your needs. If your are an expert query designer, you can design just one report and then can pass each and every thing to your report using query for multiples reports.
Happy Programing:)