How To Create Crystal Report From Sql Query

Updated date Jun 03, 2019

585.6k
8

Crystal Reports Tutorial - This tutorial explains how to create a Crystal Reports step-by-step using a SQL Server View.

Here, see how to create a Crystal Report using SQL View.

View

Why views are used in databases:

A View is one type of virtual table
A View joins and simplifies multiple tables into a single virtual table
A View can hide the complexity of data
A View uses very small space in the data store
A View is updatable easily

The following is a step-by-step instructions for creating a Crystal Reports report.

Create Crystal Report for Employee Data information

First create an Employee Data Table in your SQL Database.

Now, follow the steps for creating a Crystal Report.

Step 1

Create a table in the database. Create an Employee table in the database.

Command

create table Employee
(
Emp_IDint identity(1,1) constraint PK_Emp primary key ,
Emp_Namevarchar (30),
Emp_Contactnchar (15),
Emp_Salarydecimal (7,2)
)

Now insert employee data into the Employee Table.

Command

insert into Employee values ( 'Rakesh' , '9924194054' , '15000.22' );
insert into Employee values ( 'Amit' , '9824194555' , '17000' );
insert into Employee values ( 'Ketan' , '9824198245' , '14000' );
insert into Employee values ( 'Ketan' , '9994198245' , '12000' );
insert into Employee values ( 'Chirag' , '9824398245' , '10000' );
insert into Employee values ( 'Naren' , '9824398005' , '10000' );

Now Employee data has been inserted into the table.

Let's see it with a SQL Select Command Query in the SQL Database.

Command

Select * from Employee

Step 2

Create a VIEW in your database to display employee data information.

Command

create view vw_Employee
as
Select Emp_ID,Emp_Name,Emp_Contact,Emp_Salary
from Employee
GO

Now, your Employee database view has been created.

Step 3

Go to Visual Studio.

Step 4

Go to the Solution Explorer and right-click on your project name and seelct Add -> New Item.

Step 5

Add New Item-> Crystal Report.

Step 6

Click the Ok Button.

Step 7

Choose the data source as in the following:

Click the Next Button.

Step 8

Select the data with OLEDB (ADO) as in the following:

Click the Next button to open a new dialog.

Click the Finish button and open a new dialog box. In this, select your new view.

Step 9

Select your view Employee view.

Step 10

Select the fields to display in the report area as in the following:

And click the Finish button.

Step 11

Select the report format as in the following:

Click the Finish Button after selecting the format of the report.

Step 12

Now finally display your report in this format.

Step 13

Now add a new .aspx page to display the report as in the following:

And provide the name EmployeeDataInfo.aspx.

Step 14

Now add a Crystal Report Viewer to EmployeeDataInfo.aspx as shown in the following screeshot:

Step 15

Go to the aspx page code side as shown below:

Step 16

Write the report code in the .aspx.cs page as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
namespace Network_Portal {
public partial class EmployeeDataInfo: System.Web.UI.Page {
protected void Page_Load( object sender, EventArgs e) {
ReportDocument Report =new ReportDocument();
Report.Load(Server.MapPath("~/EmployeeData.rpt" ));
Report.SetDatabaseLogon("sa" , "sa123" , "Rakesh-PC" , "RakeshData" );
CrystalReportViewer1.ReportSource = Report;
}
}
}

Step 17

Finally run your report and display the Employee Information.