How to Schedule a Report with BI Publisher Web Service?

This is part of the BI Publisher Web Service series. I have already covered ‘How to use BI Publisher Web Service with JDeveloper 11G’ and ‘How to Run a Report with BI Publisher Web Service’. If you haven’t checked the posts and are interested in please click the above links to check.

Today, I’m going to talk about how to schedule a BI Publisher report with the Web Service within a Java application.

Which Method to Schedule Report?

So which method we can use to schedule ? It’s like the ones for running a report, there are two methods. One is ‘scheduleReport() and another is ‘scheduleReportInSession()’. ‘scheduleReport() takes a username and password while scheduleReportInSession takes a session token along with a ‘ScheduleRequest’ object. And both returns a job id, which you can use to monitor and manage the scheduled job later.

Definition:

• String scheduleReport(ScheduleRequest srequest, String username, String password)
• String scheduleReport(ScheduleRequest srequest, String SessionToken)

With those methods you can do pretty much the same things you can do from the UI. I’m going to use scheduleReport() method for my example below.

 

How to Call scheduleReportInSession()

Before you run the scheduleReportInSession() method, first you need to create a ReportRequest object by giving a report path for the report you want to run. This is exactly the same step I mentioned in the last post with runReportInSession().

Second, you need to create a DeliveryRequest object if you need to deliver the report. You can set a delivery option and its delivery type specific information here.

Third, now you need to create a ScheduleRequest object by setting the ReportRequest and DeliveryRequest objects.

And finally you can call the scheduleReportInSession() method to schedule a report.

Here is a list of those four steps:

1. Create DeliveryRequest object
2. Create ReportRequest object
3. Create ScheduleRequest object
4. Call scheduleReportInSession() method

So, let’s take a look at the each step.

 

Create DeliveryRequest Object

1) Create Delivery Option to Set Delivery Information

First you need to create a DeliveryOption object. There are different types of the objects, each of which is for each delivery type. So if you want to deliver your report by email then there is a one DeliveryOption for Email called ‘EmailDeliveryOption’. And it goes the same for other delivery options. Here is a list of the delivery options available for you with BI Publisher.

• Email
• Fax
• FTP
• Local File System
• Printer
• WebDAV

And all the options are available with the Web Service. When you create the DeliveryOption you can set each of the delivery option specific information. For example, if you want to use the email delivery option then you can set the email delivery related information such as an email subject,  an email address shown as ‘from’ or ‘cc’, etc.

Here is a list of the DeliveryOption types and a list of methods that you can use to set the delivery related information.

EmailDeliveryOption

Method	Description
void setEmailTo(String)	You can set a To Email address.
void setEmailSubject(String)	You can set a Email subject.
void setEmailFrom(String)	You can set a Email From address.
void setEmailCC(String)	You can set a CC Email Address.
void setEmailBody(String)	You can set a Email body message.

FaxDeliveryOption

Method	Description
void setFaxNumber(String)	You can set a fax number.
void setFaxServer(String)	You can set a fax server name which is the one that has already been registered at your BI Publisher Server.

Printer

Method	Description
void setPrinterName(String)	You can set a Printer name that has already been registered at your BI Publisher server environment.
void setPrintNumberOfCopy (String)	You can set a number of copy that you want to print the report in.
void setPrintRange(String)	You can set a range of pages of the report that you want to print.
void setPrintSide(String)	You can set both sides printing.
void setPrintTray(String)	You can specify which printer tray you want to use for your printing.

FTP

Method	Description
void setFtpServerName (String)	You can set a FTP server name that has already been registered at your BI Publisher server.
void setFtpUserName(String)	You can set a username for the FTP server.
void setFtpUserPassword (String)	You can set a password for the FTP server.
void setRemoteFile(String)	You can set a file name for the report output.
void setSftpOption(String)	You can set SFTP option.

Local File System

Method	Description
void setDestination(String)	You can set a full path for the report output including the report output file name so that the report can be saved on the specified location on the server’s file system.

WebDAV

Method	Description
void setServer (String)	You can set a WebDAV server name that is registered at your BI Publisher server.
void setRemoteFilePath (String)	You can set a file name for the report output and the path.
void setUserName (String)	You can set a username for the WebDAV server.
void setPassword (String)	You can set a password for the WebDAV server.
void setDeliveryAuthType (String)	You can set an authentication type of the WebDAV server.
void setDeliveryAuthTypeBasic (String)	You can set Basic authentication type.
void setDeliveryAuthTypeDigest (String)	You can set Digest authentication type.

