使用报告服务 (Reporting Services) 创建即席报告应用程序 (Ad Hoc Report Application)

Kathrine Lord
Microsoft Corporation

适用于:
Microsoft .NET framework
Microsoft SQL Server 2000
Microsoft Visual Studio.NET 2003
SQL Server 2000 Reporting Services

**摘要:**您是 .NET 编程或报告服务 (Reporting Services) 的初学者吗?下面介绍如何使用报告服务 API 创建 .NET 应用程序以创建和发布报告。

本页内容

先决条件 先决条件
使用 VS.NET 2003 创建解决方案 使用 VS.NET 2003 创建解决方案
将 Error_Page 窗体添加到项目中 将 Error_Page 窗体添加到项目中
将 RDLGenerator 窗体添加到项目中 将 RDLGenerator 窗体添加到项目中
将 ManagingReports 窗体添加到项目中 将 ManagingReports 窗体添加到项目中
小结 小结

先决条件

此项目要求将 Microsoft IIS 6.0、Microsoft Visual Studio.NET 2003、Microsoft SQL Server 2000 和 SQL Server 2000 报告服务安装在开发人员机器上。SQL Server 2000 和报告服务的版本应该是一样的。例如,如果使用的是 SQL Server 2000 Developer Edition,那么也需要使用报告服务开发人员版本。

用于报告的 SQL Server 数据源不必在本地,也不要求是同一版本。用户需要能够访问远程数据源从而加以使用。报告服务数据库(ReportServer 和 ReportServer TempDB)必须在本地。所有 SQL Server(不论是作为数据源引用的还是由报告服务使用的)都必须启用 Windows 身份验证或混合模式身份验证。

使用 VS.NET 2003 创建解决方案

  1. 打开 Visual Studio .NET 2003,并在 Start Page 上单击 New Project

  2. 选择 Visual Basic Projects, ASP.NET Web Application

  3. 将项目命名为 CreateReport,并单击 OK

    adhocrepsr_figure1

    图 1

  4. 右键单击 References,并从快捷菜单上选择 Add Reference

  5. 在 Add Reference 模式窗口上单击 Browse

  6. 浏览到 Report Manager bin 文件夹所在的位置。在默认安装的情况下,该位置为 c:\program files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\Bin。

  7. 选择 ReportingServicesWebUserInterface.dll。

  8. 在 Add Reference 模式窗口的 .NET 选项卡中,向下滚动屏幕直到找到 System.Web.Services.dll,并单击它。

    adhocrepsr_figure2

    图 2

  9. 单击 Select 按钮并将其添加到 Selected Components 列表框中。

  10. 单击 OK 以将这些程序集添加为 References。

接着,注意安全设置 — 非常重要:

  1. 将以下标识标记添加到 开始标记下的 Web.Config 文件中

    <identity impersonate="true" />
    
  1. 使用管理工具中的 Internet 信息服务管理器将 CreateReport Web 安全性设置为 Windows 身份验证。

  2. 使用 Report Manager 界面启用 My Reports 功能。为此,请登录到 http://localhost/reports 或安装报告服务时提供的名称。转到 Site Settings 并选中 Enable My Reports... 选项,如下图所示。

    adhocrepsr_figure3

    图 3

将 ReportWizard 窗体添加到项目中

  1. 添加新的 web 窗体 (Project, Add Web Form),并将其命名为 ReportWizard.aspx。

  2. 右键单击 ReportWizard.aspx,并选择 Set As Start Page

  3. 在设计器中打开 ReportWizard.aspx 并选择 HTML 视图。

    就在 <body> 的开始标记下粘贴以下脚本 (Edit, Paste As HTML),该脚本用于打开后面将要创建的 ManageReports.aspx。

    <script language="javascript">
    

function OpenNewWin() { window.open("ManageReports.aspx", "ManageReports") } </script>

  1. <form></form> 开始和结束标记内粘贴以下内容(稍后我将一一演示这些内容):

    <asp:label id="Label4" style="Z-INDEX: 103; LEFT: 32px; POSITION: 
    

absolute; TOP: 616px" runat="server" Height="24px" Width="265px" Visible="False">Choose the output format for your report</asp:label> <asp:button id="btnStartOver" style="Z-INDEX: 109; LEFT: 592px; POSITION: absolute; TOP: 616px" runat="server" Text="Start Over"></asp:button> <asp:dropdownlist id="formatDropDown" style="Z-INDEX: 102; LEFT: 296px; POSITION: absolute; TOP: 616px" runat="server" Width="199px" Visible="False"> <asp:ListItem Value="CSV">CSV</asp:ListItem> <asp:ListItem Value="EXCEL">EXCEL</asp:ListItem> <asp:ListItem Value="HTMLOWC">HTMLOWC</asp:ListItem> <asp:ListItem Value="MHTML">MHTML</asp:ListItem> <asp:ListItem Value="PDF" Selected="True">PDF</asp:ListItem> <asp:ListItem Value="IMAGE">TIFF</asp:ListItem> </asp:dropdownlist> <asp:button id="Submit" style="Z-INDEX: 101; LEFT: 520px; POSITION: absolute; TOP: 616px" runat="server" Text="Submit"></asp:button> <asp:panel id="Panel1" style="Z-INDEX: 104; LEFT: 32px; POSITION: absolute; TOP: 56px" runat="server" Height="82px" Width="784px" BorderColor="Transparent"> <H1 style="COLOR: navy">Step 1 - Select a Server:</H1> <asp:dropdownlist id="ServerList" runat="server" Width="272px" AutoPostBack="True"> <asp:ListItem Value="Select a SQL Server...">Select a SQL Server...</asp:ListItem> <asp:ListItem Value="(local)">Local Server</asp:ListItem> <asp:ListItem Value="(local)">Other Server</asp:ListItem> </asp:dropdownlist> <asp:RequiredFieldValidator id="ServerValidator" runat="server" ErrorMessage="Please select the server where your database resides" ControlToValidate="ServerList" InitialValue="Select a SQL Server..." Display="Dynamic"></asp:RequiredFieldValidator> </asp:panel> <asp:panel id="Panel2" style="Z-INDEX: 105; LEFT: 32px; POSITION: absolute; TOP: 144px" runat="server" Height="80px" Width="784px" Visible="False"> <H1 style="COLOR: navy">Step 2 - Select a Database:</H1> <asp:dropdownlist id=DatabaseList runat="server" Width="272px" AutoPostBack="True" DataValueField="name" DataTextField="name" DataSource="<%# MasterDS1 %>"> </asp:dropdownlist> <asp:RequiredFieldValidator id="DatabaseListValidator" runat="server" ErrorMessage="Please select the database" ControlToValidate="DatabaseList" InitialValue="Make a Selection..." Display="Dynamic"></asp:RequiredFieldValidator> </asp:panel> <asp:panel id="Panel3" style="Z-INDEX: 106; LEFT: 32px; POSITION: absolute; TOP: 224px" runat="server" Height="78px" Width="790px"> <H1 style="COLOR: navy">Step 3 - Select a Table:</H1> <H1 style="COLOR: navy"></H1> <asp:dropdownlist id=TableList runat="server" Width="400px" AutoPostBack="True" DataValueField="name" DataTextField="name" DataSource="<%# TablesDS1 %>"> </asp:dropdownlist> <asp:RequiredFieldValidator id="TableListValidator" runat="server" ErrorMessage="Please select a table" ControlToValidate="TableList" InitialValue="Make a Selection..." Display="Dynamic"></asp:RequiredFieldValidator> <asp:panel id="Panel4" runat="server" Width="782px" Height="248px"> <H1 style="COLOR: navy">Step 4 - Select the columns:</H1> <P dir="ltr" style="MARGIN-RIGHT: 0px"><FONT color="navy">Selected Fields:</FONT> <FONT color="navy">Available Fields:</FONT></P> <P dir="ltr" style="MARGIN-RIGHT: 0px">&nbsp; <asp:listbox id="FieldList" runat="server" Width="240px" Height="118px" SelectionMode="Multiple"></asp:listbox>&nbsp; <asp:Button id="Button2" runat="server" Text=">>>" CausesValidation="False"></asp:Button>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <asp:Button id="Button1" runat="server" Text="<<<" CausesValidation="False"></asp:Button>&nbsp; <asp:listbox id="selFieldList" runat="server" Width="240px" Height="120px" SelectionMode="Multiple"></asp:listbox></P> <P> <asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" ErrorMessage="You must select at least 1 column" ControlToValidate="FieldList" Display="Dynamic"></asp:RequiredFieldValidator></P> </asp:panel> </asp:panel> <asp:textbox id="txtStep" style="Z-INDEX: 107; LEFT: 688px; POSITION: absolute; TOP: 616px" runat="server" Width="32px" Visible="False">1</asp:textbox> <asp:label id="Label1" style="Z-INDEX: 108; LEFT: 32px; POSITION: absolute; TOP: 8px" runat="server" Width="544px" BorderColor="White" Font-Italic="True" BackColor="Transparent" Font-Size="22pt" Font- Bold="True" ForeColor="ForestGreen">Welcome to the Report Generation Wizard!</asp:label> <asp:textbox id="txtTitle" style="Z-INDEX: 110; LEFT: 32px; POSITION: absolute; TOP: 584px" runat="server" Width="552px">Enter Report Title Here...</asp:textbox> <input style="Z-INDEX: 111; LEFT: 640px; WIDTH: 136px; POSITION: absolute; TOP: 16px; HEIGHT: 24px" onclick="OpenNewWin()" type="button" value="Manage My Reports..."> <asp:RequiredFieldValidator id="RequiredFieldValidator5" style="Z- INDEX: 112; LEFT: 592px; POSITION: absolute; TOP: 584px" runat="server" Width="208px" ErrorMessage="Please enter a valid report title" ControlToValidate="txtTitle" InitialValue="Enter Report Title Here..." Display="Dynamic"></asp:RequiredFieldValidator>

  1. 选择 Design 视图,您可以看到:

    adhocrepsr_figure4

    图 4

