Crystal Report With Parameter In ASP.NET Using C#

We will first download the SAP Crystal report from the source, and install it on our system, then with the help of some simple code we will go ahead.

Initial chamber

Firstly, download the SAP Crystal report for respective IDE’s of Visual Studio,
For Visual Studio 2010 -

For Visual Studio 2013

Step 1: Open Visual Studio 2010 and create an Empty Website. Give it a suitable name crystalrpt_demo.

Step 2: In Solution Explorer you will get your empty website, then add a Web Form and SQL Server Database and Dataset. By following these steps:

For Web Form

crystalrpt_demo(Your Empty Website) - Right Click, Add New Item, then Web Form. Name it crystalrpt_demo.aspx.

For SQL Server Database

crystalrpt_demo(Your Empty Website) - Right Click, Add New Item, then SQL Server Database. Add Database inside the App_Data_folder.

For DataSet

crystalrpt_demo (Your Empty Website) - Right Click, Add New Item, then DataSet[Add DataSet inside the App_Code_folder].

Database chamber

Step 3: In Server Explorer, click on your Database [Database.mdf] - Tables, then Add New Table. Make table like the following:

Table - tbl_data [Don’t Forget to make ID as IS Identity - True].

table design

Insert some data inside your table by going to your table - tbl_data - Right Click and show tbl_data. Add data in the table.

Show table data

table

Store Procedure sp_select:

Store Procedure

Double click on the dataset that resides under App_code_Folder. Here's an image:

dataset

When you double click the dataset you get the blank area, there you have to do something like the following:

Right Click on the blank area and Add DataTable.

Add Datatable

Datatable

Change the name to table from Datatable1 to Newtbl_data, there you have to add 3 columns for ID, Name, City as we had taken in tbl_data, so add three Columns. You will get something like the following image:

Columns

After this process you have to go to the crystalrpt_demo - Right Click, Add New Item and find Crystal Report.rpt file.

Crystal Report demo

When you add this new Crystal Report Gallery opened there you can see the following image:

new Crystal Report

You got something like the following image:

Crystal Report page

When your crystal report opens like the above image, then you can find that on the left or right dock Field Explorer opens up.

database field

Right Click on Field Explorer - Database Expert, Create New Connection - you have to find the table that we had made in Dataset i.e Newtbl_data.

Once your table is visible Add that table to the right hand side pane using “ >>” button an Press OK. See the following image.

Add that table

You will get you Newtbl_data in Field Explorer like the following screenshot:

Field Explorer

Here you have to add these ID, Name and City from Database Fields to the Crystal Report - Section 3 Page Details. You can drag these fields to the Crystal Report Page Details.

Crystal Report

In the Field Explorer - Right Click on Parameter, then clicck New. Here's the screenshot:

Parameter

It will open up the Parameter Window where you have to add one parameter that is “age1”.

age1

Now go the Crystal Report Detail Section - Right Click - Report, then click Section Formula and Record.  Refer the following image:

Record

It will open up the Formula Workshop.

age

Design chamber

Step 4: Now open your crystalrpt_demo.aspx file, where we create our design for crystal report. We will add Crystal Report Viewer here.

Go to the toolbox and find Reporting, then Drag and Drop Crystal Report Viewer.

crystalrpt_demo.aspx

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <%@ Register assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>  
  4.   
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  6.   
  7. <html xmlns="http://www.w3.org/1999/xhtml">  
  8. <head runat="server">  
  9.     <title></title>  
  10. </head>  
  11. <body>  
  12.     <form id="form1" runat="server">  
  13.     <div>  
  14.   
  15.         Select Age <=   
  16.   
  17.         <asp:DropDownList ID="DropDownList1" runat="server">  
  18.             <asp:ListItem>Select Age</asp:ListItem>  
  19.             <asp:ListItem>10</asp:ListItem>  
  20.             <asp:ListItem>20</asp:ListItem>  
  21.             <asp:ListItem>15</asp:ListItem>  
  22.             <asp:ListItem>30</asp:ListItem>  
  23.             <asp:ListItem>50</asp:ListItem>  
  24.         </asp:DropDownList>  
  25.       
  26.             
  27.     <asp:Button ID="Button1" runat="server" onclick="Button1_Click"   
  28.             Text="Find Record" />  
  29.       
  30.         <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"   
  31.             AutoDataBind="true" />  
  32.       
  33.     </div>  
  34.     </form>  
  35. </body>  
  36. </html>  
Code chamber

Step 5: Open your crystalrpt_demo.aspx.cs and write some code so that our application starts working.

    crystalrpt_demo.aspx.cs

Add some Namespaces to crystalrpt_demo.aspx.cs page.

namespace

  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 System.Data;  
  8. using System.Data.SqlClient;  
  9. using CrystalDecisions.ReportSource;  
  10. using CrystalDecisions.Reporting;  
  11. using CrystalDecisions.CrystalReports.Engine;  
  12.   
  13. public partial class _Default : System.Web.UI.Page  
  14. {  
  15.   
  16.     ReportDocument rprt = new ReportDocument();  
  17.     protected void Page_Load(object sender, EventArgs e)  
  18.     {  
  19.          
  20.     }  
  21.     protected void Button1_Click(object sender, EventArgs e)  
  22.     {  
  23.         rprt.Load(Server.MapPath("~/CrystalReport.rpt"));  
  24.         SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");  
  25.         SqlCommand cmd = new SqlCommand("sp_select", con);  
  26.         SqlDataAdapter sda = new SqlDataAdapter(cmd);  
  27.         DataTable dt = new DataTable();  
  28.         sda.Fill(dt);  
  29.         rprt.SetDataSource(dt);  
  30.         rprt.SetParameterValue("age1", DropDownList1.Text);  
  31.         CrystalReportViewer1.ReportSource = rprt;  
  32.         CrystalReportViewer1.DataBind();  
  33.     }  
  34. }  
Output chamber

Output

Main report

Hope you liked it. Thank you for reading. Have a good day.


Similar Articles