Create Crystal Report Step by Step Using SQL Database 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_ID int identity(1,1) constraint PK_Emp primary key,
Emp_Name varchar(30),
Emp_Contact nchar(15),
Emp_Salary decimal(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.