Click here to Skip to main content
11,413,676 members (74,086 online)
Click here to Skip to main content

Simple Procedure to Develop Query Based Crystal Reports

, 20 Jan 2007
Rate this:
Please Sign up or sign in to vote.
Designing Crystal Reports for Single and Multiple tables using your own queries.
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->


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.


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


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; 
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; 
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.


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 ProgramingSmile | :)


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Malik Nasir
Team Leader ZonG, China Mobile Company
Pakistan Pakistan
I am currently based in Islamabad,Pakistan. I have done my MS Computer Science and I am involved in Microsoft Visual Windows/Web application development since 2003 untill that i have been working in VB, ASP and web scripting languages. Currently i am working in ZonG as Team LEad IT Projects and Planing. I am also the administrator of Business Process

Comments and Discussions

QuestionCryastal Report Grouping Pin
mk8642, 23-May-12 20:55
membermk864223-May-12 20:55 
GeneralMy vote of 5 Pin
Amuthan Sam, 1-Dec-10 3:14
memberAmuthan Sam1-Dec-10 3:14 
GeneralPassing Multiple Recordsets or stored procedures at Design time in VB6 using crystal report in a single report Pin
Wahaj Khan, 13-Jan-10 1:32
memberWahaj Khan13-Jan-10 1:32 
GeneralPDF file into Crystal report Pin
elgee77, 23-Jun-07 7:20
memberelgee7723-Jun-07 7:20 
GeneralRe: PDF file into Crystal report Pin
Malik Nasir, 26-Jun-07 2:48
memberMalik Nasir26-Jun-07 2:48 
GeneralRe: PDF file into Crystal report Pin
elgee77, 26-Jun-07 14:23
memberelgee7726-Jun-07 14:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150427.2 | Last Updated 20 Jan 2007
Article Copyright 2007 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid