Share via

Windows Azure SQL Reporting: using SOAP APIs

  • Article

Device information settings in SSRS are used to pass parameters to a rendering extension. For more information regarding device information settings in reporting services, see Passing Device Information Settings to Rendering Extensions.

For SQL Reporting, none of the options specified in the above article are feasible. We see a lot of scenarios where we would want to modify these settings, and render/export the report in the format that we are looking for.

For SQL Reporting reports, we can explore the SOAP APIs in modifying these device information settings at the run time. If we have integrated SQL Reporting within our applications, this approach is really easy, by just creating a separate function.

Here are some steps to achieve this:

  1. Generate the proxy class for ReportExecution2005.asmx.

  2. Now you have the file named: ReportExecutionService.cs. Add the file to your Windows Azure Project. You can use a function similar to the one provided below: 

     public void Render_Export()
         {
             ReportExecutionService rs = new ReportExecutionService();
             rs.Url = String.Format("https://{0}:443/ReportServer/reportExecution2005.asmx", ConfigurationManager.AppSettings["SERVER_NAME"]);
             rs.CookieContainer = new CookieContainer();
             rs.LogonUser(ConfigurationManager.AppSettings["USERNAME"], ConfigurationManager.AppSettings["PASSWORD"], ConfigurationManager.AppSettings["SERVER_NAME"]);

             byte[] result = null;
             string reportPath = "/SSRSReport/Report5";
             string format = "CSV";
             string historyID = null;
  
             // Setting the deviceinfo parameters
             string devInfo = @"<DeviceInfo><FieldDelimiter>;</FieldDelimiter></DeviceInfo>";

             string encoding;
             string mimeType;
             string extension;
             Warning[] warnings = null;
             string[] streamIDs = null;
  
             ExecutionInfo execInfo = new ExecutionInfo();
             ExecutionHeader execHeader = new ExecutionHeader();
  
             rs.ExecutionHeaderValue = execHeader;
             execInfo = rs.LoadReport(reportPath, historyID);
             String SessionId = rs.ExecutionHeaderValue.ExecutionID;

             // code snippet to set the DataSource credentials.
             if (execInfo.CredentialsRequired)
             {
                 List<DataSourceCredentials> credentials = new List<DataSourceCredentials>();
                 foreach (DataSourcePrompt dsp in execInfo.DataSourcePrompts)
                 {
                     DataSourceCredentials cred = new DataSourceCredentials();
                     cred.DataSourceName = dsp.Name;
                     cred.UserName = ConfigurationManager.AppSettings["USERNAME"];
                     cred.Password = ConfigurationManager.AppSettings["PASSWORD"];
                     credentials.Add(cred);
                 }
  
                 Console.WriteLine("Setting data source credentials...");
                 execInfo = rs.SetExecutionCredentials(credentials.ToArray());
             }
  
             // Code snippet to render the file to a specified format.
             try
             {
                 result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
                 execInfo = rs.GetExecutionInfo();
             }
             catch (Exception ex)
             {
                 throw ex;
             }
  
             // Code snippet to write the file to a specific location using FileStream object
             try
             {
                 FileStream stream = File.Create(@"D:\report.csv", result.Length);                
                 Console.WriteLine("File created.");
                 stream.Write(result, 0, result.Length);
                 Console.WriteLine("Result written to the file.");
                 stream.Close();
             }
             catch (Exception ex)
             {
                 Console.WriteLine(ex.Message);
             }
         }

  3. You can use a config file to store the Username, Password and SQL Reporting server name. The config file will have information as specified below:

     <appSettings>
   <add key="SERVER_NAME" value="SQLReportingServer.reporting.windows.net"/>
   <add key="USERNAME" value="UserName"/>
   <add key="PASSWORD" value="Password"/>    
 </appSettings>

  4. Modify the values as per your SQL Reporting server details.

  5. You might need to extend the default Reporting Services proxy class to enable cookie management, because SQL Reporting uses forms authentication.

For more information about unsupported APIs in SQL Reporting, see https://msdn.microsoft.com/en-us/library/windowsazure/gg430132#UnsupportedAPIs

Other references:

 

Any questions around this, feel free to ping me.

Thanks

Chaitanya

Comments

  • Anonymous
    July 18, 2012
    The comment has been removed
  • Anonymous
    July 18, 2012
    I am not aware of this.. I will check and get back to you.. What is the size of the report that you test this out?
  • Anonymous
    July 18, 2012
    Hi.I run the report with the resulting .pdf document of 5.9 mb. And it fires exception. If i choosed xml result type the report rendered successfully. Resulting  size of the xml file - 3.9 mb.
  • Anonymous
    July 19, 2012
    Hi, Chaitanya.Do you have any updates for me?
  • Anonymous
    July 22, 2012
    Hi Dmitriy, can you send me an email by clicking on "Email Blog Author", we will take this offline and work with you.ThanksChaitanya
  • Anonymous
    July 22, 2012
    @Dmitriy: I tried with a report which generates 7.1MB size of PDF, but didnt run into issues. What is the error that you see? Is this issue reproducible or happens everytime you run the report?