Crystal Reports Customization

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, 2^nd 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:

Collapse | Copy Code

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

Collapse | Copy Code

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.

1. Inside That OLE DB
2. A new Wizard will pop up OLE DB (ADO)
   
   
   
   
3. It will ask for Provider
4. Select (Microsoft OLE DB Provider for SQL Server)
5. Click Next Button
   
   
   

Connection Information Wizard will appear.
Just enter your SQL Server details here.


Then click on Next button


After click on Finish this wizard will appear.

1. First select your Database where you have created table.
2. Inside that you will find 3 options  
1. dbo  
2. INFORMATION_SCHEMA
3. Sys  
3. Select dbo (Here you will see 2 options)
1. Tables
2. Stored procedures
4. Select Store procedures from it.

(Because we will bind Store procedures to report to display information)

 
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)

Collapse | Copy Code

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

Collapse | Copy Code

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

Collapse | Copy Code

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

Collapse | Copy Code

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.

Collapse | Copy Code

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

Collapse | Copy Code

ReportDocument crystalReport = new ReportDocument();

After creating object we are going to load crystal report by giving its path.

Collapse | Copy Code

crystalReport.Load(Server.MapPath("~/CrystalPersonInfo.rpt"));

Here we are assigning format to export.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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 format

Collapse | Copy Code

protected 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