Bind Crystal Reports with Dataset or Datatable

• Download testCrystal.rar - 372.2 KB
• Download testCrystal.zip - 397.9 KB

What is crystal Reports.

Crystal Reports allows users to graphically design data connection(s) and report layout. In the Database Expert, users can select and link tables from a wide variety of data sources, including Microsoft Excel spreadsheets, Oracle databases, Microsoft SQL Server databases, Microsoft Access databases, Business Objects Enterprise business views, and local file system information. Fields from these tables can be placed on the report design surface, and can also be used in custom formulas, using either BASIC or Crystal's own syntax, which are then placed on the design surface. Formulas can be evaluated at several phases during report generation as specified by the developer.

Although Crystal Reports are not a part of visual studio for using crystal Reports you need to install it from SAP site. It has a great potential to work with .NET Application and provides many functionality.

Source: http://en.wikipedia.org/wiki/Crystal_Reports

Introduction

Hey folks, Many times it is needed in development that we need to bind a crystal report with a virtual database or dataset. I have search many websites when i encounter this problem and finally after a lot of effort i have found a solution which solved my problem.In This article i have described that how we can bind a crystal report with a virtual database or dataset.The one who are new to crystal reports do not worry,you just need some knowledge of c#,Ado.net,and crystal reports.

Background

Here are some of the sites link which may help you:

1)https://www.youtube.com/watch?v=CbN-kXolezQ

2) http://www.codeproject.com/Articles/10948/How-to-use-Crystal-Report-in-your-project

Using the code

Before we jump on to the code you need to install SAP crystal Reports the link is being provided

http://scn.sap.com/people/coy.yonce/blog/2010/11/12/crystal-reports-for-visual-studio-2010-production-release-now-available

In this link you need to download the Complete.exe and install it on your system.

Let's Start with the code,In this article i have created a windows application named Crystal App as shown

After Creation of the project you need to change the Target Framework from .NET Framework 4.0 client profile to .NET Framework 4.0 ,so that you can use the Crystal Report Viewer tool from the toolbox.

After this i have include an APP.config file in my project for the configuration settings i.e. to set my database connection string.

In this Project i have created two Forms named Form1 and CrystalFormView and in the References i have included the Following dll files:

CrystalDecisions.CrystalReports.Engine

CrystalDecisions.ReportSource

CrystalDecisions.Shared

CrystalDecisions.Windows.Forms

You can find this dll files in the following location of your system.

C:\Program Files (x86)\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Common\SAP BusinessObjects Enterprise XI 4.0\win32_x86\dotnet

In the first step i have added a DataSet named Dataset1 in my solution. This dataset will act like a virtual database in my solution. As Shown Below

In the Data set I have created a table named BILL TEST with 3 fields (Product,Product ID,Quantity) as shown

In the Next Step i have added a Report Wizard in my Solution named Report1.rdlc,when adding the report wizard you need to configure a few things which are shown below

In the second step you need to configure the data set and the data table with which you want to deal

In the Third step you need to drag and drop the fields which you want to show in the Report from Available Fields to Values

In the Next Step Just Click the next button and then click the Finish Button. A rdlc report will be created

in the solution the rdlc report will work as a XML file which contains the data of the datatable in the dataset with which it is bind.After these steps you need to drag and drop a Report viewer from the toolbox on the Form1. In the ReportViewer1 choose the report as report1.rdlc which we have created.As soon as you do this this will add two controls to your form BILLTESTBindingSource and Dataset1. The BILLTESTBindingSource is being used to set the datasource for the Report1.rdlc as shown in the code.

//
// Any source code blocks look like this
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace CrystalApp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            
            string connection = ConfigurationManager.ConnectionStrings["sqlbill"].ConnectionString;
            string provider = ConfigurationManager.ConnectionStrings["sqlbill"].ProviderName;
            SqlConnection con = new SqlConnection(connection);
            SqlDataAdapter sda = new SqlDataAdapter("select product as Product,productid as ProductId,quantity as Quantity from productdata", con);

            DataSet ds = new DataSet();
            sda.Fill(ds);
            ds.Tables[0].TableName = "BILLTEST";

            this.BILLTESTBindingSource.DataSource=ds;//the binding is needed when the dataset is used as a connection(or providing the data to the datatable in data set.xsd)
            //this.reportViewer1.RefreshReport();


            this.reportViewer1.RefreshReport();
        }

        private void CrystalBtn_Click(object sender, EventArgs e)
        {
            CrystalFormView crystal = new CrystalFormView();
            crystal.ShowDialog();
        }

        
    }
}

// Code for the next From Crystal Form View
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace CrystalApp
{
    public partial class CrystalFormView : Form
    {
        public CrystalFormView()
        {
            InitializeComponent();
        }

        private void CrystalFormView_Load(object sender, EventArgs e)
        {
            
           
            string connection = ConfigurationManager.ConnectionStrings["sqlbill"].ConnectionString;
            string provider = ConfigurationManager.ConnectionStrings["sqlbill"].ProviderName;
            SqlConnection con = new SqlConnection(connection);
            SqlDataAdapter sda = new SqlDataAdapter("select product as Product,productid as ProductId,quantity as Quantity from productdata", con);

            DataSet ds = new DataSet();
            sda.Fill(ds);
            ds.Tables[0].TableName = "BILLTEST";//not necessary when the connection is directly made to the database
      
BillCrystalReport bill = new BillCrystalReport();  
                bill.SetDataSource(ds);                                                                                               bill.VerifyDatabase();                                                                                     crystalReportViewer1.ReportSource = bill;                                             crystalReportViewer1.RefreshReport(); } } } 

In the code Remember that the table name should be same name of the table in the dataset. After this you can build the solution and see the data is binded with the rdlc report.

In the next step i have added a Crystal Report in the solution named BillCrystal and bind the crystal Report With the Dataset1 which contains the table BILLTEST and set the fields on the report as shown Below

In the next step i have added a Form name CrystalFormView and place a CrystalReportViewer on the Form and on the Page_Load event i have bind the CrystalReportViewer with the crystalreport name BillReport.rpt.

In the next step i have added a button on the form and on the click of the button i am opening the form CrystalFormView which contains the crystal report i.e. BillReport. In the page load event of the form named CrystalFormView i have used a disconnected layer architecture to fetch the table from the database and add that table to the dataset named ds . In the code Remember that the table name should be same name of the table in the dataset. After this i have created a object of BillReport named bill . In this i set the datasource of the crystal Report i.e. BillReport and provide the datasource the dataset which contains my data table. I have also Called the VerifyDatabase Method.

Using the Verify Database process

When you choose Verify Database from the Crystal Reports menu, the program checks the active databases and reports. If it detects changes, the report must be adapted to prevent errors.

Crystal Reports automatically adapts the report if it detects any of these changes:

• Fields have been added to the database.

• Fields that are not used in the report have been deleted from the database.

• Field positions have changed in the database.

• Data types have changed for fields in the database.

Source : http://publib.boulder.ibm.com/infocenter/rsahelp/v7r0m0/index.jsp?topic=/com.businessobjects.integration.eclipse.designer.doc/designer/Understanding_Databases14.html

After this i have set the report source of crystal report viewer and provide the value bill and called the refresh report method of crystal report viewer which will Refreshes the report displayed in the CrystalReportViewer control and that's all.

The report will look like the above.

I hope this solution will help you .Happy Learning .