How to create crystal reports with tables using dataset?

Hello Friend’s, in this tutorial we will learn “How to create crystal reports with tables using dataset”. Most of all the fields like finance, Ecommerce Company has required a daily, weekly, monthly or yearly report. A report is should be employee details, Profit or Loss, Sales and Purchase etc. In traditional way reports are write in General book but now a day’s most of company want to report by online and save to the paper. So as a technical we will see how we can achieve it. A Crystal report is a business intelligence application, currently marketed to small business by SAP SE.

Benefits of Crystal Reports

1. Customized Report: Create quickly highly formatted and pixel-perfect report.
2. Data source connectivity: Native, ODBC, OLE, DB, And JDBC connectivity to relational, OLAP, web services, XML.
3. Operating system compatibility: compatible with Microsoft Windows 7.
4. Mobile Compatibility: Open interactive reports through your mobile device.
5. Powerful report delivery options: deliver reports to your business user in their preferred language and format.

For use Crystal Report tool in your device, you must have these required things like Intel or AMD processors, Minimum 2 GB RAM and 4 GB hard drive space if you want to create report in English language. If you want to create report in all languages then you must have Minimum 8 GB hard drive space. The oldest version supported window 7. You can create the crystal report many languages like English, French, German, etc. For creating crystal reports first we would to download Crystal report (.exe) file. After download the file right click on executable file and select Run as administrator and follow the installation instruction.

Open Visual Studio Click on File Menu and select the new option and create new empty project. After project creation we have to setup folder structure. We have to create 5 main project folders (View, Model, Reports, Downloaded file, files). In this tutorial we are the crystal report for employee detail so first of all we should to create Database where we store the employee details and by using those details we will create the reports. So create new DATABASE and create one table (tb_emp) with below fields.

Table Employee

