Skip to main content

Using SSRS web services to render a report as a PDF



I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF.  The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80.
To do this you will need to use the SSRS2008  ReportExecution2005.asmx web service.
This could be accesses using the following URL assuming your SSRS server was installed using the default settings.
http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl
1.       Create a user on your AD domain with the least amount of privileges (say ReportUser)
2.       Give this account browse access on the reporting server for the desired reports.
3.       To get this working in visual studio 2010 (I am using the Premium edition)  Right click on your project and add a service reference to the above web service URL. (YourSSRSServer)
4.       Once the above is done. Create a class wrapper around the functions needed. Create a new class and call it whatever you want.
5.       Create Network keys in your web.config file with the following
NTLMUser= ReportUser
NTMLPassword= Somepassword
NTMLDomain=Yourdomain

6.       I have written the following class called SSRSReports.cs which takes a report along with the parametes and saves the report on the web server prior to rendering it to the user.

7.  using System;
8.  using System.Collections.Generic;
9.  using System.Linq;
10. using System.Web;
11. using System.IO;
12. using YourSSRSServer;
13. using System.Net;
14. using System.Configuration;
15.  
16. /// <summary>
17. /// Author: KrishanG
18. /// For this class to work the following needs to be done.
19. /// 1. Set a web reference to the SSRS reporting services http://reportserverName/reportserver/reportexecution2005.asmx?wsdl
20. /// At the time I am using SSRS2008 thus need to use the above reportexecution services
21. /// 2. Set the following in the web config file
22. /// 3. Note the Web service endpoint should be the reporting service you are using
23. ///<system.serviceModel>
24. ///  <bindings>
25. ///    <basicHttpBinding>
26. ///      <binding name="ReportExecutionServiceSoap" closeTimeout="00:01:00"
27. ///          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
28. ///          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
29. ///          maxBufferSize="6553600" maxBufferPoolSize="524288" maxReceivedMessageSize="6553600"
30. ///          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
31. ///          useDefaultWebProxy="true">
32. ///        <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647"
33. ///            maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />
34. ///        <security mode="TransportCredentialOnly">
35. ///          <transport clientCredentialType="Ntlm" proxyCredentialType="None" realm="" />
36. ///          <message clientCredentialType="UserName" algorithmSuite="Default" />
37. ///        </security>
38. ///      </binding>
39. ///    </basicHttpBinding>
40. ///   </bindings>
41. ///   <client>
42. ///     <endpoint address="http://yourServer:80/ReportServer/ReportExecution2005.asmx" binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap" contract="yourServer.ReportExecutionServiceSoap" name="ReportExecutionServiceSoap"/>
43. ///   </client>
44. ///  </system.serviceModel>
45. ///
46. ///
47. /// </summary>
48. public class SSRSReport
49. {
50.        public SSRSReport()
51.        {
52.               //
53.               // TODO: Add constructor logic here
54.               //
55.        }
56.  
57.     /// Pre: Note the parameters should be in the format
58.     ///http://reportservername/reportServer?/folder/repotname&par1=value1&para2=value3 &rs:Format=PDF
59.     ///Where the ‘&’ will divide the parameters. The first block is considered the path and report
60.     ///The render format will be PDF by defaulted
61.    public static void GetReportPDF(string reportPath, string FullFilePath)
62.     {
63.         ReportExecutionServiceSoapClient rs = new ReportExecutionServiceSoapClient();
64.  
65.         rs.ClientCredentials.Windows.ClientCredential = new NetworkCredential(ConfigurationManager.AppSettings["NTLMUser"].ToString(),
66.                                                                               ConfigurationManager.AppSettings["NTLMPassword"].ToString(),
67.                                                                               ConfigurationManager.AppSettings["NTLMDomain"].ToString());
68.  
69.         rs.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
70.  
71.  
72.         string format = "PDF";
73.         string mimeType = "application/pdf";
74.         //Note the first element is the  path and report
75.         string[] rPath = reportPath.Split('?');
76.  
77.         // Split the parameters including the functions in the next collection
78.         string[] pCollection = rPath[1].Split('&');
79.  
80.         //  split the item para and value
81.         string[] pItems = null;
82.         var rslist = new List<KeyValuePair<string, string>>();
83.         var rsFormat = new List<KeyValuePair<string, string>>();
84.  
85.  
86.  
87.         // Note I am string from element 1 not 0 since I do not want the report details
88.         for (int i = 1; i < pCollection.Count(); i++)
89.         {
90.             pItems = pCollection[i].Split('=');
91.             // if rs is not found in the first part this is a parameter
92.             if (pItems[0].IndexOf("rs:") == 0)
93.             {
94.                 rsFormat.Add(new KeyValuePair<string, string>(pItems[0], pItems[1]));
95.             }
96.             else
97.             {
98.                 rslist.Add(new KeyValuePair<string, string>(pItems[0], pItems[1]));
99.             }
100.                 }
101.          
102.                 //Set the render format looking at the function list.
103.                 foreach (var element in rsFormat)
104.                 {
105.                     if (element.Key == "rs:Format")
106.                         format = element.Value;
107.                 }
108.          
109.                 // Get the parameter count
110.                 int NoPara = rslist.Count;
111.          
112.          
113.                 YourSSRSServer.ParameterValue[] parameters = new YourSSRSServer.ParameterValue[NoPara];
114.          
115.                 for (int i = 0; i < rslist.Count; i++)
116.                 {
117.          
118.          
119.                     parameters[i] = new YourSSRSServer.ParameterValue();
120.          
121.                     parameters[i].Name = rslist[i].Key;
122.          
123.                     parameters[i].Value = rslist[i].Value;
124.          
125.                 }
126.          
127.                 byte[] results = RenderReport(rs, format, mimeType, pCollection[0], parameters);
128.          
129.                 WriteFile(results, FullFilePath);
130.          
131.             }
132.          
133.             
134.              static byte[] RenderReport(ReportExecutionServiceSoapClient rs, string format, string mimeType, string reportPath, YourSSRSServer.ParameterValue[] parameters)
135.             {
136.          
137.                 //Prepare Render arguments 
138.          
139.                 string historyID = null;
140.          
141.                 string deviceInfo = String.Empty;
142.          
143.                 string extension = String.Empty;
144.          
145.                 string encoding = String.Empty;
146.          
147.                 YourSSRSServer.Warning[] warnings = null;
148.          
149.                 string[] streamIDs = null;
150.          
151.                 byte[] results = null;
152.          
153.                 ExecutionInfo execInfo = new ExecutionInfo();
154.                 TrustedUserHeader trusteduserHeader = new TrustedUserHeader();
155.          
156.                 ExecutionHeader execHeader = new ExecutionHeader();
157.                 YourSSRSServer.ServerInfoHeader serviceInfo = new YourSSRSServer.ServerInfoHeader();
158.          
159.          
160.                 // trusteduserHeader.UserName = rs.ClientCredentials.Windows.ClientCredential.UserName;
161.          
162.          
163.                 rs.LoadReport(trusteduserHeader, reportPath, historyID, out serviceInfo, out execInfo);
164.                 execHeader.ExecutionID = execInfo.ExecutionID;
165.          
166.                 rs.SetExecutionParameters(execHeader, trusteduserHeader, parameters, "en-au", out execInfo);
167.          
168.          
169.                 rs.Render(execHeader, trusteduserHeader, format,
170.                 deviceInfo, out results, out extension,
171.                 out mimeType, out encoding,
172.                 out warnings, out streamIDs);
173.          
174.                 return results;
175.          
176.             }
177.          
178.              static void WriteFile(byte[] input, string FileName)
179.             {
180.          
181.                 //Open a file stream and write out the report 
182.                 if (File.Exists(FileName))
183.                     File.Delete(FileName);
184.          
185.                 FileStream stream = File.OpenWrite(FileName);
186.          
187.                 stream.Write(input, 0, input.Length);
188.                 stream.Close();
189.          
190.             }
191.          
192.          
193.              public static void CreateApplicationPDF(string UserID, string reportPath, string FileName)
194.              {
195.                  string Fpath;
196.                  Fpath = CreateUserFolderReturnPath(UserID);
197.          
198.                  // If the document does not exist then create it.
199.                  if (!File.Exists(Fpath + FileName))
200.                      SSRSReport.GetReportPDF(reportPath, Fpath + FileName);
201.          
202.          
203.              }
204.          
205.          
206.             public static string CreateUserFolderReturnPath(string UserId)
207.              {
208.          
209.                  string path;
210.                  string USERID = UserId;
211.                  try
212.                  {
213.          
214.          
215.                      if (!Directory.Exists(HttpContext.Current.Server.MapPath("~//docs//" + USERID)))
216.                      {
217.                          Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~//docs//" + USERID));
218.                      }
219.                      path = HttpContext.Current.Server.MapPath("~//docs//" + USERID + "//");
220.                  }
221.                  catch (Exception Ex)
222.                  {
223.          
224.                      path = "";
225.                  }
226.                  return path;
227.              }
228.          
229.          
230.         }


