Thursday, November 1, 2012

How to export SSRS report to PDF file format

These days, generating and running your business report has become an easy task by using Microsoft SSRS. Once you finish building your own report, you can run it from the web browser inside of your network or you can code your application to export the report to any format.  Since I kept getting this same question from friends and colleagues over and over, here I am posting the simple "How To."


1. Setting up the web reference

In my own application, I was using SSRS 2010 report server along with 2005 SSRS report execution server.


Specify your report server web reference URL
Specify your report execution server URL

2. Add the following procedure to your application

You can re-program this to accommodate your application environments.

// I am using the hashtable to pass "report parameter & value" pair
// If you have more than one report to run, call this procedure inside a loop of "reports"
public void Rpt_GenerateReport(string reportLocation, Hashtable userParamValues, string targetLocationFile, string renderingFormat)
        {
            SSRS.ReportingService2010 rptService = new SSRS.ReportingService2010();
            SSRSExec.ReportExecutionService rptExecute = new SSRSExec.ReportExecutionService();

            // rptService.UseDefaultCredentials = true;
            rptService.Credentials = System.Net.CredentialCache.DefaultCredentials;
            rptService.Timeout = 3000000;
            rptExecute.Credentials = System.Net.CredentialCache.DefaultCredentials;
            rptExecute.Timeout = 3000000;

            try
            {
                // run for each report
                // these variables are only for temporary usage here
                bool forRendering = true;
                string historyId = null;
                string encoding;
                string mimeType;
                string extention;
                string[] streamIDs;
                SSRS.ParameterValue[] parameterValues = null;
                SSRS.DataSourceCredentials[] dataCredentials = null;
                SSRS.ItemParameter[] itemParameters = null;
                SSRSExec.ParameterValue[] execParameterValues = null;
                SSRSExec.Warning[] warnings;

                // load the report first
                ExecutionInfo execInfo = rptExecute.LoadReport(reportLocation, null);

                // the next block of codes will get a set of real parameters from a given report and
                // assign user's defined parameter value to the parameter
                itemParameters = rptService.GetItemParameters(reportLocation,
                                                              historyId,
                                                              forRendering,
                                                              parameterValues,
                                                              dataCredentials);
                // initialize execParameterValues for this report
                execParameterValues = new SSRSExec.ParameterValue[itemParameters.Count()];
                int paramIndex = 0;

                if (itemParameters != null)
                {
                    // this report needs a report parameter & its value
                    // assign a value to the report parameter 
                    foreach (ItemParameter param in itemParameters)
                    {
                        SSRSExec.ParameterValue assignThis = new SSRSExec.ParameterValue();
                        assignThis.Label = param.Name;
                        assignThis.Name = param.Name;
                        assignThis.Value = userParamValues[param.Name].ToString();
                        execParameterValues[paramIndex++] = assignThis;
                    }
                    rptExecute.SetExecutionParameters(execParameterValues, "en-us");
                }

                // start rendering a report
                byte[] reportBytes = rptExecute.Render(renderingFormat,   // report output format
                                                        null,
                                                        out extention,
                                                        out mimeType,
                                                        out encoding,
                                                        out warnings,
                                                        out streamIDs);
                // write output to the targetLocationFile
                using (FileStream fileStream = File.OpenWrite(targetLocationFile))
                {
                    fileStream.Write(reportBytes, 0, reportBytes.Length);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                rptService.Dispose();
                rptExecute.Dispose();
            }
        }

3. Call the procedure with the right parameter name and its value

When calling the procedure, the report parameter name must be the one you are using in Name field inside your SSRS report. 

Pass "CustomerID" in Name field of Report Parameter

// check your report location in the server
// the location of your report will look like this
reportLocation = "/Reports/Your Reports Folder/Your Report File Name";

// define a new hashtable and pass report parameter and its value in this way
userParamValues["CustomerID"] = "ABC123";

// target location with a full path
targetLocationFile = "testReportOutput.PDF";

// rendering format
renderingFormat = "PDF";

// use this procedure
Rpt_GenerateReport(string reportLocation, Hashtable userParamValues, string targetLocationFile, string renderingFormat)

No comments: