This post describes how to get started with a BO Semantic Layer SDK in Eclipse. It explains the steps to setup a project and provides an example of the code that imports, modifies and exports a UNX universe. You will also find instructions how to run JAR from command line.

BO Client Tools and SL SDK

You need to have SAP BusinessObjects BI platform 4.x Client Tools and it should include Semantic Layer SDK.

The Semantic Layer SDK is not included in default installation. You can check if you have it and enable it in Control Panel > Programs > Programs and Features > SAP BusinessObjects BI platform 4.x Client Tools > Uninstall/change > Modify.

Make sure that you have “Semantic Layer Java SDK”

This post assumes that the client tools are installed in the default location, namely in

C:\Program Files (x86)\SAP BusinessObjects

If your setup is different, update the paths accordingly.

Setup New Project with SL SDK

Create a new Project in Eclipse.

Right click on the project and select Build Path > Add External Archives.

Add Semantic layer SDK library sl_sdk.jar. It is located in:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\SL SDK\java

JRE

Make sure that the project is using 32 bit Java Runtime Environment. The best is to use Java from the BusinessObjects folder.

Right click on JRE System Library and select Properties.

Click Installed JREs and add new JRE by clicking on Add > Standard VM, and then providing the following path:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\jre

Select the created JRE:

The Program

Add class Program to the project and paste the following code:

import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.CrystalEnterprise;
import com.crystaldecisions.sdk.framework.IEnterpriseSession;
import com.crystaldecisions.sdk.framework.ISessionMgr;
import com.sap.sl.sdk.authoring.cms.CmsResourceService;
import com.sap.sl.sdk.authoring.businesslayer.*;
import com.sap.sl.sdk.authoring.datafoundation.*;
import com.sap.sl.sdk.authoring.local.LocalResourceService;
import com.sap.sl.sdk.framework.SlContext;
import com.sap.sl.sdk.framework.cms.CmsSessionService;

public class Program {

  public static void main(String[] args) throws SDKException {
    String username = "Administrator";
    String password = "";
    String server = "localhost";
    String auth = "secEnterprise";
    String cmspath = "/Universes/webi universes";
    String unxname = "eFashion.unx";
    String tempFolder = ".";

    SlContext context = SlContext.create();
    IEnterpriseSession enterpriseSession = null;
    try {
      System.out.println("Connecting");
      ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
      enterpriseSession = sessionMgr.logon(username, password, server, auth);
      CmsSessionService cmsSessionService =
        context.getService(CmsSessionService.class);
      cmsSessionService.setSession(enterpriseSession);
      CmsResourceService cmsService = 
        context.getService(CmsResourceService.class);
      LocalResourceService localResourceService =
        context.getService(LocalResourceService.class);
      BusinessLayerFactory businessLayerFactory = 
        context.getService(BusinessLayerFactory.class);
      DataFoundationFactory dataFoundationFactory =
        context.getService(DataFoundationFactory.class);
             
      System.out.println("Importing universe"); 
      String blxPath = 
        cmsService.retrieveUniverse(cmspath + "/" + unxname, tempFolder, true);
      
      System.out.println("Loading universe");
      RelationalBusinessLayer businessLayer =
        (RelationalBusinessLayer) localResourceService.load(blxPath);
      String dfxPath = businessLayer.getDataFoundationPath();
      DataFoundation dataFoundation =
        (DataFoundation) localResourceService.load(dfxPath);

      System.out.println("Modifying data foundation");
      String sql = "select count(1) as Cnt from Shop_facts";
      dataFoundationFactory.createDerivedTable("New_table", sql, dataFoundation);
      localResourceService.save(dataFoundation, dfxPath, true);

      System.out.println("Reloading data foundation");
      businessLayer =
        (RelationalBusinessLayer) localResourceService.load(blxPath);
      
      System.out.println("Modifying business layer");
      RootFolder rootFolder = businessLayer.getRootFolder();
      Measure measure = 
        businessLayerFactory.createBlItem(Measure.class, "New Measure", rootFolder);
      RelationalBinding binding = (RelationalBinding)measure.getBinding();
      binding.setSelect("New_table.Cnt");
      localResourceService.save(businessLayer, blxPath, true);

      System.out.println("Exporting universe");
      cmsService.publish(blxPath, cmspath, true);
    } finally {
      if (context != null) {
        context.close();
      }
      if (enterpriseSession != null) {
        enterpriseSession.logoff();
      }
      System.out.println("Finished");
    }
  }
}

