Calling SSRS from Java

Question

Calling SSRS from Java

Ludo-ZX7R

SSCrazy

Points: 2882
More actions
November 17, 2010 at 9:52 am

#229340

Hi all,
We have some SQL Server Reports that we can access directly from http://<server>/ReportServer but we would like to be able to call them directly from our Java Application.
Reporting services exposes a web service that can be used for running and (I believe) retrieving reports. The WSDL is here on the server:
http://<server name>/reportserver/ReportService2010.asmx?wsdl
I've used AXIS 1.x to generate client classes and I have the following:
ReportingService2010Locator loc = new ReportingService2010Locator(); ReportingService2010Soap port = loc.getReportingService2010Soap(new
java.net.URL("http://<Server
name>/reportserver/ReportService2010.asmx"));
CatalogItem[] catalogItems = port.listChildren("/", true); for (CatalogItem catalogItem : catalogItems) {
System.out.println(catalogItem.getPath());
}
Running it gives me the following error:
Exception in thread "main" AxisFault
faultCode: {http://xml.apache.org/axis/}HTTP
faultSubcode:
faultString: (401)Unauthorized
faultActor:
faultNode:
faultDetail:
{}:return code: 401
{http://xml.apache.org/axis/}HttpErrorCode:401
(401)Unauthorized
...
I've tried adding a user name and password to the header:
org.apache.axis.client.Stub stub = (org.apache.axis.client.Stub) port;
stub.setHeader("http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer",
"UserName",
"...");
stub.setHeader("http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer",
"Password",
"...");
and I've tried this:
org.apache.axis.client.Stub stub = (org.apache.axis.client.Stub) port;
stub.setUsername("...");
stub.setPassword("...");
Neither of which solve the problem.
I've also tried soupUI. When I enter the WSDL URL I have to also enter the user name and password. Then I can call the webservice successfully for a period of time. It's almost like soupUI maintains a session which eventually times out and I again get the 401.
Does anyone have any experience of this and can help me out?
April 6, 2022 at 8:44 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

veeren4urs SSCrazy Points: 2763 More actions · Answer 1

i dnt know much in Java and the above issue but if possible try to call the reports in the new window by clicking on the Hyperlin something.

Thanks,

Veeren

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 2

Hi

Thanks for taking the time to answer my question.

We can access the report directly by selecting the hyperlink. The report displays and render nicely in IE so there is no problem here.

I hope this is what you were asking.

Cheers

Ludo

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 3

For anybody interested here is the solution:

import java.io.FileOutputStream;

import java.io.OutputStream;

import javax.xml.rpc.holders.ByteArrayHolder;

import javax.xml.rpc.holders.StringHolder;

import javax.xml.soap.SOAPElement;

import org.apache.axis.client.Call;

import org.apache.axis.message.SOAPHeaderElement;

import org.apache.log4j.Logger;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.*;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.holders.*;

public class GenerateReport {

public void GenerateReport(String reportName, OutputStream ouptutStream) throws Exception {

ReportExecutionServiceLocator exLoc = new ReportExecutionServiceLocator();

ReportExecutionServiceSoap rs

= exLoc.getReportExecutionServiceSoap(

new java.net.URL("http://yourserver:80/ReportServer/ReportExecution2005.asmx")

);

org.apache.axis.client.Stub clientStub = (org.apache.axis.client.Stub) rs;

clientStub._setProperty(Call.USERNAME_PROPERTY, "goraya");

clientStub._setProperty(Call.PASSWORD_PROPERTY, "goraya");

ExecutionInfo execInfo = new ExecutionInfo();

execInfo = rs.loadReport(reportName, null);

SOAPHeaderElement sessionHeader = new SOAPHeaderElement(

"http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices",

"ExecutionHeader");

SOAPElement addChildElement = sessionHeader.addChildElement("ExecutionID");

addChildElement.addTextNode(execInfo.getExecutionID());

clientStub.setHeader(sessionHeader);

String format = "PDF";

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

ByteArrayHolder result = new ByteArrayHolder();

StringHolder extension = new StringHolder();

StringHolder mimeType = new StringHolder();

StringHolder encoding = new StringHolder();

ArrayOfWarningHolder warnings = new ArrayOfWarningHolder();

ArrayOfStringHolder streamIDs = new ArrayOfStringHolder();

rs.render(format, devInfo, result, extension, mimeType, encoding, warnings, streamIDs);

ouptutStream.write(result.value);

}

public static void main(String args[]) {

String path = "/Reports/Test Report";

try {

FileOutputStream o = new FileOutputStream("C:\\TEMP\\TestReport.pdf");

new GenerateReport().GenerateReport(path, o);

} catch (Exception e) {

e.printStackTrace();

}

Ludo

vitxu Newbie Points: 1 More actions · Answer 4

Great job,

but when I tried to do it in my project, I faced with two issues:

wsimport do not create an ReportExecutionServiceLocator class;
I couldn't cast the reporting service soap into a Stub class.

Maybe it is because of my dependency:

<artifactId>jaxws-rt</artifactId>

</dependency>

I googled a lot and then I found another way to do it with this dependency:

https://surendragurjar.wordpress.com/2015/07/11/ssrs-wsdl-web-service-endpoint-using-java/

The only thing that this post does not say is how to change the endpoint url, and this is the way.

ReportExecutionService rpt = new ReportExecutionService();

ReportExecutionServiceSoap rs = this.rpt.getReportExecutionServiceSoap();

BindingProvider bp = (BindingProvider) rs;

bp.getRequestContext().replace(BindingProvider.ENDPOINT_ADDRESS_PROPERTY, this.reportServerURL);

In below you have the post copied.

Regards

SSRS wsdl web service endpoint using Java

1) Download SSRS wsdl service classes for using wsdl endpoint from SSRS server using wsimport command

wsimport.exe -Xauthfile wsxauthfile http://<servername>/reportserver/reportService2005.asmx?wsdl -s <location to download files> -extension

Here: wsxauthfile is auth credentials file, create a file where you are downloading SSRS web service classes

Contents in wsxauthfile : http://<username>:<password>@<servername>/ReportServer/reportexecution2005.asmx?wsdl

e.g.: open command prompt and create d:ssrs\lib folder

Now, you need to put wsxauthfile inside d:\ssrs\lib folder

d:\ssrs\lib>wsimport.exe -Xauthfile wsxauthfile http://<servername>/reportserver/reportService2005.asmx?wsdl -s d:\ssrs\lib -extension

2) Create dynamic web project in eclipse

Open eclipse -> File -> New -> Project… -> Web -> Dynamic Web Project

Copy all the downloaded Classes files from wsimport command in src folder inside Java Resources

Create ReportAuthenticator.java inside util package

Create PrintSSRSReport.java inside default package or wherever you want

Code snippets:

a) ReportAuthenticator.java

/*

* util.ReportAuthenticator.java

* Authenticates to the report server using NTLM.

*/

package util;

import java.net.PasswordAuthentication;

public final class ReportAuthenticator extends java.net.Authenticator{

private String username = “<domain>\\<username>”;

private String password = “<password>”;

public ReportAuthenticator() {}

protected PasswordAuthentication getPasswordAuthentication(){

return new PasswordAuthentication(username,(password.toCharArray()));

}

b) PrintSSRSReport.java

/*

* TestSSRSNew.java

* Print report on Browser using SSRS wsdl service.

*/

import java.io.IOException;

import java.net.Authenticator;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.xml.ws.Holder;

import util.ReportAuthenticator;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ArrayOfParameterValue;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ArrayOfString;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ArrayOfWarning;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ExecutionHeader;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ExecutionInfo;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ParameterValue;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ReportExecutionService;

import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.ReportExecutionServiceSoap;

import com.sun.xml.internal.ws.developer.WSBindingProvider;

public class PrintSSRSReport extends HttpServlet {

private static final long serialVersionUID = 1L;

private static final String REPORT_FORMAT = “HTML4.0“;

private static final String DEVICE_INFO = “<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>True</HTMLFragment></DeviceInfo>”;

private static final String LANGUAGE = “en-us”;

private static final String PARAMETER_NAME = “<parameter_name>“;

private static final String PARAMETER_VALUE = “<parameter_value>“;

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

ReportExecutionServiceSoap service = getService();

if (service == null)

System.exit(1);

try {

ExecutionInfo info = service.loadReport(“<report_path>”, null);

String executionId = info.getExecutionID();

System.out.println(“ExecutionID: ” + info.getExecutionID() + ” ” + info.toString());

ArrayOfParameterValue();

arrayOfParameterValue.getParameterValue();

ExecutionHeader execHeader = new ExecutionHeader();

execHeader.setExecutionID(executionId);

// Set ExecutionID to ReportExecutionServiceSoap ExecutionHeader

((WSBindingProvider)service).setOutboundHeaders(execHeader);

ArrayOfParameterValue arrayOfParameterValue = new ArrayOfParameterValue();

List<ParameterValue> parameters = arrayOfParameterValue.getParameterValue();

ParameterValue parameterValue = new ParameterValue();

parameterValue.setName(PARAMETER_NAME);

parameterValue.setValue(PARAMETER_VALUE);

parameters.add(parameterValue);

service.setExecutionParameters(arrayOfParameterValue, LANGUAGE);

Holder<byte[]> result = new Holder<byte[]>();

Holder<String> extension = new Holder<String>();

Holder<String> mimeType = new Holder<String>();

Holder<String> encoding = new Holder<String>();

Holder<ArrayOfWarning> warnings = new Holder<ArrayOfWarning>();

Holder<ArrayOfString> streamIDs = new Holder<ArrayOfString>();

// Render report to HTML

service.render(REPORT_FORMAT, DEVICE_INFO, result, extension, mimeType, encoding, warnings, streamIDs);

System.out.println(“result: ” + new String(result.value));

ServletOutputStream out = response.getOutputStream();

out.write(result.value);

out.flush();

out.close();

} catch (Exception e) {

e.printStackTrace();

}

private static ReportExecutionServiceSoap getService() {

Authenticator.setDefault(new ReportAuthenticator());

ReportExecutionService resrv = new ReportExecutionService();

ReportExecutionServiceSoap resrcs = resrv.getReportExecutionServiceSoap();

return resrcs;

}

3) Set web.xml file properties to run Servlet on Browser

Code snippet:

<?xml version=”1.0″ encoding=”UTF-8″?>

<web-app xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”http://java.sun.com/xml/ns/javaee” xsi:schemaLocation=”http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd” id=”WebApp_ID” version=”3.0″>

<display-name>SampleSSRS</display-name>

<display-name>PrintSSRSReport</display-name>

<servlet-name>PrintSSRSReport</servlet-name>

<servlet-class>PrintSSRSReport</servlet-class>

</servlet>

<servlet-mapping>

<servlet-name>PrintSSRSReport</servlet-name>

<url-pattern>/PrintSSRSReport</url-pattern>

</servlet-mapping>

</web-app>

4) Now finally some patch work: After much research on internet I found that, the code we have downloaded using wsimport command has some runtime error due to that the we could not Set ExecutionID to ReportExecutionServiceSoap -> ExecutionHeader and get Error:

“javax.xml.ws.soap.SOAPFaultException: The session identifier is missing. A session identifier is required for this operation. —> Microsoft.ReportingServices.Diagnostics.Utilities.MissingSessionIdException: The session identifier is missing. A session identifier is required for this operation.”

Fix:

a) Open ReportExecutionServiceSoap interface and just add ExecutionHeader.class in @XmlSeeAlso block on line number 22

@XmlSeeAlso({ ObjectFactory.class, ExecutionHeader.class

})

b) Open ExecutionHeader class and add @XmlRootElement(name=”ExecutionHeader”) above the

class name on line number 41

@XmlAccessorType(XmlAccessType.FIELD)

@XmlType(name = “ExecutionHeader”, propOrder = {“executionID”

})

@XmlRootElement(name=”ExecutionHeader”)

public class ExecutionHeader {

5) Host the project on any server like JBoss, Tomcat

6) Hit the “http://localhost:8080/SampleSSRS/PrintSSRSReport” on the browser and hopefully you should be able to see the report!!!!