Angelo Santagata's Blog

Accessing Fusion Data from BI Reports using Java

Angelo Santagata


In a recent article by Richard Williams on A-Team Chronicles, Richard explained how you can execute a BI publisher report from a SOAP Service and retrieve the report, as XML, as part of the response of the SOAP call.  This blog article serves as a follow on blog article providing a tutorial style walk through on how to implement the above procedure in Java.

This article assumes you have already followed the steps in Richard's blog article and created your report in BI Publisher, exposed it as a SOAP Service and tested this using SOAPUI, or another SOAP testing tool.

Following Richards guidance we know that he correct SOAP call could look like this

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
<pub:reportAbsolutePath>/~angelo.santagata@oracle.com/Bi report.xdo</pub:reportAbsolutePath>
<pub:reportRawData xsi:nil="true" >true</pub:reportRawData>
Tip :One easy way to determine the reports location is to run the report and then examine the URL in the browser.


Implementing the SOAP call using JDeveloper 11g

We can now need to implement the Java SOAP Client to call our SOAP Service. For this blog we will use JDeveloper 11g, the IDE recommended for extending Oracle Fusion, however you are free to use your IDE of choice, e.g. NetBeans, Eclipse, VI, Notepad etc, the steps will obviously be different.

Creating the project

Within JDeveloper 11g start by creating a new Application and within this application create two generic projects. Call one project “BISOAPServiceProxy” and the other “FusionReportsIntegration”. The "BISOAPServiceProxy" project will contain a SOAP Proxy we are going to generate from JDeveloper 11g and the "FusionReportsIntegration" project will contain our custom client code. It is good practice to create separate projects so that the SOAP Proxies resides in its own separate project, this allows us to regenerate the proxy from scratch without affecting any other code.

Generating the SOAP Proxy

For this example we will be using the SOAP Proxy wizard as part of JDeveloper. This functionality generates a static proxy for us, which in turn makes it easier to generate the required SOAP call later.

  1. 1. With the BISOAPService project selected, start the JDeveloper SOAP Proxy wizard.
    File-> New-> Business Tier-> Web Services-> Web Service Proxy
  2. Proxy1
  3. 2. Click Next
  4. 3. Skipping the first welcome screen, in step 2 enter the JAX-WS Style as the type of SOAP Proxy you wish to generate in step 3 enter the WSDL of your Fusion Application BI Publisher webservice WSDL. It’s best to check this URL returns a WSDL document in your web browser before entering it here. The WSDL location will normally be something like : http://<your fusion Applications Server>/xmlpserver/services/ExternalReportWSSService?wsdl
  5. Proxy2
  6. It's recommended that you leave the copy WSDL into project check-box selected.
  7. 4. Give a package name, unless you need to it's recommended to leave the Root Package for generated types to be left blank
  8. proxy3
  9. 5. Now hit Finish

Fixing the project dependencies

We now need to make sure that the “FusionReportsIntegration” is able to see classes generated by the  “BISOAPServiceProxy” proxy. To resolve this in JDeveloper we simply need to setup a dependency between the two projects.

  1. 1. With the FusionReportsIntegration project selected, right-mouse click on the project and select “Project properties
  2. 2. In the properties panel select Dependencies
  3. 3. Select the little pencil icon and in the resulting dialog select “Build Output”. This selection tells JDeveloper that “this project depends on the successful build output” of the other project.
  4. 4. Save the Dialog
  5. 5. Close [OK] the Project Properties dialog
  6. 6. Now is a good time to hit compile and make sure the SOAP proxy compiles without any errors, given we haven't written any code yet it should compile just fine.

Writing the code to execute the SOAP call

