Example of sub-query in Webi

Posted by & filed under Web Intelligence.

The post describes an interesting example of use of subqueries in Web Intelligence.

The task is to display year-to-day revenue figures.

The universe database structure is the following:

The universe contains the following objects:

  • Revenue defined as SUM(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
  • Invoice Date objects such as Invoice Date, Month, Quarter, Year.
  • Today object defined as SYSDATE (Oracle).

The task can be solved with the following query in Web Intelligence:

The generated SQL is:

SELECT
  SUM(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE),
  INVOICE_DATE.THE_DATE,
  INVOICE_DATE.YEAR
FROM
  SERVICE INNER JOIN INVOICE_LINE 
  ON (INVOICE_LINE.SERVICE_ID=SERVICE.SERVICE_ID)
  INNER JOIN SALES ON (INVOICE_LINE.INV_ID=SALES.INV_ID)
  INNER JOIN CALENDARDAY  INVOICE_DATE 
  ON (SALES.INVOICE_DATE_ID=INVOICE_DATE.DATE_ID)  
WHERE
  (
   INVOICE_DATE.YEAR  =  ANY 
     (
     SELECT
       INVOICE_DATE.YEAR
     FROM
       CALENDARDAY  INVOICE_DATE
     WHERE
       INVOICE_DATE.THE_DATE  =  SYSDATE
     )
   AND
   INVOICE_DATE.THE_DATE  <=  SYSDATE
  )
GROUP BY
  INVOICE_DATE.THE_DATE, 
  INVOICE_DATE.YEAR

How to Change Expression in Report Cells using BO Java SDK

Posted by & filed under SDK.

Problem

"I have nearly 500 reports, each report having logo on the right up corner in all tabs, in a cell having formula “boimg://logo.bmp” and read as image URL. Now i have come up with change in logo format. Now the same formula is to be changed to “boimg://logo.jpg”".

Code

private static int replaceExpression(DocumentInstance widoc, String from, String to) {
   ReportDictionary reportDictionary = widoc.getDictionary();
   ArrayList nodes = getListOfTreeNodes(widoc.getStructure(), false);
   int n = 0;
   for (TreeNode node : nodes) {
      if (node instanceof ReportCell) {
         ReportCell reportCell = (ReportCell)node;
         ReportExpression reportExpression = reportCell.getExpr();
         if (reportExpression instanceof FormulaExpression) {
            FormulaExpression formulaExpression = (FormulaExpression)reportExpression;
            if (formulaExpression.getValue().compareTo(from) == 0){
               FormulaExpression newFormulaExpression = reportDictionary.createFormula(to);
               reportCell.setExpr(newFormulaExpression);
               n += 1;
            }
         }
      }
   }
   widoc.applyFormat();
   return n;
}

public static ArrayList getListOfTreeNodes(TreeNode root, boolean onlyLeaves) {
   ArrayList nodes = new ArrayList();
   treeNodeTraversal(root, nodes, onlyLeaves);
   return nodes;
}

private static void treeNodeTraversal(TreeNode node, ArrayList nodes, boolean onlyLeaves) {
   if (!onlyLeaves || node.isLeaf()) {
      nodes.add(node);
   }
   for (int i = 0; i < node.getChildCount(); ++i) {
      treeNodeTraversal(node.getChildAt(i), nodes, onlyLeaves);
   }
}

Source

http://bukhantsov.org/tools/ReplaceInReportCell.java

How to get report drill filters

Posted by & filed under SDK.

Here is an example how to get report drill filters.

DocumentInstance doc = reportEngine.openDocument(infoObject.getID());
Reports reports = doc.getReports();
for (int i = 0; i < reports.getCount(); i++)
{
    Report report = reports.getItem(i);
    DrillInfo drillInfo = (DrillInfo) report.getNamedInterface("DrillInfo");
    DrillBar drillBar = drillInfo.getDrillBar();
    for (int j = 0; j < drillBar.getCount(); ++j) {
        DrillBarObject drillBarObject = drillBar.getItem(j);
        System.out.println(
            String.format("%s='%s'", 
                drillBarObject.getName(), 
                drillBarObject.getFilter()));
    }
}
doc.closeDocument();

Result:

Service Line='Recreation'
Year=''

How to Test All Connection using BusinessObjects SDK

Posted by & filed under SDK.

Problem

"I was doing some research in the past but could not find any solution out on the WEB, but think it would be helpful for BO admins. Tool, that can test all the universe connection from environment without using Designer tool, since in it you can test one connection at the time, but if you have 50 of them that is a lot of clicks. In our company , we often change database passwords and need to update connections setting, and sometimes we can miss 1-2 and few days later some reports will start failing or people will complain that they can run certain report (because database password was not been updated). So I was thinking about some utility that can use designer SDK that can go and loop through all the universe connections in the system and return pass or fail."

Code

static void Main(string[] args)
{
   Application application = new Application();
   try
   {
      if (args.Length != 4)
      {
         application.LogonDialog();
      }

      Connections connections = application.Connections;
      foreach (Connection connection in connections)
      {
         Console.Write(connection.Name.PadRight(40));
         try
         {
            connection.Test();
            Console.WriteLine("OK");
         }
         catch (Exception ex)
         {
            Console.WriteLine(ex.Message.Replace("\n","").Replace("\r",""));
         }
      }
   }
   catch (Exception ex)
   {
      Console.WriteLine(ex.ToString());
   }
   finally
   {
      application.Quit();
   }
}

Downloads

download source Source
download executable Executable for BOE XI 3.1 SP3 FP3.2

The executable should be run from command line as the result is printed to standard output. The arguments are: username password server auth. E.g.

TestConnections.exe Administrator "" localhost secEnterprise > c:/connectionstest.txt

If you do not specify the arguments, the designer logon dialog will appear.

Crystal Reports vs Web Intelligence

Posted by & filed under Business Objects, Web Intelligence.

Crystal Reports and Web Intelligence are two technologies from SAP BusinessObjects portfolio that provide possibility to build reports. Web Intelligence is a perfect tool for ad-hoc reporting. It is very easy to build report from a scratch even for a new user. However when the requirements are too specific, when it is necessary to build in a sophisticated logic or when a precise formatting is required, the simplicity becomes too restrictive and the reports turn into a terrible mix of miscellaneous tricks. In this situation, Crystal Reports is much better choice. Below is a technical comparison of Crystal Reports and Web Intelligence written by my colleague Maria Ruchko, an expert in both these technologies.

Crystal Reports

Web Intelligence

Data Source Crystal Reports can use universes or access a database directly. Some advanced features of universes (such as contexts) are disabled in Crystal. Database Direct connection and Universe cannot be used together. Web Intelligence can only use universes.
Prompts Report data not necessarily must be filtered by the prompt value. Prompts’ Lists of Values can be defined in the report. Default value can be calculated. Prompts within reports must be used for filtration of data (this restriction can be overcome but some tricks must be used). Lists of values are based on the database values. Customized or calculated lists of values require placing prompt into Universe (which means universe customization). Default value cannot be calculated. Current Date cannot be set as default in Date prompt.
Layout Many tools are available for work with layout  (rulers, guidelines). It is also very flexible because formulas can be used for determining position of the fields, suppression of rows etc. Especially good for working with single table reports. If report has more than one table, subreports must be used which might affect performance Web Intelligence is not flexible and it is difficult (sometimes impossible) to get precise layout, very difficult to get flexibility based on parameters (e.g. showing dynamic number of columns). Not a problem to place more than one table within the report.
Development Crystal Reports Designer license and Crystal Reports Writer are required to develop and modify reports. Reports can be developed or modified using Infoview or Webi Rich Client.
Viewing Reports can be viewed and scheduled in Infoview Reports can be viewed and scheduled in Infoview
Development Time It takes longer to develop simple report in Crystal Reports than Web Intelligence, but complex reports development time is more predictable (no need to invent tricks). It is easier to implement complex logic. Simple reports can be developed on fly if there is an intuitive universe. It takes a lot of time to develop a report based on a complex universe (to overcome restrictions of the tool). For complex reports it is sometimes easier to develop a customized universe specially for the report and put all logic in this universe.
Users Office employees who have to report in the same form (with some deviations which can be handled by parameters) or management for some standard reports or analytical reports (if manager’s are not technically qualified to build Webi reports or universe is too complex for understanding) Top management and executives, who use report for decision making (not for daily scheduled standard reports), want to make ad-hoc analysis, dig into data and decide what data they prefer to see in the report when they build it.
Notifications Crystal Reports can send an e-mail to users based on alerts, when report data meets some condition (e.g. if profit less than 0) The similar option is not available
Export formats XLS, PDF, CSV, TTX, RTF, HTML, XML XLS, PDF, CSV
Upgrade and recovery If a report is based on database directly (not using universe), upgrade can affect report only if database structure was changed. It is easy to remap fields though, which means that report can be recovered anyway. Universe changes may affect a report if an object used by the report is deleted. Sometimes it can be difficult or impossible to recover reports if the original universe was modified or removed by mistake.

Printing LoV names with Designer COM SDK

Posted by & filed under SDK.

Tutorial "Getting started with Designer SDK"

using System;
using Designer;
namespace ConsoleApplication1
{
    class Program
    {
        delegate void CleanUpMethod();

        static void Main(string[] args)
        {
            Application application = new Application();
            application.Interactive = false;

            CleanUpMethod cleanUp = delegate { application.Quit(); };
            Console.CancelKeyPress += delegate { cleanUp(); }; // to handle Ctrl+C

            try
            {
                application.Logon("Administrator", 
                    "", "localhost", "secEnterprise");                
                Universe universe =
                    application.Universes.OpenFromEnterprise(
                        "webi universes",
                        "Island Resorts Marketing",
                        false);
                PrintClasses(universe.Classes, "");
                universe.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                cleanUp();
            }
        }

        static void PrintClasses(Classes classes, String path)
        {
            foreach (Class theclass in classes)
            {
                String path2 = path + "/" + theclass.Name;
                foreach (Designer.Object theobject in theclass.Objects)
                {
                    if (theobject.HasListOfValues)
                    {
                        Console.WriteLine(path2 + ";" 
                            + theobject.Name + ";" 
                            + theobject.ListOfValues.Name);
                    }
                }
                PrintClasses(theclass.Classes, path2);
            }
        }

    }
}

Use of Outer Joins in Combination with Self Restricting Joins

Posted by & filed under Universe Designer.

Problem

BusinessObjects use some algorithm for SQL generation based on objects selected in the query and the universe. There are a number of parameters that control SQL generation. The problem is that in some cases BO generates SQL that is not intuitively expected.

What we would expect is that the self restricting joins are applied to the tables and then the tables are joined.

When SELFJOINS_IN_WHERE=No, the self restricting joins are put in the ON clause of the SQL. The generated SQL ignores the self restricting join on the table FACT.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  FACT.TYPE=0  AND  DIM.TYPE=0)

