15 October, 2013

Crystal Report in 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.


    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.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>



<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

    <link href="/aspnet_client/System_Web/4_0_30319/CrystalReportWebFormViewer3/css/default.css"

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

 </head>

<body>

    <form id="form1" runat="server">

        <div>

            <table>


                <tr>

                    <td>

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

                </tr>


            </table>

            <br />

            <div style="height: 500px; width: 500px;">

                <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True" HasPrintButton="True" HasRefreshButton="True" ReuseParameterValuesOnRefresh="True" Height="50px" Width="350px" OnReportRefresh="CrystalReportViewer1_ReportRefresh" PrintMode="ActiveX" />


            </div>


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


13 comments:

  1. If you have any suggestion, any confusion or any thing find difficult to implement. Please leave your comment here.

    ReplyDelete
  2. Hi,
    Thanks for this post, It helps me a lot...
    I have a question: can you post the files of these examples so I can implement them as Example instead of typing everything from scratch ???

    My Email address is: sbitar@aacs.ca

    Thanks a lot....

    Sam Bitar

    ReplyDelete
  3. Thanks Bro..thats helpfull...

    ReplyDelete
  4. Thanks, this article help me a lot. I have a question:
    How I can change "ApplyLogon" for Windows Authentication?

    ReplyDelete
  5. I am attempting to recreate this in VS 2013, targeting the 4.0 framework. when opening the viewreport.aspx page, I receive this error:

    0x800a139e - JavaScript runtime error: Syntax error, unrecognized expression: input#__CRYSTALSTATEctl00$MainContent$CrystalReportViewer1

    Using jquery-1.10.2.js and jquery-ui-1.10.3.js

    Any idea as to a resolution?

    ReplyDelete
  6. Hi, You have explained it nicely.
    I had already developed a Crystal Report 13.0 version in Dot Net framework 4.0 configuration & its working fine on my local machine. Problem is I am not able to see the report when I publish it. Steps I have taken:
    1. Publish the site.
    2. In IIS give virtual path & convert to application.
    3, When I click report; its giving blank page. Report is not getting displayed.
    4. I think In bin folder in IIS; report dll's should be there but they are not there.
    What am I missing? Kindly help. Its urgent. I am struggling with this for last 1 week but no luck.
    Thaks,
    Amita

    ReplyDelete
  7. thanks for this post .
    i encounter few problem . my report is consist of a command and a table with 2 parameter (date from and date to)
    1. i encounter error on this line
    tbl.Location = ci.DatabaseName + ".dbo." + tbl.Name;
    2. if didn't put the parameter in the code , it prompt me database login in which the db in the prompt is the db that I use to develop the report using crystal report , not the db I run the test
    3. if i put the parameter in the code , it doesn't prompt me login anymore but when i try export the report , it prompt me again and the db in the prompt is the db i use to develop the report.
    4. it prompt me parameter when i click next page
    5. the report size is A4 landscape , on the preview , report is landscape but the right part abit cut off

    ReplyDelete
  8. Hi,
    It works fine. But anyone knows something about licences when this is used in a web apllication that wil be distributed ?

    ReplyDelete
  9. Thank you. An inherited app (VB.NET) kept crashing on me. After looking at your Logon function, I commented out a couple of attribute additions in the ConnectionInfo object, and to my surprise and relief the report started to run rather than crash.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. thanks for this post .

    I encounter few problem . my report is uses a stored procedure.

    1. i encounter error on this line
    tbl.Location = ci.DatabaseName + ".dbo." + tbl.Name;
    2. if didn't put the parameter in the code , it prompt me database login in which the db in the prompt is the db that I use to develop the report using crystal report , not the db I run the test
    3. if i put the parameter in the code , it doesn't prompt me login anymore but when i try export the report , it prompt me again and the db in the prompt is the db i use to develop the report.
    4. it prompt me parameter when i click next page


    Hope you will help me on this.

    ReplyDelete
  12. Crystal Reports



    Our network of IT professionals bring together the expertise to address IT related issues for SMEs. If we cannot provide you with help with your problem, we will do our utmost to point you in the right direction.
    We employ a consultative approach to ensure that all your requirements are met in a timley mannor and within budget. Website: www.thesmartoffice.ie

    ReplyDelete
  13. Nice Post .......................there is a similar one i found

    http://geeksprogrammings.blogspot.com/2014/08/how-to-use-crystal-reports-in-aspnet.html

    ReplyDelete