Display Line Chart in Crystal Report in ASPNet using C and VBNet

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.

Download and install Northwind Database

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.

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.

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.

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.

As soon as you click OK you get the following dialog. In this dialog, you need to select the Using the Report Wizard option.

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.

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.

Note: There 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.

The above action opens the Chart Expert dialog. The very first Tab is Type, where you can choose the type of Chart.

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.

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.

The next one is the Text Tab where the texts of Title, Subtitle, etc. can be changed.

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.

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>

</head>

<body>

<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"/>

</form>

</body>

</html>

Namespaces

You will need to import the following namespaces.

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.

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 EventArgs) Handles 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 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.

And after adding your Web.Config should look like this.

<system.web>

</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.

And after adding your Web.Config should look like this.

<system.webServer>

</handlers>

</system.webServer>

Screenshot

Error

In case the Crystal Report is not displaying and also you could get a JavaScript error.

For solution please refer my article, Crystal Reports Bobj is undefined and bobj.isObject is not a function in Visual Studio.

Demo

View Demo

Downloads

Download Code

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

Related

Ask Question

Company

Explore

Follow us

Contact Us