Note that you need to setup the delivery servers appropriately from your BI Publisher Server UI before you use the delivery options with the Web Service. For example, if you want to use the Email delivery option then you need to setup a default Email server from the UI. Only the delivery options configured appropriately at the server can be used from the Web Service.

Example:

//Create a Delivery Option for Email delivery EMailDeliveryOption em = new EMailDeliveryOption(); //Set Email delivery related information em.setEmailTo("kanichiro.nishida@oracle.com"); em.setEmailSubject("This is a BIP Web Service Test..."); em.setEmailFrom("bip_admin@oracle.com"); em.setEmailCC("kanaugust@gmail.com"); em.setEmailBody("Test");

 

2) Create Delivery Request

Once you have created the DeliveryOption then you can create a DeliveryRequest object by passing the DeliveryOption object.

Here is a list of the methods that you can use to set the DeliveryOption to create a DeliveryRequest.

• void setEmailOption (EMailDeliveryOption)
• void setFaxOption (FaxDeliveryOption)
• void setPrintOption (PrintDeliveryOption)
• void setFTPOption (FTPDeliveryOption)
• void setLocalOption (LocalDeliveryOption)
• void setWebDAVOption (WebDAVDeliveryOption)

Example:

DeliveryRequest dr = new DeliveryRequest(); dr.setEmailOption(em);

 

Create ReportRequest Object

This is exactly the same process as I talked at the last post. So take a look at the post if you don’t know how to create the ReportRequest object.

Create ScheduleRequest Object

When you create the ScheduleRequest object you can set a report to schedule and the delivery information by setting the above mentioned two objects, DeliveryRequest and ReportRequest.

Also, you can set the following information to the ScheduleRequest object.

• Email Address for Notification
• Indication of Saving the Data
• Indication of Saving the Report Output
• Repeat Count
• Repeat Interval
• Start Date for the Scheduling
• End Date for the Scheduling
• Calendar for Scheduling
• Time Zone for Scheduled Time
• Job Name
• Delivery Request
• Report Request
• Indication of Public Scheduling
• Bursting Delivery

If you don’t set the Start Date then it would be the same as ‘immediately run’ option.

And here is a list of the methods that you can use to set such options.

Function	Method	Description
Report Request	void setReportRequest (ReportRequest)	You can set a ReportRequest object to select a report that you want to schedule and run.
Delivery Request	void setDeliveryRequest (DeliveryRequest)	You can set a DeliveryRequest object to set a delivery option. See ‘Pre-Requisite for Delivery’ section for the detail of how to set the delivery settings.
Start Date for the Scheduling	void setStartDate(Calendar)	You can set a start date for the scheduling to start. If it’s null or not specified then the scheduled job will run immediately.
End Date for the Scheduling	void setEndDate(Calendar)	You can set an end date for the scheduling.
Job Name	void setUserJobName(String)	You can set a name for this scheduling job.
Calendar	void setJobCalendar(Calendar)	You can set which calendar to use for the scheduling time. E.g. Gregorian
Time Zone	void setJobTZ(String)	You can set an appropriate time zone that you want to use for the specified time for the scheduling.
Repeat Count	void setRepeatCount(int)	You can set a repeat count for the scheduled job.
Repeat Interval	void setRepeatInterval(int)	You can set an interval time for the scheduled job to run.
Email Address for Notification	void setNotificationTo(String)	You can set an email address to send a notification of the scheduled job.
Request to send a notification message when failed	void setNotificationWhenFailed (boolean)	You can specify whether you want to send a notification message when the scheduled job fails or not.
Request to send a notification message when Success	void setNotificationWhenSuccess (boolean)	You can specify whether you want to send a notification message when the scheduled job runs successfully or not.
Request to send a notification message when Warning	void setNotificationWhenWarning (boolean)	You can specify whether you want to send a notification message when the scheduled job ends with warning or not.
Indication of Saving the Data	void setSaveDataOption(boolean)	You can specify whether you want to save the data for the scheduled job or not. The data will be saved when it’s set True and it can be accessed only through BI Publisher Enterprise UI.
Indication of Saving the Report Output	void setSaveOutputOption(boolean)	You can specify whether you want to save the report output for the scheduled job or not. The output will be saved when it’s set True and it can be accessed only through BI Publisher Enterprise UI.
Indication of Public Scheduling	void setSchedulePublicOption (boolean)	You can specify whether you want to make the scheduled job to be publicly accessible or not.
Bursting Delivery	void setScheduleBurstringOption (boolean)	You can specify whether you want to use the bursting option that is pre-configured for this report or not. In order to use this option you need to make sure that the report you’re scheduling has been configured for the bursting option through BI Publisher Enterprise UI.

