Click here to Skip to main content
15,867,704 members
Articles / Database Development / SQL Server / SQL Server 2008

Deploying Reports in Reporting Services Programmatically

Rate me:
Please Sign up or sign in to vote.
4.75/5 (11 votes)
30 Oct 2009CPOL3 min read 98.6K   2.7K   40   17
How to deploy your reports programmatically in away like BIDS

Introduction

May be it’s my bad luck may be something else, but I didn’t find a complete post how to deploy Reports, and Data Sources in Reporting Services in away like BIDS.

First it’s a tool I developed to automate the deployment of BI solution (Creating Data warehouse, installing SSIS packages, creating Jobs, create Cubes and installing reports on Reporting Services Server).

Body

Herein, I'll talk about the last thing which is deploying reports. P.S: It's my way in designing such task (Installing reports on Reporting Services Server) and it's not standard or anything else just (follow your heart :)) Let's begin, I assume you, me, or anybody else has these 3 XML files one for folders, one for data sources and one for reports.

Folders XML File Scheme

Name: Folder name to be created on Reporting Services.
ParentFolder: '/' means on the top == no parent folder.

Data Sources' XML Scheme

Name
Folder
Description
HideInListView
Enabled
ConnectionString
Extension
CredentialRetrieval
WindowsCredentials
ImpersonateUser
ImpersonateUserSpecified
Prompt
UserName
Password
EnabledSpecified
Name: Data Source name to be created on Reporting Services.
Folder: The folder in which Data Source should be in, if we use '/' means on the top == no parent folder.
Description: Data Source Description.
HideInListView: True to hide it in the Reporting Services, otherwise False.
Enabled: True to be enabled, otherwise not enabled.
ConnectionString: Data Source connection string.
Extension: Configured according to the provider for more details see below table...
ProviderExtension
Microsoft SQL ServerSQL
OLE DBOLEDB
Microsoft SQL Server Analysis ServicesOLEDB-MD
OracleORACLE
ODBCODBC
XMLXML
SAP NetWeaver BISAPBW
Hyperion EssbaseESSBASE
CredentialRetrieval: How Data Source will retrieve the credential.
WindowsCredentials: True to use Windows credential otherwise it'd use the credential provided in this XML (Username, and Password).
ImpersonateUser: Indicates whether the report server attempts to impersonate a user by using stored credentials after a data processing extension has established an authenticated connection to a data source.
ImpersonateUserSpecified: Gets or sets a value that indicates whether the ImpersonateUser property is specified.
Prompt: Gets or sets the prompt that the report server displays to the user when prompting for credentials.
UserName: Gets or sets the user name that the report server uses to connect to a data source.
Password: Sets the password that the report server uses to connect to a data source. Write-only.
EnabledSpecified: Gets or sets a value that indicates whether the Enabled property is specified.
More details on these properties http://msdn.microsoft.com/en-us/library/reportservice2005.datasourcedefinition_properties.aspx

Reports' XML Scheme

Name: Report Name.
Path: .RDL file path.
Folder: The folder in which Report should be in, if we use '/' means on the top == no parent folder.
DataSource: Report's Data Source name of Reporting Services. And these configuration keys

Configuration keys

ReportsXMLFilePath: Reports' XML File Path
DataSourcesXMLFilePath: Data Sources' XML File Path
FoldersXMLFilePath: Folders' XML File Path
ReportingServerURL: URL of Reporting Services

Open visual studio and create a C# console application (we don't need any interaction with user everything configured in the application configuration file) From the project main menu Add Web Reference or Add Service Reference then Advanced then Add Web Reference...

Add Web Reference

Add Reporting Services Reference

URL: http://{Server-Name}/reportserver/ReportService.asmx
Web reference name: Give it meaningful name..

What we did is adding web service by which we can talk to Reporting Services to ask it to do something like (create report, create data source, etc...). Let's write some very simple C# code We have method called DeployReports this method calls 3 other methods in order (CreateFolders, CreateDataSources, and CreateReports)

