VS2005＋SQL2005 Reporting Service动态绑定报表（Web）

一、综述

这里我要介绍的是如何使用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中加入

<identity impersonate="true"/>
C#

protected 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

ServerReport serverReport = reportViewer.ServerReport;
9

// 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

// Create the sales order number report parameter
17

ReportParameter salesOrderNumber = new ReportParameter();
18

salesOrderNumber.Name = "SalesOrderNumber";
19

salesOrderNumber.Values.Add("SO43661");
20

// Set the report parameters for the report
22

reportViewer.ServerReport.SetParameters(
23

new ReportParameter[] { salesOrderNumber });
24

}
25

}

Imports Microsoft.Reporting.WebForms
2

Partial Class _Default
4

Inherits System.Web.UI.Page
5

Protected Sub Page_Init(ByVal sender As Object, _
7

ByVal e As System.EventArgs) Handles Me.Init
8

If Not Page.IsPostBack Then
10

'Set the processing mode for the ReportViewer to Remote
12

reportViewer.ProcessingMode = ProcessingMode.Remote
13

Dim serverReport As ServerReport
15

serverReport = reportViewer.ServerReport
16

'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

'Create the sales order number report parameter
24

Dim salesOrderNumber As New ReportParameter()
25

salesOrderNumber.Name = "SalesOrderNumber"
26

salesOrderNumber.Values.Add("SO43661")
27

'Set the report parameters for the report
29

Dim parameters() As ReportParameter = {salesOrderNumber}
30

serverReport.SetParameters(parameters)
31

End If
33

End Sub
35

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作为报表的数据源显示报表

protected 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

LocalReport localReport = reportViewer.LocalReport;
9

localReport.ReportPath = "Sales Order Detail.rdlc";
11

DataSet dataset = new DataSet("Sales Order Detail");
13

string salesOrderNumber = "SO43661";
15

GetSalesOrderData(salesOrderNumber, ref dataset);
17

ReportDataSource dsSalesOrder = new ReportDataSource();
19

dsSalesOrder.Name = "SalesOrder";
20

dsSalesOrder.Value = dataset.Tables["SalesOrder"];
21

localReport.DataSources.Add(dsSalesOrder);
23

GetSalesOrderDetailData(salesOrderNumber, ref dataset);
25

ReportDataSource dsSalesOrderDetail = new ReportDataSource();
27

dsSalesOrderDetail.Name = "SalesOrderDetail";
28

dsSalesOrderDetail.Value = dataset.Tables["SalesOrderDetail"];
29

localReport.DataSources.Add(dsSalesOrderDetail);
31

// Create the sales order number report parameter
33

ReportParameter rpSalesOrderNumber = new ReportParameter();
34

rpSalesOrderNumber.Name = "SalesOrderNumber";
35

rpSalesOrderNumber.Values.Add("SO43661");
36

// Set the report parameters for the report
38

localReport.SetParameters(
39

new ReportParameter[] { rpSalesOrderNumber });
40

}
41

}
42

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

SqlConnection connection = new
90

SqlConnection("Data Source=(local); " +
91

"Initial Catalog=AdventureWorks; " +
92

"Integrated Security=SSPI");
93

SqlCommand command =
95

new SqlCommand(sqlSalesOrder, connection);
96

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

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 = 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

}

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

三、设计报表连接数据源

当做完以上工作后，还需要设计报表的连接数据源，用记事本打开Sales Order Detail.rdlc。你只需详细关注里面的DataSources和DataSets就可

<DataSources>
2

<DataSource Name="AdventureWorks"> --报表数据源名称必须统一
4

<ConnectionProperties>
6

<ConnectString />
8

<DataProvider>SQL</DataProvider>
10

</ConnectionProperties>
12

</DataSource>
14

</DataSources>
16

<DataSets>
18

<DataSet Name="SalesOrderDetail"> --对应于程序中dsSalesOrderDetail.Name = "SalesOrderDetail"
20

<Fields> --所有dataset中有的列必须在此申明
22

<Field Name="SalesOrderDetailID">
24

<DataField>SalesOrderDetailID</DataField>
26

<rd:TypeName>System.Int32</rd:TypeName>
28

</Field>
30

</Fields>
32

<Query>
34

<DataSourceName>AdventureWorks</DataSourceName>
36

<CommandText>
38

</CommandText>
40

<Timeout>30</Timeout>
42

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
44

</Query>
46

</DataSet>
48

</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即可）

posted on 2011-04-22 08:19 追梦人RUBY 阅读(214) 评论(0) 收藏举报