Example:

ScheduleRequest sreq = new ScheduleRequest(); //Set DeliveryRequest sreq.setDeliveryRequest(dr); //Set ReportRequest sreq.setReportRequest(req); //Set scheduling information sreq.setNotificationTo("kanichiro.nishida@oracle.com"); sreq.setSaveDataOption(true); sreq.setSaveOutputOption(true); sreq.setScheduleBurstringOption(false); sreq.setSchedulePublicOption(true); sreq.setUserJobName("WebService Job1");

 

Call scheduleReportInSession()

Now you can set the ScheduleRequest and the session token to run the scheduleReprotInSession() method. It returns a job id when it runs successfully, which you can use to monitor and manage the job. For example, in future when you need to suspend or delete the job then you will need this job id to do such.

Example:

job_id = publicReportService.scheduleReportInSession(sreq, sid);

And that’s it! Once you run the scheduleReportInSession() successfully then you should see a new job created from the BI Publisher’s scheduler UI page.

There are more BI Publisher WebService APIs available, but these I’ve covered are the most common. If you have some other APIs that you want me to cover let me know, I can talk about that too.

Here is my whole source code for this scheduling exercise.

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.EMailDeliveryOption; 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 java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import javax.servlet.*; import javax.servlet.http.*; import javax.xml.datatype.DatatypeConfigurationException; import javax.xml.datatype.DatatypeFactory; import javax.xml.datatype.XMLGregorianCalendar; 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");          }        String job_id = this.scheduleReportInSession(publicReportService, sid);         try {             publicReportService.logout(sid);         } catch (InvalidParametersException_Exception e) {             System.out.println(e);         } catch (AccessDeniedException_Exception e) {             System.out.println(e);         }     } public String getSessionID(String username, String password, PublicReportService publicReportService) throws AccessDeniedException_Exception {     String sid = publicReportService.login(username, password);     return sid; } public String scheduleReportInSession(PublicReportService publicReportService,                                     String sid){     // Set Report request attributes     ReportRequest req = new ReportRequest();     req.setAttributeFormat("pdf");     req.setAttributeLocale("en-US");     req.setAttributeTemplate("Simple");     req.setReportAbsolutePath("/HR Manager/Employee Salary Report/Employee Salary Report.xdo");     EMailDeliveryOption em = new EMailDeliveryOption();     em.setEmailTo("kanichiro.nishida@oracle.com");     em.setEmailSubject("This is a BIP Web Service Test...");     em.setEmailFrom("bip_admin@oracle.com");     em.setEmailCC("kanaugust@gmail.com");     em.setEmailBody("Test");     DeliveryRequest dr = new DeliveryRequest();     dr.setEmailOption(em);     ScheduleRequest sreq = new ScheduleRequest();     //Set DeliveryRequest     sreq.setDeliveryRequest(dr);     //Set ReportRequest     sreq.setReportRequest(req);     sreq.setNotificationTo(kanichiro.nishida@oracle.com);     sreq.setSaveDataOption(true);     sreq.setSaveOutputOption(true);     sreq.setScheduleBurstringOption(false);     sreq.setSchedulePublicOption(true);     sreq.setUserJobName("WebService Job1");     String job_id ="";     try {         job_id = publicReportService.scheduleReportInSession(sreq, sid);         System.out.println(job_id);     } catch (InvalidParametersException_Exception ie) {         System.out.println(ie);     } catch (AccessDeniedException_Exception ae) {         System.out.println(ae);     } catch (OperationFailedException_Exception oe) {         System.out.println(oe);     }      return job_id; } }

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.

 

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!

How to use BI Publisher Web Service with JDeveloper 11G?

How can I call BI Publisher Web Service from my Java application with JDeveloper?

BI Publisher provides a set of Web Service APIs, with which you can do pretty much everything you can do from the UI such as running reports, scheduling reports, creating/editing/deleting reports, etc. With JDeveloper you can easily integrate the BI Publisher Web Service with your Java application and start developing your code.

