Showing Crystal Report in Asp.Net using XML Data

Crystal Report is a powerful tool when it comes to designing reports in .Net framework. In this article, I’ll show you with example on how to add a crystal report in your Asp.Net project with report navigating options.

Add a Crystal Report in Asp.Net

The Employee Table

To begin with, I'll first create an employee table and add few rows in it. The Crystal Report needs data, and we will fetch the data from this table.

CREATE TABLE Employee 
    (EmpID INT NOT NULL , 
        EmpName VARCHAR(50) NOT NULL, 
	    Designation VARCHAR(50) NULL, 
        Department VARCHAR(50) NULL, 
        JoiningDate DATETIME NULL,
	    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmpID)
    )

Instead, use our dummy database to create more tables with rows.

Try to add more rows in the Employee table. Say around 50 rows. It is always good to have lot of data to test data centric applications and reports. Also, create tables like “List of Passengers”, “Friends List” etc.

How to add Crystal report in your Asp.Net Project?

Start visual studio. From the File menu, select New -> Web Site -> Choose ASP.NET Web Site and click OK. Choose the language you are comfirtable with. However, we are using both C# and Vb.net in this article.

In this example, I'll use an HTML Panel control, and add the Crystal Report to it. The panel control will have 5 “Buttons” and a “DropDown” control. The dropdown control will have a list of printers, extracted from the local machine. The buttons are for printing and navigating the report.

The Style and Markup
<!DOCTYPE html>
<html>
<head>
    <title>Add Crystal Report in Asp.Net</title>
    <style>
        <%--STYLE IT--%>
        .button 
        {
            text-align:center;
            color:#FFF;
            font:12px Sans-Serif;
            font-weight:normal;
            background-color:#6D9BF1;
            border:solid 1px #3079ED; 
            border-radius:2px; -moz-border-radius:2px; -webkit-border-radius:2px;
	        line-height:15px;
	        padding:3px 2px;
	    }
        .button:hover 
        {
            background-color:#4876FF;
            cursor:pointer;
        }
        .panRep 
        {
            border:solid 1px #93A0AA;
            background-color:#FFF;
            position:absolute;
            text-align:center;
            top:50px;
            left:10px;
        }
        .divprint 
        {
            font:15px Arial;
            padding:10px;
        }
        .dropdown 
        {
            text-align:center;
            color:#333;
            padding:2px;
            font:13px Arial;
        }
    </style>
</head>

<body>
    <form id="form1" runat="server">
        <asp:Panel ID="panReport" runat="server" CssClass="panRep" Visible="false">
        
            <div class="divprint" style="float:left">
                Select a Printer:  <asp:DropDownList ID="ddlPrinters" CssClass="dropdown" 
                    runat="server"></asp:DropDownList>
                <asp:Button id="btPrintRep" CssClass="button" ToolTip="Print" 
                    Text="Print" runat="server" />
            </div>
            
            <div class="divprint" style="float:left">
                Navigators:
                <asp:Button ID="btFirstPage" ToolTip="First Page" Text="<<" runat="server" />
                <asp:Button ID="btNextPage" ToolTip="Next Page" Text=">" runat="server" />
               <asp:Button ID="btPrevPage" ToolTip="Previous Page" Text="<" runat="server" />
                <asp:Button ID="btLastPage" ToolTip="Last Page" Text=">>" runat="server" />
            </div>
        </asp:Panel>
    </form>
</body>
</html>

Once the HTML part is done add a folder named Reports in the root directory. (See the below image.)

Crystal Report Demo

Now follow these steps to add a Crystal Report in your project.

01) Right click the reports folder in the Solution Explorer, and select Add New Item…
02) In the Template list, select Crystal Report. Set the name of report as Employee.rpt and click Add. You can choose your desired Language.
03) Visual studio will open Crystal Reports Gallery window. In that choose As a Blank Report option. (We’ll design the report). Click the “OK” button.


You will also need to add few crystal report references.

a) CrystalDecisions.CrystalReports.Engine
b) CrystalDecisions.Web
c) CrystalDecisions.Shared

To add the above references, select Website menu from the top menu list, and select Add Reference. In the .NET tab, find and select the above mentioned components and click the “OK” button.

To confirm your selection of the above references, right click the Project in the Solution Explorer window and select Property Pages. It will show you the list of References in your project.

Create a DataSet

We need a DataSet which will create an XML schema. In addition, the DataSet (with the extention .xsd), will have the columns required to design the report.

To create the DataSet, right click the reports folder in the Solution Explorer window and click Add New Item…. Select DATASET from the list of TEMPLATES and name it as Employee.xsd and click “OK”.


Add a DataTable

