Developing Web Report using Crystal Report in .NET 2005.

• CrystalReport_DotNET2005 - 14 Kb

Introduction

In this article I have tried to cover a cycle of developing a web report using Crystal Report in .NET 2005. I haven't put any effort for the butification of the report. The report is just showing the Employee master data from the Northwind database of SQL Server.

The report is build on XML Schema and the data and binded the report at runtime.

Requirements

1. Microsoft C#.NET 2005
2. Windows 2000/2003/XP
3. SQL Server 2000/2005(Optional). I have used SQL Server to fecth data. If in case SQL Server is not available then change the connection string in web.config and connection related syntax in Default.aspx.cs.

Creating your own Report

To start with Reports, you need start .NET 2005 Development Studio.

1. Start a new ASP .NET Web site, Choose Location as "File System" and provide a path.
2. Perform "Add New Item", choose "XML Schema".
      Add elements as the picture below.

         After saving the elements will look like this, in the XML Editor         

<BR>        <xs:element name="EmployeeID" type="xs:int" /><BR>        <xs:element name="LastName" type="xs:string" /><BR>        <xs:element name="FirstName" type="xs:string" /><BR>        <xs:element name="Title" type="xs:string" /><BR>        <xs:element name="BirthDate" type="xs:dateTime" /><BR>        <xs:element name="Address" type="xs:string" /><BR>        <xs:element name="City" type="xs:string" /><BR>        <xs:element name="Region" type="xs:string" /><BR>        <xs:element name="PostalCode" type="xs:string" /><BR>        <xs:element name="Country" type="xs:string" /><BR>        <xs:element name="HomePhone" type="xs:string" /><BR>  

   3.  Perform "Add New Item", choose "Crystal Report".

1. Give a name for the report.
2. Choose -- Using the Report Wizard. -- OK.
3. A Data window will appear, Click on "Create New Connection" and then "ADO .NET".
4. A connection windows will appear. Provide the XML Schema file that u have created just now. Finish.
5. The Schema will appear under the "ADO .NET" tag. Choose. And select using > button.
6. Click Next. Fields windows will appear. Select all using >> button.
7. Click Finish, for taking shortcut.

    4.  Open the Default.aspx in Design mode. Add CrystalReportViewer control in to it. The source will look like this.

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

    5.  Open the Default.aspx.cs file and paste the following codes.
 

using System;<BR>using System.Data;<BR>using System.Configuration;<BR>using System.Web;<BR>using System.Web.Security;<BR>using System.Web.UI;<BR>using System.Web.UI.WebControls;<BR>using System.Web.UI.WebControls.WebParts;<BR>using System.Web.UI.HtmlControls; <BR>//--SqlClient for SqlConnection and etc.<BR>using System.Data.SqlClient;<BR>//--for CrystalReports's ReportDocument.<BR>using CrystalDecisions.CrystalReports.Engine; <BR>public partial class _Default : System.Web.UI.Page <BR>{<BR>    protected void Page_Load(object sender, EventArgs e)<BR>    {<BR>        //--Sql string<BR>        String strCmd = "";<BR>        strCmd += "Select EmployeeID, LastName, FirstName, Title, BirthDate, ";<BR>        strCmd += "Address, City, Region, PostalCode, Country, HomePhone ";<BR>        strCmd += "From Employees "; <BR>        //--Opening Sql Connection<BR>        string strConn = ConfigurationManager.AppSettings["connectionstring"];<BR>        SqlConnection sqlConn = new SqlConnection(strConn);<BR>        DataSet ds = new DataSet();<BR>        SqlDataAdapter da = new SqlDataAdapter(strCmd, sqlConn); <BR>        //--this statement is very important, here the table name should <BR>        //--match with the XML Schema table name <BR>        da.Fill(ds, "Employees"); <BR>        //--Closing Sql Connection<BR>        sqlConn.Close(); <BR>        //--(Optional) I have used it to disable the properties<BR>        CrystalReportViewer1.DisplayGroupTree = false;<BR>        CrystalReportViewer1.HasCrystalLogo = false; <BR>        //--Initializing CrystalReport<BR>        ReportDocument myReportDocument;<BR>        myReportDocument = new ReportDocument();<BR>        myReportDocument.Load(Server.MapPath("Employees.rpt"));<BR>        myReportDocument.SetDataSource(ds); <BR>        //--Binding report with CrystalReportViewer<BR>        CrystalReportViewer1.ReportSource = myReportDocument;<BR>        CrystalReportViewer1.DataBind(); <BR>    }<BR>} <BR>

    6.  Open the Web.config file and change the connection string under <appSettings> as per your machine (ip address, username, password).

<BR><appSettings><BR>     <add key="connectionString" value="Data Source=9.182.223.80;Initial Catalog=Northwind;Persist Security Info=True;User ID=testUser;Password=password" /><BR>     <BR> </appSettings> <BR>   If in case the file is not available then perform "Add New Item", choose "Web Configuration File", and follow the same thing. 

    7.  Using the script

Just to check wheather Employees master table is available with data
   . Open SQL Server Query Analyzer
   . Copy the scripts
   . Paste into Query Analyzer
   . Press F5 to execute

---------------------------------<BR>-----Using Database<BR>---------------------------------<BR>Use Northwind; <BR>---------------------------------<BR>-----Selecting data from Table<BR>---------------------------------<BR>Select EmployeeID, LastName, FirstName, Title, BirthDate, <BR> Address, City, Region, PostalCode, Country, HomePhone<BR> From Employees;