理解 ReportWizard 窗体中的控件

这一页面的作用只是生成服务器名、数据库、字段列表和报告标题(如果用户需要)。这仅仅意味着它可以作为用于创建这些项目的一种方法的示例。我之所以使用这一方法,原因是它容易理解,您可以选择其他的方法来完成这些项目。例如,可以将选择绑定到视图的某些描述的权利交给用户,而保留在幕后对服务器和数据库进行命名的权利。也可以使用存储过程(但是必须更改 GenerateRDL() 方法以与之相适应)。所需要的是 .NET 开发人员的专业技术,以及对报告定义语言 (Report Definition Language) 和 MSSRS Web 服务的理解。

ServerList DropDownList

  1. 右键单击 ServerList 控件并选择 Properties

  2. 单击 Items(Collection) 旁边的椭圆。

  3. 这些项目如下所示,包括具有相同 (local) 值的本地服务器和其他服务器。需要更改这些值来适应您的环境。

    此示例中假设数据库都在本地,但唯一需要的是 ReportingServer 数据库在本地。数据源可以放在开发人员有权使用的任何 SQL Server 实例上。

    ServerList 值用于从指定服务器上的主数据库中检索数据库列表。本示例使用完整的安全性,因此这些列表项目的值必须是 SQL Server 实例的文字名或 (local)。它不能是本地主机。

    adhocrepsr_figure5

    图 5

DatabaseList DropDownList

该控件用于选择要查询的数据库,以提供 TableList 控件的值。该列表是通过查询 sysdatabases 表动态填写的,sysdatabases 表位于 ServerList 控件中选定的 SQL Server 实例的主数据库内。

TableList DropDownList

该控件用于选择填充 FieldList 控件的表或视图。该列表是通过查询 sysobjects 表动态填写的,以查询位于 DatabaseList 控件中选定的数据库的表和视图。

FieldList 和 SelFieldList DropDownList

adhocrepsr_figure6

图 6

FieldList 控件根据 TableList 控件指示的表或视图中的字段动态填充。使用该控件来选择要用于 SQL 查询的字段。必须至少选择一个用于查询的字段。

txtTitle、formatDropDown、Submit、Start Over 和 txtStep 控件

adhocrepsr_figure7

图 7

  1. txtTitle 文本框用于输入用户指定的报告标题。

  2. formatDropDown 下拉列表允许用户选择呈现报告的格式。这些选项是 PDF(默认值)、Excel、HTMLOWC(带有 Office Web 组件的 HTML)、TIF(图像)、CSV 和 MHTML(web 存档)。

  3. Submit 按钮用于验证窗体数据并且将其提交给 RDLGenerator 页面。该页面生成实际的报告,在本教程后面会将其添加到项目中。

  4. Start Over 按钮用于在开始时删除窗体数据并且重新启动向导。

  5. txtStep 文本框用于跟踪用户在向导中正在进行的步骤。该字段在用户界面上是不可见的。

    本文没有介绍此页面上的验证和标签。本文面向已经熟悉这些类型对象的开发人员。有关验证和标签的更多信息,请使用 Visual Studio .NET Help 菜单。

将数据对象添加到 ReportWizard 页面中

现在需要创建应用程序使用的数据适配器、连接和数据集。

添加数据适配器和连接来填写数据库列表

  1. 使用 Toolbox 中的 Data 选项卡,选择 SQLDataAdapter。将其拖放到设计器中。

  2. 这将启动 Data Adapter Configuration Wizard。

    adhocrepsr_figure8

    图 8

  3. 单击 Next

  4. 单击 New Connection 按钮。

    adhocrepsr_figure9

    图 9

  5. 输入 (local) 或用作报告的数据源的服务器的名称。

  6. 选择 Use Windows NT Integrated Security

  7. 选择 Master 数据库并单击 Test Connection 按钮。

  8. 如果连接测试成功,单击 OK

    adhocrepsr_figure10

    图 10

  9. 单击 Next 以继续下一步。

    接受 Choose a Query Type 屏幕上的默认 Use SQL statements,单击 Next

    adhocrepsr_figure11

    图 11

  10. 将下面的查询复制并粘贴到 Generate the SQL Statements 文本区中。

    SELECT name FROM sysdatabases WHERE (name NOT IN ('Master', 'tempdb', 'msdb', 'Model'))
    
**SQL 演示 1**

![adhocrepsr\_figure12](images/Aa902634.adhocrepsr_figure12(zh-cn,SQL.80).gif)

**图 12**
  1. 单击 Advanced Options... 按钮。

  2. 取消选择 Generate Insert, Update and Delete statements 复选框(不需要这些语句),并单击 OK

    adhocrepsr_figure13

    图 13

  3. 单击 Next,然后单击 Finish

    adhocrepsr_figure14

    图 14

  4. 使用 Properties 对话框,将 SqlDataAdapter1 的名称设置为 MasterSQLAdapter,将 SqlConnection1 的名称设置为 MasterSqlConnection1。如果不重命名这些对象,则必须将后面要粘贴的代码更改为项目中已经设置好的名称。

生成数据集

  1. 右键单击 MasterSQLAdapter 并选择 Generate Dataset

  2. 将数据集命名为 MasterDS。

添加数据适配器来填写表列表

  1. Tools 菜单上的 data 选项卡中选择 SQLDataAdapter 以创建 TablesDA。

  2. 当 Data Adapter Configuration Wizard 弹出时,单击 Cancel。不需要使用该向导来完成这些属性设置。

  3. 选择新的 Data Adapter,并单击 Properties 视图。

  4. 进行以下设置:

    Name=TablesDA1

    Connection=MasterSQLConnection1(或上面指定的名称)

    CommandText= Select name from sysobjects where (xtype IN ('u', 'v')) AND (Status >= 0) order by name

    DeleteCommand=(none)

    InsertCommand=(none)

    UpdateCommand=(none)

    adhocrepsr_figure15

    图 15

创建表数据集

  1. 右键单击 TablesAdapter1 并选择 Generate Dataset

  2. 选择 New,将其命名为 TablesDS,然后单击 OK

    adhocrepsr_figure16

    图 16

添加数据适配器来填写字段列表

  1. 按照“添加数据适配器来填写表列表”一节中所述,在不使用向导的情况下重复这些步骤来创建数据适配器。

  2. 进行以下设置:

    Name=GenerateFieldsDA

    CommandText=Select * From sysobjects

    Connection=MasterSqlConnection1

    DeleteCommand=(none)

    InsertCommand=(none)

    UpdateCommand=(none)

    不要为该适配器生成数据集。

    adhocrepsr_figure17

    图 17

将代码添加到 ReportWizard 窗体中

  1. 右键单击 ReportWizard.aspx 页面并选择 View Code

  2. 将代码演示 1 中的代码和注释复制并粘贴到下面的代码

              Public Class ReportWizard 
    
之前以设置 **Imports** 语句。

<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">'=====================================================================

' Copyright (C) Microsoft Corporation. All rights reserved. ' ' This source code is intended only as a supplement to Microsoft ' Development Tools and/or on-line documentation. See these other ' materials for detailed information regarding Microsoft code samples. ' ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A ' PARTICULAR PURPOSE. '=====================================================================*/ Imports System.IO Imports System.Collections Imports System.Data Imports System.Data.SqlClient Imports System.Text Imports System.Xml Imports System.Web.Services Imports System.Web.UI Imports System.Web.UI.Page Imports Microsoft.SqlServer.ReportingServices

**代码演示 1**
  1. 粘贴代码演示 2 中的代码来声明 Web Form Designer Generated Code 一节下的变量。

    Private m_connection As SqlConnection
    Private m_connectString As String
    Private m_commandText As String
    Private m_fields As ArrayList
    Private m_ReportName As String
    Private dbms As String = "initial catalog = master"
    Private SQLServer As String = "data source = localhost;"
    

Private ConnectionString As String = "integrated security=SSPI;persist security info=False;"

**代码演示 2**
  1. 用代码演示 3 中的代码替换 Private Sub Page_Load 语句中的现有注释:

    此代码设置环境可见性,并且根据后面介绍的 txtStep 字段中所列出的步骤集中于 ReportWizard.aspx 页面上的对象。

    ' Track the steps and set visibility/focus
        Select Case txtStep.Text
            Case 1
                txtStep.Text = 2
                Me.FindControl("ServerList")
                Panel2.Visible = False
                Panel3.Visible = False
                Panel4.Visible = False
                selFieldList.Visible = False
                txtTitle.Visible = False
                Submit.Visible = False
            Case 2
                txtStep.Text = 3
                Panel2.Visible = True
                Panel3.Visible = False
                Panel4.Visible = False
                selFieldList.Visible = False
                txtTitle.Visible = False
                Submit.Visible = False
                Me.FindControl("DatabaseList")
            Case 3
                txtStep.Text = 4
                Panel3.Visible = True
                Panel4.Visible = False
                selFieldList.Visible = False
                txtTitle.Visible = False
                Submit.Visible = False
                Me.FindControl("SqlChoiceRadioList")
            Case 4
                txtStep.Text = 5
                Me.FindControl("TableDirectRadio")
            Case Else
                Me.FindControl("txtTitle")
        End Select
    

End Sub

**代码演示 3**
  1. 接下来,在 Page_load 后面,复制并粘贴代码演示 4 中的代码来捕获 ServerList中的改变事件。

    第 1 行和第 2行动态地改变连接使用的连接字符串。

    第 3 行在“项目存在”事件发生时清除 DatabaseList

    在结构化 try-catch 异常处理程序中,第 4-7 行打开数据库,填写并绑定数据集,然后插入一个通用的列表项目。

    第 8 行中在 try-catch 语句的 Finally 中关闭 MasterSqlConnection1

    Private Sub ServerList_SelectedIndexChanged( _
    

ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles ServerList.SelectedIndexChanged ' Set the data source from ServerList value 'Line1 SQLServer = "data source = " & ServerList.SelectedValue & ";" 'Line2 MasterSqlConnection1.ConnectionString = ConnectionString & SQLServer & dbms 'Line3 DatabaseList.Items.Clear() Try 'Line4
MasterSqlConnection1.Open() ' Open connection 'Line5
MasterSQLAdapter.Fill(MasterDS1) 'Fill dataset Master DBMS 'Line6
DatabaseList.DataBind() ' Bind the data set 'Line7
DatabaseList.Items.Insert(0, "Make a Selection...") ' Add generic list item Catch ex As Exception ExceptionHandler(ex) ' Generic error handler to be added a little later Finally 'Line8
MasterSqlConnection1.Close() ' Close connection
End Try End Sub

**代码演示 4**
  1. DatabaseList_SelectedIndexChanged 复制代码演示 5 中的代码。此代码本质上与上面的代码是一样的,只是有一些额外的可见性设置。

    Private Sub DatabaseList_SelectedIndexChanged( _
    

ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles DatabaseList.SelectedIndexChanged ' Set visibility and list items

    TableList.Items.Clear()
    FieldList.Visible = False
    selFieldList.Visible = False
    dbms = "initial catalog =" &amp; DatabaseList.SelectedValue
    SQLServer = " data source = " &amp; ServerList.SelectedValue &amp; ";"
    Try
        MasterSqlConnection1.ConnectionString = ConnectionString &amp; SQLServer &amp; dbms
        MasterSqlConnection1.Open() 'Open the connection
        TablesAdapter1.Fill(TablesDS1) 'Fill the tables dataset
        TableList.DataBind() 'bind the dataset to the TableList drop down
        TableList.Items.Insert(0, "Make a selection...") 'add a generic list item
        TableList.Visible = True
    Catch ex As Exception
        ExceptionHandler(ex)
    Finally
        MasterSqlConnection1.Close() 'close the connection
    End Try

End Sub

**代码演示 5**
  1. 复制并粘贴如下代码,启用 TableList 选择更改的捕获。

    此代码准备了 SQL 语句,并且将其传递给 GenerateFields() 方法以进行处理。然后,它使用 m_fields 中的数组值填写 selFieldList

    Private Sub TableList_SelectedIndexChanged( _
    

ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles TableList.SelectedIndexChanged Try ' Set initial state of objects FieldList.Items.Clear() selFieldList.Items.Clear() FieldList.Visible = True selFieldList.Visible = True ' Prepare command and connections dbms = "Initial Catalog = " & DatabaseList.SelectedValue SQLServer = "data source = " & ServerList.SelectedValue & ";" m_commandText = "Select * From [" & TableList.SelectedValue & "]" MasterSqlConnection1.ConnectionString = ConnectionString & SQLServer & dbms MasterSqlConnection1.Open() GenerateFieldsList(m_commandText) 'Pass the command text to generate field list Dim fieldName As String For Each fieldName In m_fields selFieldList.Items.Add(fieldName) 'Fill selFieldList drop down list Next fieldName 'set visibility of objects txtTitle.Visible = True Label4.Visible = True formatDropDown.Visible = True Submit.Visible = True Panel4.Visible = True Catch ex As Exception ExceptionHandler(ex) Finally MasterSqlConnection1.Close() 'close the connection End Try End Sub

**代码演示 6**
  1. 将代码演示 7 中的 GenerateFieldsList 代码复制并粘贴到 End Class 语句前面。

    此代码来自 Reporting Services Books Online。它创建了 SQL 命令和 SQLReader。该命令设置为查询从代码演示 6 中传入的文本。执行该读取器只是为了获得架构,因为我们仅使用字段名来填写列表,并且不需要任何数据。

    Public Sub GenerateFieldsList(ByVal m_NewQuery As String)
        'create field list and type arraylists
        Dim command As SqlCommand
        Dim reader As SqlDataReader
        ' Executing a query to retrieve a fields list for the report
        command = MasterSqlConnection1.CreateCommand()
        command.CommandText = m_NewQuery
        ' Execute and create a reader for the current command
        reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
        ' For each field in the resultset, add the name to an array list
        m_fields = New ArrayList
        Dim i As Integer
        For i = 0 To reader.FieldCount - 1
            m_fields.Add(reader.GetName(i))
        Next i
        reader.Close()
    

End Sub 'GenerateFieldsList

**代码演示 7**
  1. 代码演示 8a 和 8b 用于在 selFieldListFieldList 列表框之间移动字段名的两个按钮。复制并粘贴 GenerateFieldsList 方法的 End Sub 语句下面的这些代码示例。

    如果在通过它们进行枚举时修改列表框集合,则会导致枚举错误 (mscorlib)。在 try-catch 错误处理程序中,将枚举重新设置为集合的开头,然后将其移到第一个项目以清除这一错误。

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim li As New ListItem
        Try 'Move available fields to chosen fields list
            For Each li In selFieldList.Items
                If li.Selected Then
                    FieldList.Items.Add(li.Text)
                    FieldList.Items.Item(FieldList.Items.Count - 1).Selected = True
                    selFieldList.Items.Remove(li.Text)
                End If
            Next li
        Catch ex As Exception
            ' Modifying the field lists causes enumeration to be out of synch
            ' Try to reset enumeration
            Try
                FieldList.Items.GetEnumerator.Reset()
                FieldList.Items.GetEnumerator.MoveNext()
            Catch exception As Exception
                ' ignore further enumeration errors
            End Try
        End Try
    End Sub
    
**代码演示** **8a**

<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim li As ListItem
    Try ' User changes mind and removes fields from chosen field list
        For Each li In FieldList.Items
            If li.Selected Then
                selFieldList.Items.Add(li.Text)
                FieldList.Items.Remove(li.Text)
            End If
        Next li
    Catch ex As Exception
        ' Modifying the field lists causes enumeration to be out of synch
        ' Try to reset enumeration
        Try
            FieldList.Items.GetEnumerator.Reset()
            FieldList.Items.GetEnumerator.MoveNext()
        Catch exception As Exception
            ' ignore further enumeration errors
        End Try
    Finally
        ' Ensure all items in chosen field list are selected so user doesn't
        ' have to reselect all after removing a field
        Dim i As Integer
        For i = 0 To FieldList.Items.Count() - 1
            FieldList.Items.Item(i).Selected = True
        Next
    End Try

End Sub

**代码演示 8b**
  1. 将代码演示 9 、10 和 11 中的代码示例复制并粘贴到前面显示的 Button2_Click sub 下面。

    代码演示 9 将窗体值传递给 RDLGenerator.aspx 页面。代码演示 10 重新启动报告向导,清除所有现有的窗体值。在出现错误的情况下,代码演示 11 会将用户重定向到 error_page.aspx 页面。

    Private Sub Submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit.Click
        ' pass values to RDLGenerator page
        Server.Transfer("RDLGenerator.aspx", True)
    End Sub
    
**代码演示 9**

<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">Private Sub btnStartOver_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartOver.Click
    ' start over without retaining form values
    Server.Transfer("ReportWizard.aspx", False)

End Sub

**代码演示 10**

<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">Private Sub ExceptionHandler(ByVal exception As Exception)
    'generic exception handler

Response.Redirect("Error_page.aspx?errsource=" & exception.Source & "&errmessage=Please contact the help desk for assistance.") End Sub

**代码演示 11**
  1. 关闭 ReportWizard.aspx,保存所有更改。

将 Error_Page 窗体添加到项目中

该页面使用友好消息处理错误。可以使用任何运用自如的错误处理方法。

  1. File 菜单中选择 Add New Item

    adhocrepsr_figure18

    图 18

  2. 选择 Web Form 并将其命名为 Error_Page.aspx。

  3. 从页面属性中选择 View Code,并将代码演示 12 添加到 Page Load sub。

    此代码使用 Response.Write 来显示查询字符串中的 errsourceerrmessage。如果查询字符串中没有任何内容,则显示一般性错误消息。

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Request.QueryString("errsource") = "" And Request.QueryString("errmessage") = "" Then
            Response.Write("<CENTER><H2 style='COLOR: Red'>AN ERROR HAS OCCURRED</H2> ")
            Response.Write("<HR style='COLOR: Red'>Please restart the application. If it continues, contact your help desk.<BR>")
            Response.Write("<A href='/CreateReport/'>Create a New Report</A></CENTER>")
        Else
            Response.Write("<CENTER><H2 style='COLOR: Red'>" & Request.QueryString("errsource") & "<H2>")
            Response.Write("<HR style='COLOR: Red'>" & Request.QueryString("errmessage"))
            Response.Write("<P>Contact your help desk for assistance.</P></CENTER>")
        End If
    

End Sub

**代码演示** 12
  1. 关闭 Error_Page.aspx 并保存所有更改。

    可以在 web.config 文件中也可以在 @ Page 声明中指定一个错误页面。有关更多信息,请在 VS.NET 帮助索引中查询 @ Page 声明。

将 RDLGenerator 窗体添加到项目中

RDLGenerator 是使用报告服务 API 生成实际报告的页面。

  1. 如上添加一个新的 Web 窗体,并且将其命名为 RDLGenerator.aspx。

  2. 切换到代码视图,并且将代码演示 13 中的代码示例添加到 Public Class RDLGenerator 上面,以设置 Imports 语句。

    '=====================================================================
    

' Copyright (C) Microsoft Corporation. All rights reserved. ' ' This source code is intended only as a supplement to Microsoft ' Development Tools and/or on-line documentation. See these other ' materials for detailed information regarding Microsoft code samples. ' ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A ' PARTICULAR PURPOSE. '=====================================================================*/ Imports System.Web Imports System.Text Imports System.Text.RegularExpressions Imports System.IO Imports System.Data.SqlClient Imports System.Xml Imports Microsoft.ReportingServices Imports Microsoft.SqlServer.ReportingServices

**代码演示** **13**
  1. Private Sub Page_Load 语句前面添加代码演示 14 中的变量,以声明将要使用的变量:

    Private m_query As String ' Query to be used for report
    Private dsName As String ' Name of the data source
    Private m_Fields As ArrayList ' Arraylist of fields
    Private m_Type As ArrayList ' Arraylist of field types
    Private m_ReportName As String ' Report name for referencing
    Private definition As [Byte]() = Nothing ' Report definition used to create report
    Private warnings As Warning() = Nothing ' Warning container to catch warnings
    Private parentFolder = "My Reports" ' Path used to publish reports to..user must have rights to create content
    Private parentPath As String = "/" + parentFolder ' Path from report manager URL to parentFolder
    Private filePath As String = "c:\" ' Used to store RDL files
    Private RS As New ReportingService ' Reporting Service
    Private m_ReportTitle As String ' Title text box for report
    Private FieldList As String
    Private TableName As String
    Private OrderList As String
    Private DatabaseName As String
    Private ServerName As String 'Used to store server name
    

Private m_ConnStr As String ' Used for Data Source Definition

**代码演示** **14**
  1. 用代码演示 15 替换 Page_Load 子句。请参见内联注释以获得解释。

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
        ' Get form data from ReportWizard.aspx
        ServerName = Request.Form("ServerList")
        DatabaseName = Request.Form("DatabaseList")
        TableName = Request.Form("TableList")
        FieldList = Request.Form("FieldList")
        m_ReportTitle = Request.Form("txtTitle")
    
    

Dim format As String = Request.Form("formatDropDown")

    ' Set the strReportName and remove illegal characters
    Dim strReportName As String
    If m_ReportTitle = "" Then
        strReportName = Replace(TableName, " ", "")
    Else
        strReportName = Regex.Replace(m_ReportTitle, "[^\w\@-]", "")
    End If

    'Create rendering path adding the output format
    Dim myPath As String = http://localhost/reportserver?/ &amp; _
  • parentFolder + "/" & strReportName & _

  • "&rs:Command=render&rs:Format=" + format

      'Remove spaces from database name if they exist
      If Not DatabaseName = "" Then
          dsName = Replace(DatabaseName, " ", "") &amp; "Ds"
      Else
          dsName = "MyReportsDs"
      End If
    
      'Create report path for the RDL file
      m_ReportName = "c:\" &amp; strReportName &amp; ".rdl"
    
      'Change connection string to use appropriate server and database
      m_ConnStr = "Data Source =" &amp; ServerName &amp; ";Initial Catalog =" &amp; DatabaseName
      conn.ConnectionString = "Data Source =" &amp; ServerName &amp; ";Initial Catalog =" &amp; DatabaseName
      Try
          Dim x As Integer
          Dim nField() As String = Split(FieldList, ",")
    
          'Create the Order By field list replacing any empty strings
          OrderList = Replace(FieldList, " ", "")
    
          'Add brackets to field list for SQL statement-any spaces will cause error            
    

For x = 0 To nField.Length - 1 nField(x) = "[" & nField(x) & "] As " & Replace(nField(x), " ", "") Next ' Join the arraylist to create string FieldList = Join(nField, ",")

    Catch ex As Exception
        HandleException(ex)
    End Try

    'Generate SQL statement adding the FieldList; TableName and OrderList
    m_query = "Select " &amp; FieldList &amp; " from " &amp; "[" &amp; TableName &amp; "] Order By " &amp; OrderList

    Try
        'Generate the field list
        GenerateFieldsList(m_query)
        'Generate report
        GenerateRdl(m_ReportName)
        'Publish report
        BeginPublishReport(strReportName)
        'Open report in selected format
        Response.Redirect(myPath)

    Catch ex As Exception
        HandleException(ex)
    End Try

End Sub

**代码演示** **15**
  1. 将代码演示 16 中的代码添加到 End Class 语句的前面。

    这实质上与 ReportWizard.aspx 使用的是同一 GenerateFieldsList,只是添加了一个数组来包含用于格式化的 GenerateRDL() 方法所使用的字段类型。

    Public Sub GenerateFieldsList(ByVal m_NewQuery As String)
        'create field list and type arraylists
        Dim command As SqlCommand
        Dim reader As SqlDataReader
        Try
            ' Executing a query to retrieve a fields list for the report
            command = conn.CreateCommand()
            command.CommandText = m_NewQuery
            conn.ConnectionString += conn.ConnectionString & ";integrated security= SSPI"
            conn.Open()
            ' Execute and create a reader for the current command
            reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
            ' For each field in the resultset, add the name to an array list
            ' And add the field type to an array list to set formatting in report
            m_Fields = New ArrayList
            m_Type = New ArrayList
            Dim i As Integer
            For i = 0 To reader.FieldCount - 1
                m_Fields.Add(reader.GetName(i))
                m_Type.Add(Replace(reader.GetFieldType(i).ToString, "System.", ""))
            Next i
            reader.Close()
            conn.Close()
        Catch ex As Exception
            ' ignore
        End Try
    End Sub 'GenerateFieldsList
    
**代码演示** **16**

将 RDL 写入文件

  1. 将代码演示 17 中的代码复制并粘贴到 GenerateFieldsList() 结束语句下面。

    写出到保存在磁盘上的文件,这使您可以在 VS.NET 中将 RDL 导入到报告项目中并进行修改。

    报告定义语言 (RDL) 完整记录在 Reporting Services Books Online 中。它是基于 XML 的架构。熟悉 XML 属性/元素和 HTML 标记的开发人员会发现 RDL 很容易理解。以编程方式生成 RDL 的示例(Walkthrough – Generating RDL Using the .NET Framework)在 Reporting Services Books Online 中。请参见内联代码注释以获得其他解释。

    Public Sub GenerateRdl(ByVal m_ReportName As String)
        ' Open a new RDL file stream for writing
        Dim stream As FileStream
        Dim i As Integer
        Try
        ' If a file with the same name exists, delete it
            If File.Exists(m_ReportName) Then
                File.Delete(m_ReportName)
            End If
        ' Open the file for writing
            stream = File.OpenWrite(m_ReportName)
        ' Create an XML text writer to begin writing to the file
            Dim writer As New XmlTextWriter(stream, Encoding.UTF8)
            ' Causes child elements to be indented
            writer.Formatting = Formatting.Indented
            ' Report element
            writer.WriteProcessingInstruction("xml", "version=""1.0"" encoding=""utf-8""")
            writer.WriteStartElement("Report")
            writer.WriteAttributeString("xmlns", Nothing, "http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition")
            writer.WriteElementString("Width", "6in")
            ' DataSource element
            writer.WriteStartElement("DataSources")
            writer.WriteStartElement("DataSource")
            writer.WriteAttributeString("Name", Nothing, dsName)
            writer.WriteElementString("DataSourceReference", dsName)
            writer.WriteEndElement() ' ConnectionProperties
            writer.WriteEndElement() ' DataSource
            ' DataSet element
            writer.WriteStartElement("DataSets")
            writer.WriteStartElement("DataSet")
            writer.WriteAttributeString("Name", Nothing, "DataSet1")
            ' Query element
            writer.WriteStartElement("Query")
            writer.WriteElementString("DataSourceName", dsName)
            writer.WriteElementString("CommandType", "Text")
            writer.WriteElementString("CommandText", m_query)
            writer.WriteElementString("Timeout", "30")
            writer.WriteEndElement() ' Query
            ' Fields elements
            writer.WriteStartElement("Fields")
            ' Loop through the fields to add field names
            For i = 0 To m_Fields.Count - 1
                writer.WriteStartElement("Field")
                writer.WriteAttributeString("Name", Nothing, m_Fields(i))
                writer.WriteElementString("DataField", Nothing, m_Fields(i))
                writer.WriteEndElement() ' Field
            Next 'fieldName
            ' End previous elements
            writer.WriteEndElement() ' Fields
            writer.WriteEndElement() ' DataSet
            writer.WriteEndElement() ' DataSets
            ' Body element
            writer.WriteStartElement("Body")
            writer.WriteElementString("Height", "5in")
            ' ReportItems element
            writer.WriteStartElement("ReportItems")
            'If report title was specified, create the title text box
            If m_ReportTitle <> "" Then
                writer.WriteStartElement("Textbox")
                writer.WriteAttributeString("Name", Nothing, "Title1")
                writer.WriteStartElement("Style")
                writer.WriteElementString("FontFamily", "Tahoma")
                writer.WriteElementString("FontSize", "18pt")
                writer.WriteElementString("Color", "Navy")
                writer.WriteElementString("FontWeight", "700")
                writer.WriteElementString("TextAlign", "Center")
                writer.WriteEndElement() 'style
                writer.WriteElementString("Top", "0.125in")
                writer.WriteElementString("Height", "0.375in")
                writer.WriteElementString("Width", "6.5in")
                writer.WriteElementString("CanGrow", "true")
                writer.WriteElementString("Value", m_ReportTitle)
                writer.WriteElementString("Left", "0.125in")
                writer.WriteEndElement() 'Textbox
                ' Table element
                writer.WriteStartElement("Table")
                writer.WriteAttributeString("Name", Nothing, "Table1")
                writer.WriteElementString("DataSetName", "DataSet1")
                writer.WriteElementString("Top", ".5in")
                writer.WriteElementString("Left", ".5in")
                writer.WriteElementString("Height", ".25in")
            Else
                ' Table element
                writer.WriteStartElement("Table")
                writer.WriteAttributeString("Name", Nothing, "Table1")
                writer.WriteElementString("DataSetName", "DataSet1")
                writer.WriteElementString("Top", ".125in")
                writer.WriteElementString("Left", ".125in")
                writer.WriteElementString("Height", ".25in")
            End If
           'determine the width of the table by adding the widths of each column together
            Dim width As Integer = 0
            For i = 0 To m_Fields.Count - 1
                Select Case m_Type(i)
                    Case "Decimal", "Int16", "Int32", "Int64", "Boolean"
                        width = width + 0.5
                    Case "DateTime"
                        width = width + 0.75
                    Case Else
                        width = width + 1.5
                End Select
            Next
            writer.WriteElementString("Width", width & "in")
            ' Table Columns
            writer.WriteStartElement("TableColumns")
            For i = 0 To m_Fields.Count - 1
                writer.WriteStartElement("TableColumn")
                Select Case m_Type(i)
                    Case "Decimal", "Int16", "Int32", "Int64", "Boolean"
                        writer.WriteElementString("Width", ".5in")
                    Case "DateTime"
                        writer.WriteElementString("Width", ".75in")
                    Case Else
                        writer.WriteElementString("Width", "1.5in")
                End Select
                writer.WriteEndElement() ' TableColumn
            Next 'fieldName
            writer.WriteEndElement() ' TableColumns
            ' Header Row
            writer.WriteStartElement("Header")
            writer.WriteStartElement("TableRows")
            writer.WriteStartElement("TableRow")
            writer.WriteElementString("Height", ".25in")
            writer.WriteStartElement("TableCells")
            For i = 0 To m_Fields.Count - 1
                writer.WriteStartElement("TableCell")
                writer.WriteStartElement("ReportItems")
                ' Textbox
                writer.WriteStartElement("Textbox")
                writer.WriteAttributeString("Name", Nothing, "Header" + m_Fields(i))
                'Set the background color and other header styles
                writer.WriteStartElement("Style")
                writer.WriteElementString("BackgroundColor", "DarkBlue")
                writer.WriteElementString("Color", "White")
                ' Align cell according to datatype
                Select Case m_Type(i)
                    Case "Decimal", "Int16", "Int32", "Int64"
                        writer.WriteElementString("PaddingRight", "3pt")
                        writer.WriteElementString("TextAlign", "Right")
                    Case Else
                        writer.WriteElementString("TextAlign", "Left")
                End Select
                writer.WriteElementString("FontWeight", "700")
                writer.WriteEndElement() ' End Style
                writer.WriteElementString("Top", "0in")
                writer.WriteElementString("Left", "0in")
                writer.WriteElementString("Height", ".5in")
                Select Case m_Type(i)
                    Case "Int16", "Int32", "Int64", "Boolean", "Decimal"
                        writer.WriteElementString("Width", ".5in")
                    Case "DateTime"
                        writer.WriteElementString("Width", ".75in")
                    Case Else
                        writer.WriteElementString("Width", "1.5in")
                        writer.WriteElementString("CanGrow", "true")
                        writer.WriteElementString("CanShrink", "true")
                End Select
                writer.WriteElementString("Value", m_Fields(i))
                writer.WriteEndElement() ' End Textbox
                writer.WriteEndElement() ' End ReportItems
                writer.WriteEndElement() ' EndTableCell
            Next 'fieldName
            writer.WriteEndElement() ' TableCells
            writer.WriteEndElement() ' TableRow
            writer.WriteEndElement() ' TableRows
            writer.WriteElementString("RepeatOnNewPage", "true")
            writer.WriteEndElement() ' Header
            'writer.WriteElementString("KeepTogether", "true")
            ' Details Row
            writer.WriteStartElement("Details")
            writer.WriteStartElement("TableRows")
            writer.WriteStartElement("TableRow")
            writer.WriteElementString("Height", ".25in")
            writer.WriteStartElement("TableCells")
            ' Loop through the field and type arrays to set formatting & styles
            For i = 0 To m_Fields.Count - 1
                writer.WriteStartElement("TableCell")
                writer.WriteStartElement("ReportItems")
                ' Textbox
                writer.WriteStartElement("Textbox")
                writer.WriteAttributeString("Name", Nothing, m_Fields(i))
                writer.WriteStartElement("Style")
                writer.WriteElementString("PaddingLeft", "2pt")
                ' format and align cell according to datatype
                Select Case m_Type(i)
                    Case "DateTime"
                        writer.WriteElementString("TextAlign", "Left")
                        writer.WriteElementString("Format", "MM/dd/yyyy")
                    Case "Decimal"
                        writer.WriteElementString("PaddingRight", "3pt")
                        writer.WriteElementString("TextAlign", "Right")
                        writer.WriteElementString("Format", "F")
                    Case "Int16", "Int32", "Int64"
                        writer.WriteElementString("PaddingRight", "3pt")
                        writer.WriteElementString("TextAlign", "Right")
                    Case Else
                        writer.WriteElementString("TextAlign", "Left")
                End Select
                writer.WriteEndElement() ' Style
                writer.WriteElementString("Top", "0in")
                writer.WriteElementString("Left", "0in")
                writer.WriteElementString("Height", ".5in")
                ' Create cell width dependent on datatype
                Select Case m_Type(i)
                    Case "Int16", "Int32", "Boolean", "Decimal"
                        writer.WriteElementString("Width", ".5in")
                    Case "DateTime"
                        writer.WriteElementString("Width", ".75in")
                    Case Else
                        writer.WriteElementString("Width", "1.5in")
                        writer.WriteElementString("CanGrow", "true")
                        writer.WriteElementString("CanShrink", "true")
                End Select
                writer.WriteElementString("Value", "=Fields!" + m_Fields(i) + ".Value")
                'writer.WriteElementString("HideDuplicates", "DataSet1")
                writer.WriteEndElement() ' Textbox
                writer.WriteEndElement() ' ReportItems
                writer.WriteEndElement() ' TableCell
            Next 'fieldName
            ' End Details element and children   
            writer.WriteEndElement() ' TableCells
            writer.WriteEndElement() ' TableRow
            writer.WriteEndElement() ' TableRows
            writer.WriteEndElement() ' Details
            ' End table element and end report definition file
            writer.WriteEndElement() ' Table
            writer.WriteEndElement() ' ReportItems
            writer.WriteEndElement() ' Body
            ' Set overall report margins
            writer.WriteElementString("TopMargin", "0.25in")
            writer.WriteElementString("BottomMargin", "0.25in")
            writer.WriteElementString("LeftMargin", "0.25in")
            writer.WriteElementString("RightMargin", "0.25in")
            ' End the report
            writer.WriteEndElement() ' Report
            ' Flush the writer
            writer.Flush()
        Catch ex As Exception
            HandleException(ex)
        Finally
            'Close the stream
            stream.Close()
        End Try
    

End Sub 'GenerateRdl

**代码演示** **17**
  1. 添加代码演示 18 中的 BeginPublishReport() 代码。此代码调用两个方法,一个用于创建数据源,另一个用于发布用 GenerateRDL() 编写的报告。

    Public Sub BeginPublishReport(ByVal ReportName As String)
        RS.Credentials = System.Net.CredentialCache.DefaultCredentials
        Try
    
            'Create the shared data source
            CreateDataSource()
    
            'Publish the report
            PublishReport(ReportName)
    
        Catch ex As Exception
            HandleException(ex)
        End Try
    

End Sub

**代码演示** **18**

“My Reports” 中创建共享数据源

  1. 添加代码演示 19 以创建数据源。有关定义和属性的其他信息,请参见 ReportingService.CreateDataSource Method 主题。

    Public Sub CreateDataSource()
        Dim name As String = dsName
        Dim parent As String = "/" + parentFolder
    
        'Define the data source definition.
        Dim definition As New DataSourceDefinition
        definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
        definition.ConnectString = m_ConnStr
        definition.Enabled = True
        definition.EnabledSpecified = True
        definition.Extension = "SQL"
        definition.ImpersonateUser = True
        definition.ImpersonateUserSpecified = False
        'Use the default prompt string.
        definition.Prompt = Nothing
        definition.WindowsCredentials = True
    
        'Try creating the data source
        Try
            RS.CreateDataSource(name, parent, True, definition, Nothing)
    
        Catch ex As Exception
            HandleException(ex)
        End Try
    

End Sub

**代码演示** **19**

将报告发布到 “My Reports”

  1. 将代码演示 20 复制并粘贴到 CreateDataSource 方法的 End Sub 语句后面。

    Public Sub PublishReport(ByVal reportName As String)
        Try
            'Create a stream reader and open file
            'Could also create a stream without generating an RDL file 
            Dim reader As StreamReader
            Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
    
            'read the file into the definition
            definition = New [Byte](stream.Length) {}
            stream.Read(definition, 0, CInt(stream.Length))
    
            'close the stream
            stream.Close()
    
        Catch ex As IOException
            HandleException(ex)
        End Try
    
        'Try creating the report using values filled
        Try
            Dim myPropertyValue As String = "Table/View Name: " & TableName & " from " & DatabaseName & " on " & ServerName & " server. "
            myPropertyValue += "  Columns Selected: " & OrderList & "."
            myPropertyValue += " Note: This report uses " & dsName & " shared data source."
            'Set the description of the report to list the server name, table 
            ' and fields used
            Dim newProp As New [Property]
            newProp.Name = "Description"
            newProp.Value = myPropertyValue
            Dim props(0) As [Property]
            props(0) = newProp
    
            'Create the report
            'Properties can be passed as Nothing
            warnings = RS.CreateReport(reportName, parentPath, True, definition, props)
    
            'Write warnings to event log
            If Not (warnings Is Nothing) Then
                Dim warning As Warning
                For Each warning In warnings
                    Diagnostics.EventLog.WriteEntry("Report Server", warning.Message, Diagnostics.EventLogEntryType.Warning)
                Next warning
            End If
    
        Catch ex As Exception
            HandleException(ex)
        End Try
    

End Sub

**代码演示** **20**
  1. 添加一般性错误处理程序,如代码演示 21 所示。

    Private Sub HandleException(ByVal exception As Exception)
        'Generic exception handler
        Response.Write(exception.Source & "<br>" & exception.Message & "")
    

End Sub

**代码演示** **21**
  1. 关闭 RDLGenerator.aspx,保存所有更改。

    需要 ReportWizardaspx 和 RDLGenerator.aspx 页面来动态生成连接、查询字符串和 RDL 文件,将定义和数据源发布到报告服务器上,并以指定的输出格式呈现它们。本文的其余章节将介绍如何创建一个页面以允许用户从他们的 My Reports 文件夹中删除报告和数据源。

将 ManagingReports 窗体添加到项目中

此页面的目的在于演示如何循环访问 My Reports 文件夹中的报告和数据源集合,检查依赖性,以及同时删除多个选定的对象(如果不存在依赖性)。

  1. 将新的 Web 窗体添加到项目中,并将其命名为 ManagingReports.aspx。

  2. 在设计器中打开页面并选择 HTML 视图。

  3. 删除 <form></form> 标记。

  4. 复制并 Paste as HTML 下面的 html:

    <script>
    

function OpenWin() { window.open("/Reports/Pages/Folder.aspx?ItemPath=%2fMy+Reports&IsDetailsView=False","MyReports") } </script> <form id="Form1" method="post" runat="server"> <P dir="ltr" style="MARGIN-RIGHT: 0px"><asp:label id="Label3" style="Z- INDEX: 104; LEFT: 176px; POSITION: absolute; TOP: 8px" runat="server" Height="24px" BackColor="Transparent" Font-Italic="True" BorderColor="White" ForeColor="ForestGreen" Font-Size="22pt" Font- Bold="True" Width="384px">Your Reports and Data Sources</asp:label><asp:label id="Label2" style="Z-INDEX: 103; LEFT: 408px; POSITION: absolute; TOP: 128px" runat="server" ForeColor="Navy" Font-Size="Medium" Font-Bold="True" Width="280px">Data Sources You Have Created:</asp:label><asp:checkboxlist id="chkReportList" style="Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP: 160px" runat="server" Height="24px" Width="296px" BorderWidth="1pt" BorderStyle="Inset"></asp:checkboxlist><asp:label id="Label1" style="Z- INDEX: 102; LEFT: 24px; POSITION: absolute; TOP: 128px" runat="server" ForeColor="Navy" Font-Size="Medium" Font-Bold="True" Width="288px">Reports You Have Created:</asp:label><asp:checkboxlist id="chkDSList" style="Z-INDEX: 105; LEFT: 400px; POSITION: absolute; TOP: 160px" runat="server" Width="296px" BorderWidth="1pt" BorderStyle="Inset"></asp:checkboxlist><asp:button id="btnDel" style="Z-INDEX: 106; LEFT: 144px; POSITION: absolute; TOP: 64px" runat="server" Text="Delete Selected Items"></asp:button></P> <HR style="Z-INDEX: 107; LEFT: 16px; POSITION: absolute; TOP: 104px" width="100%" SIZE="1" color="#006600"> <asp:label id="lblWarning" style="Z-INDEX: 109; LEFT: 32px; POSITION: absolute; TOP: 488px" runat="server" Height="24px" ForeColor="#C00000" Font-Size="10pt" Font-Bold="True" Width="688px" Visible="False">***Reports with subscriptions or data sources with reports using them must be deleted from the My Reports folder</asp:label> <INPUT style="Z-INDEX: 108; LEFT: 384px; WIDTH: 208px; POSITION: absolute; TOP: 64px; HEIGHT: 24px" type="button" value="View My Reports Folder" onclick="OpenWin()"> </form>

现在,您的页面应该如下所示:

adhocrepsr_figure19

图 19

将代码添加到 ManageReports 页面中

  1. 右键单击并选择 View Code。将代码演示 22 中的注释和 Imports 语句复制并粘贴到 Public Class ManageReports 上面,以创建 Imports 语句和注释。

    '=====================================================================
    

' Copyright (C) Microsoft Corporation. All rights reserved. ' ' This source code is intended only as a supplement to Microsoft ' Development Tools and/or on-line documentation. See these other ' materials for detailed information regarding Microsoft code samples. ' ' THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY ' KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE ' IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A ' PARTICULAR PURPOSE. '=====================================================================*/ Imports Microsoft.SqlServer.ReportingServices

**代码演示** **22**
  1. 接着,将代码演示 23 中的变量声明复制并粘贴到 Public Sub Page_Load() 上面。

    Private rs As New ReportingService ' Create new Reporting Service
    Private _returnedItems() As CatalogItem ' Create catalog item container
    

Private parentPath = "/My Reports" ' Path of reports..user must have the right to create and modify content.

**代码演示** **23**
  1. 用代码演示 24 中的代码替换 Page_Load 方法。

    此代码检查该页面是否为 Post Back,如果不是,则调用 FindMyReports()

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Check if page is posted back
        If Not IsPostBack Then ' begin listing reports and data sources
            FindMyReports()
        End If
    

End Sub

**代码演示** **24**
  1. 将代码演示 25 中的 FindMyReports() 代码复制并粘贴到 End Class 上面。

    有关更多信息,请参见 ReportingService.FindItems Method 主题以及 Reporting Services/Samples/Applications 文件夹中的 FindRenderSave 应用程序。请参见内联注释以获得其他解释。

    Private Sub FindMyReports()
        ' Clear any existing items
        chkReportList.Items.Clear()
        chkDSList.Items.Clear()
        ' Create a new proxy to the web service
        rs = New ReportingService
        ' Authenticate to the Web service using Windows credentials
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials
        ' Assign the URL of the Web service
        rs.Url = "http://localhost/ReportServer/ReportService.asmx"
        ' Create SearchCondition containers
        Dim conditions() As SearchCondition
        Dim condition As New SearchCondition
        ' Fill search conditions
        ' Searchable properties are Name, Description, CreatedBy,  
        ' CreationDate,ModifiedBy, and ModifiedDate
        Try
            condition.Condition = ConditionEnum.Contains 'Contains or Equals
            condition.ConditionSpecified = True 'Must be set to true
            condition.Name = "CreatedBy" 'Searching the My Reports folder for all objects
                                         'created by the current user
            condition.Value = System.Security.Principal.WindowsIdentity.GetCurrent.Name
            conditions = New SearchCondition(0) {}
            conditions(0) = condition
            ' Get the return catalog items
            _returnedItems = rs.FindItems(parentPath, BooleanOperatorEnum.Or, conditions)
            ' Verify something is returned
            If Not (_returnedItems Is Nothing) AndAlso _returnedItems.Length <> 0 Then
                Dim ci As CatalogItem
                Dim _ci As CatalogItem
                Dim su As Subscription
                ' Loop through the report and data source catalog items returned and add 
                ' them to their corresponding list collections
                For Each ci In _returnedItems
                    If ci.Type = ItemTypeEnum.Report Then
    
                ' If subscriptions exist for this report add asterisks to name
                For Each su In rs.ListSubscriptions(parentPath & "/" & ci.Name, condition.Value.ToString)
                     ci.Name = ci.Name & "***"
               ' If a subscription is found, exit the loop. There is no need to continue.
                     Exit For
                Next
                ' Fill the chkReportList checkbox group with the report names
                chkReportList.Items.Add(ci.Name)
                ' if it's not a report, check to see if it's a data source
                ElseIf ci.Type = ItemTypeEnum.DataSource Then
    
                ' If reports depend on this data source add asterisks to name
                For Each _ci In rs.ListReportsUsingDataSource(parentPath & "/" & ci.Name)
                    ci.Name = ci.Name & "***"
                ' If a report is found, exit the loop. There is no need to continue.
                    Exit For
                Next
                ' Fill the chkDSList checkbox group with the data sources
                chkDSList.Items.Add(ci.Name)
            End If
            Next ci
            End If
        Catch exception As Exception
            HandleException(exception)
        End Try
    

End Sub

**代码演示** **25**
  1. 将代码演示 26 中的代码复制并粘贴到 btnDel 单击事件的 End Class 声明上面。

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        rs = New ReportingService
    
        ' Authenticate to the Web service using Windows credentials
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    
        ' Assign the URL of the Web service
        rs.Url = "http://localhost/ReportServer/ReportService.asmx"
    
        Dim li As New ListItem
    
        ' Get the Report List items
        For Each li In chkReportList.Items
        ' loop through each report list item & check if it is selected
            If li.Selected Then
                ' If report has subscriptions set the warning label to visible
                ' and DO NOT delete
                If li.Text.EndsWith("***") Then
                    lblWarning.Visible = True
                Else ' else delete the report
                    rs.DeleteItem(parentPath & "/" & li.Text)
                End If ' end text.EndsWith
            End If  ' end li.Selected
        Next  ' list item
        ' Loop through each data source list item & check if it is selected
        For Each li In chkDSList.Items
            If li.Selected Then
            'If the data source has reports depending on it set the warning label
            'to visible
                If li.Text.EndsWith("***") Then
                    lblWarning.Visible = True
                Else ' else delete the data source
                    rs.DeleteItem(parentPath & "/" & li.Text)
                End If
            End If
        Next
        ' Regenerate report and data source lists
        FindMyReports()
    End Sub
    
**代码演示** **26**
  1. HandleException 方法中添加您选择的通用异常处理程序。可以复制并粘贴代码演示 27 或创建自己的代码。

    Private Sub HandleException(ByVal exception As Exception)
        'Generic exception handler
        Response.Write(exception.Source & "<p>" & exception.Message)
    

End Sub

**代码演示** **27**
  1. 关闭该页面并保存所有的更改。

  2. F5 构建应用程序并进行测试。

    有时会碰到 vbc : Command line error BC2017

    重新编译似乎能解决这一问题。如果您接收到此编译错误,请参考:Microsoft Knowledge Base Article - 319976

小结

使用 .NET Framework 来编写报告服务程序非常简单,并且有丰富的文档支持,与安装时附带的示例一样。希望此示例应用程序为您提供了补充知识,使您在实现企业报告环境时能得到提高。MSSRS 小组将继续努力改善开发人员和用户体验,我们期待着您的意见和反馈。

转到原英文页面