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

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 →

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

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…

Continue reading →

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?

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

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: