Click here to Skip to main content
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-->

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

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

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

License

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

Share

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 Studio.net 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 Pinmembermk864223-May-12 19:55 
GeneralMy vote of 5 PinmemberAmuthan Sam1-Dec-10 2:14 
GeneralPassing Multiple Recordsets or stored procedures at Design time in VB6 using crystal report in a single report PinmemberWahaj Khan13-Jan-10 0:32 
GeneralPDF file into Crystal report Pinmemberelgee7723-Jun-07 6:20 
GeneralRe: PDF file into Crystal report PinmemberMalik Nasir26-Jun-07 1:48 
GeneralRe: PDF file into Crystal report Pinmemberelgee7726-Jun-07 13: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 | Mobile
Web04 | 2.8.141022.1 | Last Updated 20 Jan 2007
Article Copyright 2007 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid