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

Is it necessary to have an Adobe product such as Adobe Reader installed on the server running the SSRS? I don't intend to view the SSRS generated pdf file on the server but on the client machine.

This is the vb.net equivalent code.(Slightly changed/simplified the Parameters section as I always know what params are needed for my reports).

VB

Imports System
Imports System.IO
Imports System.Web.Services.Protocols
Imports <yourproject>.rs2005

Module Module1
        Sub main()

        'Create a new proxy to the web service
        Dim rs As New rs2005.ReportingService2005()
        Dim rsExec As 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://server/site/reportservice2005.asmx"
        rsExec.Url = "http://server/site/ReportExecution2005.asmx"

        'Prepare Render arguments
        Dim historyID As String = Nothing
        Dim deviceInfo As String = Nothing
        Dim format As String = "PDF"
        Dim results As [Byte]()
        Dim encoding As String = [String].Empty
        Dim mimeType As String = [String].Empty
        Dim extension As String = [String].Empty
        Dim warnings As rsExecService.Warning() = Nothing
        Dim streamIDs As String() = Nothing

        'Default Path;
        Dim fileName As String = "C:\Users\user\Desktop\samplereport.pdf"

        'Define variables needed for GetParameters() method
        Dim _reportName As String = "/site/reportname"
        Dim _historyID As String = Nothing
        Dim _forRendering As Boolean = False
        Dim _values As ParameterValue() = Nothing
        Dim _credentials As DataSourceCredentials() = Nothing
        Dim paramcount As Integer = 0

        Try

            'Load the selected report.
            Dim ei As rsExecService.ExecutionInfo = rsExec.LoadReport(_reportName, historyID)

            'Define the parameters for the report
            Dim parameters(2) As rsExecService.ParameterValue

            parameters(0) = New rsExecService.ParameterValue()
            parameters(0).Name = "warehouse"
            parameters(0).Value = "01"

            parameters(1) = New rsExecService.ParameterValue()
            parameters(1).Name = "product"
            parameters(1).Value = "222153-003"

            parameters(2) = New rsExecService.ParameterValue()
            parameters(2).Name = "BOMQty"
            parameters(2).Value = "99"

            'Set the parameters for the report
            rsExec.SetExecutionParameters(parameters, "en-us")

            'Render the report
            results = rsExec.Render(format, deviceInfo, extension, encoding, mimeType, warnings, streamIDs)

            'Create a file stream and write the report to it
            Using stream As FileStream = File.OpenWrite(fileName)
                stream.Write(results, 0, results.Length)
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Module

Hopefully someone may find it of use.

Regards,

Alex

There seems to be a woefull lack of simple examples on how to render reports using SSRS web service but you seem to have created one! Well done and thanks.

I solved big problem which i faced using this article.

Hi, I tried out the same thing as mentioned above. But unfortunately, in my case the generated pdf file doesn't contain the contents of the report. Am I missing out anything? Any help would highly be appreciated.

-Aniket

when i deploy the application on a server and try to run it through my local machine the report is being created on server. Is there a way to generate the report on client machine?

how to add web reference to the web services exposed by Reporting Services 2005,..
I am new to rdl please help i want to render rdl report in pdf. I have deployed a report in remote server

harini

This is a great starting point for link ssrs with c#

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.

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;
            }
        }
    }
}

Did you figure it out?

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

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

My mad coder blog

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.

"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

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>

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

<br />
// setup report connection<br />
                            string deviceInfo = null;<br />
                            string extension = String.Empty;<br />
                            string mimeType = String.Empty;<br />
                            string encoding = String.Empty;<br />
                            Warning[] warnings = null;<br />
                            string[] streamIDs = null;<br />
                            string historyId = null;<br />
<br />
                            ReportExecutionService rsExec = new ReportExecutionService();<br />
                            rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;<br />
                            rsExec.Url = ConfigurationManager.AppSettings["ReportServer"] + "/ReportExecution2005.asmx";<br />
                            rsExec.LoadReport("/ReportMainFolder/ReportName", historyId);<br />
                            <br />
                            // pass parameters<br />
                            rsExec.SetExecutionParameters(<br />
                                new ParameterValue[]<br />
                                    {new ParameterValue() {Name = "ParamEvent", Value = "MyValue"}},null);<br />
                            <br />
                            // get pdf of report        <br />
                            Byte[] results = rsExec.Render("PDF", deviceInfo,<br />
                                out extension, out encoding,<br />
                                out mimeType, out warnings, out streamIDs);<br />
<br />

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

Take the bytes and add as an attachment to mail:

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

or

<br />
Response.Write(result);<br />

It's just that easy folks!

l8rs
Nick

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

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

My mad coder blog

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

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)

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

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 <reportserviceurl> <reportpath> <outputfile> <param />[..]
///

/// <param name="args" />
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)

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 Smile | :)

Tim Scarfe

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, 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.

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...

Can any body explain why I am getting blank pages when I export the report in pdf format?

Thank you.

vedd

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?

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

Introduction

Using the code

License

Comments and Discussions