How to create a webi document using Java Report Engine 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();
query.addResultObject(objects.getChildByName("Service"));
query.addResultObject(objects.getChildByName("Revenue"));

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);
filterCondition.createFilterConditionConstant("US");

The last step is to run queries.

dataProvider.runQuery();

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);
hAxis.addExpr(reportDictionary.getChildByName("Service"));
hAxis.addExpr(reportDictionary.getChildByName("Revenue"));

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) {
         attributes.setBackground(Color.white);
         attributes.setForeground(Color.black);
      } else {
         attributes.setBackground(new Color(81, 117,185));
         attributes.setForeground(Color.white);
      }
      SimpleBorder border = (SimpleBorder)attributes.getBorder();
      border.setSize(BorderSize.NONE);				
      cell.setAttributes(attributes);
      Font font = cell.getFont();
      font.setName("Arial");
      font.setSize(10);
      cell.setFont(font);
      Alignment alignment = cell.getAlignment();
      alignment.setHorizontal(HAlignmentType.LEFT);
      cell.setAlignment(alignment);
      cell.setWidth(50);
   }
}

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

documentInstance.applyFormat();

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);
documentInstance.closeDocument();

Hope this helps.

15 thoughts on “How to create a webi document using Java Report Engine SDK

  1. Rajesh

    Hi Dmytro

    I am trying to insert a row with some text values in its columns e.g Year,Sales etc..

    Can u please help me how to achieve this?

    I am new to BO.

    Thanks

    Like

    Reply
  2. Dave

    Hi, thank you for your sharing.

    But now I got a problem with getting the hierarchies from datasource, in XI 3.0, the way like this:
    doc.getDataProviders().getItem(0).getDataSource().getHierarchies();

    but it’s no longer implemented in XI 4.0, is there any other way to get it ?

    thanks in advance!

    Like

    Reply
      1. Dave

        Thank you for your reply!

        It’s realy a big problem, in my project, I need to get the dimensions and measures information from webi or cr, is there any way to do this before 4.1 ?

        Like

  3. Ashu

    Hi Dmytro ,

    Basically I am a sap BO developer (SAP BO 4.0). I am new to Java
    I am planning to migrate SAP BO (. Unv) to OBIEE (. Rpd), I searched many websites there is no converter tool available, so at last I came to know that using JAVA SDK of sap BO and JAVA SDK of OBIEE, by comparing these two I can convert (. Unv to .Rpd) can you give me some idea to develop this.

    Like

    Reply
  4. Meera Mukundan

    Hi,

    Its a very nice post and was really helpful.

    I have BO 4.0 installed on my machine. So when I am trying to run this code am getting error. Could you get us a code i.e compatible for 4.0?

    Thanks,
    Meera

    Like

    Reply
  5. Jordi

    Hello Dmytro, i have a question for you

    I am trying to access to a column of item of dataproviders like this…

    —-> ThisDocument.DataProviders.Item(j).Columns.Item(1).Item(q)

    The problem is this line is in Visual Basic Macro for XI R2 Desktop Intelligence. I need the same for java sdk 4.1 if it is possible…

    I saw in the java api getitem() method for dataprovider but i dont see nothing similar to access column objects.

    Thanks in advance for your help

    you have a very interesting website, congratulations.

    Like

    Reply
  6. Cijoy

    Hi,

    Great blog!

    I have a question about using
    dataSource.getClasses().getChildByName(“Test1”);
    in java sdk. If I have class(folder) and object(attribute) with the same name “Test1”, it will always return me the first occurance. How should I handle such cases where in I want the entire list rather than first occuring element.

    Regards,
    Cijoy

    Like

    Reply

Leave a Reply to Rajesh Cancel 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 )

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