I wrote a ‘BI Publisher Web Service development guide for Java developers with JDeveloper’ paper quite a while ago, but never talked about this topic on this blog. And there have been some changes since then. Now JDeveloper is 11G and BI Publisher has added more APIs and fixed some issues we had before.

So I’ll spend the next couple of posts talking about this topic, hope it will be useful for Java Developers out there who want to integrate with BI Publisher to run and manage BI Publisher reports within your application.

Environment

Here is my environment information:

• BI Publisher 10.1.3.4.1 with March Patch Set (9546699)
• JDeveloper 11.1.1.2
• WebLogic Server 11G (10.3.2)

Get BI Publisher WSDL File

First, you need to get a WSDL file first before start anything. You can obtain your BI Publisher Server’s WSDL file by submitting the following URL from your browser.

http://hostname:port/xmlpserver/services/PublicReportService?WSDL

Note: Somehow I couldn’t get this with my BI Publisher running on a OC4J. I don’t know why but keep getting an error message. Looks many people are just fine or it worked fine after upgraded or something. Since I didn’t have enough time to keep debugging I’ve just simply moved to my another BI Publisher Server running on WebLogic 11g Server, which is working perfect, thanks god!

Once you get the WSDL file then save it to your local system.

Create a Web Service Proxy

Now, in order to integrate with Web Service within Java application you need to create a Web Service Proxy. There is a UI wizard to generate this with JDeveloper. In the New Gallery (right click on Project and ‘New’), select Web Server Proxy under Business Tier/Web Services and follow the steps with the default options until finish. When you are asked to provide a WSDL then use the WSDL you just saved at above.

In my example I have chosen ‘JAX-WS Style’ as Client Style.

Once you click the ‘Finish’ button it automatically generates all the required Java classes to provide you a set of Java APIs each of which corresponds with each Web Service API. Also it generates the Proxy class called ‘PublicReportServiceService’ like the below.

