How to add new SQLAnywhere database

Posted by & filed under Administration.

Starting from BusinessObjects XI SP5, SAP Sybase SQL Anywhere is used as default database for BusinessObjects system databases (CMS and Audit). This post describes steps how to add a new database and provides some information about SQLAnywhere tools.

Read more »

InfoStore URI queries

Posted by & filed under SDK.

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 = 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


  • 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


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

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/

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*

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/**

You can combine

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


Cuid syntax


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,



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*


Query syntax


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




Patern is either a single word or quoted words


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


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 {
            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();
        } catch (SDKException ex) {
        } finally {
            if (enterpriseSession != null) {

How to create a webi document using Java Report Engine SDK

Posted by & filed under SDK.

This post describes the typical workflow required to create a new Webi document in BO XI 3.1. You can download the compete code here . We will consider an example how to create a simple Webi document based on Island Resorts Marketing universe that will display resort service and revenue from that service for US.

Connect to CMS and initialize engine

The first steps are establishing connection with the CMS and initializing the report engine.

// Connect to CMS
ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
IEnterpriseSession enterpriseSession = sessionMgr.logon(user, pass, host, auth);
// Initialize Webi report engine
ReportEngines reportEngines = (ReportEngines) enterpriseSession.getService("ReportEngines");
ReportEngineType type = ReportEngines.ReportEngineType.WI_REPORT_ENGINE;
ReportEngine reportEngine = (ReportEngine) reportEngines.getService(type);

Create new document

First, we perform CMS query to find the universe Island Resorts Marketing (which is one of the standard sample universes). (It is assumed that the universe exists).

IInfoStore infoStore = (IInfoStore) enterpriseSession.getService("InfoStore");
String unvQuery = "select * from CI_APPOBJECTS where SI_KIND = 'Universe'" 
                + " and SI_NAME='Island Resorts Marketing'";
IInfoObjects infoObjects = (IInfoObjects) infoStore.query(unvQuery);
IInfoObject infoObject = (IInfoObject)infoObjects.get(0);

Second, we actually create a new document.

DocumentInstance documentInstance = reportEngine.newDocument("UnivCUID="+infoObject.getCUID());

After this we initialize some helper variables. Data Provider and Query describe the same (query). Query is an interface to add objects and conditions, change properties such as maximum row retrieved etc. Data Provider is an interface primarily to run, purge the query etc. Data Source is equivalent to universe. If universe has multiple queries but each query use the same universe, there will be only one data source. Data Source provides all available objects in the universe.

DataProviders dps = documentInstance.getDataProviders();
DataProvider dataProvider = dps.getItem(0);
Query query = dataProvider.getQuery();
DataSource dataSource = dataProvider.getDataSource();

Build the query

First, we add a couple of objects available in the data source – Service and Revenue.

DataSourceObjects objects = dataSource.getClasses();

Second, we construct the condition. The condition is County='US'.

ConditionContainer container = query.createCondition(LogicalOperator.AND);
ConditionObject conditionObject = container.createConditionObject(objects.getChildByName("Country"));
FilterCondition filterCondition = conditionObject.createFilterCondition(Operator.EQUAL);

The last step is to run queries.


Build the layout

So now we can add report to the document structure. Report container contains report header, body and footer.

ReportStructure reportStructure = documentInstance.getStructure();
ReportContainer reportContainer = reportStructure.createReport("My Report");
ReportBody reportBody = reportContainer.createReportBody();

In this example, we create a table with the two fields.

ReportBlock reportBlock = reportBody.createBlock();
ReportDictionary reportDictionary = documentInstance.getDictionary();
BlockAxis hAxis = reportBlock.getAxis(TableAxis.HORIZONTAL);

When the report is created from scratch, all default values are blanks and zeros so it is important to set the necessary attributes such as color, font, width.

SimpleTable simpleTable = (SimpleTable)reportBlock.getRepresentation();
CellMatrix bodyMatrix = simpleTable.getBody();
CellMatrix headerMatrix = simpleTable.getHeader(null);
CellMatrix[] matrices = new CellMatrix[]{bodyMatrix, headerMatrix}; 
for (CellMatrix matrix : matrices) {
   for (int i=0; i<matrix.getColumnCount();++i)
      TableCell cell = matrix.getCell(0, i);
      Attributes attributes = cell.getAttributes();
      if (matrix == bodyMatrix) {
      } else {
         attributes.setBackground(new Color(81, 117,185));
      SimpleBorder border = (SimpleBorder)attributes.getBorder();
      Font font = cell.getFont();
      Alignment alignment = cell.getAlignment();

The final step is to apply format. It is important. If you miss this step, the report structure will not be modified.


Save the document

The final step is to save the document to a folder. We can use function
DocumentInstance.saveAs(title, int destinationFolderId, categories, personalCategories, overwrite)

First we need to find the destination folder id. We assume that the folder Report Samples exists.

String folderQuery = "select * from CI_INFOOBJECTS where SI_KIND = 'Folder'"
                   + " and SI_NAME='Report Samples'";
infoObjects = (IInfoObjects) infoStore.query(folderQuery);
infoObject = (IInfoObject)infoObjects.get(0); 
int folderId = infoObject.getID();

Now we can save the document with title "Test".

documentInstance.saveAs("Test", folderId, null, null, true);

Hope this helps.

Alerters & Drilling in Webi

Posted by & filed under Web Intelligence.

It is not possible to use variables as drill object in Webi reports. Here is a clever trick how to overcome this limitation using alerters:

The idea is to define a variable that calculates the drill level using the function DrillFilter() and then to use this variable in alerters to "overwrite" the value of the drill object with the value of the desired variable.

Removing time part of a date fails in Kettle

Posted by & filed under Kettle.

Might be an interesting info.

I encountered an error running a Pentaho Data Integration transformation. After simplification I got this:

Generate Row creates one row with the date field DATETIME=1981-01-01. Calculator calculates field DATE using operation "Remove time from a date". And this trivial transformation failed with a weird error:

Unexpected error : 
java.lang.IllegalArgumentException: MINUTE
	at java.util.GregorianCalendar.computeTime(Unknown Source)
	at java.util.Calendar.updateTime(Unknown Source)
	at java.util.Calendar.getTimeInMillis(Unknown Source)
	at java.util.Calendar.getTime(Unknown Source)
	at org.pentaho.di.core.Const.removeTimeFromDate(
	at org.pentaho.di.core.row.ValueDataUtil.removeTimeFromDate(
	at org.pentaho.di.trans.steps.calculator.Calculator.calcFields(
	at org.pentaho.di.trans.steps.calculator.Calculator.processRow(
	at org.pentaho.di.trans.step.BaseStep.runStepThread(

After some digging into Pentaho code, I have replicated the error in Java:

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.TimeZone;

public class Prog {

    public static void main(String[] args) throws ParseException {
        //String dateStr = "1982-01-01", timeZoneID = "Europe/Berlin";
        //String dateStr = "1981-01-01", timeZoneID = "Asia/Singapore";
        //String dateStr = "1982-01-01", timeZoneID = "Asia/Seoul";
        String dateStr = "1982-01-01", timeZoneID = "Asia/Singapore";

        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = dateFormat.parse(dateStr);        

        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);


Here Asia/Singapore is the time zone setting of the Data Integration server.

The code failed with the same error:

Exception in thread "main" java.lang.IllegalArgumentException: MINUTE
	at java.util.GregorianCalendar.computeTime(
	at java.util.Calendar.updateTime(
	at java.util.Calendar.getTimeInMillis(
	at java.util.Calendar.getTime(
	at Prog.main(

Interesting is that the code works fine for very close parameters:

  • dateStr = "1982-01-01", timeZoneID = "Europe/Berlin"
  • dateStr = "1981-01-01", timeZoneID = "Asia/Singapore"
  • dateStr = "1982-01-01", timeZoneID = "Asia/Seoul"

Asia/Seoul and Asia/Singapore is actually the same time zone.

The cause of the error has been explained here.

"When you enter a date with no time, the time is assumed to be 12:00:00 AM.

But there was no 12:00:00 AM on January 1, 1982, in Singapore. After 11:59:59 PM on December 31, 1981, Singapore jumped ahead by half an hour to 12:30 AM. It had previously been at UTC+7:30, but moved to the whole-hour zone of UTC+8."

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

Posted by & filed under 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().


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) {        

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

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);
    HTMLView htmlView = (HTMLView) report.getView(OutputFormatType.DHTML);
    writeBytes(htmlView.getContent().getBytes(), title + " " + i + ".html");


That is it.


How to determine date pattern for a Webi document using Java RE SDK

Posted by & filed under SDK.


// Determine preferred locale for the user
Locale locale = null;
try {
    IUserInfo userInfo;
    try {
        userInfo = enterpriseSession.getUserInfo();
    catch (SDKException e) {
        userInfo = null;
    if (null != userInfo) {
        try {
            if (userInfo.getPreferredViewingLocale() != null) {
                locale = userInfo.getPreferredViewingLocale();
        catch (SDKException.PropertyNotFound e) {
            locale = Locale.getDefault();
    System.out.println("Locale: " + locale);
catch(Exception e) {
if (locale != null) {
    // Open the document
    OpenDocumentParameters odp = new OpenDocumentParameters(-1, true, false);
    DocumentInstance doc = reportEngine.openDocument(infoObject.getID(), odp);

    // Determine the pattern
    FormatNumber formatNumber = doc.getDefaultFormatNumber(FormatNumberType.DATE_TIME);
    System.out.println("Date pattern: " + formatNumber.getPositive());

    // Close the document

How to refresh Deski report and export it as PDF and XLS using COM SDK

Posted by & filed under SDK.

Here is an example how to open Deski report and refresh it and export as PDF using COM SDK.

You will need Visual Studio 2010 Express, BusinessObjects Enterprise XI 3.1 client tools.

1) Create a Deski report (here C:\Users\dmytro\Desktop\Document1.rep)
2) Create a new Project "RunDeski" in VS
3) Add reference to the Deski COM SDK, Project > Add Reference > BusinessObjects 12.0 Object Library
4) Paste the code.
5) Run it.

using busobj;
namespace RunDeski
    class Program
        static void Main(string[] args)
            Application application = new Application();
                application.Interactive = false;
                application.Logon("Administrator", "", "localhost", "secEnterprise", false, true);
                IDocument doc = application.Documents.Open(@"C:\Users\dmytro\Desktop\Document1.rep");
            catch (Exception e)

Purge or Refresh Universe Lists of Values from Command Line

Posted by & filed under Tools.

The command line tool imports a universe from CMS, refreshes or purges lists of values (LoVs) and exports the universe back to CMS.


Executable for BOE XI 3.1

Source – C# VS 2010 Express solution

This software is distributed AS IS with no warranty of any kind, use it on your own risk.


/server: – BO server. If not specified, standalone mode will be used.
/user: – User name. Default – "Administrator"
/password: – password. Default – blank
/auth: – Authentication. Default – "secEnterprise"
/cmsfolder: – CMS folder with universe
/cmsuniverse: – Universe name in CMS
/file: – Local file name
/lovs: – File with list of values. Only specified LoVs will be purged/refreshed.
/purge – Purge all lists of values
/refresh – Refresh all lists of values
/save – Save all lists of values to a file

The tool can purge or refresh LoVs.

server, user, password and auth are the connection parameters. The tool can also run in standalone mode, that is when server is not specified.

When cmsfolder and cmsuniverse are specified, the tool will import the universe, handle lists of values and export it back to CMS.

The tool can work with locally saved universe using option file. In this case the tool will open the universe, handle LoVs and save it to the same location.

The tool can proceed only subset of lists of values using lovs option which is the name of the file. To build the file with all LoVs, the option save should be used.


Build a list of all lists of values and save to a file lovs.txt

LovUtil.exe /server:localhost /cmsfolder:"test examples" /cmsuniverse:Univers1  lovs:lovs.txt /save

Refresh lists of values specified in the file lovs.txt

LovUtil.exe /server:localhost /cmsfolder:"test examples" /cmsuniverse:Univers1 /lovs:lovs.txt /refresh

Refresh all lists of values for a locally saved file in standalone mode

LovUtil.exe /file:"Univers1.unv" /refresh

Join and Synchronization in Web Intelligence

Posted by & filed under Web Intelligence.

When objects are merged in a Webi document there are two possible types of the merge operation: join and synchronization. The type depends on the objects selected in a data block. To achieve expectable result, it is important to understand the difference between these operations.

Let's consider examples of each operation.


The document use two data providers. The first data provider (Reservation) has Reservation Year, Future Revenue.

The second data provider (Sales) has Invoice Year, Sales Revenue.

The list of objects in the document contains these 4 objects:

Here are the corresponding tables in the report:

We cannot use both dimensions in the same data block. If we pick one, the other is grayed. The reason for that is that BO does not know how to combine the dimensions from different data providers. It is not obvious for BO that they have the same nature (year).

To tell BO that these dimensions have the same business meaning, and that we want to merge them in the report, we need to merge the dimensions.

We merge the Invoice Year and Reservation Year.

Now we can pick Year, Sales Revenue, Future Revenue in the same table.

The values of Year are the union of Invoice Year and Reservation Year values.

Note that if we pick only measure from one data provider, all years will be displayed.


Let's add Sales Person to the second data provider:

The table has not changed:

If we add Sales Person to the table, there will be significant change than just adding one dimension:

  • Now the merged dimension is showing only values of Invoice Year.
  • Sales Revenue is also split by Sales Person.
  • Future Revenue is also split by Sales Person. Instead the total Future Revenue for 2012 is shown for each Sales Person for 2012.

(Note that blank values here correspond to nulls. Null is just a normal value.)

The data in the two data providers has different granularity. It is not possible to calculate Future Revenue for a specific sales person as this granularity is not available in the Reservation data provider. Therefore BO shows total for 2012 for each Sales Person in 2012.

The years for which only Reservations exist are also not displayed. It is not possible to show them as the granularity of Reservations is lower than granularity of Sales. They cannot be added with blank in the column for Sales Person. Here the blank value means that there is no sales person. But sales person is just not relevant for Reservations. (OK, in general the sales person is relevant to reservations, but according to the data providers, he is not)

This demonstrates two types of operations: Join and Synchronization. The operation depends on the dimensions selected to the data block, it does not depend on measures and details.

Two data providers are joined if only merged dimensions are selected to the data block. All combinations of dimensions from the first data provider are united with all combinations of dimensions from the second data provider. The measures are projected in the context of the merged dimensions. The details are looked up by the matching dimensions.

Two data providers are synchronized if beside the merged dimensions, one or more non-merged dimension is used in the data block. All non-merged dimensions should be from the same data provider. Let's call this data provider the leading data provider. All combinations of dimensions from the leading data provider are displayed in the data block. The measures from the secondary data provider are projected in context of dimensions from the secondary data provider and looked up by the matching merged dimensions.

(There can be more than two data providers involved)


We considered two separate data providers. The operations join and synchronization are possible for a single data provider on a query level.

When we pick objects from the different contexts they are synchronized or joined on the query level.

For instance Future Revenue and Sales Revenue come from different contexts. Service is a common dimension (here, the common dimension is analogue of the merged dimension):

Therefore the queries are joined (note "Join" in the root):

The data provider with a joined queries acts as a data provider with a single query.

When beside the common dimensions, there is a dimension that does not belongs to all contexts, the queries are synchronized.

The synchronized queries are not actually synchronized when creating the data provider. The operation will be chosen by the dimensions selected in the data block. If only the common objects are used, the queries will be joined.

For instance, here you see result of the join operation, even though the synchronization operation was displayed for queries: