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.

Enable NTLM Authentication in Axis

By default Axis is not configured to support NTLM Authentication. To enable NTLM Authentication Axis must be configured to use the Jakarta Commons HTTPClient library. NTLM Authentication is often required to use Windows based web services.

There are a few different methods for accomplishing this, but this is probably the quickest and simplest technique:

1. Download and copy both the Jakarta Commons HTTPClient and Codec JAR files into your project and add them to the classpath.
   
   
2. Copy and paste the following method into your own class; append it to the end of your pre-existing class or create a separate utility class:
   

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

   Note: It's probably a good idea to separate the XML markup from the Java code (move XML markup to separate file). I just left the two joined together for the sake of simplicity / laziness since it eliminated one extra step from this blog posting.

3. When you create an instance of your web service locator class pass the getEngineConfiguration() method as an argument in the constructor like this:
   

   MyWebServiceStub stub = (MyWebServiceStub)new MyWebServiceLocator(getEngineConfiguration());

   
   
4. Set your user name and password like this:

   stub.setUsername("<DOMAIN>\\<USER_NAME>"); //Must use this format
   stub.setPassword("<PASSWORD>");

   
   

For more information see the following links:

• http://dev-answers.blogspot.com/2011/03/ntlm-from-axis-soap-service-client-in-3.html
• http://tjordahl.blogspot.com/2007/03/apache-axis-and-commons-httpclient.html
• http://www.nsftools.com/stubby/ApacheAxisClientTips.htm

Configure Advanced Axis Settings from Eclipse

Occasionally you may need to configure advanced Axis settings from Eclipse. Some common reasons for this are:

• You need to increase or disable the Axis timeout if you are trying to consume a very large or complex web service.
  
  
• You need to generate code for all elements, even unreferenced ones.
  
  

To access these settings, follow these instructions:

1. Click Window -> Preferences from the Eclipse menu bar.
   
   
2. Expand the Web Services node and then select Axis Emitter in the tree view located in the left pane.
   
   

You should see the Axis Emitter screen which contains a variety of options for both Wsdl2Java and Java2Wsdl. These settings are workspace specific so you will need to redefine them again if you switch workspaces. From this screen you can check the "Generate code for all elements, even unreferenced ones" checkbox, and you can set the "Timeout in seconds" to -1 to disable it (see screenshot below).

At the time of writing this tutorial I am using Eclipse Helios with the J2EE extensions installed; options may vary slightly if using a different version.

Consume Any Web Service Using Eclipse + Axis

1. Start Eclipse and create a new workspace named wsTest (click File -> Switch Workspace -> Other…)
   
   
   
   
2. Create a new Java Project named wsTest (click File -> New -> Java Project)
   
   
   
   
3. Create a new Web Service Client Proxy (click File -> New -> Other -> Web Services -> Web Service Client). In this case we are consuming a free, public web service that returns weather information for the United States. The URL for the WSDL definition is http://wsf.cdyne.com/WeatherWS/Weather.asmx?wsdl.
   
   
   
   
4. Create a new Java Class named Test.java (click File -> New -> Class)
   
   
   
   
5. Copy and paste the following code into Test.java (overwrite the existing code):
   

   import com.cdyne.ws.WeatherWS.*;
   import java.net.URL;
   
   public class Test
   {
    public static void main(String[] arg)
    {
     try
     {
      WeatherSoapStub service = (WeatherSoapStub)new WeatherLocator().getWeatherSoap(new URL("http://ws.cdyne.com/WeatherWS/Weather.asmx"));
      WeatherReturn weather = service.getCityWeatherByZIP("47710");
      System.out.println("Location: " + weather.getCity() + ", " + weather.getState());
      System.out.println("Description: " + weather.getDescription());
      System.out.println("Temperature: " + weather.getTemperature() + " degrees");
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
    }
   }
   

   
   Note that we set the endpoint URL on line 10. The endpoint URL is typically the same as the WSDL definition URL except the endpoint URL does not include the ?WSDL query string parameter.
   
   
6. Execute the code (click Run -> Run)
   
   You should see something resembling the following output:
   
   Location: Evansville, IN
   Description: Sunny
   Temperature: 49 degrees

At the time of writing this post I am using Eclipse Helios with the J2EE extensions installed; options may vary slightly if using a different version.

---

How It Works

Eclipse is executing the Axis Wsdl2Java process which parses the WSDL file and translates it into a set of Java client files that you can then use in your own application. It is possible to accomplish the same thing outside of Eclipse by directly executing the Axis Wsdl2Java process yourself.