Click here to Skip to main content
15,868,066 members
Articles / Database Development / SQL Server

A Simple GUI Tool for SQL 2005 Reports Deployment Without using BI Development Studio

Rate me:
Please Sign up or sign in to vote.
4.79/5 (12 votes)
25 May 2008CPOL3 min read 76.3K   1.1K   41   15
A Simple GUI Tool for SQL 2005 Reports deployment without using BI Development Studio

Introduction

SQL 2005 Reports deployment is done either by choosing the deploy option in the BI Development studio, then specifying the target Server URL and the target Report Folder, which is the standard way, or deploying the reports manually by opening the Report Server URL, and creating the DataSource, the parent folder where you will add the datasource and report in, and then attaching the Reports manually, which is still not that realistic as you don’t have to do all that each time you want to deploy a report. Yet the first option is not always possible, as you may wish to deploy a report while you don’t have the BI development Studio installed on your machine. In this article, I expect that you know both techniques before reading, as I won’t be explaining everything in detail.

I made a simple GUI tool that helps in deploying the SQL Reports by consuming the web service named reportservice.asmx that the SQL Reporting Server produces and is hosted by the IIS of the Report server. Usually the Report Server has the URL as: http://<server name>/ReportServer and sometimes as http://<server name>/ReportServer$SQLExpress, or http://<server name>/ReportServer$SQL2005. This is according to what you named the SQL server Instance installed. Anyway, let’s get back to the main point. The webservice within the ReportsServer web application hosted in the IIS of the server machine actually does everything you need to deploy the Reports you want; I guess it is better to have a look at the ReportingService2005 Methods.

Anyway... let’s start having a look at the application and the code. First I will be investigating the application, and what everything means, then we will have a quick review on the main parts of the code.

How to Use the Tool?

Once you run the application, a window will appear asking you to enter the Report Manager URL, as to connect to its web service, i.e. it’s the same URL you type in the target server URL property when using the BI Development Studio for deployment. The URL you entered is saved then so that you don’t have to type it in each and every time you run the application. I use this URL, so as to modify the WebService I am consuming, to connect to the URL you will choose. To know what I mean, just have a look at this article by Christopher G. Lasater, that describes what I did actually (Dynamic Web Service).

After the previous step is done, the Web Service now is made ready to do the rest.

Next, to get started, you have to define the following:

  1. The connection string as the data source.
  2. The Data source name.
  3. The Report source (Physical Path in your machine).
  4. The Report name (The name that you will give to the Report you just browsed for).
  5. Finally, the folder (a logical folder in the Report Server not a physical folder in your machine) in which you will save the Reports and the data sources you want.

Finally, after running the Tool successfully, then opening the Report Manager, you will find a new Folder as you named in the tool, and has the DataSources and Reports you made as below.

Code Overview

If you had a look at the ReportingService2005 Methods, you will find the two main functions that you will be using mainly. They are the CreateDataSource function, and the CreateReport function. 

This is the part which creates the DataSource:

C#
public void CreateReportDataSource
	(string name, string extension, string connectionString)
        {
            listView1.Items.Add("Creating Data Source [" + txt_DataSource.Text + "]");
            ReportService.DataSourceDefinition definition = 
		new ReportServerInstaller.ReportService.DataSourceDefinition();
            definition.CredentialRetrieval = 
		ReportService.CredentialRetrievalEnum.Integrated;
            definition.ConnectString = connectionString;
            definition.Enabled = true;
            definition.Extension = extension;

            try
            {
                rs.CreateDataSource(name, FolderName, 
		chk_DataSourceOverwrite.Checked, definition, null);
                listView1.Items.Add("Data source: [" + name + "] created successfully.");
                TS_Progress.PerformStep();
            }
            catch (Exception ex)
            {
                listView1.Items.Add("ERROR creating data source: " + name);
                throw ex;
            }
        }

And here is the part which publishes the report:

C#
private void PublishReport(string reportName)
        {
            listView1.Items.Add("Publishing Report [" + reportName + "]");
            FileStream stream = File.OpenRead(ReportSource);
            definition = new byte[stream.Length];
            stream.Read(definition, 0, int.Parse(stream.Length.ToString()));
            stream.Close();

            try
            {
                rs.CreateReport(txt_ReportName.Text, FolderName, 
			chk_ReportOverwrite.Checked, definition, null);
                listView1.Items.Add("Report: 
			[" + reportName + "] created successfully.");
                TS_Progress.PerformStep();
            }
            catch (Exception ex)
            {
                listView1.Items.Add("ERROR creating Report: " + reportName);
                throw ex;
            }
        }

I have attached an installer for easy use of the tool. You don’t have to configure anything before use. Just ensure that you have the correct SQL Manager URL typed in the first form, as this does everything. Happy deployment! ;)

History

  • 25th May, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) NTP Software - www.ntpsoftware.com
Egypt Egypt
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionIs there any GUI Tool for SQL Server 2008 Reports deployment without using visual studio 2008 Pin
chaitali kudale4-Jun-12 2:08
chaitali kudale4-Jun-12 2:08 
GeneralQuestion: missing parameter after deploying via this Utility. Pin
Jayant Patel6-Jun-11 7:46
Jayant Patel6-Jun-11 7:46 
GeneralAdding SSRS 2008 Web Reference in Visual Studio 2008 Pin
seanjr12-Apr-09 4:37
seanjr12-Apr-09 4:37 
GeneralRe: Adding SSRS 2008 Web Reference in Visual Studio 2008 [modified] Pin
Ahmed IG12-Apr-09 5:29
Ahmed IG12-Apr-09 5:29 
GeneralNice work, see this tool also, Pin
Peter Bons22-Jul-08 0:09
Peter Bons22-Jul-08 0:09 
Generalimpressive Pin
originalheba17-Jun-08 2:01
originalheba17-Jun-08 2:01 
GeneralNice One Pin
Amr Elsehemy ®27-May-08 22:55
Amr Elsehemy ®27-May-08 22:55 
GeneralRe: Nice One Pin
Ahmed IG28-May-08 6:29
Ahmed IG28-May-08 6:29 
GeneralGood Job Pin
Islam ElDemery26-May-08 14:19
Islam ElDemery26-May-08 14:19 
GeneralRe: Good Job Pin
Ahmed IG28-May-08 6:28
Ahmed IG28-May-08 6:28 
GeneralGood post Pin
AE~126-May-08 3:11
AE~126-May-08 3:11 
GeneralThe source link is corrupted !! Pin
Wael R. Mansour25-May-08 21:02
Wael R. Mansour25-May-08 21:02 
I think the source code of this application is corrupted, I hope to update the link or reupload the source again..

Wael R. Roushdy

AnswerRe: The source link is corrupted !! [modified] Pin
Ahmed IG26-May-08 1:38
Ahmed IG26-May-08 1:38 
General[Message Removed] Pin
Mojtaba Vali25-May-08 19:00
Mojtaba Vali25-May-08 19:00 
GeneralRe: nice article Pin
Ahmed IG26-May-08 1:56
Ahmed IG26-May-08 1:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.