สร้าง Sub Report (Subreport) บน Crystal Report แบบ Step by Step (VB.NET /C#)

USE [mydatabase]
GO
/****** Object:  Table [dbo].[customer]    Script Date: 03/13/2012 13:42:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[customer](
	[CustomerID] [varchar](4) NOT NULL,
	[Name] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[CountryCode] [varchar](2) NULL,
	[Budget] [float] NULL,
	[Used] [float] NULL,
 CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED 
(
	[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO customer VALUES ('C001', 'Win Weerachai', '[email protected]', 'TH', 1000000, 600000);
INSERT INTO customer VALUES ('C002', 'John  Smith', '[email protected]', 'EN', 2000000, 800000);
INSERT INTO customer VALUES ('C003', 'Jame Born', '[email protected]', 'US', 3000000, 600000);
INSERT INTO customer VALUES ('C004', 'Chalee Angel', '[email protected]', 'US', 4000000, 100000);

GO
SET ANSI_PADDING OFF

USE [mydatabase]
GO
/****** Object:  Table [dbo].[audit]    Script Date: 03/13/2012 13:45:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[audit](
	[AuditID] [varchar](4) NOT NULL,
	[CustomerID] [varchar](4) NULL,
	[Date] [datetime] NULL,
	[Used] [float] NULL,
 CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED 
(
	[AuditID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO audit VALUES (1, 'C001', '2008-08-01', 100000);
INSERT INTO audit VALUES (2, 'C001', '2008-08-05', 200000);
INSERT INTO audit VALUES (3, 'C001', '2008-08-10', 300000);
INSERT INTO audit VALUES (4, 'C002', '2008-08-02', 400000);
INSERT INTO audit VALUES (5, 'C002', '2008-08-07', 100000);
INSERT INTO audit VALUES (6, 'C002', '2008-08-15', 300000);
INSERT INTO audit VALUES (7, 'C003', '2008-08-20', 400000);
INSERT INTO audit VALUES (8, 'C003', '2008-08-25', 200000);
INSERT INTO audit VALUES (9, 'C004', '2008-07-04', 100000);
INSERT INTO audit VALUES (10, 'C005', '2008-07-04', 200000);

GO
SET ANSI_PADDING OFF

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim objConn As New SqlConnection
        Dim objCmd As New SqlCommand
        Dim dtAdapter As New SqlDataAdapter

        Dim ds As New DataSet
        Dim strConnString, strSQL As String
        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"


        '*** for Main Report (Start) ***'
        Dim dtMain As DataTable
        strSQL = "SELECT * FROM customer ORDER BY CustomerID ASC "
        objConn.ConnectionString = strConnString
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd

        dtAdapter.Fill(ds)
        dtMain = ds.Tables(0)
        '*** for Main Report (End) ***'



        '*** for Audit Sub Report (Start) ***'
        Dim dtAudit As DataTable
        strSQL = "SELECT * FROM audit ORDER BY AuditID ASC "
        objConn.ConnectionString = strConnString
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd

        dtAdapter.Fill(ds)
        dtAudit = ds.Tables(0)
        '*** for Audit Sub Report (End) ***'


        '*** for Logo Sub Report (Start) ***'
        Dim dtLogo As New DataTable("logo")
        Dim dr As DataRow
        dtLogo.Columns.Add(New DataColumn("Logo", GetType(System.Byte())))
        dtLogo.Columns.Add(New DataColumn("Company", GetType(String)))
        Dim fiStream As New FileStream(Server.MapPath("Images/thaicreate.jpg"), FileMode.Open)
        Dim binReader As New BinaryReader(fiStream)
        Dim pic() As Byte = {}
        pic = binReader.ReadBytes(fiStream.Length)

        dr = dtLogo.NewRow
        dr("Logo") = pic
        dr("Company") = "ThaiCreate.Com Co,. LTD"
        dtLogo.Rows.Add(dr)

        fiStream.Close()
        binReader.Close()
        '*** for Logo Sub Report (End) ***'

       
        Dim rpt As New ReportDocument()
        rpt.Load(Server.MapPath("myCrystalReport.rpt"))

        '** for Main Report **'
        rpt.SetDataSource(dtMain)

        '** for Logo Sub Report **'
        rpt.Subreports("Logo_Sub_Report").Database.Tables(0).SetDataSource(dtLogo)

        '** for Audit Sub Report **'
        rpt.Subreports("Audit_Sub_Report").Database.Tables(0).SetDataSource(dtAudit)

        Me.CrystalReportViewer1.ReportSource = rpt


        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing

    End Sub

End Class

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 System.Data.SqlClient;
using System.IO;
using CrystalDecisions.CrystalReports.Engine;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection objConn = new SqlConnection();
        SqlCommand objCmd = new SqlCommand();
        SqlDataAdapter dtAdapter = new SqlDataAdapter();

        DataSet ds = new DataSet();
        string strConnString = null;
        string strSQL = null;
        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;";


        //*** for Main Report (Start) ***'
        DataTable dtMain = null;
        strSQL = "SELECT * FROM customer ORDER BY CustomerID ASC ";
        objConn.ConnectionString = strConnString;
        var _with1 = objCmd;
        _with1.Connection = objConn;
        _with1.CommandText = strSQL;
        _with1.CommandType = CommandType.Text;
        dtAdapter.SelectCommand = objCmd;

        dtAdapter.Fill(ds);
        dtMain = ds.Tables[0];
        //*** for Main Report (End) ***'



        //*** for Audit Sub Report (Start) ***'
        DataTable dtAudit = null;
        strSQL = "SELECT * FROM audit ORDER BY AuditID ASC ";
        objConn.ConnectionString = strConnString;
        var _with2 = objCmd;
        _with2.Connection = objConn;
        _with2.CommandText = strSQL;
        _with2.CommandType = CommandType.Text;
        dtAdapter.SelectCommand = objCmd;

        dtAdapter.Fill(ds);
        dtAudit = ds.Tables[0];
        //*** for Audit Sub Report (End) ***'


        //*** for Logo Sub Report (Start) ***'
        DataTable dtLogo = new DataTable("logo");
        DataRow dr = null;
        dtLogo.Columns.Add(new DataColumn("Logo", typeof(System.Byte[])));
        dtLogo.Columns.Add(new DataColumn("Company", typeof(string)));
        FileStream fiStream = new FileStream(Server.MapPath("Images/thaicreate.jpg"), FileMode.Open);
        BinaryReader binReader = new BinaryReader(fiStream);
        byte[] pic = {};
        pic = binReader.ReadBytes((int)fiStream.Length);

        dr = dtLogo.NewRow();
        dr["Logo"] = pic;
        dr["Company"] = "ThaiCreate.Com Co,. LTD";
        dtLogo.Rows.Add(dr);

        fiStream.Close();
        binReader.Close();
        //*** for Logo Sub Report (End) ***'


        ReportDocument rpt = new ReportDocument();
        rpt.Load(Server.MapPath("myCrystalReport.rpt"));

        //** for Main Report **'
        rpt.SetDataSource(dtMain);

        //** for Logo Sub Report **'
        rpt.Subreports["Logo_Sub_Report"].Database.Tables[0].SetDataSource(dtLogo);

        //** for Audit Sub Report **'
        rpt.Subreports["Audit_Sub_Report"].Database.Tables[0].SetDataSource(dtAudit);

        this.CrystalReportViewer1.ReportSource = rpt;


        dtAdapter = null;
        objConn.Close();
        objConn = null;

    }
}

        '** for Main Report **'
        rpt.SetDataSource(dtMain)

        '** for Logo Sub Report **'
        rpt.Subreports("Logo_Sub_Report").Database.Tables(0).SetDataSource(dtLogo)

        '** for Audit Sub Report **'
        rpt.Subreports("Audit_Sub_Report").Database.Tables(0).SetDataSource(dtAudit)