Click here to Skip to main content
12,946,816 members (50,780 online)
Click here to Skip to main content
Add your own
alternative version


32 bookmarked
Posted 20 Jan 2007

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


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
Program Manager ZonG, China Mobile Company
Pakistan Pakistan
I am working as Manager Enterprise Services where key responsibilities are to support end users on their day to day IT related issues.
The other function I am responsible is IT IPCC Services Support Since September 2013 and my key responsibilities are to maintain IPCC Uptime along with end users support at both CMPak call centers. IPCC enhancements and new feature development is also part of my job role.
The third area that was handed over to me in March 2015 is in-house apps support, where key responsibility is to provide support for all the application developed by IT department and to maintain the availability of these applications.
Previously I was working as Manager Automation & Planning and this is my key area of expertise. My responsibilities were to manage team in Project Planning, System Analysis, Requirements gathering, Preparation of SRS, Presentations to client about the requirements, getting Approvals on SRS from the Client, Database Design, Development Tracking, System Deployments, end user Feedback along with Change Management.

Planning and Defining Scope, Resource Planning, Time Estimating, Creating Charts and Schedules, Risk Analysis
Managing Risks and Issues, Monitoring and Reporting Progress, Team Leadership, Working with Vendors, Scalability were the key area of working along with an efficient problem-solver in professional way with envisions business and technical perspectives to develop workable solutions.

I started my career as developer in 2004 and promoted as team lead IT Automation in 2007 and then as manager so I spend 7 years in Software Development & Project Planning.

You may also be interested in...


Comments and Discussions

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

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

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

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