Crystal-Reports Sub-Report in Asp.Net

 Crystal-Reports Sub-Report in Asp.Net 

  In this post Crystal Reports SubReport Report, we learn on how to create a Sub-Report using Crystal Reports in asp.net.

  A Crystal Report can hold multiple sub-reports.We can create a main report and add sub-reports to each section of the main report, this help us to designing complex reports.In this crystal report we are going display department wise employee details.Now let us create a Typed Dataset with a DataTable to hold the values which will be a report in crystal Report.

To create Dataset follow below steps:
  => Right click On Project.
  => select 'Add' Menu
  => select 'New Item' Menu
  => select 'Data' Setcion in Visual C#.
  => Now Select 'Data set' form Windows.
  => Give proper Name to Dataset E.g. 'DsEmpDetails.xsd'
  => And Last step is click On 'Add' Button.

  DsEmpDetails.xsd  

Now to create datatable in dataset follow below steps:
   => Right click inside Dataset.
   => select 'Add' popup Menu.
   => select 'Datatable' option.

   => Now empty datatable with default name 'DataTable1'. By double click on it or pressing 'F2' shortcut key we can change this name.
   => Now add column to datatable right click on it and select 'Add' option from popup menu. Under 'Add' option select 'Column' option or press 'Ctrl+L' ShortCut Key.

   => Now 'DataColumn1' is column will be created in datatable. this column name we can change.
   => Now to set the data type for this column.Right on that column and select 'Properties' option from popup option or press 'Alt+Enter' ShortCut key.
   => In Properties Windows we can set data type our column from 'DataType' dropdown.We can select System.Boolean,System.String,System.Single etc.
   => After adding we wanted column in datatable save it.

The Typed Dataset will look as follows.

Now add a new CrystalReport file to the Project follow below steps:
   => Right click On Project.
   => select 'Add' Menu
   => select 'New Item' Menu
   => select 'Reporting' Setcion in Visual C#.
   => Now Select 'Crystal Report' form Windows.
   => Give proper Name to Crystal Report E.g. 'crtEmpDetials.rpt'
   => And click On 'Add' Button.

   => Now 'Crystal Report Gallery' Windows will open. From This screen Select below option and click on 'OK' button.

   => Now 'Standard Report Creation Wizard' will be open.
   => In this screen. select Project Data Option.
   => 1sty right click on it and select Refresh option.
   => now Expand it and select 'Ado.Net Datasets' option.
   => Now exppand 'Ado.Net Datasets' and select our Dataset.
   => Now exppand 'our Dataset' and select our Datatable E.g.(dtEmpDetails).
   => Select Our Datatable E.g.(dtEmpDetails) and click on '>' Button for select datatable on crystal Report and Press 'Next' Button.

   => And at last click on 'Finish' Button.

 Open the Crystal Report and design the report as required using the fields added in the Field Expert.

 Now create to sub-crystal report follow below steps.
  => Right click On Project.
  => select 'Insert' Menu
  => select 'Sub-Report' Menu.
  => Now new Screen will be open.
  => In this screen. select 'Create a Sub-Report with the report wizard' Option.

  => Now write a sub report name in text box and click on 'Report Wizard' Button.
  => In New screen click on 'Finish' button.

  => Now Click on 'OK' button .

  How to Link Dataset with crystal Sub-Report:
   => Fisrt Goto Eield Explorer.
   => Select Database Fields
   => Right Click on it & select 'Database Expert'.
   => Now new Screen will be open.
   => In this screen. select Project Data Option.
   => 1sty right click on it and select refresh option.
   => now Expand it and select 'Ado.net Datasets' option.
   => Now exppand 'Ado.net Datasets' and select our Dataset.
   => Now exppand 'our Dataset' and select our Datatable E.g.(dtEmpDetails).
   => Select Our Datatable E.g.(dtEmpDetails) and click on '>' Button for select datatable on crystal Report.
   => And at last click on 'Ok' Button.

 Now open the sub report and design the report as required using the fields added in the Field Expert.

 After Sub-Report Design Completed close it and right click on 'Sub-Report' to To show only match record on sub report on base on using values follow below steps:
  => Right click On Sub-Report inside main report.
  => select 'Change Sub-Report Links' Menu.
  => Now New window is open. In this screen from 'Avaible fields' select those fields basic we want to display data on sub-report.
  => Check the 'Select data in subreport based on field' and select the field from drop down.
  => click on 'OK' Button.

 Now we degin here Page for Call crystal report. We have fetch data from database and Connection String define in web.config. How to read connection string string from web.config Click Here.

  Code: FrmSubReportDemo.aspx  

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FrmSubReportDemo.aspx.cs" 
Inherits="ProjCrystalReport.FrmSubReportDemo" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="
form1" runat="server">
    <div>
    <h1>Crystal Report's Sub-Report  Example</h1>
        <asp:Button ID="btnDownload" runat="server" 
