Crystal Report From More Than One Table in ASP.Net

I am using the following 3 tables to create a Crystal Reports report.

  1. Customer
  2. Product
  3. Cust_Prod_Order

    Customer
                                  Image 1.

    Product
                               Image 2.

    Cust Prod Order
                                                                   Image 3.
Now look at the following.
  • Open Visual Studio then select File -> New Web Site.

    New Web Site
                                                                      Image 4.

  • The right-click on Solution Explorer then select Add New Item -> Crystal Report -> Add.

    Add New ItemCrystal Report
                                                                            Image 5.

    Crystal Report
                                                 Image 6.

  • Here Expand Create New Connection then select OLE DB(ADO) then a pop-up window will open. Select Microsoft OLE DB Provider for SQL Server then click Next.

    OLE DB Provider for SQL Server
                                                                         Image 7.

  • Now enter your SQL Server details.

    SQL server
                                                                      Image 8.

    Enter your SQL server Details
                                                                   Image 9.

  • Now select your database then select all your tables and Move.

    Select Your All Tables
                                                                            Image 10.

  • Now you can see your tables with relationships.

    your tables with relationship
                                                                       Image 11.

  • Now select the columns to show in the reports.

    show in reports
                                                                            Image 12.

  • Now you can see your report is ready. All the Columns are already in the Details sections. You can remove any column or you can add a new column by drag and drop from the Field Explorer to the report. Here I did some formatting like header text background, detail column colour and so on.

    detail column colour
                                                                               Image 13.
       
  • Now it is time to add a Report Viewer where we can show this Crystal Report. On the Default.aspx page drag and drop CrystalReportViewer from the toolbox as in the following.

    CrystalReportViewer
                                                                            Image 14.

  • My aspx code is:
    1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
    2.   
    3. <%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"  
    4.     Namespace="CrystalDecisions.Web" TagPrefix="CR" %>  
    5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
    6. <html xmlns="http://www.w3.org/1999/xhtml">  
    7. <head runat="server">  
    8.     <title>Crystal Report From More Than One Table</title>  
    9. </head>  
    10. <body>  
    11.     <form id="form1" runat="server">  
    12.     <table cellpadding="10" cellspacing="10" width="70%" height="300px" align="center"  
    13.         style="border: solid 2px gray;">  
    14.         <tr>  
    15.             <td align="center" style="background-color: SkyBlue;">  
    16.                 <span style="font-family: Times New Roman; font-size: 18pt; color: Green;">Customer  
    17.                     Product Order Detail Report</span>  
    18.             </td>  
    19.         </tr>  
    20.         <tr>  
    21.             <td align="center">  
    22.                 <asp:Panel ID="pnlReport" runat="server" Height="400px">  
    23.                     <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />  
    24.                 </asp:Panel>  
    25.             </td>  
    26.         </tr>  
    27.     </table>  
    28.     </form>  
    29. </body>  
    30. </html>  
  • Now on Page_Load event write the following code:
    1. using System;  
    2. using System.Configuration;  
    3. using System.Data;  
    4. using System.Linq;  
    5. using System.Web;  
    6. using System.Web.Security;  
    7. using System.Web.UI;  
    8. using System.Web.UI.HtmlControls;  
    9. using System.Web.UI.WebControls;  
    10. using System.Web.UI.WebControls.WebParts;  
    11. using System.Xml.Linq;  
    12. using CrystalDecisions.CrystalReports.Engine;  
    13.   
    14. public partial class _Default : System.Web.UI.Page  
    15. {  
    16.     protected void Page_Load(object sender, EventArgs e)  
    17.     {  
    18.         ReportDocument cryRpt = new ReportDocument();  
    19.         cryRpt.Load(Server.MapPath("EmployeeCrystalReport.rpt"));  
    20.         CrystalReportViewer1.ReportSource = cryRpt;           
    21.     }  
    22. }  
  • Now run your application:

    Run your Application
                                                                               Image 15.