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

  1. create table Employee  
  2. (  
  3. Emp_ID int identity(1,1) constraint PK_Emp primary key,  
  4. Emp_Name varchar(30),  
  5. Emp_Contact nchar(15),  
  6. Emp_Salary decimal(7,2)  
  7. ) 

Now insert employee data into the Employee Table.

Command

  1. insert into Employee values ('Rakesh','9924194054','15000.22');  
  2. insert into Employee values ('Amit','9824194555','17000');  
  3. insert into Employee values ('Ketan','9824198245','14000');  
  4. insert into Employee values ('Ketan','9994198245','12000');  
  5. insert into Employee values ('Chirag','9824398245','10000');  
  6. 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

  1. Select * from Employee 



Step 2

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

Command

  1. create view vw_Employee  
  2. as  
  3. Select Emp_ID,Emp_Name,Emp_Contact,Emp_Salary  
  4. from Employee  
  5. 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:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using CrystalDecisions.CrystalReports.Engine;  
  8. using CrystalDecisions.Shared;  
  9.   
  10. namespace Network_Portal {  
  11.     public partial class EmployeeDataInfo: System.Web.UI.Page {  
  12.         protected void Page_Load(object sender, EventArgs e) {  
  13.             ReportDocument Report = new ReportDocument();  
  14.             Report.Load(Server.MapPath("~/EmployeeData.rpt"));  
  15.             Report.SetDatabaseLogon("sa", "sa123", "Rakesh-PC", "RakeshData");  
  16.             CrystalReportViewer1.ReportSource = Report;  
  17.         }  
  18.     }  
  19. } 



Step 17

Finally run your report and display the Employee Information.