Crystal reports example/sample in asp.net

C# Crystal Reports sample databse

Create DataBase "crystaldb"

In the crystaldb database , create three tables

OrderMaster , OrderDetails , Product .

The Table Structure follows :

  OrderMaster

        OrderMaster_id

        OrderMaster_date

        OrderMaster_customer

        OrderMaster_createduser

  OrderDetails

        OrderDetails_id

        OrderDetails_masterid

        OrderDetails_productid

        OrderDetails_qty

  Product

        Product_id

        Product_name

        Product_price

The following picture shows the relation of tables in crystaldb database :

CREATE TABLES ON SQL

SQL command for creating tables are follows :

  CREATE TABLE [dbo].[OrderMaster] (

[OrderMaster_id] [int] NOT NULL ,

[OrderMaster_date] [datetime] NULL ,

[OrderMaster_customername] [varchar] (50),

[OrderMaster_createduser] [varchar] (50)

) ON [PRIMARY]

  CREATE TABLE [dbo].[OrderDetails] (

[OrderDetails_id] [int] NOT NULL ,

[OrderDetails_masterid] [int] NULL ,

[OrderDetails_productid] [int] NULL ,

[OrderDetails_qty] [int] NULL

) ON [PRIMARY]

  CREATE TABLE [dbo].[Product] (

[Product_id] [int] NOT NULL ,

[Product_name] [varchar] (50) ,

[Product_price] [numeric](18, 0) NULL

) ON [PRIMARY]

Order Master Table Data

Order Details Table Data

Product Table Data

C# Crystal Reports step by step

Open Visual Studio .NET and select a new CSharp Windows project.

Now you will get the default Form1.cs.

From the main menu in Visual Studio C# project select PROJECT-->Add New Item . Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.

Select Report type from Crystal Reports gallery.

Accept the default settings and click OK.

Next step is to select the appropriate connection to your database (here crstaldb). Here we are going to select OLEDB Connection for SQL Server to connect Crystal Reports in C#.

Select OLE DB (ADO) from Create New Connection .

Select Microsoft OLE DB Provider for SQL Server .

The next screen is the SQL Server authentication screen for connecting to the database - crystalDB. Select your Sql Server name , enter userid , password and select your Database Name .

Click next , Then the screen shows OLE DB Property values , leave it as it is , and then click finish button.

After you click the finish button , the next window you will get your Server name under OLEDB Connection, from there selected database name (Crystaldb) and click the tables , then you can see all your tables from your database.

From the tables list double click the Product table then you can see the Product table will come in the right side list.

Click Next Button

Select all fields from Product table to the right side list .

Click Finish Button. Then you can see the Crystal Reports designer window in your C# project. In the Crystal Reports designer window you can see the selected fields from Product table. You can arrange the field Objects and design of the screen according your requirements. After that your screen is look like the following picture.

Now the designing part is over and the next step is to call the Crystal Reports in your C# application and view it through Crystal Reports Viewer control in C#.

Select the default form (Form1.cs) you created in C# and drag a button and a CrystalReportViewer control to your form .

After you drag the CrystalReportViewer to your form , it will look like the following picture.

You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code.

using CrystalDecisions.CrystalReports.Engine;

using System;

using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;

namespace WindowsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            ReportDocument cryRpt = new ReportDocument();

            cryRpt.Load(PUT CRYSTAL REPORT PATH HERE\\CrystalReport1.rpt");

            crystalReportViewer1.ReportSource = cryRpt;

            crystalReportViewer1.Refresh();

        }

    }

}

C# Crystal Reports from multiple tables

Select all table from the table list to right side list box, because we are creating report from three tables ( OrderMaster, OrderDetails, Product) . If you don't know up to this part of the tutorial , refer previous tutorial for up to selecting databese for Crystal reports.

The next step is to make relations between these selected tables. Here we are connecting the related fields from each table. For that we arrange the tables in visible area in the list (this is not necessary ) and select the fields that we want to make relation and drag to the related field of the other selected tables. After made the relations with tables the screen is look like the following picture .

Next step is to select the fields from the selected tables ( OrderMaster, OrderDetails, Product) . Here we are selecting the fields Customername , orderdate from ordermastertable , Productname from product table and quantity from order details table. The field selection screen is look like the following picture .

After select the fields from tables, click the Finish button because now we are not using any other functionalities of the Crystal Reports wizard. After that you will get the Crystal Reports designer window . You can arrange the fields in the designer window according to your requirement to view the report .

For re-arranging fields in the designer window , you can drag the field object on the screen . For editing right click the field object and select Edit Text Object. The following picture shows the sample of designer window after rearrange the field.

Now the designing part is over and the next step is to call the Crystal Reports in C# and view it in Crystal Reports Viewer control .

Select the default form (Form1.cs) you created in C# and drag a button and a CrystalReportViewer control to your form .

After you drag the CrystalReportViewer to your form , it will look like the following picture.

You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code.

using CrystalDecisions.CrystalReports.Engine;

Copy and paste the following source code and run your C# project

Next :  C# Crystal Reports String parameter

Download Source Code

Print Source Code

using System;

using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;

namespace WindowsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            ReportDocument cryRpt = new ReportDocument();

            cryRpt.Load(PUT CRYSTAL REPORT PATH HERE\\CrystalReport1.rpt");

            crystalReportViewer1.ReportSource = cryRpt;

            crystalReportViewer1.Refresh();

        }

    }

}

C# Crystal Reports String parameter

Select the Field Explorer from CrystalReport Menu. (Up to here explained in detail in the previous section C# Crystal Reports from multiple tables)

Then you can see Field Explorer in the Left hand side.

Select Parameter Field from Field Explorer and right Click on Parameter Field.

Fill the appropriate name for Name and Prompting text fields like the following picture.

After creating the parameter field , we have to create the selection formula for the Crystal Reports parameter.

For creating selection formula , Right click on Crystal Reports designer window , select REPORT -> SELECTION FORMULA -> RECORD .

Now you can see the record Selection Formula Editor Screen. For creating selection formula , you have to select the fields from above field list and make the formula .

First you have to select OrderMaster.OrderMaster_customername from Report Field and select the comparison operator and select the parameter field. Double click each field then it will be selected.

Form the following picture you can understand how to select fields.

You can close Selection Formula Editor screen after creating selection formula.

Now the designing part is over and the next step is to call the Crystal Reports in C# and view it in Crystal Reports Viewer control .

Select the default form (Form1.cs) you created in C# and drag a button and a CrystalReportViewer control to your form .

You have to include CrystalDecisions.CrystalReports.Engine in your C# Source Code.

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using System;

using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

namespace WindowsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            ReportDocument cryRpt = new ReportDocument();

            cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt");

            ParameterFieldDefinitions crParameterFieldDefinitions ;

            ParameterFieldDefinition crParameterFieldDefinition ;

            ParameterValues crParameterValues = new ParameterValues();

            ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();

            crParameterDiscreteValue.Value = textBox1.Text;

            crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields;

            crParameterFieldDefinition = crParameterFieldDefinitions["Customername"];

            crParameterValues = crParameterFieldDefinition.CurrentValues;

            crParameterValues.Clear();

            crParameterValues.Add(crParameterDiscreteValue);

            crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);

            crystalReportViewer1.ReportSource = cryRpt;

            crystalReportViewer1.Refresh(); 

        }

    }

}