I have the list of reports in a database table and read this into a data table. The following generates a bunch of reports for a user who accesses a page on the unload event.

The values in
DR[“Rpt”] would look like this. Similar to accessing a reporting using URL parametes.

http://yourSSRSServer/?/folder/reportname&para1=value1&para2=value2&rs:Command=Render&rs:Format=PDF
The name of the outputfle.
Dr[“RptName”] would look like  NameofPDFTobeCreated.pdf

protected override void OnUnload(EventArgs e)
    {
        if (dtApplist != null)
        foreach (DataRow Dr in dtApplist.Rows)
        {
            if (Dr["Rpt"].ToString().Trim()!="")
                YourSSRSServer.CreateApplicationPDF(Foldername,Dr["Rpt"].ToString(), Dr["RptName"].ToString());

          
        }
       

    }

Hope this will help someone else as I spent over a week getting this right. Happy coding! :)

Comments

  1. Thanks, worked for me. However, my application uses local Reporting Service, so that exposing username/password isn't necessary with the following line:

    rs.ClientCredentials.Windows.ClientCredential = (NetworkCredential) System.Net.CredentialCache.DefaultCredentials;

    ReplyDelete

Post a Comment

Popular posts from this blog

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

If you ever wanted to include the eGate payment gateway in your ASP.Net site and want to save yourself the time in creating the class, here’s how to do it. First things first you will need a merchant account with ANZ who will give you the following ·          Merchant Account ·          User name ·          password The details could be downloaded from the ANZ web site http://www.anz.com.au/australia/business/merchant/DownloadDevKit.asp I am doing this for an Aus base client thus the process might be different to your own country. Please check with the issuing bank for details. Have a read of the Merchant Admin guide. Login to the merchant account and create an operator ID, Login as the operator and obtain the access code. I will not going to the details as this is all documented in the admin guide. What you will need for the implementation would be vpc_AccessCode vpc_Merchan MIGS gateway URL:https://migs.mastercard.com.au/vpcdps The site has numerous

Read any Json data into a table structure using Dynamic SQL and save time write long queries.

Let's say you have a MYSQL database that stores form data as a JSON string. To read the data and manipulate it as SQL, you can use SQL link services. Here's how it works: First, you create a temporary table to store the data from MYSQL. You can use the following SQL script to do this: sql /****** Object: Table [dbo].[tbljson] Script Date: 29/03/2023 10:37:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[#tbljson]( [ID] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [form_id] [ int ] NOT NULL , [params] [nvarchar](max) NULL , CONSTRAINT [PK_tbljson] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ] GO Once you've created the temporary table, you can insert the values from the MYSQL database into it using the following SQL script: insert into #tbljson s