Calculated Default Value for a Prompt

Posted by & filed under Web Intelligence.

It is possible to specify a constant default value for a prompt. But what if the default value need to be calculated.

For example, there should be an optional prompt for the statement date in the report. If the date is not specified by the user, the current date should be used.

It is possible to implement the logic with the following query filter:

If the date is not specified in the prompt, the optional prompts are ignored and the condition will be reduced to:

Statement Date Equal to Today

If the date is specified, the condition will be equivalent to:

Statement Date Equal to Prompt "Enter Statement Date"

because

A or (A and B) <=> (A and True) or (A and B) <=> A and (True or B) <=> A

Removing multiple hyperlinks in Excel

Posted by & filed under Other.

There are some techniques on the web on how to remove all hyperlinks from Excel document. These include removing using Paste special and a macro. Sometimes it is difficult to use Paste special because of merged cells, and macro sounds too frightening and also it may spoil your formatting.

Another technique is to use Find and Replace.

  • Hit Ctrl+H
  • Click Options
  • For the field Find what, click the arrow on the button Format
  • Select option Choose Format From Cell
  • Pick the format of a cell with hyperlink
  • In the field Replace with, choose the format for a number without hyperlink
  • If you want to remove hyperlinks in the whole workbook, change Within from Sheet to Workbook.
  • Click Replace All

Links

http://www.google.com/search?q=Removing+multiple+hyperlinks+in+Excel

Handling Records Removal in Dimension Tables

Posted by & filed under Kettle.

The records can be removed from a table in a operational system, but we never remove records from dimension tables. This post describes how the removal of records can be handled in dimension tables.

Let's consider an example. There is a list of contact persons in a source system. The list is loaded into a dimension table in a data warehouse. Contact persons can be removed from the operational system, but not from dimension tables. The task is to list the current contact persons based on the data from the data warehouse. What can help us to identify the current records? Read more »

Time Sheet Status – Webi Exercise

Posted by & filed under Web Intelligence.

It seems to be a good Webi exercise (for beginners).

Employees submit weekly time sheets. Each time sheet has time sheet lines. Time sheet line can have one of three statuses: Submitted, Approved and Rejected.

The status of the time sheet is defined by the statuses of the time sheet lines:

  • When for a week one or more lines are rejected the whole time sheet status is “Rejected”.
  • When for a week one or more lines are submitted, there are no lines rejected, but there can be lines approved, the time sheet status is “Submitted”.
  • When all lines are approved the time sheet status is “Approved”.
  • When there are no time sheet lines, the status is “Missing”.

Solution…

Read more »

Break on Measure in Web Intelligence

Posted by & filed under Web Intelligence.

Sometimes it makes good sense to apply break on measures.

Let's build a query based on the eFashion universe that contains product Lines, product Promotion Cost, Store Name, and Sales Revenue. Promotion cost is money spent for advertising a product, it is not directly related to stores. So the report is showing the same product promotion cost for different stores.

Technically speaking, the queries from two contexts are merged in the table. The first query is from "Promotion" context, it contains [Lines] and [Promotion Cost USD]. Second query is from "Shop facts" context and it contains [Lines], [Store name], and [Sales revenue]. The queries are merged by [Lines].

This looks not nice because it might seem that the same amount is spent for each store for a product. The readability can be improved by defining break on the measure.

Add break on Promotion Cost, Remove break header and footer rows, Show table header, Center values across break

However defining the break only on the measure is a wrong way. You can get very unexpected results. For example, when the promotion cost will be same for a couple of products, or when a calculation is used (see a puzzle in the end of the post). It would be more correct to define a break by product lines and then the break by promotion cost.

Remove break from "Promotion Cost", Add break for the column "Lines", and then again Add break for the column "Promption Cost", Remove break header and footer rows, Show table header, Center values across break

Ok, that's it. The point it is if you want to have a break for a measure, you have to define break for the corresponding dimension.

The next question is what if we do not want to see the break by Lines as a break, i.e. we want the product line name in each row. A simple solution is to duplicate column Lines, and "hide" the one with the break.

Add another column "Lines", change expression for the first column to =[Lines]+"", Hide the column "Lines"

This does not look neat because you cannot really hide the column. But you can build nice appearance using alert that replaces value of the cell with Promotion Cost.

Remove column "Promption Cost", Expand the first column ("Lines" with break), Move it to the place of "Promotion Cost", create alert with condition "Cell content is not null", specify =[Promotion Cost USD] in the Display field, configure alignment and color.

Now a puzzle for geeks

You have a table with Lines, Store name, Promption Cost USD, and Sales revenue, the break is defined on the column Promption Cost USD.

Replace expression in the column Promption Cost USD with:

=If [Lines]="Dresses" Or [Lines]="Accessories" Then [Promotion Cost USD] Else 0

and explain the result:

Why is 9,500 shown for "Dresses" and not 13,000?

How to Change SQL in Webi Document Using Java RE SDK

Posted by & filed under SDK.

This post briefly describes how to change SQL in Webi document. The following code will work for a Webi document with one simple query (say one object, without subqueries, combined queries, etc):

DocumentInstance widoc = wiRepEngine.openDocument(31223); // 31223 is ID of the webi document
SQLDataProvider sdp = (SQLDataProvider)(widoc.getDataProviders().getItem(0)); 
SQLSelectStatement sss = (SQLSelectStatement)(sdp.getSQLContainer().getChildAt(0));
sss.setSQL("SELECT * FROM (" + sss.getSQL() +") A");
sdp.validateSQL();
sdp.changeSQL();
widoc.save();
widoc.closeDocument();

If the document contained the only object "Resort" from universe "Island Resorts Marketing", the query was:

SELECT
  Resort.resort
FROM
  Resort

After applying the script the query will become:

Note that this is now a custom query.

