Integrate Java with Microsoft SQL Server Reporting Services 2005/2008

The best way to accomplish this is through the use of the ReportExecution2005 web service that is provided out of the box by Microsoft SQL Server Reporting Services.

1. Start Eclipse and create a new workspace named ssrsClient
   
   
2. Create a new Java project called ssrsClient
   
   
3. Configure the advanced Axis settings so that "Generate code for all elements, even unreferenced ones" is checked, and so that "Timeout in seconds" is set to -1. (See my previous post, Configure Advanced Axis Settings from Eclipse, for more details)
   
   
4. Create a web service client proxy for the ReportExecution2005 web service; the URL for the WSDL definition will be http://<HOST>/reportserver/ReportExecution2005.asmx?wsdl. (See my previous post, Consume Any Web Service Using Eclipse + Axis, for more details)
   
   
5. Enable NTLM Authentication in Axis by adding the Jakarta Commons HTTPClient and Codec JAR files to the classpath. (See my previous post, Enable NTLM Authentication in Axis, for more details)
   
   
6. Create a new Java Class named Test.java
   
   
7. Copy and paste the following code into Test.java (overwrite the existing code)
   

   import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.*;
   import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.holders.*;
   import java.net.URL;
   import javax.xml.rpc.holders.ByteArrayHolder;
   import javax.xml.rpc.holders.StringHolder;
   import javax.xml.soap.SOAPException;
   import org.apache.axis.message.SOAPHeaderElement;
   
   public class Test
   {
    public static void main(String[] arg)
    {
     ReportExecutionServiceSoapStub service = getService();
     ParameterValue[] parameters = new ParameterValue[1];
     parameters[0] = new ParameterValue();
     parameters[0].setName("<PARAMETER_1_NAME>");
     parameters[0].setValue("<PARAMETER_1_VALUE>");
     
     try
     {
      ExecutionInfo info = service.loadReport("/<FOLDER_NAME>/<REPORT_NAME>", null); //Load report
      setExecutionId(service, info.getExecutionID()); //You must set the session id before continuing
      service.setExecutionParameters(parameters, "en-us"); //Set report parameters
      
      String format = "HTML4.0"; //Valid options are HTML4.0, MHTML, EXCEL, CSV, PDF, etc
      String deviceInfo = "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>True</HTMLFragment></DeviceInfo>"; //Only generate an HTML fragment
      ByteArrayHolder result = new ByteArrayHolder();
      StringHolder extension = new StringHolder();
      StringHolder mimeType = new StringHolder();
      StringHolder encoding = new StringHolder();
      ArrayOfWarningHolder warnings = new ArrayOfWarningHolder();
      ArrayOfStringHolder streamIDs = new ArrayOfStringHolder();   
      service.render(format, deviceInfo, result, extension, mimeType, encoding, warnings, streamIDs); //Render report to HTML
      
      System.out.println(new String(result.value)); //Prints the report HTML; this could be embedded in a JSP
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
     
    }
    
    public static void setExecutionId(ReportExecutionServiceSoapStub service, String id) throws SOAPException
    {
     SOAPHeaderElement sessionHeader = new SOAPHeaderElement("http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices", "ExecutionHeader");
     sessionHeader.addChildElement("ExecutionID").addTextNode(id);
     service.setHeader(sessionHeader);
    }
    
    public static ReportExecutionServiceSoapStub getService()
    {
     try
     {
      String endpoint = "http://<HOST>/reportserver/ReportExecution2005.asmx";   
      ReportExecutionServiceSoapStub service = (ReportExecutionServiceSoapStub)new ReportExecutionServiceLocator(getEngineConfiguration()).getReportExecutionServiceSoap(new URL(endpoint));
      service.setUsername("<DOMAIN>\\<USER_NAME>");
      service.setPassword("<PASSWORD>");
      return service;
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
     
     return null;
    }
    
    public static org.apache.axis.EngineConfiguration getEngineConfiguration()
    {
     java.lang.StringBuffer sb = new java.lang.StringBuffer();
     
     sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n");
     sb.append("<deployment name=\"defaultClientConfig\"\r\n");
     sb.append("xmlns=\"http://xml.apache.org/axis/wsdd/\"\r\n");
     sb.append("xmlns:java=\"http://xml.apache.org/axis/wsdd/providers/java\">\r\n");
     sb.append("<globalConfiguration>\r\n");
     sb.append("<parameter name=\"disablePrettyXML\" value=\"true\"/>\r\n");
     sb.append("<parameter name=\"enableNamespacePrefixOptimization\" value=\"true\"/>\r\n");
     sb.append("</globalConfiguration>\r\n");
     sb.append("<transport name=\"http\" pivot=\"java:org.apache.axis.transport.http.CommonsHTTPSender\"/>\r\n");
     sb.append("<transport name=\"local\" pivot=\"java:org.apache.axis.transport.local.LocalSender\"/>\r\n");
     sb.append("<transport name=\"java\" pivot=\"java:org.apache.axis.transport.java.JavaSender\"/>\r\n");
     sb.append("</deployment>\r\n");
     
     return new org.apache.axis.configuration.XMLStringProvider(sb.toString());
    }
   }
   

   
   
8. Replace all the placeholders with actual values:
   

   Lines 13-17

   Replace the report parameter names/values with your own report parameter names/values. Increase the size of the array if you have more than one parameter.
   
   

   Line 21

   Replace the report path with the path to your own report. Do NOT include the .RDL extension in this path.
   
   

   Line 55

   Replace the report host with your own report host. This is the endpoint for the ReportExecution2005 web service.
   
   

   Lines 57-58

   Replace the domain, username, and password with your own. These are the Windows credentials that will be used to access the report.
   
   

9. Execute the code. You should see the report HTML printed as output. If you face an authentication error (401) then verify NTLM is enabled and also try adding

   
   3
   
   
   
   

   in your rsreportserver.config under reportserver for basic authentication with SSRS (Thanks Sridhar Iyer).
   
   If you still receive an authentication error (401) then it may be that SSRS and/or Windows is using NTMLv2 (verified for Windows 7). See JB's comment near the bottom of this page and also visit the following link for more information: http://devsac.blogspot.com/2010/10/supoprt-for-ntlmv2-with-apache.html. (Thanks JB!)
   
   
10. Package all the classes into a JAR file and copy it into the WEB-INF/lib folder of your web app. Use the same technique as above to embed the rendered report HTML in a JSP file.
    
    
11. Build a nice user interface that accepts the appropriate report parameters and passes them through to the web service. Add an "Export" drop down list on the report page that allows users to export the report to different formats like Excel, PDF, CSV, etc. You do not have to completely reload the report every time you export it to a different format; once you initially obtain the Report Execution ID you can save it and re-use for rendering to other formats.