一、综述
这里我要介绍的是如何使用VS2005中的ReportViewer控件动态显示需要绑定的报表（*.rdlc）。由于我们已经很习惯使用DataSet作为数据源去绑定类似于GridView或者DataGrid等控件，那么是否有方法将DataSet作为数据源就像绑定GridView这样去绑定报表呢？答案肯定的。
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WebForms
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Init(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Init
If Not Page.IsPostBack Then
'Set the processing mode for the ReportViewer to Local
reportViewer.ProcessingMode = ProcessingMode.Local
Dim localReport As LocalReport
localReport = reportViewer.LocalReport
localReport.ReportPath = "Sales Order Detail.rdlc"
Dim dataset As New DataSet("Sales Order Detail")
Dim salesOrderNumber As String = "SO43661"
'Get the sales order data
GetSalesOrderData(salesOrderNumber, dataset)
'Create a report data source for the sales order data
Dim dsSalesOrder As New ReportDataSource()
dsSalesOrder.Name = "SalesOrder"
dsSalesOrder.Value = dataset.Tables("SalesOrder")
localReport.DataSources.Add(dsSalesOrder)
'Get the sales order detail data
GetSalesOrderDetailData(salesOrderNumber, dataset)
'Create a report data source for the sales
'order detail data
Dim dsSalesOrderDetail As New ReportDataSource()
dsSalesOrderDetail.Name = "SalesOrderDetail"
dsSalesOrderDetail.Value = _
dataset.Tables("SalesOrderDetail")
localReport.DataSources.Add(dsSalesOrderDetail)
'Create a report parameter for the sales order number
Dim rpSalesOrderNumber As New ReportParameter()
rpSalesOrderNumber.Name = "SalesOrderNumber"
rpSalesOrderNumber.Values.Add("SO43661")
'Set the report parameters for the report
Dim parameters() As ReportParameter = {rpSalesOrderNumber}
localReport.SetParameters(parameters)
End If
End Sub
Private Sub GetSalesOrderData(ByVal salesOrderNumber As String, _
ByRef dsSalesOrder As DataSet)
Dim sqlSalesOrder As String = _
"SELECT SOH.SalesOrderNumber, S.Name AS Store, " & _
" SOH.OrderDate, C.FirstName AS SalesFirstName, " & _
" C.LastName AS SalesLastName, E.Title AS " & _
" SalesTitle, SOH.PurchaseOrderNumber, " & _
" SM.Name AS ShipMethod, BA.AddressLine1 " & _
" AS BillAddress1, BA.AddressLine2 AS " & _
" BillAddress2, BA.City AS BillCity, " & _
" BA.PostalCode AS BillPostalCode, BSP.Name " & _
" AS BillStateProvince, BCR.Name AS " & _
" BillCountryRegion, SA.AddressLine1 AS " & _
" ShipAddress1, SA.AddressLine2 AS " & _
" ShipAddress2, SA.City AS ShipCity, " & _
" SA.PostalCode AS ShipPostalCode, SSP.Name " & _
" AS ShipStateProvince, SCR.Name AS " & _
" ShipCountryRegion, CC.Phone AS CustPhone, " & _
" CC.FirstName AS CustFirstName, CC.LastName " & _
" AS CustLastName " & _
"FROM Person.Address SA INNER JOIN " & _
" Person.StateProvince SSP ON " & _
" SA.StateProvinceID = SSP.StateProvinceID " & _
" INNER JOIN Person.CountryRegion SCR ON " & _
" SSP.CountryRegionCode = SCR.CountryRegionCode " & _
" RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " & _
" LEFT OUTER JOIN Person.Contact CC ON " & _
" SOH.ContactID = CC.ContactID LEFT OUTER JOIN" & _
" Person.Address BA INNER JOIN " & _
" Person.StateProvince BSP ON " & _
" BA.StateProvinceID = BSP.StateProvinceID " & _
" INNER JOIN Person.CountryRegion BCR ON " & _
" BSP.CountryRegionCode = " & _
" BCR.CountryRegionCode ON SOH.BillToAddressID " & _
" = BA.AddressID ON SA.AddressID = " & _
" SOH.ShipToAddressID LEFT OUTER JOIN " & _
" Person.Contact C RIGHT OUTER JOIN " & _
" HumanResources.Employee E ON C.ContactID = " & _
" E.ContactID ON SOH.SalesPersonID = " & _
" E.EmployeeID LEFT OUTER JOIN " & _
" Purchasing.ShipMethod SM ON SOH.ShipMethodID " & _
" = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" & _
" S ON SOH.CustomerID = S.CustomerID " & _
"WHERE (SOH.SalesOrderNumber = @SalesOrderNumber)"
Using connection As New SqlConnection( _
"Data Source=(local); " & _
"Initial Catalog=AdventureWorks; " & _
"Integrated Security=SSPI")
Dim command As New SqlCommand(sqlSalesOrder, connection)
Dim parameter As New SqlParameter("SalesOrderNumber", _
salesOrderNumber)
command.Parameters.Add(parameter)
Dim salesOrderAdapter As New SqlDataAdapter(command)
salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder")
End Using
End Sub
Private Sub GetSalesOrderDetailData( _
ByVal salesOrderNumber As String, _
ByRef dsSalesOrder As DataSet)
Dim sqlSalesOrderDetail As String = _
"SELECT SOD.SalesOrderDetailID, SOD.OrderQty, " & _
" SOD.UnitPrice, CASE WHEN " & _
" SOD.UnitPriceDiscount IS NULL THEN 0 " & _
" ELSE SOD.UnitPriceDiscount END AS " & _
" UnitPriceDiscount, SOD.LineTotal, " & _
" SOD.CarrierTrackingNumber, " & _
" SOD.SalesOrderID, P.Name, P.ProductNumber " & _
"FROM Sales.SalesOrderDetail SOD INNER JOIN " & _
" Production.Product P ON SOD.ProductID = " & _
" P.ProductID INNER JOIN " & _
" Sales.SalesOrderHeader SOH ON " & _
" SOD.SalesOrderID = SOH.SalesOrderID " & _
"WHERE (SOH.SalesOrderNumber = @SalesOrderNumber) " & _
"ORDER BY SOD.SalesOrderDetailID"
Using connection As New SqlConnection( _
"Data Source=(local); " & _
"Initial Catalog=AdventureWorks; " & _
"Integrated Security=SSPI")
Dim command As New SqlCommand(sqlSalesOrderDetail, _
connection)
Dim parameter As New SqlParameter("SalesOrderNumber", _
salesOrderNumber)
command.Parameters.Add(parameter)
Dim salesOrderDetailAdapter As New SqlDataAdapter(command)
salesOrderDetailAdapter.Fill(dsSalesOrder, _
"SalesOrderDetail")
End Using
End Sub
End Class
这里我要介绍的是如何使用VS2005中的ReportViewer控件动态显示需要绑定的报表（*.rdlc）。由于我们已经很习惯使用DataSet作为数据源去绑定类似于GridView或者DataGrid等控件，那么是否有方法将DataSet作为数据源就像绑定GridView这样去绑定报表呢？答案肯定的。
让我们先看下最简单的连接方法：Reporting Service使用dataset.xsd作为数据源，建立一个页面page.aspx对应一个reportviewer控件对应一张report.rdlc报表对应一个dataset.xsd数据源。运用这种模式，那么在VS2005中提供了一个强大的向导，只需按照向导操作，网上资料或者MSDN中都介绍的很详细如何使用这个向导，在此不再详细描述。
其次，也是我主要要介绍的，动态绑定到ado.net dataset：由于大部分数据都是采用ado.net dataset作为数据源绑定，如果使用提供的向导操作便不能进行动态绑定，对于后台操作非常不便，故不能使用向导操作。现在要设法动态绑定ado.net dataset到指定的report。
优点在于代码简单，操作灵活；
缺点在于设计report文件将变得非常令人头疼。
report文件是xml格式的，如果不使用向导那么就必须人为的去写这个xml文件（我研究了很久，没找到其他设计报表的方法，这麽写也是摸索出来的，掌握了就很方便了），特别当存储过程是动态的时候，设计这个报表的数据源将变得更为烦锁。但是同样的优点在于一切的问题只要掌握了如何去写这个文件，那么就等于解决了所有问题，独立性强。
二、使用WebForms ReportViewer控件
在一个WEB应用程序中，为了浏览在服务器上(report server)配置好的报表或者在本地文件系统（local file system）上的报表，你可以使用WebForms ReportViewer控件。
增加一个ReportViewer控件到一个Web应用程序
1、 增加一个新的Microsoft ASP.NET Web Site使用Microsoft Visual C# 或者 Microsoft Visual Basic。
2、 从工具箱中将ReportViewer控件拖到设计页面上，命名为reportViewer，当增加完之后，ReportViewer任务标签中会出现提示让你选择一个报表，此处可不去管它。
3、使用远程处理模式（Remote Processing Mode）浏览报表
下面的例子详细展示了如何提交一个报表存在于报表服务器上，这个例子使用Sales Order Detail Report。这张表报存在于SQL2005的示例（AdventureWorks）中，你只需安装SQL2005的示例，便能从里面找到，更详细信息关于示例，请看AdventureWorks Report Samples。
这个例子使用Windows综合认证，所以你必须首先在web.config中加入
<!-- Web.config file. --> <identity impersonate="true"/>
C#
1protected void Page_Init(object sender, EventArgs e)
2{
3 if (!Page.IsPostBack)
4 {
5 // Set the processing mode for the ReportViewer to Remote
6 reportViewer.ProcessingMode = ProcessingMode.Remote;
7
8 ServerReport serverReport = reportViewer.ServerReport;
9
10 // Set the report server URL and report path
11 serverReport.ReportServerUrl =
12 new Uri("http://localhost/reportserver");
13 serverReport.ReportPath =
14 "/AdventureWorks Sample Reports/Sales Order Detail";
15
16 // Create the sales order number report parameter
17 ReportParameter salesOrderNumber = new ReportParameter();
18 salesOrderNumber.Name = "SalesOrderNumber";
19 salesOrderNumber.Values.Add("SO43661");
20
21 // Set the report parameters for the report
22 reportViewer.ServerReport.SetParameters(
23 new ReportParameter[] { salesOrderNumber });
24 }
25}
protected void Page_Init(object sender, EventArgs e)2
{
3
if (!Page.IsPostBack)4
{
5
// Set the processing mode for the ReportViewer to Remote6
reportViewer.ProcessingMode = ProcessingMode.Remote;7
8
ServerReport serverReport = reportViewer.ServerReport;9
10
// Set the report server URL and report path11
serverReport.ReportServerUrl =12
new Uri("http://localhost/reportserver");13
serverReport.ReportPath =14
"/AdventureWorks Sample Reports/Sales Order Detail";15
16
// Create the sales order number report parameter17
ReportParameter salesOrderNumber = new ReportParameter();18
salesOrderNumber.Name = "SalesOrderNumber";19
salesOrderNumber.Values.Add("SO43661");20
21
// Set the report parameters for the report22
reportViewer.ServerReport.SetParameters(23
new ReportParameter[] { salesOrderNumber });24
}25
}VB
1Imports Microsoft.Reporting.WebForms
2
3Partial Class _Default
4 Inherits System.Web.UI.Page
5
6 Protected Sub Page_Init(ByVal sender As Object, _
7 ByVal e As System.EventArgs) Handles Me.Init
8
9 If Not Page.IsPostBack Then
10
11 'Set the processing mode for the ReportViewer to Remote
12 reportViewer.ProcessingMode = ProcessingMode.Remote
13
14 Dim serverReport As ServerReport
15 serverReport = reportViewer.ServerReport
16
17 'Set the report server URL and report path
18 serverReport.ReportServerUrl = _
19 New Uri("http://localhost/reportserver")
20 serverReport.ReportPath = _
21 "/AdventureWorks Sample Reports/Sales Order Detail"
22
23 'Create the sales order number report parameter
24 Dim salesOrderNumber As New ReportParameter()
25 salesOrderNumber.Name = "SalesOrderNumber"
26 salesOrderNumber.Values.Add("SO43661")
27
28 'Set the report parameters for the report
29 Dim parameters() As ReportParameter = {salesOrderNumber}
30 serverReport.SetParameters(parameters)
31
32 End If
33
34 End Sub
35
36End Class
Imports Microsoft.Reporting.WebForms2
3
Partial Class _Default4
Inherits System.Web.UI.Page5
6
Protected Sub Page_Init(ByVal sender As Object, _7
ByVal e As System.EventArgs) Handles Me.Init8
9
If Not Page.IsPostBack Then10
11
'Set the processing mode for the ReportViewer to Remote12
reportViewer.ProcessingMode = ProcessingMode.Remote13
14
Dim serverReport As ServerReport15
serverReport = reportViewer.ServerReport16
17
'Set the report server URL and report path18
serverReport.ReportServerUrl = _19
New Uri("http://localhost/reportserver")20
serverReport.ReportPath = _21
"/AdventureWorks Sample Reports/Sales Order Detail"22
23
'Create the sales order number report parameter24
Dim salesOrderNumber As New ReportParameter()25
salesOrderNumber.Name = "SalesOrderNumber"26
salesOrderNumber.Values.Add("SO43661")27
28
'Set the report parameters for the report29
Dim parameters() As ReportParameter = {salesOrderNumber}30
serverReport.SetParameters(parameters)31
32
End If33
34
End Sub35
36
End Class
4、使用本地处理模式(Local Processing Mode)浏览报表
这个例子同样使用了AdventureWorks Report Samples，你可以在本文章的最下面点击下载我做好的例子。
（1） 打开一个Web Site提供报表的增加
（2） 从菜单栏选择增加现有项
（3） 将C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\ Sales Order Detail.rdl 报表文件加入项目
（4） 重命名Sales Order Detail.rdl为Sales Order Detail.rdlc
（5） 下面的例子将会在本地模式下建立一个dataset作为报表的数据源显示报表
C#
1protected void Page_Init(object sender, EventArgs e)
2{
3 if (!Page.IsPostBack)
4 {
5 // Set the processing mode for the ReportViewer to Local
6 reportViewer.ProcessingMode = ProcessingMode.Local;
7
8 LocalReport localReport = reportViewer.LocalReport;
9
10 localReport.ReportPath = "Sales Order Detail.rdlc";
11
12 DataSet dataset = new DataSet("Sales Order Detail");
13
14 string salesOrderNumber = "SO43661";
15
16 GetSalesOrderData(salesOrderNumber, ref dataset);
17
18 ReportDataSource dsSalesOrder = new ReportDataSource();
19 dsSalesOrder.Name = "SalesOrder";
20 dsSalesOrder.Value = dataset.Tables["SalesOrder"];
21
22 localReport.DataSources.Add(dsSalesOrder);
23
24 GetSalesOrderDetailData(salesOrderNumber, ref dataset);
25
26 ReportDataSource dsSalesOrderDetail = new ReportDataSource();
27 dsSalesOrderDetail.Name = "SalesOrderDetail";
28 dsSalesOrderDetail.Value = dataset.Tables["SalesOrderDetail"];
29
30 localReport.DataSources.Add(dsSalesOrderDetail);
31
32 // Create the sales order number report parameter
33 ReportParameter rpSalesOrderNumber = new ReportParameter();
34 rpSalesOrderNumber.Name = "SalesOrderNumber";
35 rpSalesOrderNumber.Values.Add("SO43661");
36
37 // Set the report parameters for the report
38 localReport.SetParameters(
39 new ReportParameter[] { rpSalesOrderNumber });
40 }
41}
42
43private void GetSalesOrderData(string salesOrderNumber,
44 ref DataSet dsSalesOrder)
45{
46 string sqlSalesOrder =
47 "SELECT SOH.SalesOrderNumber, S.Name AS Store, " +
48 " SOH.OrderDate, C.FirstName AS SalesFirstName, " +
49 " C.LastName AS SalesLastName, E.Title AS " +
50 " SalesTitle, SOH.PurchaseOrderNumber, " +
51 " SM.Name AS ShipMethod, BA.AddressLine1 " +
52 " AS BillAddress1, BA.AddressLine2 AS " +
53 " BillAddress2, BA.City AS BillCity, " +
54 " BA.PostalCode AS BillPostalCode, BSP.Name " +
55 " AS BillStateProvince, BCR.Name AS " +
56 " BillCountryRegion, SA.AddressLine1 AS " +
57 " ShipAddress1, SA.AddressLine2 AS " +
58 " ShipAddress2, SA.City AS ShipCity, " +
59 " SA.PostalCode AS ShipPostalCode, SSP.Name " +
60 " AS ShipStateProvince, SCR.Name AS " +
61 " ShipCountryRegion, CC.Phone AS CustPhone, " +
62 " CC.FirstName AS CustFirstName, CC.LastName " +
63 " AS CustLastName " +
64 "FROM Person.Address SA INNER JOIN " +
65 " Person.StateProvince SSP ON " +
66 " SA.StateProvinceID = SSP.StateProvinceID " +
67 " INNER JOIN Person.CountryRegion SCR ON " +
68 " SSP.CountryRegionCode = SCR.CountryRegionCode " +
69 " RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " +
70 " LEFT OUTER JOIN Person.Contact CC ON " +
71 " SOH.ContactID = CC.ContactID LEFT OUTER JOIN" +
72 " Person.Address BA INNER JOIN " +
73 " Person.StateProvince BSP ON " +
74 " BA.StateProvinceID = BSP.StateProvinceID " +
75 " INNER JOIN Person.CountryRegion BCR ON " +
76 " BSP.CountryRegionCode = " +
77 " BCR.CountryRegionCode ON SOH.BillToAddressID " +
78 " = BA.AddressID ON SA.AddressID = " +
79 " SOH.ShipToAddressID LEFT OUTER JOIN " +
80 " Person.Contact C RIGHT OUTER JOIN " +
81 " HumanResources.Employee E ON C.ContactID = " +
82 " E.ContactID ON SOH.SalesPersonID = " +
83 " E.EmployeeID LEFT OUTER JOIN " +
84 " Purchasing.ShipMethod SM ON SOH.ShipMethodID " +
85 " = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" +
86 " S ON SOH.CustomerID = S.CustomerID " +
87 "WHERE (SOH.SalesOrderNumber = @SalesOrderNumber)";
88
89 SqlConnection connection = new
90 SqlConnection("Data Source=(local); " +
91 "Initial Catalog=AdventureWorks; " +
92 "Integrated Security=SSPI");
93
94 SqlCommand command =
95 new SqlCommand(sqlSalesOrder, connection);
96
97 command.Parameters.Add(
98 new SqlParameter("SalesOrderNumber",
99 salesOrderNumber));
100
101 SqlDataAdapter salesOrderAdapter = new
102 SqlDataAdapter(command);
103
104 salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder");
105}
106
107private void GetSalesOrderDetailData(string salesOrderNumber,
108 ref DataSet dsSalesOrder)
109{
110 string sqlSalesOrderDetail =
111 "SELECT SOD.SalesOrderDetailID, SOD.OrderQty, " +
112 " SOD.UnitPrice, CASE WHEN " +
113 " SOD.UnitPriceDiscount IS NULL THEN 0 " +
114 " ELSE SOD.UnitPriceDiscount END AS " +
115 " UnitPriceDiscount, SOD.LineTotal, " +
116 " SOD.CarrierTrackingNumber, " +
117 " SOD.SalesOrderID, P.Name, P.ProductNumber " +
118 "FROM Sales.SalesOrderDetail SOD INNER JOIN " +
119 " Production.Product P ON SOD.ProductID = " +
120 " P.ProductID INNER JOIN " +
121 " Sales.SalesOrderHeader SOH ON " +
122 " SOD.SalesOrderID = SOH.SalesOrderID " +
123 "WHERE (SOH.SalesOrderNumber = @SalesOrderNumber) " +
124 "ORDER BY SOD.SalesOrderDetailID";
125
126 using (SqlConnection connection = new
127 SqlConnection("Data Source=(local); " +
128 "Initial Catalog=AdventureWorks; " +
129 "Integrated Security=SSPI"))
130 {
131
132 SqlCommand command =
133 new SqlCommand(sqlSalesOrderDetail, connection);
134
135 command.Parameters.Add(
136 new SqlParameter("SalesOrderNumber",
137 salesOrderNumber));
138
139 SqlDataAdapter salesOrderDetailAdapter = new
140 SqlDataAdapter(command);
141
142 salesOrderDetailAdapter.Fill(dsSalesOrder,
143 "SalesOrderDetail");
144 }
145}
protected void Page_Init(object sender, EventArgs e)2
{3
if (!Page.IsPostBack)4
{5
// Set the processing mode for the ReportViewer to Local6
reportViewer.ProcessingMode = ProcessingMode.Local;7
8
LocalReport localReport = reportViewer.LocalReport;9
10
localReport.ReportPath = "Sales Order Detail.rdlc";11
12
DataSet dataset = new DataSet("Sales Order Detail");13
14
string salesOrderNumber = "SO43661";15
16
GetSalesOrderData(salesOrderNumber, ref dataset);17
18
ReportDataSource dsSalesOrder = new ReportDataSource();19
dsSalesOrder.Name = "SalesOrder";20
dsSalesOrder.Value = dataset.Tables["SalesOrder"];21
22
localReport.DataSources.Add(dsSalesOrder);23
24
GetSalesOrderDetailData(salesOrderNumber, ref dataset);25
26
ReportDataSource dsSalesOrderDetail = new ReportDataSource();27
dsSalesOrderDetail.Name = "SalesOrderDetail";28
dsSalesOrderDetail.Value = dataset.Tables["SalesOrderDetail"];29
30
localReport.DataSources.Add(dsSalesOrderDetail);31
32
// Create the sales order number report parameter33
ReportParameter rpSalesOrderNumber = new ReportParameter();34
rpSalesOrderNumber.Name = "SalesOrderNumber";35
rpSalesOrderNumber.Values.Add("SO43661");36
37
// Set the report parameters for the report38
localReport.SetParameters(39
new ReportParameter[] { rpSalesOrderNumber });40
}41
}42
43
private void GetSalesOrderData(string salesOrderNumber,44
ref DataSet dsSalesOrder)45
{46
string sqlSalesOrder =47
"SELECT SOH.SalesOrderNumber, S.Name AS Store, " +48
" SOH.OrderDate, C.FirstName AS SalesFirstName, " +49
" C.LastName AS SalesLastName, E.Title AS " +50
" SalesTitle, SOH.PurchaseOrderNumber, " +51
" SM.Name AS ShipMethod, BA.AddressLine1 " +52
" AS BillAddress1, BA.AddressLine2 AS " +53
" BillAddress2, BA.City AS BillCity, " +54
" BA.PostalCode AS BillPostalCode, BSP.Name " +55
" AS BillStateProvince, BCR.Name AS " +56
" BillCountryRegion, SA.AddressLine1 AS " +57
" ShipAddress1, SA.AddressLine2 AS " +58
" ShipAddress2, SA.City AS ShipCity, " +59
" SA.PostalCode AS ShipPostalCode, SSP.Name " +60
" AS ShipStateProvince, SCR.Name AS " +61
" ShipCountryRegion, CC.Phone AS CustPhone, " +62
" CC.FirstName AS CustFirstName, CC.LastName " +63
" AS CustLastName " +64
"FROM Person.Address SA INNER JOIN " +65
" Person.StateProvince SSP ON " +66
" SA.StateProvinceID = SSP.StateProvinceID " +67
" INNER JOIN Person.CountryRegion SCR ON " +68
" SSP.CountryRegionCode = SCR.CountryRegionCode " +69
" RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " +70
" LEFT OUTER JOIN Person.Contact CC ON " +71
" SOH.ContactID = CC.ContactID LEFT OUTER JOIN" +72
" Person.Address BA INNER JOIN " +73
" Person.StateProvince BSP ON " +74
" BA.StateProvinceID = BSP.StateProvinceID " +75
" INNER JOIN Person.CountryRegion BCR ON " +76
" BSP.CountryRegionCode = " +77
" BCR.CountryRegionCode ON SOH.BillToAddressID " +78
" = BA.AddressID ON SA.AddressID = " +79
" SOH.ShipToAddressID LEFT OUTER JOIN " +80
" Person.Contact C RIGHT OUTER JOIN " +81
" HumanResources.Employee E ON C.ContactID = " +82
" E.ContactID ON SOH.SalesPersonID = " +83
" E.EmployeeID LEFT OUTER JOIN " +84
" Purchasing.ShipMethod SM ON SOH.ShipMethodID " +85
" = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" +86
" S ON SOH.CustomerID = S.CustomerID " +87
"WHERE (SOH.SalesOrderNumber = @SalesOrderNumber)";88
89
SqlConnection connection = new90
SqlConnection("Data Source=(local); " +91
"Initial Catalog=AdventureWorks; " +92
"Integrated Security=SSPI");93
94
SqlCommand command =95
new SqlCommand(sqlSalesOrder, connection);96
97
command.Parameters.Add(98
new SqlParameter("SalesOrderNumber",99
salesOrderNumber));100
101
SqlDataAdapter salesOrderAdapter = new102
SqlDataAdapter(command);103
104
salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder");105
}106
107
private void GetSalesOrderDetailData(string salesOrderNumber,108
ref DataSet dsSalesOrder)109
{110
string sqlSalesOrderDetail =111
"SELECT SOD.SalesOrderDetailID, SOD.OrderQty, " +112
" SOD.UnitPrice, CASE WHEN " +113
" SOD.UnitPriceDiscount IS NULL THEN 0 " +114
" ELSE SOD.UnitPriceDiscount END AS " +115
" UnitPriceDiscount, SOD.LineTotal, " +116
" SOD.CarrierTrackingNumber, " +117
" SOD.SalesOrderID, P.Name, P.ProductNumber " +118
"FROM Sales.SalesOrderDetail SOD INNER JOIN " +119
" Production.Product P ON SOD.ProductID = " +120
" P.ProductID INNER JOIN " +121
" Sales.SalesOrderHeader SOH ON " +122
" SOD.SalesOrderID = SOH.SalesOrderID " +123
"WHERE (SOH.SalesOrderNumber = @SalesOrderNumber) " +124
"ORDER BY SOD.SalesOrderDetailID";125
126
using (SqlConnection connection = new127
SqlConnection("Data Source=(local); " +128
"Initial Catalog=AdventureWorks; " +129
"Integrated Security=SSPI"))130
{131
132
SqlCommand command =133
new SqlCommand(sqlSalesOrderDetail, connection);134
135
command.Parameters.Add(136
new SqlParameter("SalesOrderNumber",137
salesOrderNumber));138
139
SqlDataAdapter salesOrderDetailAdapter = new140
SqlDataAdapter(command);141
142
salesOrderDetailAdapter.Fill(dsSalesOrder,143
"SalesOrderDetail");144
}145
}VB
Imports System.DataImports System.Data.SqlClientImports Microsoft.Reporting.WebFormsPartial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Init(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Init If Not Page.IsPostBack Then 'Set the processing mode for the ReportViewer to Local reportViewer.ProcessingMode = ProcessingMode.Local Dim localReport As LocalReport localReport = reportViewer.LocalReport localReport.ReportPath = "Sales Order Detail.rdlc" Dim dataset As New DataSet("Sales Order Detail") Dim salesOrderNumber As String = "SO43661" 'Get the sales order data GetSalesOrderData(salesOrderNumber, dataset) 'Create a report data source for the sales order data Dim dsSalesOrder As New ReportDataSource() dsSalesOrder.Name = "SalesOrder" dsSalesOrder.Value = dataset.Tables("SalesOrder") localReport.DataSources.Add(dsSalesOrder) 'Get the sales order detail data GetSalesOrderDetailData(salesOrderNumber, dataset) 'Create a report data source for the sales 'order detail data Dim dsSalesOrderDetail As New ReportDataSource() dsSalesOrderDetail.Name = "SalesOrderDetail" dsSalesOrderDetail.Value = _ dataset.Tables("SalesOrderDetail") localReport.DataSources.Add(dsSalesOrderDetail) 'Create a report parameter for the sales order number Dim rpSalesOrderNumber As New ReportParameter() rpSalesOrderNumber.Name = "SalesOrderNumber" rpSalesOrderNumber.Values.Add("SO43661") 'Set the report parameters for the report Dim parameters() As ReportParameter = {rpSalesOrderNumber} localReport.SetParameters(parameters) End If End Sub Private Sub GetSalesOrderData(ByVal salesOrderNumber As String, _ ByRef dsSalesOrder As DataSet) Dim sqlSalesOrder As String = _ "SELECT SOH.SalesOrderNumber, S.Name AS Store, " & _ " SOH.OrderDate, C.FirstName AS SalesFirstName, " & _ " C.LastName AS SalesLastName, E.Title AS " & _ " SalesTitle, SOH.PurchaseOrderNumber, " & _ " SM.Name AS ShipMethod, BA.AddressLine1 " & _ " AS BillAddress1, BA.AddressLine2 AS " & _ " BillAddress2, BA.City AS BillCity, " & _ " BA.PostalCode AS BillPostalCode, BSP.Name " & _ " AS BillStateProvince, BCR.Name AS " & _ " BillCountryRegion, SA.AddressLine1 AS " & _ " ShipAddress1, SA.AddressLine2 AS " & _ " ShipAddress2, SA.City AS ShipCity, " & _ " SA.PostalCode AS ShipPostalCode, SSP.Name " & _ " AS ShipStateProvince, SCR.Name AS " & _ " ShipCountryRegion, CC.Phone AS CustPhone, " & _ " CC.FirstName AS CustFirstName, CC.LastName " & _ " AS CustLastName " & _ "FROM Person.Address SA INNER JOIN " & _ " Person.StateProvince SSP ON " & _ " SA.StateProvinceID = SSP.StateProvinceID " & _ " INNER JOIN Person.CountryRegion SCR ON " & _ " SSP.CountryRegionCode = SCR.CountryRegionCode " & _ " RIGHT OUTER JOIN Sales.SalesOrderHeader SOH " & _ " LEFT OUTER JOIN Person.Contact CC ON " & _ " SOH.ContactID = CC.ContactID LEFT OUTER JOIN" & _ " Person.Address BA INNER JOIN " & _ " Person.StateProvince BSP ON " & _ " BA.StateProvinceID = BSP.StateProvinceID " & _ " INNER JOIN Person.CountryRegion BCR ON " & _ " BSP.CountryRegionCode = " & _ " BCR.CountryRegionCode ON SOH.BillToAddressID " & _ " = BA.AddressID ON SA.AddressID = " & _ " SOH.ShipToAddressID LEFT OUTER JOIN " & _ " Person.Contact C RIGHT OUTER JOIN " & _ " HumanResources.Employee E ON C.ContactID = " & _ " E.ContactID ON SOH.SalesPersonID = " & _ " E.EmployeeID LEFT OUTER JOIN " & _ " Purchasing.ShipMethod SM ON SOH.ShipMethodID " & _ " = SM.ShipMethodID LEFT OUTER JOIN Sales.Store" & _ " S ON SOH.CustomerID = S.CustomerID " & _ "WHERE (SOH.SalesOrderNumber = @SalesOrderNumber)" Using connection As New SqlConnection( _ "Data Source=(local); " & _ "Initial Catalog=AdventureWorks; " & _ "Integrated Security=SSPI") Dim command As New SqlCommand(sqlSalesOrder, connection) Dim parameter As New SqlParameter("SalesOrderNumber", _ salesOrderNumber) command.Parameters.Add(parameter) Dim salesOrderAdapter As New SqlDataAdapter(command) salesOrderAdapter.Fill(dsSalesOrder, "SalesOrder") End Using End Sub Private Sub GetSalesOrderDetailData( _ ByVal salesOrderNumber As String, _ ByRef dsSalesOrder As DataSet) Dim sqlSalesOrderDetail As String = _ "SELECT SOD.SalesOrderDetailID, SOD.OrderQty, " & _ " SOD.UnitPrice, CASE WHEN " & _ " SOD.UnitPriceDiscount IS NULL THEN 0 " & _ " ELSE SOD.UnitPriceDiscount END AS " & _ " UnitPriceDiscount, SOD.LineTotal, " & _ " SOD.CarrierTrackingNumber, " & _ " SOD.SalesOrderID, P.Name, P.ProductNumber " & _ "FROM Sales.SalesOrderDetail SOD INNER JOIN " & _ " Production.Product P ON SOD.ProductID = " & _ " P.ProductID INNER JOIN " & _ " Sales.SalesOrderHeader SOH ON " & _ " SOD.SalesOrderID = SOH.SalesOrderID " & _ "WHERE (SOH.SalesOrderNumber = @SalesOrderNumber) " & _ "ORDER BY SOD.SalesOrderDetailID" Using connection As New SqlConnection( _ "Data Source=(local); " & _ "Initial Catalog=AdventureWorks; " & _ "Integrated Security=SSPI") Dim command As New SqlCommand(sqlSalesOrderDetail, _ connection) Dim parameter As New SqlParameter("SalesOrderNumber", _ salesOrderNumber) command.Parameters.Add(parameter) Dim salesOrderDetailAdapter As New SqlDataAdapter(command) salesOrderDetailAdapter.Fill(dsSalesOrder, _ "SalesOrderDetail") End Using End SubEnd Class
三、设计报表连接数据源
当做完以上工作后，还需要设计报表的连接数据源，用记事本打开Sales Order Detail.rdlc。你只需详细关注里面的DataSources和DataSets就可
1 <DataSources>
2
3 <DataSource Name="AdventureWorks"> --报表数据源名称必须统一
4
5 <ConnectionProperties>
6
7 <ConnectString />
8
9 <DataProvider>SQL</DataProvider>
10
11 </ConnectionProperties>
12
13 </DataSource>
14
15 </DataSources>
16
17 <DataSets>
18
19 <DataSet Name="SalesOrderDetail"> --对应于程序中dsSalesOrderDetail.Name = "SalesOrderDetail"
20
21 <Fields> --所有dataset中有的列必须在此申明
22
23 <Field Name="SalesOrderDetailID">
24
25 <DataField>SalesOrderDetailID</DataField>
26
27 <rd:TypeName>System.Int32</rd:TypeName>
28
29 </Field>
30
31 </Fields>
32
33 <Query>
34
35 <DataSourceName>AdventureWorks</DataSourceName>
36
37 <CommandText>
38
39 </CommandText>
40
41 <Timeout>30</Timeout>
42
43 <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
44
45 </Query>
46
47 </DataSet>
48
49 </DataSets>
50
<DataSources>2
3
<DataSource Name="AdventureWorks"> --报表数据源名称必须统一4
5
<ConnectionProperties>6
7
<ConnectString />8
9
<DataProvider>SQL</DataProvider>10
11
</ConnectionProperties>12
13
</DataSource>14
15
</DataSources>16
17
<DataSets>18
19
<DataSet Name="SalesOrderDetail"> --对应于程序中dsSalesOrderDetail.Name = "SalesOrderDetail"20
21
<Fields> --所有dataset中有的列必须在此申明22
23
<Field Name="SalesOrderDetailID">24
25
<DataField>SalesOrderDetailID</DataField>26
27
<rd:TypeName>System.Int32</rd:TypeName>28
29
</Field>30
31
</Fields>32
33
<Query>34
35
<DataSourceName>AdventureWorks</DataSourceName>36
37
<CommandText>38
39
</CommandText>40
41
<Timeout>30</Timeout>42
43
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>44
45
</Query>46
47
</DataSet>48
49
</DataSets>50
其中DataSet 的<DataSet Name="SalesOrderDetail">属性对应于程序中的
Dim dsSalesOrderDetail As New ReportDataSource()
dsSalesOrderDetail.Name = "SalesOrderDetail"//对应该属性
dsSalesOrderDetail.Value =dataset.Tables("SalesOrderDetail")//绑定已经存在的dataset
localReport.DataSources.Add(dsSalesOrderDetail)
只要rdlc文件符合规范，其中的Fields设置为所有得出列名，定义的datasource name没有规定，但是必须统一，既可。你只要按照这样的格式去写一个报表文件，那么任何dataset都是可以绑定上去的，也就是说比如从将一个存储过程返回的dataset绑定到一张报表上面。
提示：
所有的列名申明将是一个非常令人头疼的问题，我所能作到的最简单的方法是按照向导作出一张报表，然后提取里面的所有fields复制到我要产生的报表中，如果你有更好的方法还请尽快告诉我，谢谢。
最后就是在报表上增加你想要显示的数据了，增加Reports Item，绑定参数等等，在此不作为本文介绍的内容。
四、总结
对于动态绑定报表的优点实在是太多了，你可以像绑定一个DataGrid那样去绑定一张报表，而且Reporting Services支持强大导出的功能也会让你的报表更满足你的需要。你可以从不同的页面传递不同的参数到同一个报表显示页面，根据参数的不同显示不同的报表，后台的代码是非常简单，唯一比较繁琐的任务都在于报表本身的设计。
呵呵，总算写完了，如果你有更好的方法还请一定联系我，大家交流，共同进步。
示例程序代码：/Files/Carlwave/reportSample.rar（要求：安装了SQL2005和VS2005即可）
浙公网安备 33010602011771号