Monday, April 19, 2010

How to Run a Report with BI Publisher Web Service?

This is a follow up post from the last post ‘How to use BI Publisher Web Service with JDeveloper 11G’. Today, I want to talk about how to run a BI Publisher report with the Web Service within a Java Servlet. This post is assuming that you have already generated Java Web Service Proxy with JDeveloper, which creates a set of Java classes/methods that are required to call BI Publisher Web Service APIs. If you haven’t please take a look at ‘How to use BI Publisher Web Service with JDeveloper 11G’ first.

Which Method to run Report?

You can use a runReport() or runReportInSession() method to have BI Publisher Enterprise server to run a report and get the report output. The report output will be returned in binary format so you need to take care of handling the binary format appropriately in order to display or locate the report output.

Here are the definitions of runReport() and runReportInSession().

  • ReportResponse runReport(Request request, String username, String password);
  • ReportResponse runReport(Request request, String sessionToken);

While with runReport() method you need to always pass username and password, with runReportInSession() you can just pass a session token after you get with login() method. (See ‘How to use BI Publisher Web Service with JDeveloper 11G’ for the detail of the login() method.)

I’m going to use the runReportInSession() for my example below.

How to Call runReportInSession()?

First of all you need to create a ReportRequest object, with which you can set a report that you want to run. You can also set a report layout template name, report output format type such as ‘pdf, ‘html’, etc, and locale (language and territory) information. Once the ReportRequest is set then you can pass it to the runReportInSession() method along with the session token. The runReportInSession() willl return a ReportResponse object, which contains the report output in a binary format along with the report meta-data information such as content type, language information.

Here is a visualized process flow of the runReport() method.

image

 

So basically there are three steps.

  1. Create ReportRequest object
  2. Call runReportInSession() method
  3. Get a report from ReportResponse object

Create ReportRequest Object

You can set the following information when you create the ReportRequest object

  • Absolute Path to the Report
  • Report output format
  • Locale (Language and Country/Territory)
  • Template name

Function

Method

Description

Output format

void setAttributeFormat()

You can set a desired report output format such as ‘pdf’, ‘rtf’, ‘excel’, etc.

Locale

void setAttributeLocale()

You can set a user’s preferred report locale such as ‘en-US’ for English/America, ‘fr-FR’ for French/France. Based on this setting BI Publisher Enterprise will decide which language translation to use for the specified template if available.

Template Names

void setAttributeTemplate()

You can set a desired template among available templates that are registered for the specified report.

Absolute Path to the Report

void setReportAbsolutePath()

You can set an absolute path for the report that you want to run.

Example:

ReportRequest req = new ReportRequest(); //Instantiate ReportRequest object

req.setAttributeFormat("pdf"); //Set a report output format
req.setAttributeLocale("en-US"); //Set a report locale
req.setAttributeTemplate("Simple"); //Set a layout template name
req.setReportAbsolutePath("/HR Manager/Employee Salary Report/Employee Salary Report.xdo"); //Set a report path

 

Call runReportInSession()

You can call the runReportInSession() method through the publicReportService Java Web proxy client object. It returns a ReportResponse object.

Example:

ReportResponse res = new ReportResponse(); //Instantiate ReportResponse object

res = publicReportService.runReportInSession(req, sid);

 

Get a Reprot from ReportResponse Object

Here is a list of the methods that you can use to retrieve information from the ReportResponse object. And the most you care would be the getReprotBytes(), which returns a report output in a binary format.

Function

Method

Description

Report Content

byte[] getReportBytes()

You can get the report content itself. It returns the report in a form of binary so you need to take care of a proper encoding to retrieve the data and handle it appropriately.

Report Content Type

String getReportContentType()

You can get the report’s content type. For example if the report output is generated in a PDF format then it will return ‘application/pdf’.

Report Locale

String getReportLocale()

You can get a locale information of the generated report.

Example:

response.setContentType(res.getReportContentType()); // Set content type to HttpServletResponse

byte[] binaryBytes = res.getReportBytes(); // Get report content from ReportResponse

Troubleshoot

You might not get any report content from the ReportResponse object. If that’s the case you want to add the following code to set a data chunk size to ‘-1’. I had to set this before in order to get the report output successfully, but now it’s working fine without the setting with my latest BI Publisher Server environment. (March ‘10 patchset).