With the SOAP Proxy generated, the project dependency setup, we’re now ready to write the code which will call the BI Server using the generated SOAP Proxy

  1. 1. With the Fusion Reports Integration selected , right mouse Click -> New -> Java -> Java Class
  2. 2. Enter a name, and java package name, for your class
  3. 3. Ensure that “Main Method” is selected. This is so we can execute the code from the command line, you will want to change this depending on where you execute your code from, e.g. A library, a servlet etc.
  4. 4. Within the main method you will need to enter the following code snippet, once this code snippet is pasted you will need to correct and resolve imports for your project.
  5. 1.

    ExternalReportWSSService_Service externalReportWSSService_Service;

    // Initialise the SOAP Proxy generated by JDeveloper based on the following WSDL xmlpserver/services/ExternalReportWSSService?wsdl

    externalReportWSSService_Service = new ExternalReportWSSService_Service();

    // Set security Policies to reflect your fusion applications

    SecurityPoliciesFeature securityFeatures = new SecurityPoliciesFeature(new String[]

    { "oracle/wss_username_token_over_ssl_client_policy" });

    // Initialise the SOAP Endpoint

    ExternalReportWSSService externalReportWSSService = externalReportWSSService_Service.getExternalReportWSSService(securityFeatures);

    // Create a new binding, this example hardcodes the username/password,

    // the recommended approach is to store the username/password in a CSF keystore

    WSBindingProvider wsbp = (WSBindingProvider)externalReportWSSService;

    Map<String, Object> requestContext = wsbp.getRequestContext();

    //Map to appropriate Fusion user ID, no need to provide password with SAML authentication

    requestContext.put(WSBindingProvider.USERNAME_PROPERTY, "username");

    requestContext.put(WSBindingProvider.PASSWORD_PROPERTY, "password");

    requestContext.put(WSBindingProvider.ENDPOINT_ADDRESS_PROPERTY, "https://yourERPServer:443/xmlpserver/services/ExternalReportWSSService");

    // Create a new ReportRequest object using the generated ObjectFactory

    ObjectFactory of = new ObjectFactory();

    ReportRequest reportRequest = of.createReportRequest();

    // reportAbsolutePath contains the path+name of your report

    reportRequest.setReportAbsolutePath("/~angelo.santagata@oracle.com/Bi report.xdo");

    // We want raw data


    // Get all the data


    // Flatten the XML response


    // ByPass the cache to ensure we get the latest data


    // Run the report

    ReportResponse reportResponse = externalReportWSSService.runReport(reportRequest, "");

    // Display the output, note the response is an array of bytes, you can convert this to a String

    // or you can use a DocumentBuilder to put the values into a XLM Document object for further processing

    System.out.println("Content Type="+reportResponse.getReportContentType());

    System.out.println("Data ");


    String data=new String (reportResponse.getReportBytes());


  6. Going through the code

    LineWhat does it do
    1-3This is the instantiation of a new class containing the WebService Proxy object. This was generated for us earlier
    5Initialise a new instance of a security policy object, with the correct security policy, for your Oracle Fusion server . The most common security policy is that of “oracle/wss_username_token_over_ssl_client_policy", however your server maybe setup differently
    8Calls the factory method to initialise a SOAP endpoint with the correct security features set
    9-16These lines setup the SOAP binding so that it knows which endpoint to execute (i.e. the Hostname+URI of your webservice which is not necessarily the endpoint where the SOAP Proxy was generated, the username and the password.In this example we are hard coding the details because we are going to be running this example on the command line. If this code is to be  executed on a JEE server, e.g. Weblogic, then we recommend this data is stored in the Credential store as CSF keys.
    17-19Here we create a reportRequest object and populate it with the appropriate parameters for the SOAP call. Although not mandatory its recommended that you use the objectFactory generated by the SOAP proxy wizard in JDeveloper.
    21This set the ReportPath parameter, including path to the report
    23This line ensures we get the raw data without decoration, layouts etc.
    25By default BI Publisher publishes data on a range basis, e.g. 50 rows at a time, for this usecase we want all the rows, and setting this to -1 will ensure this
    27Tells the webservice to flatten out the XML which is produced
    29This is an optional flag which instructs the BI Server to bypass the cache and go direct to the database
    30This line executes the SOAP call , passing the “reportReport” object we previously populated as a parameter. The return value is a reportResponse object
    34-39These lines print out the results from the BI Server. Of notable interest is the XML document is returned as a byte array. In this sample we simply print out the results to the output, however you would normally pass the resulting XML into Java routines to generate a XML Document.



Because we are running this code from the command line as a java client code we need to import the Fusion Apps Certificate into the Java Key Store. If you run the code from within JDeveloper then the java keystore is stored in <JDeveloperHome>\wlserver_10.3\server\lib\DemoTrust.jks

Importing certificates


  1. 1. Download the Fusion Applications SSL certificate, using a browser like internet explorer navigate to the SOAP WSDL URL
  2. 2. Mouse click on the security Icon which will bring you to the certificate details
  3. 3. View Certificate
    4. Export Certificate as a CER File
  4. 5. From the command line we now need to import the certificate into our DemoTrust.jks file using the following commandkeytool -import -alias fusionKey -file fusioncert.cer -keystore DemoIdentity.jks


Now ready to run the code!

With the runReport.java file selected press the “Run” button, if all goes well then the code will execute and you should see the XML result of the BI Report displayed on the console.


Join the discussion

Comments ( 7 )
  • guest Monday, October 24, 2016


    I followed your article and created the jdeveloper projects accordingly. Now when i am running the RunReport.java jdeveloper asks for Run Target. I am new to jdeveloper, could you please help me what should i give as a run target.

    Thanks in advance.

  • Angelo Monday, October 24, 2016

    I think you need to select the java file you want to run then right mouse click run on that file

  • guest Tuesday, November 8, 2016


    I am using Jdeveloper 12c version and implemented the projects as per this article. But getting the following error when i am running "RunReport.java". Could you please help me resolving the below error ? Is there any other steps i need to follow apart from the steps you mentioned in your article ?

    SEVERE: java.io.FileNotFoundException: .\config\jps-config.xml (The system cannot find the path specified)

    INFO: Policy subject is not registered.

    SEVERE: java.io.FileNotFoundException: .\config\jps-config.xml (The system cannot find the path specified)

    SEVERE: java.io.FileNotFoundException: .\config\jps-config.xml (The system cannot find the path specified)

    SEVERE: java.io.FileNotFoundException: .\config\jps-config.xml (The system cannot find the path specified)

    SEVERE: java.io.FileNotFoundException: .\config\jps-config.xml (The system cannot find the path specified)

    INFO: EffectivePolicySetFeature not on the binding, will look up policy set for; ResourcePattern [absolutePortableExpression=///UNKNOWN|#MODULE|WS-Client({http://xmlns.oracle.com/oxp/service/PublicReportService}ExternalReportWSSService#ExternalReportWSSService,wls)]

    SEVERE: java.io.FileNotFoundException: .\config\jps-config.xml (The system cannot find the path specified)

    INFO: EffectivePolicySetFeature=oracle.j2ee.ws.common.wsm.EffectivePolicySetFeature@6082c985

    Process exited with exit code 0.

  • Juan Pablo Monday, January 9, 2017


    I follow your guide and it worked smoothly! Thanks for the tips.

    Now I'm trying to run a report which has parameters, do you have an example where you pass parameters to the report?



  • guest Wednesday, January 18, 2017

    Can you provide some example how to add a parameter for a report in this example

  • guest Monday, February 13, 2017

    Hi how to pass the parameters to the datamodels using web service methods?

  • guest Monday, February 13, 2017

    Hi how to pass the parameters to the datamodels using web service methods?

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.