public class PublicReportServiceClient {   @WebServiceRef   private static PublicReportServiceService publicReportServiceService;   public static void main(String [] args) {     publicReportServiceService = new PublicReportServiceService();     PublicReportService publicReportService = publicReportServiceService.getPublicReportService();     // Add your code to call the desired methods.   } }

Now you can start writing your own code with the BI Publisher Web Service corresponding Java methods after the ‘Add your code to call….’ line.

Call BI Publisher Web Service

First, I’m going to use a simple API called ‘login(String username, String password)’ to test if the web service integration works. This API accepts two parameters, one is username and another is password. It passes those two values to the BI Publisher Server and returns a session token if the combination of the username and password is valid. You can use this session token later for any following actions such as running a report, scheduling a report, etc, without needing to be authenticated at every communication with the Server.

Anyway, so this ‘login()’ API is one of the BI Publisher Web Service APIs and I can call it through the Web Server Proxy. In the example below ‘publicReportService’ is the proxy, once this is instantiated then I can call it through the proxy ‘publicReportService’.

With the below example, I’m setting ‘Administrator’ for the username and ‘Administrator’ for the password, and trying to print the returned session token in the standard out.

public class PublicReportServiceClient {   @WebServiceRef   private static PublicReportServiceService publicReportServiceService;   public static void main(String [] args) throws AccessDeniedException_Exception,                                                   MalformedURLException {     publicReportServiceService = new PublicReportServiceService();     PublicReportService publicReportService = publicReportServiceService.getPublicReportService();     String sessionid = publicReportService.login("Administrator", "Administrator");     System.out.println(sessionid);       } }

When I run this program I get a session token like ‘6691223D7D070473ABA31D91AAB89’, which means the login was successful and I can use this token later when I call other BI Publisher Web Service APIs.

How to Change Endpoint?

Now, as long as I’m developing and testing in this instance I would be fine. But what if I need to migrate my code to a QA or Production and I need to run the BI Publisher calls against a different BI Publisher Server?

When you create the Web Server Proxy with JDeveloper it generates it with a default endpoint URL setting, which is retrieved from the WSDL file. That means, every time I run the BI Publisher Web Service it goes against a BI Publisher server with the endpoint URL. So I want to dynamically change the endpoint URL address instead of hard-coding it somewhere.

With 10G JDeveloper it generated an API that I can use to set the endpoint URL address. But with 11G JDeveloper I need to do that in a slight different way, which is to set that as part of the proxy construction. With my example, I can set that when I initiate ‘publicReportServiceService’. I need to provide a URL, which is the endpoint, and a QName, which consists of namespace and localname. My example, those are something like following.

• Endpoint URL: http://knishida-lap.us.oracle.com:7001/xmlpserver/services/PublicReportService
• Namespace: http://xmlns.oracle.com/oxp/service/PublicReportService
• Localname: PublicReportServiceService

You can find these information from your BI Publisher Server WSDL file, or you just need to change the hostname and port number of the example above. And you can set those values something like below.

Before:

publicReportServiceService = new PublicReportServiceService();

After:

//publicReportServiceService = new PublicReportServiceService(); 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"));

Now you have a control of which BI Publisher Server instance you want to communicate with the Web Service calls. Great!

Test with Java Servlet

With the above example I was testing with a Java application. But for the next couple of posts I want to use a Java Servlet to demonstrate other APIs. In case you are not familiar with Java Servlet, don’t worry, you can create it with JDeveloper pretty easily. Just select ‘HTTP Servlet’ under ‘Web Tier/Servlet’ in the ‘New Gallery’ menu and follow the wizard steps.

Now I created a Java Servlet to test called ‘BipServletTest’ under the same project. I’m going to use this Servlet to go over the next couple of posts to introduce other BI Publisher Web Service APIs such as runReport, scheduleReport, etc, so stay tuned!

Cheat Sheet for BI Publisher Integration with BIEE - Part 2

This is a follow up from the last post and today I’m going to talk about the following three integration points. 

• Embedding BI Publisher Reports into BI Dashboard
• Answers Request as Data Source for BI Publisher Report
• BI Server as Data Source for BI Publisher Report

Embedding BI Publisher Reports into Dashboard

There are two ways to embed BI Publisher reports within the Dashboard page. One is to directly embed the report output (e.g. PDF, HTML, etc) into the page.

Another is to list the BI Publisher reports link(s) so that the users can click the link and open the report in another window.

You can choose between the Embedded Content or Link option by selecting it under Display Mode in the Property.

When you select the Report Link option the report folder path will be shown as the list as default. Of course you can change this by clicking ‘Rename’ button in the Dashboard edit page.

Default:

After Rename:

 

 

View Latest Version will pick the latest version of all the previously run reports. So if your report might take some time and you’d rather want to display a pre-run report for your users then check this option.

Troubleshooting

Can’t see any BI Publisher report in the dialog

That means probably your BI Publisher server registration setting is not right. Open instanceconfig.xml file under %OracleBIData%/web/config folder and see all the BI Publisher URL addresses are set appropriately. Your BI Publisher server might be running on a different server machine or on a different J2EE instance with a different port number. Here is an sample of the BI Publisher registration in the configuration file.

 

<AdvancedReporting> <ReportingEngine>XmlP</ReportingEngine> <Volume>XmlP</Volume> <ServerURL>http://knishida-lap:9704/xmlpserver/services/XMLPService</ServerURL> <WebURL>http://knishida-lap:9704/xmlpserver</WebURL> <AdminURL>http://knishida-lap:9704/xmlpserver/servlet/admin</AdminURL> <AdminCredentialAlias>bipublisheradmin</AdminCredentialAlias> </AdvancedReporting>

Also, if your BI Publisher is running with SSL setting then make sure you set ‘https’ instead of ‘http. See this post for the SSL setup for BI Publisher.

Performance is so slow

We had a performance issue before when we used the report list option but the issue was addressed by a BI Publisher’s December patch set. If you hit this issue I’d recommend you to apply this patch set. The latest patch set is 9546699. Tim has posted the detail at his BI Publisher official blog.

 

Answers as Data Source

You can use BI Answers request as a data source for your report. This is a useful and the most popular option to build BI Publisher reports against data from BI Server. Especially when you have already developed BI Answers reports all you need to do is to point to one of the Answers reports and start building the report layout with BI Publisher’s RTF Template, I mean MS-Word.

Troubleshooting

I don’t see anything in the BI Catalog window.

When you have a problem like this these are the places where you want to check.

1. Presentation Server configuration in BI Publisher Administrator Web page
2. Make sure you test with the Administrator’s Username/Password to login to BI Answers
3. Make sure Presentation Server and Java Host are up and running
4. If your BIEE is setup with SSL then make sure you choose ‘https’ instead of ‘http as ‘Server Protocol’.

Best Practice

While it’s easy to just point to the Answers report to get the report data, I personally recommend a use of BI Server as a data source instead of BI Answers. Using BI Server as a data source requires you to write the BI logical query but you can either copy the sql queries from BI Answers ‘Advanced’ tab,

or use BI Publisher’s query builder so that you don’t need to type the SQL queries manually, though I do know that the query builder has some limitation to build complex SQL queries.

Why I’m recommending to use BI Server as a data source? There are two benefits of using BI Server as your data source over BI Answers.

1. You cannot control the XML element name, which BI Publisher automatically generates based on the BI Answer’s original column name (not the custom name). This ends up sometimes really a long XML element name, which is hard to deal with in RTF template development. (See below sample XML)
2. Having BI Server as the data source means you are directly accessing to the BI Server. Having BI Answers as the data source means you are accessing to BI Answers (Presentation Server) first then have BI Answers accessing to the BI Server. So the more layers involved the more the performance will have an impact.

Here is the sample XML data file generated by BI Answers.

Yes, it looks ugly. And it gets even worse when you have a case statement like you see in the above example. This long XML element name brings in a challenge when you develop a RTF template and want to use a condition with such XML element name. But, if I use the BI Server as a data source and copy and paste the query then the XML data can become like the below.

Now it makes your RTF template development and maintenance much easier. I used a SQL Query data type for the above, but of course you can use the Data Template, with which you can have even more control not only on the XML element name but also the XML structure.

 

BI Server (RPD) as Data Source - Direct Access

As I have already mentioned about this feature in the above section, this allows you to access directly to the BI Server and retrieve data for the BI Publisher reporting. With this option basically BI Publisher is acting like BI Answers. Both reporting tools are communicating to the BI Server similar way, though one is through ODBC and another is through JDBC.

Configuration

With the default installation you should see a sample BI Server JDBC connection called ‘Oracle BI EE’. You can either update this to suit to your environment or create a new one. You need hostname and port number information plus BI Server (RPD) Administrator’s username/password. The port number is not the OC4J or HTTP port number, it is the one for BI Server. You can find this port number from NQSConfig.INI file. Look for RPC_SERVICE_OR_PORT to find the port number. (e.g. RPC_SERVICE_OR_PORT = 9703)

If your BIEE is configured in clustering then the Connect String would be something like this.

jdbc:oraclebi://BI-CCS-01:9706/PrimaryCCS=BI-CCS-01;PrimaryCCSPort=9706;SecondaryCCS=BI-CCS-02;SecondaryCCSPort=9706

You can find the detail in the BIEE Deployment Guide.

Best Practice

SQL Query vs. Data Template

With this option you can use either SQL Query or Data Template as the data type. Though it’s easy and simple to use the SQL Query type but I’d recommend a use of Data Template because of the following reasons.

• You have more control on the data structure
• You can use all the aggregate functions to do the aggregated calculation
• You can combine multiple data from different data sources

Proxy Authentication

If you have implemented Row Level security in the RPD then you want to check ‘Use Proxy Authentication’ check box. This option allows BI Publisher to send the session user name to the data source so that the data source (BI Server in this case) can be aware of it.

Conclusion

That’s pretty much it. But I know that some of you might still have a problem in this integration setup. And things can get a bit more complicated when SSL or HA/Clustering configuration come into a picture. I’ve helped a lot of folks at our clients to troubleshoot or fix their issues to make the integration work. Some projects went super fine without any trouble in this integration but some didn’t. But the key is whether if you understand how it works and the integration architecture well. Once you understand it then you know where to look at when the problem arise or at least you can guess where could be wrong.

If you are interested in someone to come in to provide a health check or troubleshoot let me know. We have many consultants who have done this in complex environments so we can help!

Cheat Sheet for BI Publisher Integration with BIEE - Part 1

Do you still have BIEE and BI Publisher integration not working ?

This integration of BI Publisher and BIEE has been there for quite a while and I’m sure many of you have done this and enjoying the integration. But still today, I hear from the fields that some people out there having some challenge to set it up. Though all the configuration steps required for the integration are all documented in Oracle BI EE Infrastructure Installation and Configuration guide, I wanted to go through all the integration points briefly and talk about the best practice and configuration tips.

This post is rather for someone who have already gone though the Installation guide yet still facing some problem. This is not an introduction and I'm not covering the detail of the configuration steps so I can’t express enough but please make sure you have already gone through the documentation where you can find the detail information.

 

Security

You can integrate BI Server security model as BI Publisher’s security model. It is strongly recommended to use the BI Server as the BI Publisher’s security model if you need to integrate those two products.

Before you setup:

• Before you try to integrate the BI Server security make sure you have enabled ‘Super’ user in the BI Publisher’s Security page so that you can always login even when the security integration didn’t work.
• Test the connect string under JDBC data source before you use that for the Security Model setting.

Points to note:

• Once the integration is done now you manage all the users and groups in BI Server (RPD). That means you cannot add any user or group from BI Publisher’s Administrator web UI.
• All the groups of the BI Server will be shown as Roles in BI Publisher’s security page. In the Security page you need to assign appropriate report folders and data sources to each necessary role so that the users in the group can access to appropriate reports and run.
• You need to create those XMLP_xxx groups in BI Server (RPD). These groups should be already there if you have installed the BIEE suite, but may not with a standalone version or upgraded from the older releases. if you don’t see them then create them as groups and assign users to those groups to provide appropriate BI Publisher functionality.
• The port number for the JDBC connection is not 9704, it’s 9703 if you use the default configuration of BIEE. You can find this port number from NQSConfig.INI file. Look for RPC_SERVICE_OR_PORT to find the port number. (e.g. RPC_SERVICE_OR_PORT = 9703)
• There is not cascading group concept for the BI Publisher’s pre-built privilege (role) such as XMLP_ADMIN. For example, if you want to provide an Administrator role to a user A then you need to directly assign the user to the role called ‘XMLP_ADMIN’. Assigning a group, which belongs to ‘XMLP_ADMIN’, to the user A will not provide the Administrator’s role to the user.
• Though the XMLP_xxx roles don’t support the cascading group concept but other roles does, which you use to assign the users to appropriate report folders and data sources.

Troubleshoot:

Can’t Login!

Once you have setup BI Publisher to use BIEE’s security model you should be able to loging to BI Publisher with the BIEE user. However, there is one thing to note that BI Publisher has a different rule on which letters can be used for the password from the one for BIEE. So if you are using a special character in the BIEE password and getting a login error this is where you want to check.

Also make sure that the Connect String for the BI Server is correct.

 

Access to BI Publisher from BIEE - More Products

Though you can always access to BI Publisher from its own login page you can also access it from BIEE’s web UI by navigating from ‘More Products/BI Publisher’ menu.

Troubleshoot:

Getting an error when select BI Publisher from the menu!

This integration is the trickiest one based on what I have been seeing at our customer projects. In order for this to work you need to add Publisher Administrator credentials to Oracle BI Presentation Services Credential Store by using BI Server’s cryptotool.

When BI Presentation Services establishes a connection with BI Publisher it uses BIP's Administrator username and password stored with the alias 'bipublisheradmin' in the BI Presentation Services Credential Store. The Credential Store can be a XML file and the default file is credentialstore.xml, which can be found at %OracleBIData_Home%\web\config. The password is encrypted and stored in the Creadentail Store file.

In order to create (or update) the Credential Store file you can use a command line utility called cryptotools, which can be found at %ORACLEBI_HOME%\web\bin for Windows. This tool will add a pair of username and password into a creadentialstore.xml file.

The steps required for this are documented in the Installation and Configuration guide. But here is an example of how you can run the cryptotools utility to store the credentials.

Example:

Assuming your OBI admin user is, 'Administrator' and its password is 'Administrator', you can run the cryptotools utility to add 'bipublisheradmin' user alias like following.

C:\OracleBIData\web\config>cryptotools credstore -add -inFile credentialstore.xml -alias bipublisheradmin -username Administrator -password Administrator -passphrase admin

Once you run the above command you'll be asked the following two questions, answer 'y' to the both questions.

 

>Do you want to write the passphrase to the xml? y/n (n): y >File "credentialstore.xml" exists. Do you want to overwrite it? y/n (y):

Every time you change the Administrator’s password you need to run the cryptotools utility to store the new credentials to the credential store.

 

Tomorrow!

I will talk about other integration points that allow you to use BI Answers or BI Server as data source tomorrow, stay tuned!