ReportRequest req = new ReportRequest(); //Instantiate ReportRequest object

req. setSizeOfDataChunkDownload (-1); //download all

Conclusion

Now you know how to call BI Publisher Web Service to run a report. My example is trying to get the report output and display it in a browser, but of course you can display it as part of your application UI or locate it on a file system.

Here is my whole source code. Again, I’m using a Java Servlet to exercise my code with BI Publisher Web Service.

package project1;

import com.oracle.xmlns.oxp.service.publicreportservice.AccessDeniedException;
import com.oracle.xmlns.oxp.service.publicreportservice.AccessDeniedException_Exception;
import com.oracle.xmlns.oxp.service.publicreportservice.DeliveryRequest;
import com.oracle.xmlns.oxp.service.publicreportservice.InvalidParametersException;
import com.oracle.xmlns.oxp.service.publicreportservice.InvalidParametersException_Exception;
import com.oracle.xmlns.oxp.service.publicreportservice.LocalDeliveryOption;
import com.oracle.xmlns.oxp.service.publicreportservice.OperationFailedException;
import com.oracle.xmlns.oxp.service.publicreportservice.OperationFailedException_Exception;
import com.oracle.xmlns.oxp.service.publicreportservice.PublicReportService;
import com.oracle.xmlns.oxp.service.publicreportservice.PublicReportServiceClient;

import com.oracle.xmlns.oxp.service.publicreportservice.PublicReportServiceService;
import com.oracle.xmlns.oxp.service.publicreportservice.ReportRequest;

import com.oracle.xmlns.oxp.service.publicreportservice.ReportResponse;

import com.oracle.xmlns.oxp.service.publicreportservice.ScheduleRequest;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;

import java.net.URL;

import javax.servlet.*;
import javax.servlet.http.*;

import javax.xml.namespace.QName;

public class BipServletTest extends HttpServlet {
    private static final String CONTENT_TYPE =
        "text/html; charset=windows-1252";
    private static PublicReportServiceService publicReportServiceService;

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
    }

    public void doGet(HttpServletRequest request,
                      HttpServletResponse response) throws ServletException,
                                                           IOException {
        response.setContentType(CONTENT_TYPE);
        OutputStream os = response.getOutputStream();
        //PrintWriter out = response.getWriter();
        String sid = "";
        publicReportServiceService = new PublicReportServiceService(new URL("http://knishida-lap.us.oracle.com:7001/xmlpserver/services/PublicReportService"),
                                               new QName("http://xmlns.oracle.com/oxp/service/PublicReportService",
                                                         "PublicReportServiceService"));
        PublicReportService publicReportService =
            publicReportServiceService.getPublicReportService();
        //To get a session id
       try {
            sid = this.getSessionID("Administrator", "Administrator", publicReportService);
        } catch (AccessDeniedException_Exception e) {
            System.out.println("invalid user");

        }
        //To generate a report and display it
        ReportResponse res = this.getReportInSession(publicReportService, sid);
        byte[] binaryBytes = res.getReportBytes();
        os.write(binaryBytes);
        response.setContentType(res.getReportContentType());
    }

    public String getSessionID(String username, String password, PublicReportService publicReportService) throws AccessDeniedException_Exception {
        String sid = publicReportService.login(username, password);
        return sid;
    }

    public ReportResponse getReportInSession(PublicReportService publicReportService,
                                             String sid) {

        ReportRequest req = new ReportRequest();
        ReportResponse res = new ReportResponse();

        req.setAttributeFormat("pdf");
        req.setAttributeLocale("en-US");
        req.setAttributeTemplate("Simple");
        req.setReportAbsolutePath("/HR Manager/Employee Salary Report/Employee Salary Report.xdo");
        //req. setSizeOfDataChunkDownload (-1); //download all

        try {
            res = publicReportService.runReportInSession(req, sid);
            System.out.println(res.getReportContentType());           

        } catch (Exception e) {
            {
                System.out.println(e);
            }
        }

        return res;
    } 

}

I’ll talk about how to schedule a BI Publisher report with the Web Service tomorrow, so stay tuned!