C#
/// <summary>
/// Deploys Folders, DataSources, and Reports in Reporting Services by values configured in the application configuration file.
/// </summary>
private void DeployReports()
{
CreateFolders(
ConfigurationSettings.AppSettings["FoldersXMLFilePath"]);
CreateDataSources(
ConfigurationSettings.AppSettings["DataSourcesXMLFilePath"]);
CreateReports(Report.GetReports(
ConfigurationSettings.AppSettings["ReportsXMLFilePath"]));
}
/// <summary>
/// Creates Folder in Reporting Services.
/// </summary>
/// <param name="folderXMLFilePath"> XML file path holds folder information.</param>
private void CreateFolders(string folderXMLFilePath)
{
ReportingService reportingServicesClient =
new ReportingService(); 
reportingServicesClient.Credentials = System.Net.CredentialCache.DefaultCredentials; //default credential who runs the application
XDocument xmlDoc = XDocument.Load(folderXMLFilePath); //loads XML file to XDocument object
try
{
var result = from c in xmlDoc.Descendants("Folder") //gets Folder sections in the XMLDocument object (xmlDoc) 
select new //foreach <Folder> node get the value of <Name> node and <ParentFolder> node.
{
name = (string)c.Element("Name").Value,
parentFolder = (string)c.Element("ParentFolder").Value
};
foreach (var row in result)
{
reportingServicesClient.CreateFolder(row.name, row.parentFolder, null); //Creates new folder on the Reporting Service Server, it takes folder name and the parent folder name if parent folder name = '/' so there's no parent folder
Logging.Log(string.Format("Folder {0} created successfully", row.name)); //logs in case of success.  
}
}
catch (Exception er)
{
Logging.Log(er.Message);//logs in case of failure 
}
}
}
/// <summary>
/// Creates Data Sources in Reporting Services.
/// </summary>
/// <param name="datasourceXMLFilePath"> XML file path holds Data Sources information.</param>
private void CreateDataSources(string datasourceXMLFilePath)
{
ReportingService reportingServicesClient =
new ReportingService();
reportingServicesClient.Credentials = System.Net.CredentialCache.DefaultCredentials;//default credential who runs the application
DataSourceDefinition tempDataSource;  
XDocument xmlDoc = XDocument.Load(datasourceXMLFilePath);//loads XML file to XDocument object 
try
{
var result = from c in xmlDoc.Descendants("DataSource")//gets DataSource sections in the XMLDocument object (xmlDoc) 

select new //foreach <DataSource> node in the xmlDoc get name, folder, etc nodes
{
name = (string)c.Element("Name").Value,
folder = (string)c.Element("Folder").Value,
description = (string)c.Element("Description").Value,
hideInListView = (string)c.Element("HideInListView").Value,
enabled = (string)c.Element("Enabled").Value,
connectionString = (string)c.Element("ConnectionString").Value,
extension = (string)c.Element("Extension").Value,
credentialRetrieval = (string)c.Element("CredentialRetrieval").Value,
windowsCredentials = (string)c.Element("WindowsCredentials").Value,
impersonateUser = (string)c.Element("ImpersonateUser").Value,
impersonateUserSpecified = (string)c.Element("ImpersonateUserSpecified").Value,
prompt = (string)c.Element("Prompt").Value,
userName = (string)c.Element("UserName").Value,
password = (string)c.Element("Password").Value,
enabledSpecified = (string)c.Element("EnabledSpecified").Value
};
foreach (var row in result)
{
CredentialRetrievalEnum credentialRetrieval; //creates new instance from CredentialRetrievalEnum 
EnumConverter ec =
new EnumConverter(typeof(CredentialRetrievalEnum)); //creares new instance from EnumConverter to convert value string ((string)c.Element("CredentialRetrieval").Value) to CredentialRetrievalEnum 
credentialRetrieval = (CredentialRetrievalEnum)ec.ConvertFromString(row.credentialRetrieval); //here's how we converting
tempDataSource = new DataSourceDefinition(); //new DataSourceDefinition for each DataSource in xmlDoc
//here's setting some properties to tempDataSource from xmlDoc
tempDataSource.CredentialRetrieval = credentialRetrieval;
tempDataSource.ConnectString = row.connectionString;
tempDataSource.Enabled = bool.Parse(row.enabled);
tempDataSource.EnabledSpecified = bool.Parse(row.enabledSpecified);
tempDataSource.Extension = row.extension;
tempDataSource.ImpersonateUserSpecified = bool.Parse(row.impersonateUserSpecified);
tempDataSource.ImpersonateUser = bool.Parse(row.impersonateUser);
tempDataSource.Prompt = row.prompt;
tempDataSource.WindowsCredentials = bool.Parse(row.windowsCredentials);
if(!String.IsNullOrEmpty(row.userName))//if not null use username mentioned in the xml file
tempDataSource.UserName = row.userName;
if(!String.IsNullOrEmpty(row.password))//if not null use password mentioned in the xml file
tempDataSource.Password = row.password;
//user name and password should be used together....
try
{
reportingServicesClient.CreateDataSource(row.name, row.folder, true, tempDataSource,
null);//creates new datasource in the reporting service server
Logging.Log(string.Format("Data Source {0} has created successfully", row.name));//logs in case of success
}
catch (SoapException e)
{
Logging.Log(e.Detail.InnerXml.ToString());//logs soapException for more details on the exception occured
}
}
}
catch (Exception er)
{
Logging.Log(er.Message);//logs in case of failuar
}
}
/// <summary>
/// Creates Reports in Reporting Services.
/// </summary>
/// <param name="reports">Array from Report to be created on the Reporting Service Server.</param>
private void CreateReports(Report[] reports)
{
ReportingService rsc =
new ReportingService();
rsc.Credentials = System.Net.CredentialCache.DefaultCredentials;//use default credential 
foreach (Report aReport in reports)
{
Byte[] definition = null;//the defination of report
Warning[] warnings = null;//warning if there's warinign in report creation
try
{
FileStream stream = File.OpenRead(aReport.Path);
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int)stream.Length);
stream.Close();
}
catch (IOException e)
{
Logging.Log(e.Message);//logs if there's an exception with the physical file
}
try
{
rsc.CreateReport(aReport.Name, aReport.Folder, true, definition, null);//creates new report on the Reporting Services Server
#region Setting Report Data Source
DataSourceReference reference = new DataSourceReference();//creates new instance from DataSourceReference 
reference.Reference = aReport.DataSource;//set the reference to the datasource name 
DataSource[] dataSources = new DataSource[1];//creates an array of 1 of DataSource
DataSource ds = new DataSource();//creates new instance of DataSource
ds.Item = (DataSourceDefinitionOrReference)reference;
ds.Name = aReport.DataSource.Split(’/').Last();//I just need the name not the full path so I split the full name of the datasource by '/' then get the last part like /1/2/3/4/5 I just need 5
dataSources[0] = ds;
rsc.SetReportDataSources(aReport.Folder + "/" + aReport.Name, dataSources);//sets report to datasource
#endregion
if (warnings != null)
{
foreach (Warning warning in warnings)
{
Logging.Log(string.Format("Report: {0} has warnings", warning.Message));//logs if there's any warning in the report creation
}
}
else
Logging.Log(string.Format("Report: {0} created successfully with no warnings", aReport.Name));//logs in case of success
}
catch (SoapException e)
{
Logging.Log(e.Detail.InnerXml.ToString());//logs more details on the error
}
}
}
Report\Logger Class will be attached in the Source code...
I don't see any tough code to explain any developer familiar with C# will understand it very well, but if you have any question please feel free to ask me.

