How to run a webi document and export the result to PDF, Excel etc using BO Java Report Engine SDK

This post describes the typical code required to run a Webi document and export the result to PDF, Excel, CSV or XML file. Here is the compete code .

The workflow is the following

  1. Open a webi document
  2. Run queries
  3. Set prompts and contexts
  4. Export
  5. Close

The important is that the queries should be run before setting the prompts.

Open a Webi document

Let’s assume that we found an Id of  Webi document.

DocumentInstance doc = reportEngine.openDocument(infoObject.getID());

Run queries

The queries can be run using DocumentInstance.refresh() or DataProviders.runQueries().

doc.refresh();

Prepare answers to prompts

It is convenient to create a map that for each prompt provides a set of values, and later use this map to enter the prompts.

HashMap<String, String[]> answers = new HashMap<String, String[]>();
answers.put("Country:", new String[]{"US", "France"});
answers.put("Year:", new String[]{"FY2004"});

(Here we assume that the document has two prompts “Country:” and “Year:”.)

Enter the prompts values

For each prompt, the code looks up the values in the map, enters the values using Prompt.enterValues().

After that, it sets the prompts using DocumentInstance.setPrompts(). At this point, if all mandatory prompts are set, the document is be refreshed.

Note that you can get name of the report in two ways. Prompt.getID() return original name and Prompt.getName() returns localized name (if there are translations for the document).

Prompts prompts = doc.getPrompts();
for (int i = 0; i < prompts.getCount(); i++) {
    Prompt prompt = prompts.getItem(i);
    String[] answer = answers.get(prompt.getID());
    if (answer != null) {        
        prompt.enterValues(answer);
    }
}
doc.setPrompts();

Check if the document has been refreshed

If there are mandatory prompts that are not answered, setPrompts() will not refresh document. If so we print error message.

if (doc.getMustFillPrompts()) {
    System.out.println("ERROR: Mandatory prompts has not been entered");
}

Also it is possible that there are multiple contexts and one need to be selected in order to run the document. In most cases the documents are designed to avoid prompting about contexts, so here we assume that there is no need to select one. But just in case, we check this also.

if (doc.getMustFillContexts()) {
    System.out.println("ERROR: Context has not been selected");
}

Export to PDF

We can export complete document, a report of the document, or data providers.

For instance to export the document to PDF, we can get view in the PDF format and write the contents to a file.

BinaryView binaryView2 = (BinaryView)doc.getView(OutputFormatType.PDF); 
String title = infoObject.getTitle();
writeBytes(binaryView2.getContent(), title + ".pdf");

Here we use an auxiliary function that writes byte array to a file.

public static void writeBytes(byte[] data, String filename) throws IOException {
    File file = new File(filename); 
    FileOutputStream fstream = new FileOutputStream(file); 
    fstream.write(data); 
    fstream.close();
}

Export to Excel

We can export the document to an Excel file using similar code:

BinaryView xlsView = (BinaryView)doc.getView(OutputFormatType.XLS);
writeBytes(xlsView.getContent(), title + ".xls");

There are two types of Excel output format type, OutputFormatType.XLS is optimized for presentation and OutputFormatType.XLSDataCentric is optimized for data manipulation.

Export to CSV

We can export data from one or all data providers to a CSV file:

CSVView csvView = (CSVView)doc.getDataProviders().getView(OutputFormatType.CSV);
writeBytes(csvView.getContent().getBytes(), title + ".csv");

BinaryView.getContent() returns content as byte array, while CSVView.getContent() returns String. Therefore we use String.getBytes() to adhere the function writeBytes().

Export to HTML

It is also possible to export each report of the document individually for instance to a HTML files. In this case you need to change pagination mode to listing otherwise you can get partial result.

Reports reports = doc.getReports();
for (int i = 0; i < reports.getCount(); i++)
{
    Report report = reports.getItem(i);
    report.setPaginationMode(PaginationMode.Listing);
    HTMLView htmlView = (HTMLView) report.getView(OutputFormatType.DHTML);
    writeBytes(htmlView.getContent().getBytes(), title + " " + i + ".html");
}

Done

That is it.

doc.closeDocument();

122 thoughts on “How to run a webi document and export the result to PDF, Excel etc using BO Java Report Engine SDK

  1. SK

    HI Everyone,
    I have the below code which is running file to fetch me a URL for a report and when that url is used in jsp page it fetcehd the BO screen from where user can drill down on the export to excel manually.
    But , My requirement is to create the excel directly with the report when I run the code.
    Could someone help on this. Thanks in advance!!

    import com.crystaldecisions.sdk.framework.IEnterpriseSession;
    import com.crystaldecisions.sdk.exception.SDKServerException;
    import java.util.*;
    import com.jpmorganchase.pfitr.boreport.BOUtil;
    import com.jpmorganchase.pfitr.common.exception.PfitrBusinessException;

    public class BusinessObjectReport {

    public static void main(String args[]) throws Exception {

    System.out.println(“Started…”);

    BusinessObject();

    }

    static void BusinessObject(){

    String userId = “Z9991119”;
    if(userId!=null){
    userId=userId.toUpperCase();
    }

    String pword = “xyzz”;//commonsProps.getProperty(“detailRepPword”);
    String cms=”ct5ld001:7500″;//(String) request.getParameter(“CMS”);
    String boHref=””;
    try
    {
    IEnterpriseSession iSession = BOUtil.getEnterpriseSession(userId,cms,pword,”secEnterprise”);
    String loginToken = BOUtil.getLoginToken(iSession);

    String boHref_old =”http:\\ct5ld001.svr.us.jpmchase.net:7118\\OpenDocument\\opendoc\\openDocument.jsp?”;
    //(String) request.getParameter(“BO_HREF”);
    boHref_old= boHref_old.replace(“*$a”,”&”);
    boHref_old= boHref_old.replace(“*$p”,”+”);
    boHref_old= boHref_old.replace(“*$r”,”%”);
    boHref_old= boHref_old.replace(“*$q”,”?”);
    boHref_old= boHref_old.replace(“*$s”,”//”);
    boHref=boHref_old +””;
    int indexWin=boHref.indexOf(“sWindow=New”);
    if(indexWin != -1)
    {
    String boHref1=boHref.substring(0,indexWin);
    String boHref2=boHref.substring(indexWin+12,boHref.length());
    boHref=boHref1+boHref2;
    }
    // boHref = handleAsOfDate(boHref);
    //boHref = handleReportingPeriod(boHref);
    //String datePrompts=commonsProps.getProperty(“datePrompt”);
    List dateList = new ArrayList();
    //dateList=Arrays.asList(datePrompts.split(“;”));
    for (int i = 0; i “+boHref);

    }
    catch(PfitrBusinessException e)
    {
    System.out.println(“You do not have access to Business Object. “+e.getMessage());
    }
    catch(SDKServerException e)
    {
    System.out.println(“You do not have access to Business Object. “+e.getMessage());
    }

    }

    }

    Like

    Reply
  2. Siddu

    Hi Dmytro,

    I have scheduled webi report in excel format.How can I protect excel data from unauthorized users.Through Java SDK how can we achive this.

    Thanks,
    Siddu

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s