CREATE TABLE [dbo]. [tbl_emp] (
 [Id] [int] IDENTITY (1, 1) NOT NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [DOB] [date] NULL,
 [City] [varchar](50) NULL,
 [Contact] [varchar](50) NULL,
 [Department] [varchar](50) NULL,
 [ImageUrl] [nvarchar](max) NULL,
 CONSTRAINT [PK_tbl_emp] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

For developing this Crystal Report application we use the Entity framework DataBase first Model, so we need to install the EntityFramewok Package. Right click on project and click on Manage Nuget Packages Search the Entity Framework in Browse then select the version and click on Install. Now we will add the Model.edmx file in the Model folder. Right click on Model folder and click on Add New Item, select the Data option from the left panel you can see many option in center panel, select the ADO.NET Entity Data Model and give the proper name of Model file then click on Add button, there open Entity Data Model Wizard select the EF Designer from database and click on Next button and on next panel Choose the Data Connection, If you want to create new connection then click on New connection and create connection but we have already created connection so select the Data Connection and bottom of Model one textbox available and in that textbox given entity name click on Next button, choose the table and views that you want to include in .edmx file and there you can view the Model Namespace and finally click on Finish button. We couldn’t know which entity name we can use in code so expand the Model.edmx file and then expand the Model1.Context.tt file and open the Model.Context.cs file, in the constructor you can see the name of entity.

EmployeeReport.xsd

You know that we are creating crystal report using DatSet, so create a new folder DataSet in Reports folder. After creating the Dataset folder Right click on DataSet folder click on Add New Item. In left panel side select Data Option when you select the Data option on center panel you can see many options, so from that options select the DataSet option that creating the (.xsd) file click on Add button. Open the created DataSet (.xsd) file when open this file you can see the DataSet options in Toolbox. If you don’t see this then click Toolbox link on your opened DataSet (.xsd) file. Drag and Drop the DataTable on DataSet (.xsd) file and click on Header section of DataTable there you must write the DataTable Name then Right click on DataTable and Add New columns. After added all the columns you can change the data type of columns, for that select the column and then right click on that column select the Properties, In Property window you can see the Column data type and you can change the data type for that column. For ProfilePicture field we have set the Byte [] datatype.

EmployeeReport.rpt

Let’s creating Crystal Report, Right click on Report folder and select Add New Item option, on New Item Popup Modal from Left Panel select the Reporting option. A Reporting option available if you have installed the Cystal Report otherwise is not available there. In the center panel select the Crystal Reports and click on Add button, Select the Standard option from Crystal Report Gallery Modal and click ok button. There Open Standard Report Creation Wizard and Expand the Project Data option because we are creating report using DataSet. If we create report by Database table then we have to choose My Connections option. So expand the Project Data Option then expand the ADO.NET DataSets option, there you can see your created DataTable name, select that DataTable name and click on (>) arrow for adding in Selected Tables and click on Next button in the next step you would select the properties which you want to display in report. So Select properties and add it in Fields to Display Section and then click on Next button and at the last step Select the Report Style we select the Standard Report Style and finally click on Finish button.

Open the (.rpt) file which you have just created, you can see there all fields are available that we have selected. There are 5 sections are available (Report Header, Page Header, Details, Report Footer, Page Footer). We use only Details section Let’s add the Labels like first name, last name, so right click on opened (.rpt) file page and select the Insert option and then select the Text Object and add write the text which is you want to show. You can see Profile Picture field is a space of Picture showing.

Let’s Create the Web Form; there we add one button of “Create Report”. On button click we get the employee details from DataBase and set in Report properties, get the images from folders. We have already images in employee folder and employee folder is in files folder. We get the images which we will use for displaying on report and after report is created that report store in DownloadedFile folder.

EmployeeList.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeList.aspx.cs" Inherits="CrystalReport.View.EmployeeList" %>
<!DOCTYPE html>
<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js" type="text/javascript"></script>
    <title>Employee Report</title>
    <script type="text/javascript">
        function SaveLabel(url) {
            if (url != null) {
                $("#aFileLink").attr("href", url);
                document.getElementById("aFileLink").click();
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <a href="javascript:void(0);" id="aFileLink" target="_blank" download></a>           
            <asp:Button ID="btnReport" runat="server" Text="Create Report" OnClick="btnReport_Click" />
        </div>
    </form>
</body>
</html>

We should to create Employee class with those properties that we added in DataTable. We get the employee details and that details set in List of Employee class object when creating report.

Employee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CrystalReport
{
    public class Employee
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string DOB { get; set; }
        public string City { get; set; }
        public string Contact { get; set; }
        public string Department { get; set; }
        public Byte[] ProfilePicture { get; set; }
    }
}

In the EmployeeList.aspx.cs file created the Create Report button click event and we have create the functions these are useful for Profile Picture set on Crystal Report.

EmployeeList.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalReport.Model;
using CrystalDecisions.CrystalReports.Engine;
using System.IO;
using System.Data;
using CrystalDecisions.Shared;
using System.Drawing;
using System.Drawing.Imaging;

namespace CrystalReport.View
{
    public partial class EmployeeList : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void btnReport_Click(object sender, EventArgs e)
        {
            try
            {
                ReportDocument rprt = new ReportDocument();
                ExampleEntities entity = new ExampleEntities();
                List<Employee> listOfEmployee = new List<Employee>();
                List<tbl_emp> employeeList = entity.tbl_emp.ToList();

                foreach (var item in employeeList)
                {
                    Employee employee = new Employee();
                    employee.FirstName = item.FirstName;
                    employee.LastName = item.LastName;
                    employee.DOB = item.DOB.Value.ToString("dd-MM-YYYY");
                    employee.City = item.City;
                    employee.Contact = item.Contact;
                    employee.Department = item.Department;
                    if (!string.IsNullOrEmpty(item.ImageUrl))
                    {
                        employee.ProfilePicture = GetBytesFromImage(Server.MapPath(Path.Combine("~", item.ImageUrl)));
                    }
                    listOfEmployee.Add(employee);

                }

                DataTable dt1 = new DataTable("EmployeeReport");
                dt1.Columns.Add("FirstName", System.Type.GetType("System.String"));
                dt1.Columns.Add("LastName", System.Type.GetType("System.String"));
                dt1.Columns.Add("DOB", System.Type.GetType("System.String"));
                dt1.Columns.Add("City", System.Type.GetType("System.String"));
                dt1.Columns.Add("Contact", System.Type.GetType("System.String"));
                dt1.Columns.Add("Department", System.Type.GetType("System.String"));
                dt1.Columns.Add("ProfilePicture", System.Type.GetType("System.Byte[]"));

                foreach (var item in listOfEmployee)
                {
                    dt1.Rows.Add(item.FirstName, item.LastName, item.DOB, item.City, item.Contact, item.Department, item.ProfilePicture);
                }
                rprt.Load(Server.MapPath("/Reports/EmployeeReport.rpt"));
                rprt.SetDataSource(dt1);

                string fileNamePath = Server.MapPath("/DownloadedFile/");
                if (!System.IO.Directory.Exists(fileNamePath))
                {
                    System.IO.Directory.CreateDirectory(fileNamePath);
                }

                string fileName = "Employee Report" + DateTime.Now.ToString("dd-MM-yyyy") + ".pdf"; ;
                fileNamePath = System.IO.Path.Combine(fileNamePath, fileName);
                rprt.ExportToDisk(ExportFormatType.PortableDocFormat, fileNamePath);
                rprt.Close();
                rprt.Dispose();

                string FilePath = System.IO.Path.Combine("/DownloadedFile/" + fileName);
                System.Web.UI.ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), string.Concat("AjaxList_fillList", ""), "SaveLabel('" + FilePath + "');", true);
            }
            catch (Exception ex)
            {

            }
        }

        private byte[] GetBytesFromImage(string FilePath)
        {
            byte[] imgbyte = null;
            try
            {
                FileStream fs;
                BinaryReader br;
                if (File.Exists(FilePath))
                {
                    fs = new FileStream(FilePath, FileMode.Open);
                }
                else
                {
                    fs = null;
                    return imgbyte;
                }
                br = new BinaryReader(fs);
                imgbyte = new byte[fs.Length + 1];
                imgbyte = br.ReadBytes(Convert.ToInt32((fs.Length)));
                br.Close();
                fs.Close();
                imgbyte = ResizeImagesBytes(imgbyte);
            }
            catch (Exception ex)
            {
                return null;
            }
            return imgbyte;
        }

        public byte[] ResizeImagesBytes(byte[] data)
        {
            using (var ms = new MemoryStream(data))
            {
                var newImage = new Bitmap(ms);
                //var resized = new Bitmap(newImage, new Size(newImage.Width / 4, newImage.Height / 4));
                //return ReplaceTransparency(newImage, System.Drawing.Color.White);
                //byte[] resizeImage = ReplaceTransparency(newImage, System.Drawing.Color.White);
                //using (var ts = new MemoryStream(resizeImage))
                //{
                //    var System.Drawing.Image = new Bitmap(ts);
                Bitmap modifiedImage = FixedSize(newImage, 300, 300);

                ImageConverter converter = new ImageConverter();
                byte[] returndata = (byte[])converter.ConvertTo(modifiedImage, typeof(byte[]));
                return returndata;
                //}
            }

        }

        static System.Drawing.Bitmap FixedSize(System.Drawing.Image imgPhoto, int Width, int Height)
        {
            int sourceWidth = imgPhoto.Width;
            int sourceHeight = imgPhoto.Height;
            int sourceX = 0;
            int sourceY = 0;
            int destX = 0;
            int destY = 0;

            float nPercent = 0;
            float nPercentW = 0;
            float nPercentH = 0;

            nPercentW = ((float)Width / (float)sourceWidth);
            nPercentH = ((float)Height / (float)sourceHeight);
            if (nPercentH < nPercentW)
            {
                nPercent = nPercentH;
                destX = System.Convert.ToInt16((Width -
                              (sourceWidth * nPercent)) / 2);
            }
            else
            {
                nPercent = nPercentW;
                destY = System.Convert.ToInt16((Height -
                              (sourceHeight * nPercent)) / 2);
            }

            int destWidth = (int)(sourceWidth * nPercent);
            int destHeight = (int)(sourceHeight * nPercent);

            Bitmap bmPhoto = new Bitmap(Width, Height,
                              PixelFormat.Format16bppRgb555);
            bmPhoto.SetResolution(imgPhoto.HorizontalResolution,
                             imgPhoto.VerticalResolution);

            Graphics grPhoto = Graphics.FromImage(bmPhoto);
            grPhoto.Clear(Color.White);
            grPhoto.InterpolationMode =
                    System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;

            grPhoto.DrawImage(imgPhoto,
                new Rectangle(destX, destY, destWidth, destHeight),
                new Rectangle(sourceX, sourceY, sourceWidth, sourceHeight),
                GraphicsUnit.Pixel);

            grPhoto.Dispose();
            return bmPhoto;
        }
    }
}