Saturday 3 August 2019

Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net

In this article I will explain a short step by step tutorial with example on how to add charts to the ASP.Net Crystal Report Viewer control.
The Crystal Report will display a Line Chart populated from SQL Server database in ASP.Net using C# and VB.Net.
Typed DataSet will be used for populating the Chart data from SQL Server database in the Crystal Report.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
 
 
1. Add Typed DataSet to the ASP.Net Website
The Crystal Report will make use of disconnected architecture i.e. there will be no direct open connection with database and hence a Typed DataSet needs to be created which will be used to hold the data for the Crystal Report.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
 
2. Adding DataTable to the Typed DataSet
The next step would be to add a DataTable to the Typed DataSet.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
 
3. Adding Columns or fields to DataTable
In the DataTable, you will need to specify the column names which needs to be displayed in the Crystal Report.
Note: The Column Names of the DataTable must exactly match with the actual column or alias names in the query.
 
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
By default all the columns are of String Data Type but you can also change the data type as per your need using the Properties window.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
 
4. Add Crystal Report to the ASP.Net Website
Now you will need to add a Crystal Report to the ASP.Net Application. You can give it name as per your choice.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
As soon as you click OK you get the following dialog. In this dialog, you need to select the Using the Report Wizard option.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
4. Once you press OK in the above dialog, the Report Wizard starts and you get the following dialog where you need to choose the type of Database connection for your Crystal Report. Since here we are using Typed DataSet, you will need to choose the CustomDataSet.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
Next the Wizard will ask for the Columns or Fields from the CustomDataSet you need to display on the Crystal Reports. You can choose either all or specific fields as per you choice.
For displaying Chart, both the columns will be needed and hence both are selected.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
NoteThere are more steps in the Wizards but those are Optional hence are not included in this article.
 
Once you click Finish button, your Crystal Report will be generated. You will need to right click on the Crystal Report and select Insert and then Chart option from the Context Menu.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
The above action opens the Chart Expert dialog. The very first Tab is Type, where you can choose the type of Chart.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
The next is the Data Tab where you will need to decide which Column will be used for display and which Column will be used for calculations.
Hence the ShipCity column is added to the On change of box and the OrderId is added to the Show values box.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
You will notice that an aggregate SUM function is automatically applied to the OrderId.
But since we don’t want to SUM OrderId, You will have to modify it to make use of COUNT aggregate function by clicking on the Set Summary Operation button.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
The next one is the Text Tab where the texts of Title, Subtitle, etc. can be changed.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
Once the desired Text is set, you will need to click the OK button and you will notice the Line Chart ready on your Crystal Report.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
 
HTML Markup
The HTML Markup consists of a Crystal Report Viewer control.
<%@Page Language="C#" AutoEventWireup="true" CodeBehind="Report.aspx.cs" Inherits="Report" %>
 
<%@Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"/>
    </form>
</body>
</html>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
 
VB.Net
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
 
 
Populating the Crystal Report
Inside the Page Load event, the records from the database are populated into the CustomDataSet which then is used to populate the Crystal Report.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        ReportDocument crystalReport = new ReportDocument();
        crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
        CustomDataSet dsOrders = GetData();
        crystalReport.SetDataSource(dsOrders);
        CrystalReportViewer1.ReportSource = crystalReport;
    }
}
 
private CustomDataSet GetData()
{
    string query = "SELECT ShipCity, OrderId FROM Orders WHERE ShipCountry = 'France'";
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
 
            sda.SelectCommand = cmd;
            using (CustomDataSet dsOrders = new CustomDataSet())
            {
                sda.Fill(dsOrders, "DataTable1");
                return dsOrders;
            }
        }
    }
}
 
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgsHandles Me.Load
    If Not Me.IsPostBack Then
        Dim crystalReport As New ReportDocument()
        crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"))
        Dim dsOrders As CustomDataSet = GetData()
        crystalReport.SetDataSource(dsOrders)
        CrystalReportViewer1.ReportSource = crystalReport
    End If
End Sub
 
Private Function GetData() As CustomDataSet
    Dim query As String = "SELECT ShipCity, OrderId FROM Orders WHERE ShipCountry = 'France'"
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As New SqlCommand(query)
    Using con As New SqlConnection(conString)
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
 
            sda.SelectCommand = cmd
            Using dsOrders As New CustomDataSet()
                sda.Fill(dsOrders, "DataTable1")
                Return dsOrders
            End Using
        End Using
    End Using
End Function
 
 
Registering the Crystal Report Image Handler in Web.Config
Following are the two configurations depending on the Application Pool being used for your application.
IIS Classic Application Pool
You will need to add the following handler to <httpHandlers> in <system.web> section of your Web.Config.
 
<add verb="GETpath="CrystalImageHandler.aspxtype="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
 
And after adding your Web.Config should look like this.
<system.web>
 <httpHandlers>
   <add verb="GETpath="CrystalImageHandler.aspxtype="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
 </httpHandlers>
</system.web>
 
IIS Integrated Application Pool
You will need to add the following handler to <handlers> in <system.webServer> section of your Web.Config.
 
<add name="CrystalImageHandlerverb="GETpath="CrystalImageHandler.aspxtype="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
 
And after adding your Web.Config should look like this.
<system.webServer>
 <handlers>
   <add name ="CrystalImageHandlerverb="GETpath="CrystalImageHandler.aspxtype="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
 </handlers>
</system.webServer>
 
 
Screenshot
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 
 
Error
In case the Crystal Report is not displaying and also you could get a JavaScript error.
Display Line Chart in Crystal Report in ASP.Net using C# and VB.Net
 

No comments:

Post a Comment