Not clear?

Try to start from this: http://bukhantsov.org/2011/08/getting-started-with-businessobjects-java-sdk/ :)

Use OLE DB instead of ODBC for SQL Server

Posted by & filed under Administration.

The quickest way to set up connection to SQL Server database from BusinessObjects is to create 32 bit System ODBC data source.

The most irritating drawback of this approach is that if there is an error in universe, it is not possible to see the real error message. For all kinds of errors, you will see the same message:

A database error occured. The database error text is: [Microsoft][ODBC Driver Manager]Statement(s) could not be prepared.. (WIS 10901)

A solution is to use OLE DB connection. In this case the error message will be more descriptive, e.g.:

First, you need to install Microsoft SQL Server 2008 Native Client.

Second, set up BusinessObjects connection to use SQL Server OLE DB instead of ODBC using the database parameters. Open connection (in Tools>Connections), click button Back, and select OLE DB as network layer, and set up the connection.

 

Use of Alerts in Webi

Posted by & filed under Web Intelligence.

This post is a quick guide how to add a simple alert to a table in Webi.

Images

Upload necessary images to the BO server in the folder

[Business Objects]\BusinessObjects Enterprise 12.0\images

In this example images green_arrow.gif and red_arrow.gif were used.

Preparation

Create new Webi document based on Island Resort Marketing universe, Pull object Resort, Year, Revenue into the query. Create the variable Revenue 2004 with the following expression:

=Sum(If [Year]="FY2004" Then [Revenue])

and Revenue 2005

=Sum(If [Year]="FY2005" Then [Revenue])

Defining Alert

Click the alerter button on the tool bar.

Create new alerter, and in the created alerter add two sub-alerters: one for green arrow when Revenue for 2004 is less then Revenue for 2oo5, and one for red arrow in the opposite situation.

For the first sub-alerter, select "Image from URL", and enter "boimg://greet_arrow.gif" in the URL field. Boimg refers to the folder [Business Objects]\BusinessObjects Enterprise 12.0\images. Do the same for the second sub-alerter.

Apply the created alerter for the column Revenue 2005.

This will add the arrows to the cells:

Another use of Alerts

Another use of alerts is to replace blank (on the left side) values with NA (on the right side):

 

Importing Linked Universes

Posted by & filed under SDK.

Problem

Let's assume there are two universes: Base and Derived. The Derived universe is linked to Base (Base does not have any link to other universes).

If you are importing Derived and then Base using .NET Designer SDK – everything is ok.

Universe derivedUnv = application.Universes.OpenFromEnterprise("", "Derived", false);
Universe baseUnv = application.Universes.OpenFromEnterprise("", "Base", false);

If you try to import Base and then Derived, import of the Derived universe will fail with the error: 'Base' is already open. Please close it and try again.

Let's assume that we need to import a bunch of universes. The derived universes should be imported first, and the base universes should be imported last. How to determine if the universe is a base universe?

"Solution"

For me the most simple and working solution has been to avoid problem. I usually process universes one-by-one, i.e. I do not import many universes.

Solution

A solution is to query CMS to get the list of base universes:

List<String> list = new List<string>();

CrystalDecisions.Enterprise.InfoObjects unv_objects =
    infoStore.Query("SELECT * FROM CI_APPOBJECTS WHERE SI_KIND = 'Universe' "
                   +"AND SI_DERIVEDUNIVERSE.SI_TOTAL>0");

foreach (CrystalDecisions.Enterprise.InfoObject unv_object in unv_objects)
{
    list.Add(unv_object.CUID);
}

Now you can import the derived universes:

foreach (StoredUniverse unv in src_folder.StoredUniverses)
{
    if (!list.Contains(unv.CUID))
    {
        application.Universes.OpenFromEnterprise(src_path, unv.Name, false);
    }
}

and then the base universes:

foreach (StoredUniverse unv in src_folder.StoredUniverses)
{
    if (list.Contains(unv.CUID))
    {
        application.Universes.OpenFromEnterprise(src_path, unv.Name, false);
    }
}

Details

Let me know if you need details or complete code..

Compile and run BO SDK Java tool from Command Line

Posted by & filed under SDK.

You can compile and run java tools without installation of IDE. You need JDK and BO SDK libraries. On BO server (with default settings), JDK can be found in

C:\Program Files (x86)\Business Objects\javasdk\bin

Batch to setup class path (env.bat)

@echo off
set libs=C:\Program Files (x86)\Business Objects\common\4.0\java\lib
set cp=%libs%/boconfig.jar
set cp=%cp%;%libs%/cecore.jar
set cp=%cp%;%libs%/celib.jar
set cp=%cp%;%libs%/cesdk.jar
set cp=%cp%;%libs%/cesession.jar
set cp=%cp%;%libs%/corbaidl.jar
set cp=%cp%;%libs%/ebus405.jar
set cp=%cp%;%libs%/javacsv.jar
set cp=%cp%;%libs%/jtools.jar
set cp=%cp%;%libs%/logging.jar
set cp=%cp%;%libs%/rebean.common.jar
set cp=%cp%;%libs%/rebean.jar
set cp=%cp%;%libs%/rebean.wi.jar
set cp=%cp%;%libs%/SL_plugins.jar
set cp=%cp%;%libs%/wilog.jar
set cp=%cp%;%libs%/external/xpp3.jar
set cp=%cp%;%libs%/external/xpp3_min.jar;
@echo on
set cp="%cp%"

Test program (Program.java)

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

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");
            // something useful
        } catch (SDKException ex) {
            ex.printStackTrace();
        } finally {
            if (enterpriseSession != null)
                enterpriseSession.logoff();    
        }
        System.out.println("Finished!");
    }
}

Compile and run

env.bat
javac -cp %cp% Program.java
java Program