How to create sub reports in crystal report?


In this article I am going to explain about the sub report concept in the Crystal report. This concept is may be used in your project to show relevant details of the user in other report. Here I am explained in simple way.

Learn how to create sub reports in crystal report?


Description


For an example I have collect employee details and store in the two different table. First table I store employee professional details and the other table I store the employee personal details. When customer take a print of particular employee detail that time we need to show both details (Professional and personal).

In that situation Sub reports is one of the option to bind data in separate report sheet show it to customer.

Table structure


 
create table emp (eno int,empname varchar(50),sal decimal(15,2),desig varchar(40))

create table empdet (eno int,add1 varchar(40),add2 varchar(40),city varchar(40),state varchar(40),country varchar(40))


Default page : Client side


In this page I am collection information about the employee like professional and personal details and store it in two different table.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>ASP.NET Cystal Report wih Subreport</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellpadding="0" align="center" width="600">
<tr>
<td height="30" colspan="2">
<b>New Employee Registration</b>
</td>
</tr>
<tr>
<td height="40" colspan="2" align="center">
<asp:Label ID="Label1" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td height="30">
Employee No
</td>
<td>
<asp:TextBox ID="txteno" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Employee Name
</td>
<td>
<asp:TextBox ID="txtempname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Salary
</td>
<td>
<asp:TextBox ID="txtsal" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Designation
</td>
<td>
<asp:TextBox ID="txtdesign" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30" colspan="2" align="left">
<b>Personal Details</b>
</td>
</tr>
<tr>
<td height="30">
Address1
</td>
<td>
<asp:TextBox ID="txtAdd1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Address2
</td>
<td>
<asp:TextBox ID="txtAdd2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
City
</td>
<td>
<asp:TextBox ID="txtcity" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
State
</td>
<td>
<asp:TextBox ID="txtstate" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30">
Country
</td>
<td>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td height="30" colspan="2" align="center">
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>


Design side look like this
images

Default page : Server side


Insert data into the two different table using below code

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
SqlCommand sqlcmd = new SqlCommand();

protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
sqlcon.Open();
sqlcmd = new SqlCommand("insert into emp(eno,empname,sal,desig) values('" + txteno.Text + "','" + txtempname.Text + "','" + txtsal.Text + "','" + txtdesign.Text + "')", sqlcon);
sqlcmd.ExecuteNonQuery();

sqlcmd = new SqlCommand("insert into empdet(eno,add1,add2,city,state,country) values('" + txteno.Text + "','" + txtAdd1.Text + "','" + txtAdd2.Text + "','" + txtcity.Text + "','" + txtstate.Text + "','" + txtCountry.Text + "')", sqlcon);
sqlcmd.ExecuteNonQuery();

Label1.Text = "Record insert successfully";
clear();
}
catch (Exception ex)
{

}
finally
{
sqlcon.Close();
}
}

void clear()
{
foreach (Control c in form1.Controls) //This loop takes all controls from the form1
{
//Clear all textbox values
if (c is TextBox)
((TextBox)c).Text = "";

//clear all check boxes
if (c is CheckBox)
((CheckBox)c).Checked = false;

//Clear all radio buttons
if (c is RadioButton)
((RadioButton)c).Checked = false;
}
}
}

Create Dataset with fields


Now right click on the project name add DataSet1.xsd into the App_Code folder and right click on the designer to add new datatable with the table fields like below
images

Create Crystal Report
Now right click on the Project name again and add new crystal report in the project name it as CrystalReport.rpt
Then right click on the Field Explorer and choose database expert choose newly created DataSet1 -> DataTable1 and click ok button to add that DataSet1 Fields in the crystal report designer.
images

Design Crystal report
Design your crystal report with the Field explorer fields and follow below steps to create sub report
1) Right click on your section choose Insert section below
images

2) Right click on details section choose -- > Insert sub report
images

3) If you design already design sub report then choose a crystal report in a project otherwise choose Create a sub report with report wizard
images

4) Now double click on the sub report to open sub report
images

5) Design sub report using field explorer and after that right click and close sub report
images

6) Desgin your primary report to using datatable1
images

That's all now create one page to display crystal report in web page

Default2 Page: Client side



<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!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>Crystal Report Grouping</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Enter Employee Number to display details<asp:TextBox ID="txeno" runat="server"></asp:TextBox>
<br /><br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" /><br /><br />
<div style="text-align:center">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</div>
</div>
</form>
</body>
</html>


Default2 Page Server side



using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.Web;
using CrystalDecisions.ReportSource;

public partial class Default2 : System.Web.UI.Page
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ToString());
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
DataTable dt1 = new DataTable();

protected void Page_Load(object sender, EventArgs e)
{
btnSubmit_Click(this,new EventArgs());
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
string query;
try
{
sqlcon.Open();
query = "select * from emp where eno='" + txeno.Text + "'";
sqlcmd = new SqlCommand(query, sqlcon);
da = new SqlDataAdapter(sqlcmd);
dt.Clear();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
//Assign data to the primary report
ReportDocument RptDoc = new ReportDocument();
RptDoc.Load(Server.MapPath("~/CrystalReport.rpt"));
RptDoc.SetDataSource(dt);

query = "select * from empdet where eno='" + txeno.Text + "'";
sqlcmd = new SqlCommand(query, sqlcon);
da = new SqlDataAdapter(sqlcmd);
dt1.Clear();
da.Fill(dt1);

//Get sub report and assign datatable1 to that report
foreach (ReportObject repOp in RptDoc.ReportDefinition.ReportObjects)
{
if (repOp.Kind == ReportObjectKind.SubreportObject)
{
string SubRepName = ((SubreportObject)repOp).SubreportName;
ReportDocument subRepDoc = RptDoc.Subreports[SubRepName];
subRepDoc.SetDataSource(dt1);
}
}
//Assign report to the crystal report viewer
CrystalReportViewer1.ReportSource = RptDoc;
CrystalReportViewer1.DataBind();
}
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
sqlcon.Close();
}

}
}

Output
The output of the above code snippet is look like this
images

Sub report display in next page.
images

Source Code Detail:
Here with I have attached source code Crystal report with sub report example download it and try it.
Front End : ASP.NET
Code behind : C#

Conclusion:
I hope this article help to know Sub Report concept in crystal report.


Attachments

  • Source_code (43691-251116-CrystalRptwithSubReport.rar)
  • Comments

    Guest Author: Asha Bhatt30 Mar 2012

    Hi,

    I am getting error after attached your folder in VS and build the project at my locale machine. please see the below error.

    Error 70 D:\AshaFrom13Dec2010\28 March 2012\CrystalRptwithSubReport\Default.aspx: ASP.NET runtime error: Could not load file or assembly 'CrystalDecisions.CrystalReports.Engine, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304' or one of its dependencies. The system cannot find the file specified. (D:\AshaFrom13Dec2010\28 March 2012\CrystalRptwithSubReport\web.config line 45) D:\AshaFrom13Dec2010\28 March 2012\CrystalRptwithSubReport\Default.aspx 1 D:\...\CrystalRptwithSubReport\ 1

    Guest Author: Asha Bhatt30 Mar 2012

    Error 70 D:\AshaFrom13Dec2010\28 March 2012\CrystalRptwithSubReport\Default.aspx: ASP.NET runtime error: Could not load file or assembly 'CrystalDecisions.CrystalReports.Engine, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304' or one of its dependencies. The system cannot find the file specified. (D:\AshaFrom13Dec2010\28 March 2012\CrystalRptwithSubReport\web.config line 45) D:\AshaFrom13Dec2010\28 March 2012\CrystalRptwithSubReport\Default.aspx 1 D:\...\CrystalRptwithSubReport\ 1

    Author: Ravindran30 Mar 2012 Member Level: Diamond   Points : 0

    Asha make sure you have installed crystal report specified version

    Guest Author: arul31 May 2012

    System.NullReferenceException: Object reference not set to an instance of an object. at Crystal_Report.btnSubmit_Click(Object sender, EventArgs e) in d:\crystal Report\Crystal Report.aspx.cs:line 36System.NullReferenceException: Object reference not set to an instance of an object. at Crystal_Report.btnSubmit_Click(Object sender, EventArgs e) in d:\crystal Report\Crystal Report.aspx.cs:line 36

    Author: arul R31 May 2012 Member Level: Silver   Points : 2

    System.NullReferenceException: Object reference not set to an instance of an object. at Crystal_Report.btnSubmit_Click(Object sender, EventArgs e) in d:\crystal Report\Crystal Report.aspx.cs:line 36System.NullReferenceException: Object reference not set to an instance of an object. at Crystal_Report.btnSubmit_Click(Object sender, EventArgs e) in d:\crystal Report\Crystal Report.aspx.cs:line 36


    This is my error

    Author: arul R01 Jun 2012 Member Level: Silver   Points : 0

    Hi ravi,,

    This is also my Error

    Author: arul R01 Jun 2012 Member Level: Silver   Points : 3

    CrystalDecisions.CrystalReports.Engine.DataSourceException: The report has no tables. at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSourceInternal(Object val, Type type) at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSource(DataTable dataTable) at Crystal_Report.btnSubmit_Click(Object sender, EventArgs e) in d:\crystal Report\Crystal Report.aspx.cs:line 64CrystalDecisions.CrystalReports.Engine.DataSourceException: The report has no tables. at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSourceInternal(Object val, Type type) at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSource(DataTable dataTable) at Crystal_Report.btnSubmit_Click(Object sender, EventArgs e) in d:\crystal Report\Crystal Report.aspx.cs:line 64

    Guest Author: BigHeart18 Jul 2012

    this work fine but i am getting these error lines when evr i load the page,,
    System.Data.SqlClient.SqlException (0x80131904): Error converting data type varchar to numeric. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at WebApplication1.cry.Button3_Click(Object sender, EventArgs e) in C:\Users\Administrator\Documents\Visual Studio 2010\Projects\WebApplication1\WebApplication1\cry.aspx.cs:line 72

    Author: Ravindran19 Jul 2012 Member Level: Diamond   Points : 0

    BigHeart,

    Make sure you bind interger value to integer data type vice versa. If you bind varchar data to numeric then you got this kind of error.

    Guest Author: Abhishek Kumar28 Nov 2012

    This is Abhishek Kumar and I have got a problem in crystal report that I have to print No. Of Label Record in crystal report per page through command prompte that I have select No. Of Record in Selection Box DialogBox and then the Next DiallgBox Come to ask for No. of record for per page printing. then how to do this please tell me any body to me

    Thank You.

    Guest Author: anil11 Mar 2013

    I am using two sub-report in main report and bind sub-report dynamically but only one sub report is showing data IE second sub-report is not showing data

    Guest Author: Nilesh Vaghela10 Apr 2013

    Data not being displayed of sub report in main report
    please help

    Guest Author: balu21 May 2013

    I don't know the levels in sub reports,can u please help.Is there any levels in sub reports

    Author: Ajay Shedge19 Jul 2014 Member Level: Bronze   Points : 3

    Hello sir, can I insert another sub report in main report.
    In above example you have create Employee Personal Details in Employee Details such that I want to insert another sub report Employee Attendance in Employee Details and where will be my sub report code in .cs page?. after that my output will be
    Employee Details
    Employee Personal Details
    Employee Attendance
    Please Help me



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: