很多朋友都用过reporting service,以及使用有关工具制作报表(文件格式为rdl),通常情况下,这些报表需要部署到报表服务器,然后再统一访问。当报表比较多的时候,我们自然而然地会想,是否有什么方法批量上传报表么?答案是肯定的。
第一种方式,可以使用reporting service自带的一个rs的工具,通过提供一个脚本文件(扩展名为rss),就可以实现该需求。微软提供了一个范例如下
C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples\PublishSampleReports.rss
大致的脚本和使用方式如下
可以看出,这些所谓的脚本是用VB.NET编写的。其实就是.NET编程,但因为它是所谓脚本的方式,反倒有些地方不是很自然了。例如代码中的rs在哪里定义的,是什么类型呢?我的理解是这个rs其实就是指向了reporting service所提供的那些web service。但是的确有点费解。
'=============================================================================
' File: PublishSampleReports.rss
'
' Summary: Demonstrates a script that can be used with RS.exe to
' publish the sample reports that ship with Reporting Services.
'
'---------------------------------------------------------------------
' This file is part of Microsoft SQL Server Code Samples.
'
' 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.
'=============================================================================
'
' 1.0 Documentation
'
' Read the following in order to familiarize yourself with the sample script.
'
' 1.1 Overview
'
' This sample script uses a script file (.rss) and the script environment to run
' Web service operations on a specified report server. The script creates a folder
' that you specify as a command-prompt variable using the 杤 switch, and then
' publishes the sample reports that ship with Reporting Services to a report server.
' Depending on the location of your sample reports, you may need to modify the
' value of the filePath variable, which references the path to your sample reports.
'
' 1.2 Script Variables
'
' Variables that are passed on the command line with the -v switch:
'
' (a) parentFolder - corresponds to the folder that the script creates and uses
' to contain your published reports
'
' 1.3 Sample Command Lines
'
'
' 1.3.1 Use the script to publish the sample reports to an AdventureWorks Sample Reports folder.
'
' rs -i PublishSampleReports.rss -s http://myserver/reportserver
'
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim parentFolder As String = "AdventureWorks Sample Reports"
Dim parentPath As String = "/" + parentFolder
Dim filePath As String = "C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports\"
Public Sub Main()Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder {0} created successfully", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'Create the AdventureWorks shared data source
CreateSampleDataSource("AdventureWorks", "SQL", "data source=(local);initial catalog=AdventureWorks")
CreateSampleDataSource("AdventureWorksDW", "OLEDB-MD", _
"data source=localhost;initial catalog=Adventure Works DW")
'Publish the sample reports
PublishReport("Company Sales")
PublishReport("Employee Sales Summary")
PublishReport("Product Catalog")
PublishReport("Product Line Sales")
PublishReport("Sales Order Detail")
PublishReport("Territory Sales Drilldown")
End Sub
Public Sub CreateSampleDataSource()Sub CreateSampleDataSource(name As String, extension As String, connectionString As String)
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = extension
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parentPath, False, definition, Nothing)
Console.WriteLine("Data source {0} created successfully", name)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Public Sub PublishReport()Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
Try
warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
第二种方式,自己编写一个工具来完成更加丰富的控制。这里可以用任何的语言,只要你熟悉即可。
这里的关键在于,添加对reporting service的web service的引用。下面是它所有的方法
"ReportingService2005" 说明
文档
The Reporting Services Web Service enables you to manage a report server and its contents including server settings, security, reports, subscriptions, and data sources.
方法
- CancelBatch ( )
- CancelJob ( JobID As string ) As boolean
- CreateBatch ( ) As string
- CreateDataDrivenSubscription ( Report As string , ExtensionSettings As ExtensionSettings , DataRetrievalPlan As DataRetrievalPlan , Description As string , EventType As string , MatchData As string , Parameters As ArrayOfParameterValueOrFieldReference ) As string
- CreateDataSource ( DataSource As string , Parent As string , Overwrite As boolean , Definition As DataSourceDefinition , Properties As ArrayOfProperty )
- CreateFolder ( Folder As string , Parent As string , Properties As ArrayOfProperty )
- CreateLinkedReport ( Report As string , Parent As string , Link As string , Properties As ArrayOfProperty )
- CreateModel ( Model As string , Parent As string , Definition As base64Binary , Properties As ArrayOfProperty ) As ArrayOfWarning
- CreateReport ( Report As string , Parent As string , Overwrite As boolean , Definition As base64Binary , Properties As ArrayOfProperty ) As ArrayOfWarning
- CreateReportHistorySnapshot ( Report As string ) As string
- CreateResource ( Resource As string , Parent As string , Overwrite As boolean , Contents As base64Binary , MimeType As string , Properties As ArrayOfProperty )
- CreateRole ( Name As string , Description As string , Tasks As ArrayOfTask )
- CreateSchedule ( Name As string , ScheduleDefinition As ScheduleDefinition ) As string
- CreateSubscription ( Report As string , ExtensionSettings As ExtensionSettings , Description As string , EventType As string , MatchData As string , Parameters As ArrayOfParameterValue ) As string
- DeleteItem ( Item As string )
- DeleteReportHistorySnapshot ( Report As string , HistoryID As string )
- DeleteRole ( Name As string )
- DeleteSchedule ( ScheduleID As string )
- DeleteSubscription ( SubscriptionID As string )
- DisableDataSource ( DataSource As string )
- EnableDataSource ( DataSource As string )
- ExecuteBatch ( )
- FindItems ( Folder As string , BooleanOperator As BooleanOperatorEnum , Conditions As ArrayOfSearchCondition ) As ArrayOfCatalogItem
- FireEvent ( EventType As string , EventData As string )
- FlushCache ( Report As string )
- GenerateModel ( DataSource As string , Model As string , Parent As string , Properties As ArrayOfProperty ) As ArrayOfWarning
- GetCacheOptions ( Report As string ) As boolean
- GetDataDrivenSubscriptionProperties ( DataDrivenSubscriptionID As string ) As string
- GetDataSourceContents ( DataSource As string ) As DataSourceDefinition
- GetExecutionOptions ( Report As string ) As ExecutionSettingEnum
- GetExtensionSettings ( Extension As string ) As ArrayOfExtensionParameter
- GetItemDataSourcePrompts ( Item As string ) As ArrayOfDataSourcePrompt
- GetItemDataSources ( Item As string ) As ArrayOfDataSource
- GetItemType ( Item As string ) As ItemTypeEnum
- GetModelDefinition ( Model As string ) As base64Binary
- GetModelItemPermissions ( Model As string , ModelItemID As string ) As ArrayOfString
- GetModelItemPolicies ( Model As string , ModelItemID As string ) As ArrayOfPolicy
- GetPermissions ( Item As string ) As ArrayOfString3
- GetPolicies ( Item As string ) As ArrayOfPolicy
- GetProperties ( Item As string , Properties As ArrayOfProperty ) As ArrayOfProperty
- GetRenderResource ( Format As string , DeviceInfo As string ) As base64Binary
- GetReportDefinition ( Report As string ) As base64Binary
- GetReportHistoryLimit ( Report As string ) As int
- GetReportHistoryOptions ( Report As string ) As boolean
- GetReportLink ( Report As string ) As string
- GetReportParameters ( Report As string , HistoryID As string , ForRendering As boolean , Values As ArrayOfParameterValue , Credentials As ArrayOfDataSourceCredentials ) As ArrayOfReportParameter
- GetResourceContents ( Resource As string ) As base64Binary
- GetRoleProperties ( Name As string ) As ArrayOfTask
- GetScheduleProperties ( ScheduleID As string ) As Schedule
- GetSubscriptionProperties ( SubscriptionID As string ) As string
- GetSystemPermissions ( ) As ArrayOfString3
- GetSystemPolicies ( ) As ArrayOfPolicy
- GetSystemProperties ( Properties As ArrayOfProperty ) As ArrayOfProperty
- GetUserModel ( Model As string , Perspective As string ) As base64Binary
- InheritModelItemParentSecurity ( Model As string , ModelItemID As string )
- InheritParentSecurity ( Item As string )
- ListChildren ( Item As string , Recursive As boolean ) As ArrayOfCatalogItem
- ListDependentItems ( Item As string ) As ArrayOfCatalogItem
- ListEvents ( ) As ArrayOfEvent
- ListExtensions ( ExtensionType As ExtensionTypeEnum ) As ArrayOfExtension
- ListJobs ( ) As ArrayOfJob
- ListModelDrillthroughReports ( Model As string , ModelItemID As string ) As ArrayOfModelDrillthroughReport
- ListModelItemChildren ( Model As string , ModelItemID As string , Recursive As boolean ) As ArrayOfModelItem
- ListModelPerspectives ( Model As string ) As ArrayOfModelCatalogItem
- ListReportHistory ( Report As string ) As ArrayOfReportHistorySnapshot
- ListRoles ( SecurityScope As SecurityScopeEnum ) As ArrayOfRole
- ListScheduledReports ( ScheduleID As string ) As ArrayOfCatalogItem
- ListSchedules ( ) As ArrayOfSchedule
- ListSecureMethods ( ) As ArrayOfString
- ListSubscriptions ( Report As string , Owner As string ) As ArrayOfSubscription
- ListSubscriptionsUsingDataSource ( DataSource As string ) As ArrayOfSubscription
- ListTasks ( SecurityScope As SecurityScopeEnum ) As ArrayOfTask
- Logoff ( )
- LogonUser ( userName As string , password As string , authority As string )
- MoveItem ( Item As string , Target As string )
- PauseSchedule ( ScheduleID As string )
- PrepareQuery ( DataSource As DataSource , DataSet As DataSetDefinition ) As DataSetDefinition
- RegenerateModel ( Model As string ) As ArrayOfWarning
- RemoveAllModelItemPolicies ( Model As string )
- ResumeSchedule ( ScheduleID As string )
- SetCacheOptions ( Report As string , CacheReport As boolean )
- SetDataDrivenSubscriptionProperties ( DataDrivenSubscriptionID As string , ExtensionSettings As ExtensionSettings , DataRetrievalPlan As DataRetrievalPlan , Description As string , EventType As string , MatchData As string , Parameters As ArrayOfParameterValueOrFieldReference )
- SetDataSourceContents ( DataSource As string , Definition As DataSourceDefinition )
- SetExecutionOptions ( Report As string , ExecutionSetting As ExecutionSettingEnum )
- SetItemDataSources ( Item As string , DataSources As ArrayOfDataSource )
- SetModelDefinition ( Model As string , Definition As base64Binary ) As ArrayOfWarning
- SetModelDrillthroughReports ( Model As string , ModelItemID As string , Reports As ArrayOfModelDrillthroughReport )
- SetModelItemPolicies ( Model As string , ModelItemID As string , Policies As ArrayOfPolicy )
- SetPolicies ( Item As string , Policies As ArrayOfPolicy )
- SetProperties ( Item As string , Properties As ArrayOfProperty )
- SetReportDefinition ( Report As string , Definition As base64Binary ) As ArrayOfWarning
- SetReportHistoryLimit ( Report As string , UseSystem As boolean , HistoryLimit As int )
- SetReportHistoryOptions ( Report As string , EnableManualSnapshotCreation As boolean , KeepExecutionSnapshots As boolean )
- SetReportLink ( Report As string , Link As string )
- SetReportParameters ( Report As string , Parameters As ArrayOfReportParameter )
- SetResourceContents ( Resource As string , Contents As base64Binary , MimeType As string )
- SetRoleProperties ( Name As string , Description As string , Tasks As ArrayOfTask )
- SetScheduleProperties ( Name As string , ScheduleID As string , ScheduleDefinition As ScheduleDefinition )
- SetSubscriptionProperties ( SubscriptionID As string , ExtensionSettings As ExtensionSettings , Description As string , EventType As string , MatchData As string , Parameters As ArrayOfParameterValue )
- SetSystemPolicies ( Policies As ArrayOfPolicy )
- SetSystemProperties ( Properties As ArrayOfProperty )
- UpdateReportExecutionSnapshot ( Report As string )
- ValidateExtensionSettings ( Extension As string , ParameterValues As ArrayOfParameterValueOrFieldReference ) As ArrayOfExtensionParameter
最后我开发的一个自己使用的小工具,如图所示。
该工具可以对一个目录下,所有报表以及数据源进行上传(可以递归子文件夹),同时整个程序是多线程的。