Accessing MS Reporting Services with Java


In this article I describe how to access Microsoft Reporting Services Web Service interface with a Java web service client.


Microsoft Reporting Services


MS Reporting Services (MSRS) provides its full functionality via two Web services endpoints. The WSDL for the endpoints are accessed using the following URL’s:


http://<Server Name>/ReportServer/ReportService2005.asmx?wsdl
http://<Server Name>/ReportServer/ReportExecution2005.asmx?wsdl


The first on is the Management Endpoint. It is used for doing all the management tasks on the reporting server (manage report access, adding reports, manage data sources, etc).


The second on is for rendering reports. An example how this is done, is described in the following sections.


Environment


Here are the tools I used to develop the Java Web Service client.


-          JDK 6u11


-          Apache Tomcat 6.0.18


-          Ant 1.7


-          Eclipse JEE


-          Metro 1.4


As host for the Reporting Services 2008 I used MS Window 2008.


Creating the Web service proxy


The Web service proxy is created by a tool called wsimport. It is part of the Metro toolkit, and is started from the command line.


Here is the step by step guide for creating the proxy classes:


1.       Create a temporary directory (e.g. c:\temp)


2.       Create a subdirectory called ‚src’.


3.       Open a command line window and change path to c:\temp.


4.       Execute wsimport:
wsimport  http://servername/reportserver/reportExecution2005.asmx?wsdl –s src –extension


5.  If you want to access the management endpoint, execute the following statement as well:
wsimport http://servername/reportserver/ReportServcie2005.asmx?wsdl –s src


The execution of wsimport command produces Web service proxy classes. The source code for the proxy is stored into the src folder, and the compiled classes are put into the root folder from where you called wsimport.


In this case we are not interested in the compiled version, because there is a little bug in the generated proxy classes we have to fix manually.


Include the proxy classes in your project


As mentioned, there is a little bug in the proxy classes. To be able to fix this bug, we have to include the source code of the proxy in the project we want to calls the Reporting Serivces Web service. An alternative way, can be to build a new project for the proxy, fix the problem there, and use the compiled output of this project in your Web service client project.


The necessary change has to be done in the ExecutionHeader class.


@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name=”ExecutionHeader”, propOrder={“executionID”})
@XmlRootElement(name=”ExecutionHeader”)
public class ExecutionHeader…


Using the Web service to render a report


The following code sample shows, how to render a report with the Web service-



String reportPath = "/AdventureWorks 2008 Sample Reports/Employee Sales Summary 2008";
String format = "HTML4.0";
String historyID = null;
String devInfo = "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>True</HTMLFragment></DeviceInfo>";
String executionID = null;
Holder<String> extension = null;
Holder<String> mimeType = null;
Holder<String> encoding = null;
Holder<ArrayOfWarning> warnings = null;
Holder<ArrayOfString> streamIDs = null;
Holder<byte[]> result = new Holder<byte[]>();


ReportExecutionService res = new ReportExecutionService();
ReportExecutionServiceSoap ress = res.getReportExecutionServiceSoap();

BindingProvider bp = (BindingProvider)ress;
WSBindingProvider wsbp = (WSBindingProvider)ress;


// Sessions erlauben
bp.getRequestContext().put(BindingProvider.SESSION_MAINTAIN_PROPERTY, true);


ExecutionInfo execInfo = new ExecutionInfo();
            


// Parameterliste erzeugen
ArrayOfParameterValue apv = new ArrayOfParameterValue();
List<ParameterValue> apvList = apv.getParameterValue();    


ParameterValue param0 = new ParameterValue();
param0.setName("ReportMonth");
param0.setValue(Month);
ParameterValue param1 = new ParameterValue();
param1.setName("ReportYear");
param1.setValue(Year);
ParameterValue param2 = new ParameterValue();
param2.setName("EmployeeID");
param2.setValue(Employee);
apvList.add(param0);
apvList.add(param1);
apvList.add(param2);


// Report vorbereiten
execInfo = ress.loadReport(reportPath, historyID);
// ExecutionID für den nächsten Aufruf merken
executionID = execInfo.getExecutionID();      
//bp.getRequestContext().put("sessionID", executionID);


// ExecutionHeader Element erzeugen und es für den nächsten Aufruf
// and den WSBindingProvider übergeben
ExecutionHeader eh = new ExecutionHeader();
eh.setExecutionID(executionID);
wsbp.setOutboundHeaders(eh);
      


// Parameter an den Report übergeben
ress.setExecutionParameters(apv, "en-us");


// Report anfordern
ress.render(format, devInfo, result, extension, mimeType, encoding, warnings, streamIDs);

// Ergebnis des Aufrufs ausgeben

String resultString = new String(result.value);
            


In the example, the result is an HTML fragment, stored in a string variable. This fragment can be integrated in a web page as shown in the picture below. In this example, the content below the input fields is the output of the Web service call.


 



 


Comments (10)

  1. Anonymous says:

    Thanks for the help!  It was a great help.   I did have to add this line though:

    apv.parameterValue = apvList

  2. Anonymous says:

    Where can i find source code for WSBindingProvider ? I am pretty new to this, really appreciate any help on this.

  3. Anonymous says:

    Nevermind. I got it working. I had to recreate the proxy classes and use jaxws-rt-2.1.4.jar and it worked like a charm. Thanks a ton for the post.

  4. Anonymous says:

    Thanks – awesome information…

    My datasource requires credentials so I set them with setExecutionCredentials, but then get "Query execution failed for data set 'DataSet1'. —> Database specified in security token is not supported by this server." error.  Looking at the soap messages i can't see a security token or reference to a database… wondering if anyone else had encountered the same thing?

  5. Anonymous says:

    Thanks for your sharing, that helps a lot . But  I came across a issue. I use 'render' method to generate a HTML fragment , but when that report contains image, That image will exist as a 'report server URL request' in the HTML.

    In that case, the authorization is required ,  I must input the server username and password to make the image available.

    Is there any method to avoid this , Or I am doing something wrong ?

    Thanks a lot and expecting your help.

  6. Anonymous says:

    If am using WSBindingprovider to set  setOutBoundHeader with Websphere7.0 it is thowing ClassCastException.

    Thanks advance for any help.

  7. Anonymous says:

    The code above for SQL 2008 or 2005? can I use 2005 wsdl to connect 2008 server?

  8. Anonymous says:

    and what is the purpose of String devInfo = "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>True</HTMLFragment></DeviceInfo>";

  9. Anonymous says:

    String devInfo = "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>True</HTMLFragment></DeviceInfo>";

    I do not understand this line of code

  10. Anonymous says:

    You can use axis, axis-wsdl4j instead of metro.

Skip to main content