When SELFJOINS_IN_WHERE=Yes, the self restricting joins are put in the WHERE clause.  The effect of this is that the outer join turns into inner join in the generated SQL.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID)
WHERE
  ( FACT.TYPE=0  )
  AND  ( DIM.TYPE=0  )

In fact, we expect the following SQL:

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  DIM.TYPE=0)
WHERE
  ( FACT.TYPE=0 )

BusinessObjects cannot place self restricting joins conditionally depending on the join structure. (Probably this will make the algorithm too complex, so this is not a bad thing)

A workaround is to avoid using of self joins using derived tables.

A Solution when SELFJOINS_IN_WHERE=No

Replace FACT with a derived table FACT_D

SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT_D.A,
  DIM.B
FROM
  DIM RIGHT OUTER JOIN (
  SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0
  ) FACT_D
  ON (DIM.TYPE=0  AND  FACT_D.DIM_ID=DIM.DIM_ID)

A Solution when SELFJOINS_IN_WHERE=Yes

Replace DIM with a derived table DIM_D

SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT.A,
  DIM_D.B
FROM
  (
  SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0
  ) DIM_D RIGHT OUTER JOIN FACT
  ON (FACT.DIM_ID=DIM_D.DIM_ID)
WHERE
  ( FACT.TYPE=0 )