Text="DownLoad Pdf" 
            onclick="btnDownload_Click" />
    </div>
    </form>
</body>
</html>

  Code: FrmSubReportDemo.aspx.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using CrystalDecisions.CrystalReports.Engine;
using System.IO;
using System.Configuration;
using System.Data.OracleClient;

namespace ProjCrystalReport
{
    public partial class FrmSubReportDemo : System.Web.UI.Page
    {
        static string strConn = ConfigurationManager.ConnectionStrings["LearnAsp"].ToString();
        private OracleConnection objConn 
= new OracleConnection(strConn);
        
        protected void btnDownload_Click(object sender, EventArgs e)
        {
            DataTable dtDept = new DataTable();
            DataTable tbEmp = new DataTable();
            DataSet DsEmp = new DataSet();
            String query = " select * from dept ";
            String query2 = " select deptno,empno,ename,job from emp";
            OracleCommand objCmd = new OracleCommand(query, objConn);
            DataTable dt = new DataTable();
            objConn.Open();
            OracleDataAdapter oda = new OracleDataAdapter(objCmd);
            oda.Fill(dtDept);

            OracleCommand objCmd2 = new OracleCommand(query2, objConn);
            OracleDataAdapter odaEmp = 
new OracleDataAdapter(objCmd2);
            odaEmp.Fill(tbEmp);
            objConn.Close();

            dtDept.TableName = "dtDept";
            tbEmp.TableName = "dtEmp";
            DsEmp.Tables.Add(dtDept.Copy());
            DsEmp.Tables.Add(tbEmp.Copy());
            GenerateEmpReport(DsEmp);
        }
        public void GenerateEmpReport(DataSet EmpDetRpt)
        {
            String ReportPath = "";
            String ExportPath = "";
            String PDFNAME = "EmpDetails" + 
DateTime.Now.ToString("ddMMyyyyhhmmss") + ".pdf";
            String creatfpdf = Server.MapPath(@"~/GarbageBin/");
            var finalPDF = System.IO.Path.Combine(creatfpdf, PDFNAME);

            ReportPath = Server.MapPath("~\\MainRptEmpInfo.rpt");
            ExportPath = Server.MapPath("~\\GarbageBin\\") + PDFNAME;

            String[] Parameter = new String[1];
            var ParameterVal = new dynamic[1];
            Parameter[0] = "printby";
            ParameterVal[0] = "abc";

            ShowReport(EmpDetRpt, ReportPath, ExportPath, Parameter, ParameterVal);
            System.Net.WebClient client = new System.Net.WebClient();
            Byte[] buffer = client.DownloadData(finalPDF);

            if (buffer != null)
            {
                Response.ContentType = "application/pdf";
                Response.AddHeader("content-length", buffer.Length.ToString());
                Response.BinaryWrite(buffer);
                Response.End();
            }           
        }

        public static void ShowReport(DataSet Ds, 
String RptPath, 
String ExportPath, String[] Parameter, dynamic ParameterValue)
        {
            ReportDocument rpt = new ReportDocument(); 
            rpt.Load(RptPath);
            rpt.SetDataSource(Ds);

            for (int i = 0; i < Parameter.Length; i++)
            {
                rpt.SetParameterValue(Parameter[i], ParameterValue[i]);
            }

            rpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.
PortableDocFormat,ExportPath);
            rpt.Close();
            rpt.Dispose();
        }
    }
}

  Output  

  That's it we have created a Sub-Report using Crystal Reports and displayed it within browser in an Asp.Net page.