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