Category Archives: Web Intelligence

BusinessObjects Web Intelligence

Example of sub-query in Webi

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:

  • 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:


Crystal Reports vs 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
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.

Hierarchies in Webi

The task is to display a fixed level hierarchy together with totals for each level. This can be done using breaks. The most straightforward implementation might look like this:

If there are many levels and the level names are long, the table will be very wide, with a lot of empty space. It would be better to have more compact layout, for example:

This post describes how to implement such layout. Continue reading

Calculated Default Value for a Prompt

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"


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

Time Sheet Status – Webi Exercise

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”.


Continue reading

Break on Measure in 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?

Use of Alerts in Webi

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


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.


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


Cell Borders in Web Intelligence

Web Intelligence cell borders function differently from the borders in Excel. The border in Web Intelligence can have different color from sides. For example, left of two adjacent cells can have gray right border, right can have black left border. The color of the border on the screen depends on the rendering algorithm, i.e. browser, presentation format (PDF, HTML, XLS), View/Edit mode.

To demonstrate this, let’s create a report, add a table with three objects to it. Select the middle column and change its borders to be black from each side:

The result can be the following:

You can change spacing between cells and see the cells and the borders:

To make sure that borders are always displayed correctly, set the same color from both sides:

Without spacing, the border will look as desired:

+0 does matter in Web intelligence

In university I learned that adding zero does matter sometimes. However I did not expect to see this in Web Intelligence.

The fact is that the following two expressions are not the equal:

=(Average([Revenue])) In ([Year])
=(Average([Revenue])+0) In ([Year])

The query:

The result:

So the expression (Average([Revenue])+0) In ([Year]) is not equal to (Average([Revenue])) In ([Year]).  But it is equal to: Average([Revenue] In ([Year];[Quarter];[Month])) In ([Year]).

The funny thing this that this is a normal behavior, and there is explanation for this (provided by SAP):

The output context of the aggregation is [Year] in both cases, however the input context of the aggregation is different. In first expression the input is Body ([Year], [Quarter]), however in the second expression the input is the detail context of the [Year], [Quarter] and [Month].

Dynamic Formatting in Web Intelligence

You can get the response string to a prompt using Web Intelligence function UserReponse(). However the function returns string value which is formatted according to Web Intelligence preferred locale. To make handling of date strings independent on the locale, dynamic formats can be used.

Dynamic Formats in ToDate Function

To convert the date prompt string into date, ToDate function can be used.

  date ToDate(date_string;format)

If locale is French (Canada), the format should be “yyyy-MM-dd HH:mm:ss”.

For example,

= ToDate(UserResponse("Date:");"yyyy-MM-dd HH:mm:ss")

To make a report independent on the format (and correspondingly the locale), the dynamic formats can be used. The dynamic input formats are “INPUT_DATE_TIME”, “INPUT_DATE”.


= ToDate(UserResponse("Date:");"INPUT_DATE_TIME")

Handling Date and DateTime Format

Before SP2 of BOE XI 3.1, the user could enter date in both date and datetime format, and conversion using specific format would not work correctly in all situations. The formula that accounts the both possible formats:


Dynamic Formats with FormatDate

The dynamic formats can be used in FormatDate() function.


BusinessObjects documentation does not list the available dynamic formats. Some of them can be found in dialog “Number Format”.