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.
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.
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.
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>
<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 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 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="GET" path="CrystalImageHandler.aspx" type="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="GET" path="CrystalImageHandler.aspx" type="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="CrystalImageHandler" verb="GET" path="CrystalImageHandler.aspx" type="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 ="CrystalImageHandler" verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
</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.
No comments:
Post a Comment