Right click the DataSet (Employee.xsd file) and select Add -> DataTable

Add a DataTable in Crystal Report


Add Columns in your DataTable

The columns you will add in your DataTable will be the columns in the Crystal Report. So, right click on the DataTable in the DataSet and add columns. Repeat the process one by one until we have added all the columns.

Note: The column names should match with columns in your SQL query.

Add Colunms to DataTable in Crystal Report


Add Employee.xsd to Employee.rpt (Crystal Report file)

Open “Employee.rpt” and in the “Field Explorer” window, right click “Database Fields”, and select “Database Expert”.

Choose Create New Connection and select ADO.NET in the Database Expert window.

Set Connection in Crystal Report

In the File Path option, choose the Employee.xsd file and click the Finish button. It will finally ask you to add the select tables. See the image below.

Set Connection in Crystal Report

Once you click the OK button, the Employee table will be added under Database Fields in the Crystal Report Field Explorer window. Add the columns by dragging them in the Main Report section of the report. Design the report according to your requirement and save it.

The image below, shows the final layout of the report

Crystal Report Layout


Finally, we have designed our report. Now we will write the code to call the report from our application along with the code for navigating the report.

C#
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    // DECLARE THE "REPORT" AND "REPORT VIEWER" OBJECT.
    CrystalDecisions.CrystalReports.Engine.ReportDocument Report = 
        new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    CrystalDecisions.Web.CrystalReportViewer CrystalReportViewer =
        new CrystalDecisions.Web.CrystalReportViewer();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack) { 
            // ADD THE LIST PRINTERS IN THE DROPDOWN CONTROL.
            foreach  (String printer in 
                    System.Drawing.Printing.PrinterSettings.InstalledPrinters)
            {
                ddlPrinters.Items.Add(printer);
            }

            LoadData();
        }
    }
    private void LoadData()
    {
        // CALL "ShowReport()" FUNCTION.
        ShowReport("SELECT *FROM dbo.Employee", "Employee.rpt", "Employee", panReport); 
    }

    private Boolean ShowReport(String sQuery, String rptFileName, String sTableName, 
        Panel panReport)
    {
        using (SqlConnection conn = new SqlConnection("Data Source=dna;
                Persist Security Info=False;" +
               "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;
                Connect Timeout=30;"))
        {
            System.Data.SqlClient.SqlDataAdapter objDataAdapter = 
                new System.Data.SqlClient.SqlDataAdapter (sQuery, conn);
            DataSet obj_ds = new DataSet();

            // SET TIMEOUT PERIOD, SINCE THE ENTIRE PROCESS IS ONLINE. MIGHT TAKE SOME TIME.
            objDataAdapter.SelectCommand.CommandTimeout = 500;
            objDataAdapter.Fill(obj_ds, sTableName);
                
            // LOAD THE REPORT.
            Report.Load(System.AppDomain.CurrentDomain.BaseDirectory + "reports\\" +    
                    rptFileName + "");
            Report.SetDataSource(obj_ds);       // SET REPORT DATA SOURCE.

            CrystalReportViewer.BorderStyle = BorderStyle.None;
            CrystalReportViewer.DisplayGroupTree = false;
            CrystalReportViewer.DisplayToolbar = false;     // DON'T DISPLAY TOOL BAR.
            CrystalReportViewer.Zoom(150);                  // ZOOM FACTOR 150%.
            CrystalReportViewer.BestFitPage = false;
            CrystalReportViewer.HasCrystalLogo = false;
            CrystalReportViewer.Width = 1180;
            CrystalReportViewer.Height = 700;
            CrystalReportViewer.ReportSource = Report;

            // FINALLY, ADD THE VIEWER WITH A PANEL CONTROL ON THE PAGE.
            panReport.Controls.Add(CrystalReportViewer);
            panReport.Visible = true;

            return true;
        }
    }
    ' NAVIGATORS (FIRST PAGE, NEXT PAGE, PREVIOUS PAGE AND LAST PAGE)
    ' NOTE:  TO SEE THIS CONTROLS IN ACTION, ADD MORE DATA IN THE "EMPLOYEE" TABLE.
    protected void btFirstPage_Click(object sender, EventArgs e)
    {
        LoadData();
        CrystalReportViewer.ShowFirstPage();
    }
    protected void btNextPage_Click(object sender, EventArgs e)
    {
        LoadData();
        CrystalReportViewer.ShowNextPage();
    }
    protected void btPrevPage_Click(object sender, EventArgs e)
    {
        LoadData();
        CrystalReportViewer.ShowPreviousPage();
    }
    protected void btLastPage_Click(object sender, EventArgs e)
    {
        LoadData();
        CrystalReportViewer.ShowLastPage();
    }
}
Vb.Net
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    ' DECLARE THE "REPORT" AND "REPORT VIEWER" OBJECT.
    Protected Report As CrystalDecisions.CrystalReports.Engine.ReportDocument = _
        New CrystalDecisions.CrystalReports.Engine.ReportDocument
    Protected CrystalReportViewer As CrystalDecisions.Web.CrystalReportViewer = _
        New CrystalDecisions.Web.CrystalReportViewer

    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        If Not Page.IsPostBack Then
            ' ADD THE LIST PRINTERS IN THE DROPDOWN CONTROL. (OPTIONAL)
            For Each printer As String In System.Drawing.Printing.PrinterSettings.InstalledPrinters
                ddlPrinters.Items.Add(printer)
            Next printer

            LoadData()
        End If
    End Sub

    Private Sub LoadData()
        ' CALL "ShowReport()" FUNCTION.
        ShowReport("SELECT *FROM dbo.Employee", "Employee.rpt", "Employee", panReport) 
    End Sub

    ' BIND THE REPORT WITH THE DATABASE TABLE AND SHOW IT.
    Private Function ShowReport(ByVal sQuery As String, ByVal rptFileName As String, _
        ByVal sTableName As String, ByRef panReport As Panel) As Boolean

        Try
            Using conn = New SqlConnection("Data Source=dna;Persist Security Info=False;" & _
                "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;")

                Dim objDataAdapter As New System.Data.SqlClient.SqlDataAdapter _
                    (sQuery, conn)
                Dim obj_ds As New DataSet

                ' SET TIMEOUT PERIOD, SINCE THE ENTIRE PROCESS IS ONLINE. MIGHT TAKE SOME TIME.
                objDataAdapter.SelectCommand.CommandTimeout = 500
                objDataAdapter.Fill(obj_ds, sTableName)

                ' LOAD THE REPORT.
                Report.Load(System.AppDomain.CurrentDomain.BaseDirectory() & _
                    "reports\" & rptFileName & "")
                Report.SetDataSource(obj_ds)                        ' SET REPORT DATA SOURCE.

                CrystalReportViewer.BorderStyle = BorderStyle.None
                CrystalReportViewer.DisplayGroupTree = False
                CrystalReportViewer.DisplayToolbar = False          ' DON'T DISPLAY TOOL BAR.
                CrystalReportViewer.Zoom(150)                       ' ZOOM FACTOR 150%.
                CrystalReportViewer.BestFitPage = False
                CrystalReportViewer.HasCrystalLogo = False
                CrystalReportViewer.Width = "1180"
                CrystalReportViewer.Height = "770"
                CrystalReportViewer.ReportSource = Report

                ' FINALLY, ADD THE VIEWER WITH A PANEL CONTROL ON THE PAGE.
                panReport.Controls.Add(CrystalReportViewer)
                panReport.Visible = True

                ShowReport = True
            End Using
        Catch ex As Exception
            ShowReport = False
        Finally
        End Try
    End Function

    ' NAVIGATORS (FIRST PAGE, NEXT PAGE, PREVIOUS PAGE AND LAST PAGE)
    ' NOTE:  TO SEE THIS CONTROLS IN ACTION, ADD MORE DATA IN THE "EMPLOYEE" TABLE.
    Protected Sub btFirstPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btFirstPage.Click
        LoadData()
        CrystalReportViewer.ShowFirstPage()
    End Sub

    Protected Sub btNextPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btNextPage.Click
        LoadData()
        CrystalReportViewer.ShowNextPage()
    End Sub

    Protected Sub btPrevPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btPrevPage.Click
        LoadData()
        CrystalReportViewer.ShowPreviousPage()
    End Sub

    Protected Sub btLastPage_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btLastPage.Click
        LoadData()
        CrystalReportViewer.ShowLastPage()
    End Sub
End Class

In the form1_Load event we will add the list of printers (Optional) in a DropDown control. This works fine on the “localhost” using a LAN connection. We suggest you convert crystal reports into ".pdf" files, if working on the web, since these printers will not be available.

The LoadData() procedure calls ShowReport() function which accepts 4 parameters.

01) SQL query which will fetch the data from “Employee” table.
02) The report file (“Employee.rpt”) which will be displayed.
03) The name of the Table for the DateSet in the function.
04) The “ID” of the “Panel” control, used to display the report on the web page.

That is it. You are ready to deploy the report. If you have any queries of suggestions, please leave a message below and don't forget to share this article with your friends.

Thanks for reading.

Related Posts:

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+
comments powered by Disqus

Join our Google Plus Community and be a part of a discussion!