Tag Archives: web intelligence

Dynamic Formatting in Web Intelligence

Leave a reply

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

So:

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

=If(IsError(ToDate(UserResponse("Date:");"INPUT_DATE"));
            ToDate(UserResponse("Date:");"INPUT_DATE_TIME");
            ToDate(UserResponse("Date:");"INPUT_DATE"))

Dynamic Formats with FormatDate

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

 =FormatDate(CurrentDate();"LONG_DATE")

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

ForceMerge() example

Leave a reply

The Webi document is built on two queries. First query dimensions are Country and Resort:

There is only one dimension – Resort in the second query.

The queries are merged on Resort.

The first table in the result contains expected numbers. While in the second table, sum of Number of guests (565+446+540=1551) is duplicated for each Country. The explanation for this is that in the second table, dimension Resort is not used, so the queries are not merged:

Webi function ForceMerge can help in this situation.

ForceMerge forces Web Intelligence to account for synchronized dimensions in measure calculations when the synchronized dimensions do not appear in the calculation context of the measure

Using ForceMerge([Number of guests]) for the column will produce the following result:

Sometimes we have to use ForceMerge, sometimes it can improve performance of SQL queries. However try to avoid it if possible, this will make document logic more clear.