Crystal Report with ASP.net Step by Step

For business or other managerial purpose, it is very important to prepare reports. We use SAP crystal reports in asp.net often for reporting purpose. Before going to the details, I hope you already know how to prepare a crystal report (something.rpt file). Here I used SAP crystal report; data is retrieved by a stored procedure which required two parameters. I used SAP Crystal Report 13.0.5 with visual studio 2012. A stored procedure is responsible to provide data to crystal report. So, let’s implement crystal report in asp.net.

Step 1: Create a report file

Create a report file (rpt_get_employee_info_dept_id_team_id.rpt). Write a stored procedure (rsp_get_employee_info_by_dept_id_team_id) to retrieve data. Set the stored procedure as crystal report data source. Design crystal reports.

Step 2: Add Assemblies

Add the following references to your web site. Version of the assembly is most important. I used assembly version: 13.0.2000.0

CrystalDecisions.CrystalReports.Engine;

CrystalDecisions.Shared;

CrystalDecisions.Web

Step 3: Create a report base class

This is a custom class, responsible to generate crystal report document. Some global variable is used in this class for configuration purpose. Server name, database name, user id, password is hard coded here for easily understand but I recommend, it should come from web configuration file.

string ServerName = "MAHEDEE-PC"; //Database server name to which report connected
string DataBaseName = "TestDB"; //Database name to which report connected
string UserID = "sa"; //Database user name to which report connected
string Password = "sa"; //Database user password to which report connected
ReportDocument crReportDocument = new ReportDocument(); //Crystal report document object

In this class some methods are used for different purposes.

  •  public ReportDocument PFSubReportConnectionMainParameter(string ReportName, ArrayList ParamArraylist, string ReportFolder)  – This is the base method to generate report document
  • public void PassParameter(int ParameterIndex, string ParameterValue) – Set parameter value in crystal report on corresponding index
  • private bool Logon(CrystalDecisions.CrystalReports.Engine.ReportDocument cr, string server, string database, string user_id, string password) – Cystal report login check to the server
  • private bool ApplyLogon(CrystalDecisions.CrystalReports.Engine.ReportDocument cr, CrystalDecisions.Shared.ConnectionInfo ci) – Supporting method of “Logon” to login check.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using CrystalDecisions.CrystalReports.Engine;
using System.Collections;
using CrystalDecisions.Shared;
///
/// The base class to generate report document
///

