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

View

Why views are used in databases:

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