Introduction
What Is Crystal Reports?
"In simplest terms, Crystal Reports is a report design tool that allows you to create reports capable of retrieving and formatting a result set from a database or other data source. In addition to simply reading data from a data source, Crystal Reports has its own formula language for creating calculations and includes a number of features that can be used to turn raw data into presentation-quality reports, with graphs, charts, running totals, and so on." (David McAmis, Professional Crystal Reports for Visual Studio .NET, 2nd edition)We give this option to users to download there on copy of result what they want from search result.
Sqlserver Part
Create a Simple Table Name as PersonInfo:
create Table PersonInfo
(
PersonID int primary key IDENTITY(1,1) NOT NULL,
PersonName Nvarchar(100) NULL,
PersonAge int NULL,
PersonAddress Nvarchar(100) NULL,
PersonProfession nvarchar(100) NULL
)
Inserting Records
Insert Records into Table from backend
Selecting Records for displaying on report Using StoredProcedure
CREATE Proc Usp_getPersonRecords
as
SELECT * FROM PersonInfo
Create a new ASP.NET Web Application Project.
Add New Web form -> Name it as ExportRecords.aspx.
Adding Crystal Report
When this Screen appears in front of you, just select (Standard) and Press Ok button.
The Standard Report Creation Wizard will pop
Select Create New Connection.
- Inside That OLE DB
- A new Wizard will pop up OLE DB (ADO)
- It will ask for Provider
- Select (Microsoft OLE DB Provider for SQL Server)
- Click Next Button
Just enter your SQL Server details here.
Then click on Next button
After click on Finish this wizard will appear.
- First select your Database where you have created table.
- Inside that you will find 3 options
- dbo
- INFORMATION_SCHEMA
- Sys
- Select dbo (Here you will see 2 options)
- Tables
- Stored procedures
- Select Store procedures from it.
The Store procedures
Usp_getPersonRecords will appear in the list of data sources, add the Store procedures to selected table list by clicking on the right arrow.
Click Next button
This wizard will appear.
Select all columns and then clicking on the right arrow to move in Fields to Display
Click on Next Button
We will not perform Grouping Just click Next Button
This wizard will appear.
We will not perform Record Filter Just click Next Button
This wizard will appear.
Select Standard from it and click Finish.
After Finishing the crystal report will appear to you.
Just Save it.
Let’s Move from sql to Web forms now. (ExportRecords.aspx)
On Page Add Crystal Report viewer and three images buttons
(I have add images to buttons)<form id="form1" runat="server">
<div style="margin: 0px; overflow: auto;">
<table width="100%">
<tr>
<td align="center">
<asp:ImageButton ID="Img1" Height="50px" ImageUrl="~/Images/PdfImage.jpg"
runat="server" onclick="Img1_Click" />
<asp:ImageButton ID="img2" Height="50px" ImageUrl="~/Images/index.jpg"
runat="server" onclick="img2_Click" />
<asp:ImageButton ID="img3" Height="50px" ImageUrl="~/Images/docx.png"
runat="server" onclick="img3_Click" />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</td>
</tr>
</table>
</div>
</form>
Like this View you will see
On load for Binding crystal Report i have created Method
GenerateReport().
You need to add Connection string above Page load.
SqlConnection con = new SqlConnection<br />(ConfigurationManager.ConnectionStrings["Myconstr"].ToString());
And add Connection string In Web.config.
For doing connection with database you need to add connection string to database Here is example of it.
Just replace here with your database value.
Data source
Database
User id
Password
<connectionStrings> <add name="Myconstr" connectionString="data source=SAI-PC; Database=MotorTraining; user id=sa; password=Pass$123 ;" providerName="system.data.sqlclient"/> </connectionStrings>Here I have bind crystal report on page load event.
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { GenerateReport(); } }This is method which I am using for binding crystal report on page load.
protected void GenerateReport() { SqlCommand cmd = new SqlCommand("Usp_getPersonRecords", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataTable datatable = new DataTable(); da.Fill(datatable); // getting value according to imageID and fill dataset ReportDocument crystalReport = new ReportDocument(); // creating object of crystal report crystalReport.Load(Server.MapPath("~/CrystalPersonInfo.rpt")); // path of report crystalReport.SetDataSource(datatable); // binding datatable CrystalReportViewer1.ReportSource = crystalReport; }
How It Works
On button click we are going to Export crystal report in pdf, Excel, html, format.We are bringing all data from sql server into dataset and binding dataset to crystal report data source.
Creating object of report document
ReportDocument crystalReport = new ReportDocument();
After creating object we are going to load crystal report by giving its path.
crystalReport.Load(Server.MapPath("~/CrystalPersonInfo.rpt"));Here we are assigning format to export.
crystalReport.ExportToHttpResponse(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat,
Response, true, "PersonDetails");
First Button (Img1 for exporting records in PDF format)
On button click we are going to Export crystal report in PDF format.protected void Img1_Click(object sender, ImageClickEventArgs e)
{
SqlCommand cmd = new SqlCommand("Usp_getPersonRecords", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable datatable = new DataTable();
da.Fill(datatable); // getting value according to imageID and fill dataset
ReportDocument crystalReport = new ReportDocument(); // creating object of crystal report
crystalReport.Load(Server.MapPath("~/CrystalPersonInfo.rpt")); // path of report
crystalReport.SetDataSource(datatable); // binding datatable
CrystalReportViewer1.ReportSource = crystalReport;
crystalReport.ExportToHttpResponse
(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Response, true, "PersonDetails");
//here i have use [ CrystalDecisions.Shared.ExportFormatType.PortableDocFormat ] to Export in PDF
}
First Second (Img2 for exporting records in Excel format)
On button click we are going to Export crystal report in Excel format.protected void img2_Click(object sender, ImageClickEventArgs e)
{
SqlCommand cmd = new SqlCommand("Usp_getPersonRecords", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable datatable = new DataTable();
da.Fill(datatable); // getting value according to imageID and fill dataset
ReportDocument crystalReport = new ReportDocument(); // creating object of crystal report
crystalReport.Load(Server.MapPath("~/CrystalPersonInfo.rpt")); // path of report
crystalReport.SetDataSource(datatable); // binding datatable
CrystalReportViewer1.ReportSource = crystalReport;
crystalReport.ExportToHttpResponse
(CrystalDecisions.Shared.ExportFormatType.ExcelRecord, Response, true, "PersonDetails");
//here i have use [ CrystalDecisions.Shared.ExportFormatType.ExcelRecord ] to Export in Excel
}
First Third (Img3 for exporting records in Word format)
On button click we are going to Export crystal report in Word formatprotected void img3_Click(object sender, ImageClickEventArgs e)
{
SqlCommand cmd = new SqlCommand("Usp_getPersonRecords", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable datatable = new DataTable();
da.Fill(datatable); // getting value according to imageID and fill dataset
ReportDocument crystalReport = new ReportDocument(); // creating object of crystal report
crystalReport.Load(Server.MapPath("~/CrystalPersonInfo.rpt")); // path of report
crystalReport.SetDataSource(datatable); // binding datatable
CrystalReportViewer1.ReportSource = crystalReport;
crystalReport.ExportToHttpResponse
(CrystalDecisions.Shared.ExportFormatType.WordForWindows, Response, true, "PersonDetails");
//here i have use [ CrystalDecisions.Shared.ExportFormatType.WordForWindows ] to Export in Word
}
Now save and run project.
All records will appear on Crystal report.
With five buttons to Export.
On clicking of PDF button.
On clicking Excel button.
On clicking of Word button.
On clicking of HTML button.
On clicking of RTF button
Making Easy to Understand
Step 2:
Step 3:
Step 4:
Step 5:
Preparing to build the security system for use in application, we need to configure the membership provider in web.config file. The following settings for Forms Authentication, Membership and Role provider are applied in the web.config file.
Registration page for the users can be easily created by using the
available create user wizard and the following event handlers:
The users can be authenticated by using the 
In the Admin Panel, the features to Add, Edit, Delete and Assign Roles to users are provided to the administrator.