Run Configuration

Run the program. It will fail but a Run Configuration will be created.

Open the created run configuration in Run > Run Configurations

Add the following VM arguments

-Dbusinessobjects.connectivity.directory=”C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer”

Add PATH variable with the value:

C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86

Running the Code

The code uses eFashion.unx which should be located in webi universes. So you need to convert eFashion universe to eFashion.unx in Information Design Tool.

Now we are ready to run the program.

Click the button Run. The tool should execute without error and add table New_table and a measure New measure:

Running the Code from Command Line

Export the program to Executable JAR file (e.g. unxdemo.jar) from File > Export.

Create a batch file with the following script:

SET BO=C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0
SET PATH=%BO%\win32_x86
SET JAVA=%BO%\win32_x86\jre
SET CD=%BO%\dataAccess\connectionServer
SET CP=unxdemo.jar;%BO%\SL SDK\java\sl_sdk.jar;%BO%\java\lib\*
“%JAVA%\bin\java.exe” -Dbusinessobjects.connectivity.directory=“%CD%“ -cp “%CP%“ Program
PAUSE

Run the batch:

There are three things that are important when you build a program that uses Semantic Layer SDK.

• You need to include sl_sdk.jar to the class path.
• You need to use 32 bit java
• The system property businessobjects.connectivity.directory should be set to the connection server folder
  -Dbusinessobjects.connectivity.directory=”C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer”
• The system variable PATH should be set to win32_x86
  C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86

It is quite easy to see if sl_sdk.jar is included, your program will fail quickly with NoClassDefFoundError. It might be not so obvious with the other three. The following code may help you to check them:

System.out.println("java = " + System.getProperty("java.version") + ", " 
  + System.getProperty("sun.arch.data.model") + " bit");
System.out.println("path = " + System.getenv("PATH"));
System.out.println("businessobjects.connectivity.directory = "
  + System.getProperty("businessobjects.connectivity.directory"));

In this post you will find an example of how to use Web Intelligence RESTful Web Services SDK with Java. The code displays names of the variables for each web intelligence document.

To run the code, you will need json library that can be found for instance on http://mvnrepository.com/artifact/org.json/json/20160212

Example (Program.java)

import org.json.JSONArray;
import org.json.JSONObject;

public class Program {
  public static void main(String[] args) throws Exception {
    // Establish connection
    Bo4Connection connection = new Bo4Connection("http://ANALYTIX:6405/biprws");
    connection.connect("Administrator", "1-Password", "secEnterprise");
    try {
      // Get list of documents
      String docs = connection.query("GET", "/documents", "application/json");
      JSONArray documents = new JSONObject(docs).getJSONObject("documents").getJSONArray("document");
      for (int i = 0; i < documents.length(); i++) {
        JSONObject document = documents.getJSONObject(i);
        int id = document.getInt("id");
	// Get information about the document
        String doc = connection.query("GET", "/documents/" + id, "application/json");
        JSONObject info = new JSONObject(doc).getJSONObject("document");
        String name = info.getString("name");
        String path = info.getString("path");
        System.out.println(path + "/" + name);
	// Get variables of the document
        String var = connection.query("GET", "/documents/" + id + "/variables", "application/json");
        JSONArray variables = new JSONObject(var).getJSONObject("variables").getJSONArray("variable");
        for (int j = 0; j < variables.length(); j++) {
	  // Print variable information
          JSONObject variable = variables.getJSONObject(j);
          String variableName = variable.getString("name");
          System.out.println(variableName);
        }
      }
    } finally {
      connection.disconnect();
    }
  }
}

API (Bo4Connection.java)

The helper API is very simple and contains a constructor and 3 functions: connect(username, password, auth), disconnect(), and query(method, link, format).

