Tight integration with the .NET environment is the
important feature for the popularity of Microsoft SQL Server 2005. As a result,
it is possible to perform the some of the common tasks by using different options. This allows
the developers/administrators to do their work by selecting the options that
they are comfortable with. For example, you can manage/administer the report sever using
the SQL Server Management Studio OR the web-based Report Manager. In this
article, will look at the various ways for deploying reports on the report server. [It is
assumed that the reports are already created]
How to deploy reports?
It is possible to deploy the reports in 3 ways:
- Using the BI Development studio.
- Using the SQL Server Management Studio.
- Programmatically. [Using the RS.EXE utility]
Using BI Development studio:
- Open the Project Properties dialog-box and set the TargetServerURL. It is the URL of the report server, on which the reports will be deployed. [Figure 1]
- Right-Click on the Project and select Deploy [Figure 2]. This will deploy the reports and Data-sources defined in the project on the specified report server.
Using SQL Server Management Studio:
- Connect to the Reporting services database.
- Right-click on Home and select the desired options. [Figure3]
Select New Folder to create a new folder on the server. Select New Data Source option for defining a data-source for the reports. Select Import File for adding/deploying the reports on the server.
Using the RS.EXE utility:
- It is a Command-line utility.
- Used for performing Deployment and Administrative tasks programmatically.
- Executes the RSS file. [Reporting Services Script file]
- Command to execute:
rs i MyRSS.rss -s http://localhost/reportserver
- Parameters can also be passed. These parameters are the global variables in the RSS script file. Multiple parameters can be passed.
rs i MyRSS.rss -s http://localhost/reportserver -v param1="value1" -v param2="value2"
How to write the RSS file:
- VB.NET code file [with rss extension].
- Can contain user-defined function and global parameters. The value for the global parameters has to be supplied from the command line.
- It should have a Main () procedure.
- By default, the namespaces System.IO, System.Xml and System.Web.Services are included.
- A reference variable rs for the reporting services web service is generated automatically when the script is executed using the RS.EXE utility. This variable allows access to all the functionality of web service class library [of reporting services].
Extract the MyReport.zip file in the D\MyReports folder.
The sample RSS file creates a data-source, which points to the Northwind database on the (local) sql server instance using Windows authentication. The function used is: CreateDataSource(name, extension, connectionstring)
CreateDataSource("MyDataSource", "SQL", "Data Source= (local);Initial Catalog=Northwind")
It also publishes a sample report using the CreateReport function.
CreateReport(NameofReport, ParentFolder, Overwrite, ReportDefinition, Properties)
CreateReport(MyReport, /MyReports, False, definition, Nothing)
If report contains any external images, then it has to be also deployed as a resource. This is not required if the image is embedded in the report. The function used is: CreateResource(ImageName, ParentFolder, Overwrite, Definition, MIME type, Properties)
CreateResource(ImageName, parentPath, False, definition, "image/jpeg",Nothing)
For executing the rss file, open the command prompt and set the directory to D:\MyReports and run the following command:
rs -i MyRSS.rss -s http://localhost/ReportServer
Output will look like:
After successful execution, MyReports folder will be created on the report server. It can be viewed using the Report Manager.
The reports can be deployed using the above 3 methods.
Reporting Services script files can be used to run any of the Report Server Web
service operations like creating schedules, subscriptions, security settings,
etc. Replicating report server would be easy using the Reporting Services