Think Simple: October 2013

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

d. 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" %>

<!DOCTYPE html>

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

</head>

<body>

<div>

<table>

<tr>

<td>

<asp:Button ID="btnViewReport" Text="View Report" runat="server" OnClick="btnViewReport_Click" /></td>

</tr>

</table>

<br />

</div>

</form>

</body>

</html>

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;
    }

}

Now your report is ready to view in asp.net. Just click on the “View Report” button, you will see the report.

Think Simple

Pages

15 October, 2013

Crystal Report in ASP.net Step by Step