Is the use of Enterprise SDKs in a thick-client/desktop application supported

Posted by & filed under SDK.

Question:

BusinessObjects Enterprise XI, Crystal Reports Server XI, and Crystal Enterprise contain COM, .NET, and Java SDKs. Is the use of these SDKs in a thick-client or desktop application supported?

Answer:

No, the use of BusinessObjects Enterprise SDKs is not supported in a thick-client or desktop application because the BusinessObjects Enterprise SDK is only intended for use in web applications. To build BI platform thick-client or desktop applications, use Business Objects web services.

 Explanation:

  • Thick client applications are not supported because of the CORBA implementation we use with the Enterprise services.
  • Thick client applications are typically installed on many client systems. The large install base would put a strain on the CMS because an individual CORBA connection would be created for each client logon to the CMS server.
  • Web applications don't present this problem because the application server will only create one CORBA connection to the CMS server for all the client sessions initiated.
  • While the thick client application will likely work without error, there is no escalation path to the technical development team for any issues specifically related to the thick client application. Any issue must be reproduceable in a web environment in order to be tracked as a bug.
  • Only the Enterprise Web Services SDK is supported in a thick client application.

Source:

SAP note 1219135 and related discussion

How to retrieve Wid file for a Web Intelligence document

Posted by & filed under SDK.

public static boolean downloadWid(IInfoObject infoObject, String filename)
       throws SDKException
{
    if (infoObject.getKind() != "Webi")
        return false;
    IFiles files = infoObject.getFiles();
    IRemoteFile file = (IRemoteFile) files.get(0);
    file.download(filename);
    file.commit();
    return true;
}

BusinessObjects Enterprise XI 3.1 – Supported Platforms

Posted by & filed under Administration.

Links

Search on SAP

Supported Browsers BOE XI 3.1 SP3

Supported Browsers BOE XI 3.1 SP5

See the SAP documentation for details.