Generating PDF reports programmatically using SQL Server Reporting Services 2005, in C#

Introduction

SQL Server Reporting Services (SSRS) 2005 is the latest version of the reporting technology from Microsoft. This article explains a way to create PDF reports programmatically using web services exposed by SQL Server Reporting Services 2005 in C#.

Using the code

First create a report which accepts boolean as its input parameter, and deploy that to the reporting server. The code below explains how to render the report in PDF format programmatically, using C#.

Step 1: Create and deploy the report.
Step 2: Add a web reference to the web services exposed by Reporting Services 2005, i.e., ReportExecution2005 and ReportService2005.

Step 3: Declare the following variables:

private rs2005.ReportingService2005 rs;
private rsExecService.ReportExecutionService rsExec;

Step 4: Initialize the web services and set the authentication for the web services.

// Create a new proxy to the web service
rs = new rs2005.ReportingService2005();
rsExec = new rsExecService.ReportExecutionService();

// Authenticate to the Web service using Windows credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Assign the URL of the Web service
rs.Url = "http://ICCW1023/ReportServer" + 
         "$REPORTSERVER2005/ReportService2005.asmx";
rsExec.Url = "http://ICCW1023/ReportServer" + 
             "$REPORTSERVER2005/ReportExecution2005.asmx";

Step 5: Write code to render the report in PDF format.

// Prepare Render arguments
string historyID = null;
string deviceInfo = null;
string format = "PDF";
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
rsExecService.Warning[] warnings = null;
string[] streamIDs = null;

// Default Path;
string fileName = @"c:\samplereport.pdf";

// Define variables needed for GetParameters() method
// Get the report name
string _reportName = @"/MyReports/Report";
string _historyID = null;
bool _forRendering = false;
ParameterValue[] _values = null;
DataSourceCredentials[] _credentials = null;
ReportParameter[] _parameters = null;

try
{
  // Get if any parameters needed.
  _parameters = rs.GetReportParameters(_report, _historyID, 
                _forRendering, _values, _credentials);

  // Load the selected report.
  rsExecService.ExecutionInfo ei = 
        rsExec.LoadReport(_reportName, historyID);

  // Prepare report parameter.
  // Set the parameters for the report needed.
  rsExecService.ParameterValue[] parameters = 
         new rsExecService.ParameterValue[1];

  // Place to include the parameter.
  if (_parameters.Length > 0 )
  {
    parameters[0] = new rsExecService.ParameterValue();
    parameters[0].Label = "verzamelgroepAP";
    parameters[0].Name = "verzamelgroepAP";
    parameters[0].Value = "true";
  }
  rsExec.SetExecutionParameters(parameters, "en-us");
  results = rsExec.Render(format, deviceInfo, 
            out extension, out encoding,
            out mimeType, out warnings, out streamIDs);

  // Create a file stream and write the report to it
  using (FileStream stream = File.OpenWrite(fileName))
  {
    stream.Write(results, 0, results.Length);
  }
}
catch (Exception ex)
{
  MessageBox.Show( ex.Message);
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Sivakumar Mahalingam

Web Developer

France

Member

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

My vote of 9/10

eka808

4:40 14 Feb '12

This is a great starting point for link ssrs with c#
Sign In·View Thread·Permalink

My vote of 5

Member 8006384

8:31 16 Sep '11

I was having trouble finding out how to interface to a Report Server project until I came across this article. I used the code and had no trouble creating a report.
Sign In·View Thread·Permalink

Dhruval Shah

3:01 30 Jan '10

One or more data source credentials required to run the report have not been specified. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: One or more data source credentials required to run the report have not been specified.

My application gives me above error

How to solve it.

Actually i am able to give Network credential, but i don't know how to give Database credential to object.

Following is my code.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Configuration;
 

 
namespace MLAFMSPresentation
{
    public class MyReportRenderer
    {
        private localhost.ReportingService2005 rs;
        private rsExecution.ReportExecutionService rsExec;
 

        public void renderTest(string filepath, string targetpath)
        {
            rs = new localhost.ReportingService2005();
            rsExec = new rsExecution.ReportExecutionService();
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
            //rsExec.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
            //System.Net.CredentialCache.DefaultNetworkCredentials.Domain = ConfigurationManager.AppSettings["NetworkDomain"].ToString();
            //System.Net.CredentialCache.DefaultNetworkCredentials.UserName = ConfigurationManager.AppSettings["NetworkUser"].ToString();
            //System.Net.CredentialCache.DefaultNetworkCredentials.Password = ConfigurationManager.AppSettings["NetworkPassword"].ToString();
            //rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
            rs.Credentials = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["NetworkUser"].ToString(), ConfigurationManager.AppSettings["NetworkPassword"].ToString(), ConfigurationManager.AppSettings["NetworkDomain"].ToString());
 
            rs.Url = "http://192.168.0.22:8080/ReportServer_SQL2008/ReportService2005.asmx";
            rsExec.Url = "http://192.168.0.22:8080/ReportServer_SQL2008/ReportExecution2005.asmx";
 

            string historyID = null;
            string deviceInfo = null;
            string format = "PDF";
            Byte[] results;
            string encoding = String.Empty;
            string mimeType = String.Empty;
            string extension = String.Empty;
            rsExecution.Warning[] warnings = null;
            string[] streamIDs = null;
 
            // Default Path;

            string fileName = targetpath;
            string _reportName = filepath;
            string _historyID = null;
            bool _forRendering = false;
            localhost.ParameterValue[] _values = null;
            localhost.DataSourceCredentials[] _credentials = null;
 
            
            localhost.ReportParameter[] _parameters = null;
 
            try
            {
                _parameters = rs.GetReportParameters(_reportName, _historyID, _forRendering, _values, _credentials);
                rsExecution.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
                rsExecution.ParameterValue[] parameters = new rsExecution.ParameterValue[4];
 
                // ReportParameter[] parameters = new ReportParameter[4];

                parameters[0] = new rsExecution.ParameterValue();
                parameters[0].Name = "pk_RequestId";
                parameters[0].Label = "pk_RequestId";
                parameters[0].Value = "9";
 

                parameters[1] = new rsExecution.ParameterValue();
                parameters[1].Name = "Userid";
                parameters[1].Label = "Userid";
                parameters[1].Value = "1";
 

 
                parameters[2] = new rsExecution.ParameterValue();
                parameters[2].Name = "fk_FinancialYearId";
                parameters[2].Label = "fk_FinancialYearId";
                parameters[2].Value = "1";
 

                parameters[3] = new rsExecution.ParameterValue();
                parameters[3].Name = "fk_Constituencyid";
                parameters[3].Label = "fk_Constituencyid";
                parameters[3].Value = "1";
 
                //if (_parameters.Length > 0)
                //{
                //    parameters[0] = new rsExecution.ParameterValue();
                //    parameters[0].Label = "" parameter="" label="">";
                //    parameters[0].Name  = "" parameter="" name="">";
                //    parameters[0].Value = "" parameter="" value="">";
                //}
                rsExec.SetExecutionParameters(parameters, "en-us");
 
                results = rsExec.Render(format, deviceInfo,
                          out extension, out encoding,
                          out mimeType, out warnings, out streamIDs);
                using (FileStream stream = File.OpenWrite(fileName))
                {
                    stream.Write(results, 0, results.Length);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Re: One or more data source credentials required to run the report have not been specified. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: One or more data source credentials required to run the report have not been spec

Ista

2:48 13 Jun '11

Did you figure it out? -------------------------------------------------------- 1 line of code equals many bugs. So don't write any!! My mad coder blog
Sign In·View Thread·Permalink

C# Implmentation

Missy Casner

3:51 23 Sep '09

I understand the C# code written here. I use Stored Procedures all the time in SQLServer 2005. However, I have never deployed C# code. Can someone give me some hints on taking what Sivakumar has given us and getting it implemented on our SQLServer 2005? I plan to add it or integrate it with an e-mailing stored procedure I have.
Sign In·View Thread·Permalink	2.00/5 (1 vote)

Sample of url to reporting service

d00_ape

3:35 25 Aug '09

"Step 2: Add a web reference to the web services exposed by Reporting Services 2005, i.e., ReportExecution2005 and ReportService2005."

Can you give any example of url to the reporting service. I've published a report and can browse it i IE7 with this url: http://n29769:8080/Reports/Pages/Report.aspx?ItemPath=%2fReport+Project1%2fUsers[^]

but get "The URI prefix is not recognized" when try to add a Web Reference in VS2008

_____________________________

...and justice for all

Try this it will work.

M Varghese

9:36 27 Jan '09

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
 
 
namespace ReportServerProject
{
    class MyReportRenderer
    {
        private rs2005.ReportingService2005 rs;
        private rs2005Execution.ReportExecutionService rsExec;
 

        public void renderTest()
        {
            rs = new rs2005.ReportingService2005();
            rsExec = new rs2005Execution.ReportExecutionService();
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
            rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
            rs.Url = "http://<servername>/ReportServer/ReportService2005.asmx";
            rsExec.Url = "http://<servername>/ReportServer/ReportExecution2005.asmx";
 

            string historyID = null;
            string deviceInfo = null;
            string format = "PDF";
            Byte[] results;
            string encoding = String.Empty;
            string mimeType = String.Empty;
            string extension = String.Empty;
            rs2005Execution.Warning[] warnings = null;
            string[] streamIDs = null;
 
            // Default Path;

            string fileName = @"c:\samplereport.pdf";
            string _reportName = @"/My Reports/<reportname>";
            string _historyID = null;
            bool _forRendering = false;
            rs2005.ParameterValue[] _values = null;
            rs2005.DataSourceCredentials[] _credentials = null;
            rs2005.ReportParameter[] _parameters = null;
 
            try
            {
                _parameters = rs.GetReportParameters(_reportName, _historyID, _forRendering, _values, _credentials);
                rs2005Execution.ExecutionInfo ei = rsExec.LoadReport(_reportName, historyID);
                rs2005Execution.ParameterValue[] parameters = new rs2005Execution.ParameterValue[1];
                if (_parameters.Length > 0)
                {
                    parameters[0] = new rs2005Execution.ParameterValue();
                    parameters[0].Label = "<replace with="" parameter="" label="">";
                    parameters[0].Name  = "<replace with="" parameter="" name="">";
                    parameters[0].Value = "<replace with="" parameter="" value="">";
                }
                rsExec.SetExecutionParameters(parameters, "en-us");
                results = rsExec.Render(format, deviceInfo,
                          out extension, out encoding,
                          out mimeType, out warnings, out streamIDs);
                using (FileStream stream = File.OpenWrite(fileName))
                {
                    stream.Write(results, 0, results.Length);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
 

        }
    }
}
</replace></replace></replace></reportname></servername></servername>

Re: Try this it will work.

Ista

5:46 30 Mar '10

actually this is even easier. You have far too much code and this guy made things confusing without saying where to grab info



// setup report connection

                            string deviceInfo = null;

                            string extension = String.Empty;

                            string mimeType = String.Empty;

                            string encoding = String.Empty;

                            Warning[] warnings = null;

                            string[] streamIDs = null;

                            string historyId = null;

 

                            ReportExecutionService rsExec = new ReportExecutionService();

                            rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;

                            rsExec.Url = ConfigurationManager.AppSettings["ReportServer"] + "/ReportExecution2005.asmx";

                            rsExec.LoadReport("/ReportMainFolder/ReportName", historyId);

                            

                            // pass parameters

                            rsExec.SetExecutionParameters(

                                new ParameterValue[]

                                    {new ParameterValue() {Name = "ParamEvent", Value = "MyValue"}},null);

                            

                            // get pdf of report        

                            Byte[] results = rsExec.Render("PDF", deviceInfo,

                                out extension, out encoding,

                                out mimeType, out warnings, out streamIDs);

Walla...almost no code, it's easy to manage and your done.

Take the bytes and add as an attachment to mail:



mailMessage.Attachments.Add(new Attachment(new MemoryStream(attachment),String.Format("{0}_DocumentSavingsReport.pdf", "ReportName") ));



Response.Write(result);

It's just that easy folks!

l8rs
Nick

--------------------------------------------------------

1 line of code equals many bugs. So don't write any!!

My mad coder blog

How to export to PDF with password ?

rajspselvaraj

23:31 20 Nov '08

Hi I have created the PDF file successfully. but i need to protect my pdf file by using the password.. Is is possible ?.. if any one know kindly help me.. Thanks and Regards Selvaraj
Sign In·View Thread·Permalink	4.00/5 (3 votes)

Re: How to export to PDF with password ?

Gufran Sheikh

22:39 21 Mar '10

Hi,
I protected the pdf using ISharpText Liabrary, its open source liabray.
check the below code.

Imports iTextSharp.text
Imports iTextSharp.text.pdf
 
Dim password As String = "yourPassword"
Dim outputFile As String = "c:\myPasswordPdf.pdf"
 
Dim reader As PdfReader = New PdfReader("PathForPdf")
PdfEncryptor.Encrypt(reader, New IO.FileStream(outputFile, FileMode.Create), True, password, password, PdfWriter.ALLOW_PRINTING)

PDF Performance

Lautmann

1:55 13 Nov '08

I have a couple of reports about 30 pages long mixed with graphs, matrixes and tables.

I access these reports using an url. When rendering to html it takes about 30 seconds, and when rendering to pdf it takes about 90 seconds. These reports has been converted from crystal reports, crystal manages to render the same report to pdf in 30 seconds.

Is there anything I can do to improve rendering performance (to pdf)?

regards
Richard

Try this instead

Member 5528554

6:09 4 Oct '08

Sorry I looked at this and it didn't work and looks like rubbish, I have made a far simpler version here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ReportExecutionServiceReference;

namespace Reporting
{
public class ReportPDFTools
{
public byte[] RenderPDFReport( string url, string reportPath, ReportParameter[] parameters )
{
ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

string format = "PDF";
string historyID = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;

ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();

execInfo = rs.LoadReport(reportPath, historyID);

rs.SetExecutionParameters(
parameters.Select(
p => new ParameterValue
{ Label = p.Name,
Name = p.Name,
Value = p.Value }
).ToArray(), "en-us");

rs.ExecutionHeaderValue = execHeader;
rs.ExecutionHeaderValue.ExecutionID = execInfo.ExecutionID;

return rs.Render(format, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);
}

}

public struct ReportParameter {
public string Name;
public string Value;
}
}

You can then drive this from a simple console application method, this is what I did:

///

/// -p, --renderpdf-report [..]
///

///
private static void RenderPDFReport(string[] args)
{
if (args.Contains("-p") || args.Contains("--renderpdf-report"))
{
if( args.Where( s => !s.Contains("=") ).Count() != 4 )
throw new ArgumentException( "Expected 4 non-param arguments, failing." );

Console.WriteLine("Rendering PDF Report...");

ReportPDFTools pdfTools = new ReportPDFTools();

ReportParameter[] _params =
args.Where(
s => s.Contains("=") )
.Select(
s => new ReportParameter{
Name=s.Split('=')[0],
Value=s.Split('=')[1] }
).ToArray();

File.WriteAllBytes(args[3],
pdfTools.RenderPDFReport(args[1], args[2], _params)
);
}
}

Regards

Tim Scarfe (www.developer-x.com)

Re: Try this instead

Member 5528554

6:18 4 Oct '08

We are losing all our nice white space formatting here! I uploaded the bits to http://www.developer-x.com/dropbox/ReportingServices/RenderPDFReport.txt Have fun Tim Scarfe
Sign In·View Thread·Permalink	2.00/5 (1 vote)

pdf blank pages problem

Member 2212581

9:58 21 Jul '08

i found the solution here for the blank pages problem with pdf :
http://blogs.msdn.com/bwelcker/archive/2005/08/19/Alien-Lanes-_2800_Logical-and-Physical-Pagination-Rules_2900_.aspx[^]

QUOTE someone :"after i read your article on setting page margins, i set all page margins to zero: topmargin = 0, rightmargin = 0, leftmargin = 0 and bottommargin = 0. and it's now printing without blank page.

thank you so much brian!"

i have also the problem when exporting to pdf

Member 2212581

4:52 21 Jul '08

i have also the problem when exporting to pdf, i get blank pages in the middle of the report. i have a report that generates data grouped by territory, and each group has a different size. so if u try to put 10 lines of data by page, it can't work, i get blank pages anyway.
Sign In·View Thread·Permalink	1.50/5 (2 votes)

Cannot Create new Subscription SSRS Report......

sriramjava

3:22 5 Jun '08

Hi,

I have Deployed reports in my Report Server.
All the reports are working fine,
Then i send mail from particular user,
so try to create a new subscription,
but error thrown...

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting) Get Online Help"

in my project folder am used two rdl files
example for (Report1.rdl, Report2.rdl)
& two Data Source (Remote1.rds, Remote2.rds)

Report1.rdl file is point to one Remote Machine using Remote1.rds datasource, Report2.rdl file is point to another Remote Machine using Remote2.rds datasource

its properly running in my ReportServer area

but create new subscription area thrown an error..
anybody give idea for that issue..
thanks for advance
regards,
Ram...

blank pages for when I exported to pdf format?

kuncha

17:54 4 Jun '08

Can any body explain why I am getting blank pages when I export the report in pdf format? Thank you. vedd
Sign In·View Thread·Permalink	5.00/5 (1 vote)

Getting a large blank spaces while exporting to PDF

enihar

4:34 15 Apr '08

I have a report which contains so many components.Some of these components are having the Visibility conditions.The Problem is that I am getting large white spaces between components while exporting the report to PDF.How can I avoid getting these spaces in PDF?
Sign In·View Thread·Permalink	2.00/5 (2 votes)

Re: Getting a large blank spaces while exporting to PDF

tobiasboehler

2:39 9 Sep '08

I have the same problem. Really annoying. Some spaces i could avoid because there were overlapping items. but now there are no overlapping items. argh... I'm using a list in a list. Both lists contain some textboxes. thats all. Please help
Sign In·View Thread·Permalink	1.00/5 (1 vote)

Problems with reports that take parameters

mtembene

10:39 22 Aug '07

I have managed to sort out the code above and get the application compiling and executing. However there seems to be a problem with the parameter that I define and and set with the SetExecutionParameters() method.

Everytime I try to run my report the following error is displayed

This report requires a default or user-defined value for the report
parameter 'contractid'. To run or subscribe to this report, you must provide a parameter value.

I am setting this parameter as follows:

parameters[0] = new ParameterValue();
parameters[0].Name = "contractid";
parameters[0].Value = "1";
rs.SetExecutionParameters(parameters, "en-us");
where rs is an ExecutionService.

If anyone knows what I am doing wrong or what I need to change on my report to get this working I'd like to hear from you.

mtembene

Re: Problems with reports that take parameters

mtembene

11:44 22 Aug '07

OK I figured it out after a bit of hacking.

In the end I discovered that though the parameter I had to set was a string the report writer and a set of predefined valid parameter values that had to be met. That was my first problem.

When running the report manually I could pick the parameter I wanted from a drop down list. The drop downlist actually provided a Guid as a parameter to the report.

So in the end I had to create a Guid in my code. Assign it a valid value and then add that value as a string to the parameter for my report.

Obvious in the end.

Re: Problems with reports that take parameters

shayles

19:08 30 Mar '08

Hi - I have exactly the same problem with drop-down choices in a report, but dont quite understand what you mean when you mention the use of a GUID. Do you mean that each option in the dropdown has an associated GUID that is the real value of the parameter - if so how do you get these values. Hope to hear from you soon. Scott Hayles (scott.hayles@ntsolutions.com.au)
Sign In·View Thread·Permalink

Help Getting Running...

corpr8

11:20 12 Jun '07

Hi All, I am having trouble getting this running - does anybody have a sample of this in a VS solution that I could have a look at please? Sorry for being dense... All help greatfully received... :-/
Sign In·View Thread·Permalink

Re: Help Getting Running...

corpr8

2:33 13 Jun '07

Hi - with help I think I am making progress...

I am running into a problem very similar to the thread below this:

The report on my SSRS instance is stored in Home > Pauls Case Report > Report1

So I assume the path for the reportname variable should be "/Pauls Case Report/Report1" However - this returns me back a 404 error (not found)...

I can see it's the rs.getparameters block thats throwing this...

Any idea's?

Brgds, Paul. Confused | :confused:

Re: Help Getting Running...

vikas chaudhary

3:37 13 Jun '07

A sample for VS development is as :-

ReportService2005.CatalogItem myCatalog=new TestApp.ReportService2005.CatalogItem();
ExecutionHeader header=new ExecutionHeader();
ExecutionInfo rpt = myReportExec.LoadReport("/Report Project/Report1", null);
myReportExec.ExecutionHeaderValue = header;
myReportExec.ExecutionHeaderValue.ExecutionID = rpt.ExecutionID;

--------To load a report------.

Hope this helps bcoz ur problem is for loading only.