License

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


Written By
Software Developer LINK Development
Egypt Egypt
Business Intelligence Developer at LINK Development which is a wholly subsidiary of LINKdotNET, graduated from Faculty of Computers and Information | Helwan University, Cairo, Egypt | 2003 - 2007, Computer Science as a Major, he has a passion for community, latest knowledge delivering and Q&A forums.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Peter Held15-Dec-19 20:59
Peter Held15-Dec-19 20:59 
QuestionAssociating a shared datasource to the report Pin
Member 21821563-Jul-17 6:06
Member 21821563-Jul-17 6:06 
QuestionAccess reporting service on remote machine Pin
SujayC8-Sep-15 2:45
professionalSujayC8-Sep-15 2:45 
GeneralMy vote of 1 Pin
Member 113962091-Mar-15 21:58
Member 113962091-Mar-15 21:58 
BugYour report class code has an error, it is not executing Pin
Member 1139620925-Feb-15 23:22
Member 1139620925-Feb-15 23:22 
QuestionReporting Services Automatic Deployment in SQL 2012 Data Tools Pin
Mostafa Asaduzzaman11-Feb-14 13:35
Mostafa Asaduzzaman11-Feb-14 13:35 
I am unable to run the project to SQL 2012 deployment for the report. When I tried to add http://ramy-mahrous/ReportServer/ReportService.asmx[^] as service reference I got the error message "
CSS
There was an error downloading 'http://ramy-mahrous/ReportServer/ReportService.asmx'.
The remote name could not be resolved: 'ramy-mahrous'
Metadata contains a reference that cannot be resolved: 'http://ramy-mahrous/ReportServer/ReportService.asmx'.
There was no endpoint listening at http://ramy-mahrous/ReportServer/ReportService.asmx that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details.
The remote name could not be resolved: 'ramy-mahrous'
If the service is defined in the current solution, try building the solution and adding the service reference again.

"
I am running on VS 2012

Regards,
Mostafa
QuestionDeplying reports in reportng services in SQL 2012 programmatically Pin
kiss10-Feb-14 3:04
kiss10-Feb-14 3:04 
QuestionAutomatic update of SSAS Cube Pin
asif rehman baber5-Dec-13 6:49
asif rehman baber5-Dec-13 6:49 
QuestionSSRS 2012 Pin
Nickysqlboy6-Aug-13 5:28
Nickysqlboy6-Aug-13 5:28 
QuestionWhat is the password for Extracting the ZIP file? Pin
brainvibe12-Sep-12 5:11
brainvibe12-Sep-12 5:11 
QuestionNeed Help : got error in application Pin
Member 289782416-Mar-12 1:41
Member 289782416-Mar-12 1:41 
GeneralU get my 5 * Pin
ngjafo12-Mar-12 4:52
ngjafo12-Mar-12 4:52 
GeneralMy vote of 5 Pin
ngjafo12-Mar-12 4:48
ngjafo12-Mar-12 4:48 
GeneralMy vote of 5 Pin
WebMaster23-Nov-11 19:42
WebMaster23-Nov-11 19:42 
QuestionCan you tell me how to Create a Custom Datasource to the report? Pin
Srinivas3923-Sep-11 4:08
Srinivas3923-Sep-11 4:08 
GeneralData Source cannot be found Pin
clubict13-Jun-11 7:06
clubict13-Jun-11 7:06 
GeneralNeed help in SSAS Cube deployment Pin
Mriganka Deka9-Feb-11 0:33
Mriganka Deka9-Feb-11 0:33 

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.