14 comments:

  1. How can parameters be passed to the report? Thanks.

    ReplyDelete
  2. Adr, I will cover this with the next post, stay tuned.

    ReplyDelete
  3. I can pass parameters via the Web Service just fine, but can't figure out how to display the value of those parameters in my report header.

    ReplyDelete
  4. Great post! But could you please also do an example how to run report in jsf file (on button click)?

    ReplyDelete
  5. Can we pass a calendar as a parameter?? or Can we set a field directly as a parameter with drop down list?? where as the data for the parameter comes fomr the application.

    ReplyDelete
  6. Hi,
    I heave very large report and I cant figure out how to get generated report in datachunks?

    ReplyDelete
  7. Anybody can send me an example of a webservice defined as source data in BI Pub ??? I' dont see any example in Internet. I'm using AXIS with Tomcat to host webservices and I can't make a webservice to run in BI Publisher !!!
    Thanks...

    ReplyDelete
  8. Hi, I've found this tutorial very helpful, it's really nice, but I have a problem, when a test the servlet with IE 8.0.7600 64.bit edition it prompts me a download window, I have to download the file y put it the .pfs extension so I can open the report. When I Test the same servlet with FireFox or Google Chrome it shows the Report with no problem, so my question is: is there a way to see the report in the Internet Explorer without having to donwload the report or there is something wrong in my Servlet??

    Sorry for my english :p

    Gustavo

    ReplyDelete
  9. Hi,
    I need to pass parameter to BIP Report from Java.
    Can anyone help .

    Thanks..

    ReplyDelete
  10. Hi there!
    We have trouble with fault processing while running reports using web service API.
    I beleive soap fault message, returned by BI Publisher, does not conform wsdl definition.
    This is an example of soap fault message:

    soapenv:Server.userException
    oracle.apps.xdo.webservice.exception.AccessDeniedException: java.lang.SecurityException: Failed to log into BI Publisher: invalid username or password.


    kenga.msk.mt



    please note tag

    wsdl requires



    instead.

    I've try to modify wsdl and now I'm able to process fault like

    but original fault message does not contains reqired namespace declaration, so I still not able to parse and proceed that faults.

    ReplyDelete
  11. Hi Nishida,

    I am really newbie in BIP, I need to ask your help that are there any posibble if i want to run more than 1 report in 1 click button or 1 call and it must be trigger by user.

    Thanks

    ReplyDelete
  12. Hi Nishida,
    I'm very new to BIP, I really need your help for following queries
    1. Is is possible to integrate custom built 2FA Authentication(could be Token-OTP or SMS-OTP) with BIP Login or do we need to develop separate web application and somehow do login to BIP as well(not sure if this could be possible.
    2. Our application will have some input screens as well. Will it be possible to create screens in BIP.
    3. Entire UI needs to be custom built.

    We are thinking of creating separate Web application and run reports using web services APIs. Can you help to comment if we are moving in right direction or is it possible to do above things in BIP itself

    Thanks & Rgds

    ReplyDelete
  13. Hi,

    Thanks for really nice example it . . help me to achieve my goal.

    Now I facing an issue while invoking .. a report which accept parameters.
    it throwing below exception ..
    =====================================================================
    java.lang.NullPointerException
    at java.io.OutputStream.write(OutputStream.java:58)
    at bpi_webservice.BipServletTest.doGet(BipServletTest.java:63)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
    at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
    at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
    at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
    at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:111)
    at java.security.AccessController.doPrivileged(Native Method)
    at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:313)
    at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:413)
    at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:94)
    at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:161)
    at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:136)
    at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
    at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
    at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
    at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
    at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277)
    at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
    at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)
    SUBSYSTEM = HTTP USERID = SEVERITY = Error THREAD = [ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)' MSGID = BEA-101020 MACHINE = IND-MHP3DW75691 TXID = CONTEXTID = b37e107dc437f893:-20e948db:13b4c033a57:-8000-0000000000000089 TIMESTAMP = 1354192022505
    WatchAlarmType: AutomaticReset
    WatchAlarmResetPeriod: 30000

    Please help.
    Waiting for your reply.
    Thanks,
    Sunil

    ReplyDelete
  14. Thank Kan Nishida for this useful post.

    How can i get the flash report output ?
    I set req.setAttributeFormat("flash"); but it didn't work.

    ReplyDelete