The constructor’s parameter is the link to web services.

Bo4Connection connection = new Bo4Connection(“http://ANALYTIX:6405/biprws”);

The call to web services is performed with function query. For instance, to get list of documents we send a GET request to /documents, and we want to get result in json format.

connection.query(“GET”, “/documents”, “application/json”);

To get the list of all possible request, please refer to RESTful Web Services SDK Developer Guides on http://scn.sap.com/docs/DOC-27465

import java.io.*;
import java.net.*;
import java.nio.charset.StandardCharsets;
import org.json.JSONObject;

public class Bo4Connection {
  
  private String biprws;
  private String token;
  
  public Bo4Connection(String biprws) {
    this.biprws = biprws;
  }
  
  public String query(String method, String link, String format) throws Exception {
    return query(method, biprws + "/raylight/v1" + link, format, token, null);
  }
  
  public void connect(String username, String password, String auth) throws Exception {
    String link = biprws + "/logon/long/";    
    String method = "POST";
    String format = "application/json";
    String body = "<attrs xmlns=\"http://www.sap.com/rws/bip\">"
      + "<attr name=\"userName\" type=\"string\">" + username + "</attr>"
      + "<attr name=\"password\" type=\"string\">" + password + "</attr>"
      + "<attr name=\"auth\" type=\"string\">" + auth + "</attr>"
      + "</attrs>";
    JSONObject json = new JSONObject(query(method, link, format, null, body));
    token = json.getString("logonToken");
  }
  
  public void disconnect() throws Exception {
    String link = biprws + "/logoff/";    
    String method = "POST";
    String format = "application/json";
    query(method, link, format, null, null);
  }

  public static String query(String method, String link, String format, 
      String token, String content) throws Exception {
    HttpURLConnection conn = null;
    try {
      URL url = new URL(link);    
      conn = (HttpURLConnection) url.openConnection();
      conn.setRequestMethod(method);
      conn.setRequestProperty("Accept", format);
      if (token != null) {
        String logonToken = "\"" + token + "\"";
        conn.setRequestProperty("X-SAP-LogonToken", logonToken);
      }
      conn.setDoOutput(true);
      conn.setDoInput(true);
      if (content != null) {
        conn.setRequestProperty("Content-Type", 
            "application/xml; charset=utf-8");
        conn.setRequestProperty("Content-Length", 
            Integer.toString(content.length()));
        OutputStreamWriter out = new OutputStreamWriter(conn.getOutputStream());
        out.write(content, 0, content.length());
        out.flush();
      }
      conn.connect();
      if (conn.getResponseCode() != 200) {
        throw new Exception("HTTP Error Code: " + conn.getResponseCode() 
          + " " + conn.getResponseMessage());
      }
      BufferedReader br = new BufferedReader(
        new InputStreamReader(conn.getInputStream(), StandardCharsets.UTF_8));
      StringBuilder result = new StringBuilder(); 
      String output;
      while ((output = br.readLine()) != null) {
        result.append(output);
        result.append('\n');
      }
      br.close();
      return result.toString();
    } finally {
      if (conn != null) {
        conn.disconnect();
      }
    }
  }
}

I have struggled quite a lot with the error “Interface requested not found : csLIB” when trying to open a semantic layer using SAP BO SL SDK 4.1. It was quite a complex issue so I will summarize it here in case someone has a similar problem.

Here I am using a local universe Test with SQL Server connection.

The code is simple.

import com.sap.sl.sdk.authoring.businesslayer.RelationalBusinessLayer;
import com.sap.sl.sdk.authoring.local.LocalResourceService;
import com.sap.sl.sdk.framework.SlContext;

public class Test {
  public static void main(String[] args) {
    SlContext context = SlContext.create();
    String businessLayerPath = ".\\Test.blx";
    LocalResourceService service = context.getService(LocalResourceService.class);
    RelationalBusinessLayer businessLayer = 
                        (RelationalBusinessLayer) service.load(businessLayerPath);
    service.close(businessLayer);
    context.close();
    System.out.println("OK");
  }
}

Batch for compilation and execution

@echo off
set JAVA_HOME=C:/Program Files (x86)/Java/jdk1.6.0_45
set JAVA="%JAVA_HOME%/bin/java"
set JAVAC="%JAVA_HOME%/bin/javac"

set BO=C:/SAP BusinessObjects/SAP BusinessObjects Enterprise XI 4.0
set CS=%BO%/dataAccess/connectionServer
set CP=%BO%/SL SDK/java/sl_sdk.jar;%BO%/java/lib/*

set PATH=%BO%/win32_x86

%JAVAC% -classpath "%CP%" Test.java
%JAVA% -Dbusinessobjects.connectivity.directory="%CS%" -classpath "%CP%" Test
pause

Everything was done according to SL SDK documentation but I still got the error:

Exception in thread “main” com.sap.tools.commons.exception.NestedException: Interface requested not found : csLIB
Caused by: com.sap.connectivity.cs.core.CSError: Interface requested not found : csLIB
Caused by: java.lang.UnsatisfiedLinkError: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\cs_jni.dll: The specified procedure could not be found

I started investigating DLL loading process using Process Monitor and found that the library cs_jni.dll depends on the library icuin30.dll from win32_x86. However I had an older version in the folder C:\Windows\SysWOW64\. And since the system directories are checked before going through directories in the PATH variables, a wrong version was picked.

I have overwritten the icu??30.dll libraries in SysWOW64 with the libraries from win32_x86 and the code started to work.

(The “icu” stands for “International Components for Unicode”.)

I do not know the impact of the change to other application, use it at your own risk. If you are going to try it, make backup of the existing files.

Here is some code for paged querying InfoStore (BO XI 3.1):

String uri = "path://InfoObjects/**[si_kind='Webi' and si_instance=0]"; 
PagingQueryOptions options = new PagingQueryOptions();
IPageResult ips = infoStore.getPagingQuery(uri, options);
Iterator<String> pageResultIter = ips.iterator();
while (pageResultIter.hasNext()) {
    String pageQuery = pageResultIter.next();
    IStatelessPageInfo pageInfo = infoStore.getStatelessPageInfo(pageQuery, options);
    String sql = pageInfo.getPageSQL();
    System.out.println(sql);
    IInfoObjects infoobjects = infoStore.query(sql);
    // do something with the infoobjects ...
}

Let’s look at it line by line.

String uri = "path://InfoObjects/**[si_kind='Webi' and si_instance=0]";

We will query all Webi documents in the CMS using path query. Double asterisk mean recursive search. Root Folder is displayed as Public Folders in InfoView.

PagingQueryOptions options = new PagingQueryOptions();

PagingQueryOptions determines how the result will be paged. You can provide some parameters to the constructor.

• Page size – the number of objects returned in the page
• Whether the query is incremental. If the query is incremental, the page will always be equal to the page size. If the query is not incremental, paging is determined during the initial query, so if some objects are deleted or added, the page size might vary.
• Options – control if the query will be decoded (e.g. useful if your query contains %), whether to include security parameters to the query, exclude temporary objects

Details can be found in the documentationPagingQueryOptions.

IPageResult ips = infoStore.getPagingQuery(uri, options);
Iterator<String> pageResultIter = ips.iterator();

Initial paging query provides string iterator. The iterator will return the same URI query but with paging parameters.

while (pageResultIter.hasNext()) {
    String pageQuery = pageResultIter.next();
    ...
}

This is the classic way to loop using a Java iterator.

IStatelessPageInfo pageInfo = infoStore.getStatelessPageInfo(pageQuery, options);
String sql = pageInfo.getPageSQL();
IInfoObjects infoobjects = infoStore.query(sql);

These lines builds the SQL queries to retrieve the objects of the page.

To export Webi report to MHTML, the function HTMLView.getContent(Writer,String,String) should be used.

// widoc is an instance of DocumentInstance
Reports reports = widoc.getReports();
HTMLView htmlView = (HTMLView) reports.getItem(0).getView(OutputFormatType.MHTML);
FileWriter fw = new FileWriter("report.mhtml");
htmlView.getContent(fw,"","");
fw.close();

Make sure that libraries xpp3.jar, xpp3_min.jar, xalan.jar and xercesImpl.jar from common\4.0\java\lib\external are included in the class path.

There is an alternative to InfoStore sql query – it is BO URI queries. In some situations URI queries are significantly shorter and clearer.

For instance, if we need to find all Webi document in folder Test, we will have to run multiple queries to get the result with SQL queries. First you need to find Test folder in the root folder. Then you can only form a SQL to find Webi reports.

String sql0 = "select si_id from ci_infoobjects where si_parent_cuid='"
    + CeSecurityCUID.RootFolder.FOLDERS + "' and si_name='Test'";
int id = ((IInfoObject)infoStore.query(sql0).get(0)).getID();
String sql = "select si_id from ci_infoobjects where si_parentid=" 
    + id + " and si_instance=0 and si_kind='Webi'";

Using URI queries, the logic is far clearer

String uri = "path://InfoObjects/Root Folder/Test/[si_kind='Webi' and si_instance=0]";
String sql = infoStore.getStatelessPageInfo(uri, new PagingQueryOptions()).getPageSQL();

(Both versions lack error handling)

URI syntax

I will try to describe syntax of URI. Non-terminal elements are marked in red.

protocol://expression[[condition]][@attributes][?parameters]

protocol = query | path | cuid | search

Condition should be a valid WHERE condition, attributes are fields separated by comma, parameters are speparated by ampersand and have format name=value. Conditions, attributes and parameters are optional

It is better to see it on an example.

path://InfoObjects/Root Folder/Test/**[si_kind=’Webi’ and si_instance=0]@si_name,si_description?OrderBy=si_parentid

here,

• path is a protocol,
• InfoObjects/Root Folder/Test/** is path expression,
• si_kind=’Webi’ and si_instance=0 is a condition,
• si_name,si_description are attributes, and
• OrderBy=si_parentid is a parameter.

This query will find all Webi documents in folder Test (located in Public Folders) and its subfolders. The corresponding SQL query is

SELECT TOP 200 si_name,si_description,SI_CUID,SI_PARENT_CUID 
FROM CI_INFOOBJECTS WHERE SI_ANCESTOR IN (1851) AND (si_kind='Webi' and si_instance=0) 
ORDER BY si_parentid,SI_ID

(Here 1851 is the ID of Test folder).

The conditions are simply added to the where clause. Attributes are used to restrict the list of fields in select clause (SI_CUID and SI_PARENT_CUID will always be added). If attributes are not specified all fields will be returned. Properties are primarily used for paging query. You can specify order with OrderBy.

Path expression syntax

path://root/path

root = InfoObjects | AppObjects | SystemObjects | *

path = path/node[/]

where node can be name of object, search pattern (e.g. *de* all objects that contain ‘de’ in their name) or recursive search wild char **. If the experssion ends with slash, children will be returned.

For instance, here are the queries to find object in the root folder with name ‘Test’

URI	path://InfoObjects/Root Folder/Test
SQL	SELECT TOP 200 SI_CUID, SI_NAME, SI_PARENT_CUID, SI_DESCRIPTION, SI_CREATION_TIME, SI_KIND, SI_CHILDREN, SI_OWNER, SI_PATH, SI_CORPORATE_CATEGORIES_ID, SI_PERSONAL_CATEGORIES_ID, SI_FILES, SI_INSTANCE, SI_SCHEDULE_STATUS, SI_LAST_SUCCESSFUL_INSTANCE_ID, SI_KEYWORD FROM CI_INFOOBJECTS WHERE SI_PARENTID IN (23) AND SI_NAME=’Test’ ORDER BY SI_ID

where 23 is ID of the root folder (aka Public Folders).

(In the following examples, I will only show WHERE clause.)

Trailing slash says to find direct children:

URI	path://InfoObjects/Root Folder/Test/
SQL	… WHERE SI_PARENTID IN (1851) …

Plus sign allows to include the parent in the result:

URI	path://InfoObjects/Root Folder/Test+/
SQL	… WHERE (SI_PARENTID IN (1851) OR SI_ID IN (1851)) …

You can use asterisk character to search using pattern:

URI	path://InfoObjects/Root Folder/Test/*de*
SQL	… WHERE SI_PARENTID IN (1851) AND SI_NAME LIKE ‘%de%’ …

You can use a recursive search using double asterisk. The following query will return all objects in Test and its subfolders. Note a nice field SI_ANCESTOR  that allows recursive search in folders:

URI	path://InfoObjects/Root Folder/Test/**
SQL	… WHERE SI_ANCESTOR IN (1851) …

You can combine

URI	path://InfoObjects/Root Folder/Test+/**
SQL	… WHERE (SI_ANCESTOR IN (1851) OR SI_ID IN (1851)) …

 

Cuid syntax

cuid://<list>/path

list = cuids | ids

cuids = cuids,cuid

ids = ids,id

You can either list IDs or CUIDs but you cannot mix them in the same list. For instance,

cuid://<AfRWaT5_131NlLLf5bRMLKY,AcgOFGfhCzJEg.VjnPaidmI>

cuid://<12,11>

You can use the path expression. For instance, the following URI query will find all objects in the folder with ID=1851 that contain ‘de’ in them.

URI	cuid://<1851>/*de*
SQL	… WHERE SI_PARENTID IN (1851) AND SI_NAME LIKE ‘%de%’ …

 

Query syntax

query://{sql}

For instance,

URI	query://{select * from ci_infoobjects where si_parentid=1851}?BPPSIZE=99999
SQL	SELECT TOP 99999 * FROM ci_infoobjects WHERE si_parentid=1851 ORDER BY SI_ID

 

Search

search://{pattern}?options

Patern is either a single word or quoted words

search://{Revenue}

search://{‘US’ ‘France’ ‘Germany’}

Options are separated by ampersand and have form name=value. Possible options are SearchName, SearchKeywords, SearchDescription, CaseSensitive, MatchAllWords, FindWithoutWords, MatchExact, IncludeInstances.

URI

search://{‘US Report’}?CaseSensitive=true&MatchAllWords=true

SQL

SELECT TOP 200 SI_CUID, SI_NAME, SI_PARENT_CUID, SI_DESCRIPTION, SI_CREATION_TIME, SI_KIND, SI_OWNER, SI_CORPORATE_CATEGORIES_ID, SI_PERSONAL_CATEGORIES_ID, SI_FILES, SI_INSTANCE, SI_SCHEDULE_STATUS, SI_LAST_SUCCESSFUL_INSTANCE_ID, SI_KEYWORD FROM CI_INFOOBJECTS WHERE (((SI_NAME LIKE ‘%[U][S][ ][R][e][p][o][r][t]%’) OR (SI_KEYWORD LIKE ‘%[U][S][ ][R][e][p][o][r][t]%’)) AND (SI_INSTANCE=0)) ORDER BY SI_ID

Code

import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.*;
import com.crystaldecisions.sdk.occa.infostore.*;
import com.crystaldecisions.sdk.uri.*;
import com.crystaldecisions.sdk.exception.*;

public class Program {
    public static void main(String[] args) {
        IEnterpriseSession enterpriseSession = null;
        try {
            System.out.println("Connecting...");
            ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
            enterpriseSession = sessionMgr.logon("Administrator", "", 
                "localhost", "secEnterprise");
            IInfoStore infoStore = (IInfoStore) enterpriseSession.getService("InfoStore");
            PagingQueryOptions options = new PagingQueryOptions();
            String uri = "path://InfoObjects/Root Folder/Test/[si_kind='Webi' and si_instance=0]";
            IStatelessPageInfo pageInfo = infoStore.getStatelessPageInfo(uri, options);
            String sql = pageInfo.getPageSQL();
            System.out.println(uri);
            System.out.println(sql);
        } catch (SDKException ex) {
            ex.printStackTrace();
        } finally {
            if (enterpriseSession != null) {
                enterpriseSession.logoff(); 
            }
        }
        System.out.println("Finished!");
    }
}

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();