public class ReportBase
{

string ServerName = "MAHEDEE-PC"; //Database server name to which report connected
string DataBaseName = "TestDB"; //Database name to which report connected
string UserID = "sa"; //Database user name to which report connected
string Password = "sa"; //Database user password to which report connected

ReportDocument crReportDocument = new ReportDocument();

public ReportBase()
{
}
///
///Base method to generate report document.
///

public ReportDocument PFSubReportConnectionMainParameter(string ReportName, ArrayList ParamArraylist, string ReportFolder)
{
int ObjArrayA, ObjArrayB, Paraindex;
string Paravalue;
ObjArrayB = 0;

string path = ReportFolder + @"\" + @ReportName; //Full path of report

try
{
crReportDocument.Load(path); //Load crystal Report
}
catch (Exception ex)
{

string msg = "The report file " + path +
" can not be loaded, ensure that the report exists or the path is correct." +
"\nException:\n" + ex.Message +
"\nSource:" + ex.Source +
"\nStacktrace:" + ex.StackTrace;
throw new Exception(msg);
}

//Ensure login to the database server
if (!Logon(crReportDocument, ServerName, DataBaseName, UserID, Password))
{
string msg = "Can not login to Report Server " +
"\nDatabase Server: " + ServerName +
"\nDatabase:\n" + DataBaseName +
"\nDBUser:" + UserID +
"\nDBPassword:" + Password;
throw new Exception(msg);
}

Logon(crReportDocument, ServerName, DataBaseName, UserID, Password);

//To Check Parameter Feild Array have the Same Amount of Parameter Feild in the Report
int ParamArrayCount, ParameterFieldCount;
//Getting Value from the Array

if (ParamArraylist.Count != 0)
{
ParamArrayCount = (ParamArraylist.Count / 2);

//Getting Value From the Report (Parameter and Formula Feild)
ParameterFieldCount = crReportDocument.DataDefinition.ParameterFields.Count;

//Parameter on The Report and Array List Parameter Amount is not the same
ParamArrayCount = ParameterFieldCount;

for (ObjArrayA = 0; ObjArrayA < ((ParamArraylist.Count / 2)); ObjArrayA++)
{
Paraindex = (int)ParamArraylist[ObjArrayB]; //Parameter index
Paravalue = (string)ParamArraylist[ObjArrayB + 1]; //Paramter Value
PassParameter(Paraindex, Paravalue);
ObjArrayB = ObjArrayB + 2;
}
}

return crReportDocument;

}

///
/// Set parameter value in crystal report on corresponding index
///

public void PassParameter(int ParameterIndex, string ParameterValue)
{
// '
// ' Declare the parameter related objects.
// '
ParameterDiscreteValue crParameterDiscreteValue;
ParameterFieldDefinitions crParameterFieldDefinitions;
ParameterFieldDefinition crParameterFieldLocation;
ParameterValues crParameterValues;

crParameterFieldDefinitions = crReportDocument.DataDefinition.ParameterFields;
crParameterFieldLocation = (ParameterFieldDefinition)crParameterFieldDefinitions[ParameterIndex];
crParameterValues = crParameterFieldLocation.CurrentValues;
crParameterDiscreteValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
crParameterDiscreteValue.Value = System.Convert.ToString(ParameterValue);
crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldLocation.ApplyCurrentValues(crParameterValues);
}

//Check whether crytal report can login to the server
private bool Logon(CrystalDecisions.CrystalReports.Engine.ReportDocument cr, string server, string database, string user_id, string password)
{
// Declare and instantiate a new connection info object.
CrystalDecisions.Shared.ConnectionInfo ci;
ci = new CrystalDecisions.Shared.ConnectionInfo();

ci.ServerName = server;
ci.DatabaseName = database;
ci.UserID = user_id;
ci.Password = password;//password;
// ci.IntegratedSecurity = false;

// If the ApplyLogon function fails then return a false for this function.
// We are applying logon information to the main report at this stage.
if (!ApplyLogon(cr, ci))
{
return false;
}

// Declare a subreport object.
CrystalDecisions.CrystalReports.Engine.SubreportObject subobj;

// Loop through all the report objects and locate subreports.
// If a subreport is found then apply logon information to
// the subreport.
foreach (CrystalDecisions.CrystalReports.Engine.ReportObject obj in cr.ReportDefinition.ReportObjects)
{
if (obj.Kind == CrystalDecisions.Shared.ReportObjectKind.SubreportObject)
{
subobj = (CrystalDecisions.CrystalReports.Engine.SubreportObject)obj;
if (!ApplyLogon(cr.OpenSubreport(subobj.SubreportName), ci))
{
return false;
}
}
}

// Return True if the code runs to this stage.
return true;

}

///
///This function is called by the "Logon" function. It loops through the report tables and applies the connection information to each table.
///

private bool ApplyLogon(CrystalDecisions.CrystalReports.Engine.ReportDocument cr, CrystalDecisions.Shared.ConnectionInfo ci)
{
// This function is called by the "Logon" function
// It loops through the report tables and applies
// the connection information to each table.

// Declare the TableLogOnInfo object and a table object for use later.
CrystalDecisions.Shared.TableLogOnInfo li;
// For each table apply connection info.

foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in cr.Database.Tables)
{

li = tbl.LogOnInfo;
li.ConnectionInfo.ServerName = ci.ServerName;
li.ConnectionInfo.DatabaseName = ci.DatabaseName;
li.ConnectionInfo.UserID = ci.UserID;
li.ConnectionInfo.Password = ci.Password;
tbl.ApplyLogOnInfo(li);
tbl.Location = ci.DatabaseName + ".dbo." + tbl.Name;

// Verify that the logon was successful.
// If TestConnectivity returns false, correct table locations.
if (!tbl.TestConnectivity())
{
return false;

}
}
return true;
}

}

Step 4: Create Report Viewer pages

In this project, I have created a report viewer page – ViewReport.aspx. Here some steps need to follow.

a. Register Assembly – Register assembly CrystalDecisions.Web to the page directive

<%@ Register Assembly=”CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304″ Namespace=”CrystalDecisions.Web” TagPrefix=”CR” %>
b. Add crystal report viewer – Add crystal report viewer to view report.

<CR:CrystalReportViewer ID=”CrystalReportViewer1″ runat=”server” AutoDataBind=”True” 

HasPrintButton=”True”HasRefreshButton=”True” ReuseParameterValuesOnRefresh=”True” 

Height=”50px” Width=”350px”OnReportRefresh=”CrystalReportViewer1_ReportRefresh” PrintMode=”ActiveX” />

c. Manually config CSS file – Sometimes crystal report shows blank page due to some technical problem so add the following css link in between head tag

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

A button (btnViewReport) is used to display report

Source file of ViewReport.aspx

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”ViewReport.aspx.cs” Inherits=”ViewReport” %>

<%@ Register Assembly=”CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304″

Namespace=”CrystalDecisions.Web” TagPrefix=”CR” %>

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

rel=”stylesheet” type=”text/css” />

HasRefreshButton=”True”ReuseParameterValuesOnRefresh=”True” Height=”50px” Width=”350px”

OnReportRefresh=”CrystalReportViewer1_ReportRefresh” PrintMode=”ActiveX” />

Code behind of ViewReport.aspx

Some methods are used in code behind file for different purpose.

  • protected void btnViewReport_Click(object sender, EventArgs e) – Click event handler of btnViewReport button. In this method two parameters of crystal report are used as hardcoded. In real application, it should come as input control dynamically.
  • private void GetReportDocument() – Responsible to generate report document using ReportBase class.
  •  private void ViewCystalReport() – Set generated report document as Crystal Report viewer report source and display report in report viewer.
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ViewReport : System.Web.UI.Page
{

ArrayList ParameterArrayList = new ArrayList(); //Report parameter list
ReportDocument ObjReportClientDocument = new ReportDocument(); //Report document

protected void Page_Load(object sender, EventArgs e)
{

}

protected void btnViewReport_Click(object sender, EventArgs e)
{
/*The report with two parameters. */
ParameterArrayList.Add(0);
ParameterArrayList.Add("1"); //Parameter 1 with input 1. This is actually dept id according to report parameter
ParameterArrayList.Add(1);
ParameterArrayList.Add("1"); //Parameter 2 with input 1. This is actually team id according to report parameter

GetReportDocument(); //Generate Report document
ViewCystalReport(); //View report document in crystal report viewer

}

/*Generate report document*/
private void GetReportDocument()
{
ReportBase objReportBase = new ReportBase();
string sRptFolder = string.Empty;
string sRptName = string.Empty;

sRptName = "rpt_get_employee_info_dept_id_team_id.rpt"; //Report name
sRptFolder = Server.MapPath("~/Reports"); //Report folder name

ObjReportClientDocument = objReportBase.PFSubReportConnectionMainParameter(sRptName, ParameterArrayList, sRptFolder);

//This section is for manipulating font and font size. This an optional section
foreach (Section oSection in ObjReportClientDocument.ReportDefinition.Sections)
{
foreach (ReportObject obj in oSection.ReportObjects)
{
FieldObject field;
field = ObjReportClientDocument.ReportDefinition.ReportObjects[obj.Name] as FieldObject;

if (field != null)
{
Font oFon1 = new Font("Arial Narrow", field.Font.Size - 1F);
Font oFon2 = new Font("Arial", field.Font.Size - 1F);

if (oFon1 != null)
{
field.ApplyFont(oFon1);
}
else if (oFon2 != null)
{
field.ApplyFont(oFon2);
}
}
}
}
}

protected void CrystalReportViewer1_ReportRefresh(object source, ViewerEventArgs e)
{
//OnInit(e);
//ViewCystalReport();
}

///
/// To view crystal report
///

private void ViewCystalReport()
{

//Set generated report document as Crystal Report viewer report source
CrystalReportViewer1.ReportSource = ObjReportClientDocument;
}

}

Cr-empdept-team-wise