Skip to Content

This page is part of the…

BI
Upgrade Series

Overview

This document describes the corrections and changes to the calculation engine in Web Intelligence 4.1 compared to Web Intelligence XI 3.1, XI 3.0, and XIR2 SP06 and SP03. It compares the new behavior of the calculation engine to its behavior in the previous versions.

It also suggests migration strategies for accommodating the calculation engine changes.

It gives a description of the formula rewrite mechanism introduced in 4.1 SP03 to preserve the reports created with an older version, from specific changes.

(Document authored by Pierre Saurel & Pascal Gaulin / Web Intelligence Product Experts)

Table of contents

Introduction

The calculation engine for Web Intelligence was updated for Business Objects XI 3.0 and 3.1 to include several corrections and improvements. These changes are present in the 4.1 releases.


This document describes these changes and the way they might affect the calculation results in Web Intelligence documents.

 

Where() Operator

“Where” operator works on measures

Prior to XI 3.0, the “Where” operator accurately supported conditions on dimensions or detail objects only. Conditions on measures were possible, but did not always return accurate results.


Web Intelligence XI 3.0 fully supports the usage of measures in “Where” conditions.


More details can be found in the documentation.

“Where” operator on measure with a condition on a formula based on a dimension

Previously, dimensions were incorrectly added to the dimensional context of the condition. Now dimensions are only used for the conditional evaluation.


=[Revenue] Where ( DataProviderType(DataProvider([Quarter])) = “Universe”;))

when used in a table with [Quarter], the result of the formula with the condtion was processed without [quarter] in the table (same value replicated for each different quarter).

Document migration:

User can aggregate on  the related dimension in the context of the measure (=[Revenue]  ForAll([Quarter]) Where ( DataProviderType(DataProvider([Quarter])) = “Universe”;))

From BI 4.1 SP03, to ensure that you receive results for this formula that correspond to the previous document versions, the system automatically rewrites the formula using an ad-hoc parameter with the “where” operator to specify the dimension to take into consideration ((=[Revenue] Where ( DataProviderType(DataProvider([Quarter])) = “Universe”;([Quarter]))).

This functionality is available as of BI 4.1 SP03 for documents created using the following versions:

  • XIR2 all releases
  • XI3.0 all releases
  • XI3.1 SP01 RTM and All FPs
  • XI3.1 SP02 RTM and All FPs
  • XI3.1 SP03 RTM
  • XI3.1 SP04 RTM
  • XI3.1 SP05 RTM
  • BI4.0 SP01 RTM and All Patches
  • BI4.0 SP02 RTM and All Patches
  • BI4.0 SP03 RTM and All Patches
  • BI4.0 SP04 RTM and All Patches

For more details, refer to the Automatic Formula Rewrite section, below.

Interaction between a context modifier on a measure aggregation and the “Where” operator

Dimensions were incorrectly added as dimensional contexts into the list of dimensions for the context modifiers that have been applied to a measure. This problem happened when “where” operators that used conditions on dimensions were used on expressions that used measures and context modifiers.


Example:

AggregationFct( [measure] forall([dim1]) ) where ( condition on [dim2])

Was processed as: AggregationFct( [measure] forall([dim1];[dim2]) ) where ( condition on [dim2])

Is now processed as: AggregationFct( [measure] forall([dim1]) ) where ( condition on [dim2])


Interaction between a context modifier on a dimension and the “Where” operator

For a “where” operator with a condition on a dimension applied to an expression on a dimension with context modifier, the dimension of the condition was incorrectly added to the context modifier.


Example:

[dim 1] in ([dim 2]) where( condition on [dim1]) was

Interpreted before as: [dim 1] in ([dim 2],[dim1) where( condition on [dim1]) and is

Interpreted now as: [dim 1] in ([dim 2]) where( condition on [dim1])


Migration:

To get the previous behavior, swap the “where” operator and the context modifier. Example: [dim 1] where( condition on [dim1] ) in ([dim 2]).

“Where” operator is incorrectly applied when outside of an aggregation expression

For a “where” operator with a condition on a dimension outside an aggregation function, the “where” condition was incorrectly applied before the aggregation calculation. The condition is now applied after the aggregation with the respect to calculation accordingly of the parenthesis.


Example:

AggregationFct ([measure]) Where([dim] ..).

Before, where([dim]) was applied on measure before “agregationFct”.

Now, “aggregationFct” is applied on [measure] and the “Where” is applied after.


Migration:

To get the previous behavior, move the “Where” expression inside the parenthesis. Example: AggregationFct ([measure] Where([dim]…))

Filters

NoFilter() function and “In Break” context modifier

When using the NoFilter() function, the filters would be applied when they were not supposed to, if an “In Break” parameter was used. This problem has been fixed and the filters are now ignored, as expected.

Using filters on object details with multiple values

Details can have multiple values. When displayed in a table together with the dimension object which they depend on, they could show #MULTIVALUE (when there are multiple detail values for a single dimension value), unless the “Avoid duplicate row aggregation” table setting has been checked.


Filtering on details with multiple values would not select the individual values on rows where they show as #MULTIVALUE. To work around this issue, it was then necessary to check the “Avoid duplicate row aggregation” table setting.


This problem has been fixed: when a filter is applied to an object detail where it shows as #MULTIVALUE, this will correctly select the actual value.

Example: We have an object [Range] with a detail [Detail] which has multiple values:

Table with detail.png

We set a filter on [Detail] to select the values “220” (which is part of the #MULTIVALUE) and “350”.


Before the fix: Error: the “220” [Detail] value does not show in the table, although it has been selected in the filter:

With a filter on Detail - before.png

After the fix:The “220” [Detail] value will correctly show in the table, even when the “Avoid duplicate row aggregation” setting is unchecked:

With a filter on Detail - after.png

Versions where this behavior has changed:

  • XI 3.1 since SP7 patch 3
  • 4.1 since SP4 patch 10, SP5 patch 6, SP6 patch 1 and SP7

Running Calculations

Running calculations will not reset

After 4.1 SP03, the running calculations will not automatically reset for each new section value. As a result, the calculation for the first cell of a block for a particular section value instance is based on the last cell value of the block from the previous section instance.


Before 4.1 SP03, the running calculation was reset for each new section value.

In the example below, the running sum for 2005 (cell in bold) is independent from the running sum for 2004.

Reset1.jpg

After 4.1 SP03, the running calculation for the current section value is based on the calculation from the previous section. In the example below the running sum for 2005(cell in bold) is based on the running sum for 2004.

  Reset2.jpg

Migration:

To keep the original behavior, specify a list of dimensions as a reset parameter (3rd parameter of the function running[Calculation]):

=RunningSum([Sales Revenue];([State])).

From 4.1 SP03, to ensure that you receive results for this formula that correspond to the previous document version, the system automatically rewrites this formula accordingly (using the keyword “section” as 2nd operand of the running calculation). This function is available only for documents created before XI 2 SP 05.9 versions. For more details, refer to the following section “Automatic formula rewrite” .

Data order in running calculations

A running calculation was not respecting the order of the data but the default order of the result set. The running calculation now takes into account the graphically displayed order of the data (table or chart).


Running calculations in cross tables and reset context

By default “Running Sum” is evaluated in a cross-table following a row direction (from left to right row by row).

With XI.x version, when adding a dimension as reset context (3rd parameter), the “running sum” was improperly evaluated on column based direction (from the top to the bottom column after column).

Now, in this case it is processed following a row direction.

Example: =RunningSum([Sales revenue];([State])),

Previously:  column direction (wrong) processing:

New behavior: row direction processing:

Migration: to get the previous result (processing by column)  with a new version (BI 4.1 SP03), the user can use the value COL as 2nd parameter.

From BI 4.1 SP03.3, to ensure that you receive results for this formula that correspond to the previous document versions, the system automatically rewrites the formula using an ad-hoc parameter FORCE_COL with the “RunningSum” function to force the process order to column in ther body of the cross-table.

This functionality is available as of BI 4.1 SP03.3 for documents created using the following versions:

  • All XI 3.X versions,
  • BI 4.0 patch 2.20, 2.21
  • BI 4.0 SP5 and all patches
  • BI 4.0 SP06 and patches 6.1, 6.2, 6.3, 6.4
  • BI 4.0 SP07
  • BI 4.1
  • BI 4.1 SP1 and patch 1.1

For more details, refer to the section on Automatic Formula Rewrite, below.


Running sums with reset in cross table footers

In cross-table footers, the RunningSum() function will sum up the values of its measure

  • per row if it is in the row footer
  • per column if it is in the column footer


Example:

In the following table, we have a running sum of the measure used in the body, in the column and row footers:

/wp-content/uploads/2013/04/snapshot_874873.png

If this running sum has a reset dimension on one of the cross-table axis, then it will reset its value at the end of this axis. On the other axis, the reset dimension will be ignored. For example, in the footer of each row, if the reset dimension is [Year]:

Clipboard02.png

Similarly, with [Quarter], in the footer of each column:

Clipboard03.png

In previous versions, the running sum in the footer of the other axis would give unpredictable results. Typically, with a reset on [Year] in both the row and column footers, the result in the column footers would be meaningless:

Clipboard04.png

Versions where this wrong behavior has been corrected:

  • XI 3.1 since SP6
  • 4.0 since SP4
  • 4.1

Date Functions

LastDayOfWeek() uses Monday as first day of week

To respect the ISO 8601 standard, and to be consistent with the DayNumberOfWeek() function, the LastDayOfWeek() function now considers Monday as the first day of the week instead of Sunday.


Example:

XI R2:  LastDayOfWeek(todate(“05/11/2005”;”MM/dd/yyyy”)) returns 14 May 2005 (Saturday),

XI 3.1: LastDayOfWeek(todate(“05/11/2005”;”MM/dd/yyyy”)) returns 15 May 2005 (Sunday).

Migration:

To keep the original behavior, use the RelativeDate() function:

RelativeDate(LastDayOfWeek(todate(“05/11/2005”;”MM/dd/yyyy”)),-1) returns 14 May 2005 (Saturday).


Wrong time zone for formula with “CurrentDate” and a date field

The time zone of the server was applied to the “CurrentDate” evaluation (instead of UTC) when used with another date field in a formula. It is now evaluated in the UTC time zone.

“Week” function

The function “Week” was returning an incorrect number for when the last day of a leap year is a Monday. (This situation occurs every 28 years).


Before update: Week # of Monday December the 31th of 2012 = 53

After update: Week # of Monday December the 31th of 2012 = 1

“MonthsBetween” function

A set of days over two months was considered a month if the starting day # < ending day # of ending date. This was not working for months ending with day 30 (29/28) compared to a month ending with day 31.


(4.1 SP1 to come) A set of days over two months is now considered a month if the starting day # <=  ending day # and if ending day # is the end of the month and the starting day # > ending day #.


Before fix: MonthsBetween(31/03/2008 , 30/04/2008) =  0

After fix: MonthsBetween(31/03/2008 , 30/04/2008) = 1


Merged Objects

Aggregation functions return correct values for original dimensions inside merged dimensions

Prior to XI R2 SP06, Web Intelligence did not return a correct result in the body of a table when aggregating an original dimension that participates in a merged dimension. (Note that the result is correct when the related dimension is in the table or in a free standing cell).


In the example below, depending on the query , the number of resorts is different. When asked for a count of the resorts from query 1 or 2, Web Intelligence returns the total number of resorts for the merged object instead of the individual object.

/wp-content/uploads/2013/04/5_1_a_202370.png

After SP03, the system returns the correct count for the queried objects.

/wp-content/uploads/2013/04/5_1_b_202371.png 

Aggregation functions can process individual objects inside a merged object

The aggregation function (e.g: Count, Min, Max) applied to an object [A] participating in a merged object, was processed on the value set of the merged object instead of the given object [A]. It is now processed on the original object [A] value set.


Document migration:

To get the previous behavior, you can replace the original object by the merged object.

From BI 4.1 SP03 (patch2 or upper required), to ensure that you receive results for this formula that correspond to the previous version, the system automatically rewrites the formula using an ad-hoc function “useMerged” with the aggregation expression as a parameter to force the use of the merged dimension. This is available on request on BI 4.1 SP03 for reports created with earlier version of XI 3.1 SP03.2. For more details, refer to the following section:Automatic formula rewrite.

Aggregation on a variable based on individual objects inside a merged object

An aggregation on a variable object whose formula is based on an object [A] that is participating in a merged object, was processed based on the merged object instead of the given object [A]. The aggregation is now processed according to the given object [A].


Migration:

To get the previous behavior, replace the original object with the merged object.



Aggregation in free cells of an object participating to a merged object, combined with the Where() operator

In free cells, the aggregation function (e.g.: Count, Min, Max) applied to an object [A] participating to a merged object was processed on the value set of the merged object instead of the given object [A], when the context of this aggregation was modified by the Where() operator.

Workflow example:

  1. We have a first query “Query1” giving a single value for the [Year] dimension and a second query giving two other values for the same dimension.
  2. When in a table, the formula =Count([Query1].[Year]) Where([Query1].[Quarter]=”Q1”) would return 1, which is the correct result.
  3. When in a free cell, the same formula would return 3, which is the result of the merged [Year] dimension (the single value from Query1 + the two values from the second query).

This behavior was found in WebI XI 3.1 SP1 and was corrected in XI 3.1 SP2.

A regression was found in the following versions, when the “Extend merged dimension values” document setting was activated:

  • XI 3.1 SP5 FP5.6
  • XI 3.1 SP6 FP6.3 to FP6.5
  • XI 3.1 SP7

This regression was corrected on the same branches, in later patches.

To get the previous behavior, replace the object with the merged object.

Aggregation of Merged Data from Business Warehouse (BW)

Data fetched from a BW data source have a unique key allowing data with similar values to be treated as different.

In earlier versions of WebI 4.0, this key was wrongly managed when the data was merged, resulting in spurious rows when in a table, such as in the example below.

Example with [Region] as the merged dimension:

/wp-content/uploads/2013/04/img1_671987.png

Since WebI 4.0 SP5 patch 5, this issue has been corrected. The keys are correctly managed and the above table will show the properly aggregated data with no additional rows:

/wp-content/uploads/2013/04/img2_671988.png

Versions where this issue has been fixed:

  • 4.0 SP5 patch 5
  • 4.0 since SP6
  • 4.1 since RTM

Merged dimensions combined with dimension objects


When using in the same table a merged dimension and an object participating to that merged dimension, Web intelligence 4.0 will perform an intersection of the values coming from the merged dimension and the values coming from the participating object.


Example: We have two queries, each of them returning a year dimension, which are merged together:

Image1.png

When using the merged year with the year from the 1st query, the intersection of the two objects results in the values 2004 and 2005, while with the year from the 2nd query, the intersection of the two objects results in the values 2005 and 2006:

Image2.png

In version 4.1, this behavior has been modified and Web Intelligence will perform a union instead of an intersection of the values. This new behavior has been implemented to comply with the general behavior of Web Intelligence regarding the use of merged dimensions, where the merged dimension always take precedence over any object participating to that merge, thus showing all values from the merged object.


This new behavior results in the same list of values whatever the query where the object comes from. For instance, in the above example, this will result in the values 2004, 2005 and 2006 whether the year object comes from the 1st or the 2nd query:

Image3.png

Versions where this behavior has changed:

  • XI 3.1 since SP4 patch 3, SP5 patch 3 and SP6
  • 4.0 since SP5 patch 15, SP6 patch 10, SP7 patch 6, SP8 patch 1 and SP9
  • 4.1 since SP1 patch 5, SP2 patch 1 and SP3

Custom sorts on merged objects


When defining a custom sort on an object, this custom sort is propagated to all instances of that object in the Web Intelligence document. This is not the case with simple ascending or descending sorts, which only apply to the block where they are selected.

When merging custom sorted objects, their custom sorts are disabled. The reason is that objects participating to a merge all share the same list of values, which could therefore result in conflicting custom sorts. The custom sorts are automatically re-enabled when the object is unmerged.

Note that it is still possible to define a custom sort on a merged object. This custom sort will apply to all objects participating to the merge.


Example:


1) Before merge, [Query 1].[City] has a custom sort showing Chicago before Boston:



2) After [Query 1].[City] and [Query 2].[City] have been merged, the custom sort on [Query 1].[City] is disabled and does not show either on the merged object:



In some Web Intelligence versions, the custom sorts are not disabled when objects are merged. Instead, the custom sort of the first selected object is applied to the merged object as well as to all participating objects. Because of the impact on migrated documents, this wrong behavior has been corrected.


Versions where the wrong behavior can be found:

  • 4.1 SP5 up to patch 12, SP6 up to patch 6 and SP7 up to patch 2


Versions where the correct behavior can be found:

  • XI 3.1
  • 4.0
  • 4.1 up to SP4, SP5 since patch 13, SP6 since patch 7, SP7 since patch 3 and since SP8
  • 4.2


Data Ranking

“Ranked by” option using a dimension which is not in the table

Up until 4.0 SP07, a dimension used in the “Ranked by” option of the Ranking functionality is always taken into account, even when this dimension is not part of the table where the ranking is applied.

Example: Ranking the top 2 [Quantity sold] by [Store name]:

Year

State

Store name

Quantity sold

2005

New York

e-Fashion New York Magnolia

9,990

2006

New York

e-Fashion New York Magnolia

11,651

2005

California

e-Fashion Los Angeles

9,792

2006

California

e-Fashion Los Angeles

9,869

Behavior until 4.0 SP07: if [Store name] is not part of the table, this will not modify the ranking:

Year

State

Quantity sold

2005

New York

9,990

2006

New York

11,651

2005

California

9,792

2006

California

9,869

Starting from 4.0 SP07, if [Store name] is not part of the table, then the “Ranked by” option is ignored and we therefore get a different ranking. Note that, in this particular case, the aggregated measures are not sorted ([Quantity sold]):

Year

State

Quantity sold

2006

California

17,769

2006

New York

19,109

This behavior change can be found into the following versions:

  • In BI 4.0:
    • SP07, since Patch 7
    • SP08, since Patch 3
    • SP09, since Patch 1
    • SP10 and all patches
  • In BI 4.1:
    • SP03, up to Patch 6
    • SP04, up to Patch 3
    • SP05

Starting from 4.1 SP03 Patch 7, 4.1 SP04 Patch 4 and 4.1 SP05 Patch 1, we are reverting to the original behavior (prior to version 4.0 SP07), i.e.: whether or not the dimension used in the “Ranked-by” option is part of the table, this will modify the ranking of the table.


Ranking data by a dimension, in sections


In Web Intelligence 4.0 prior to SP11, ranked measures were not properly sorted when the data was within a section and ranked by a dimension.

For example: top 3 [Sales revenue] ranked by [State] in the [Year] section:

Wrong sort in section.png

When a measure is ranked by a dimension, the sort expression is: =[M] in ([D]), where [M] is the measure and [D] is the dimension it is ranked by.

If in addition the data is within a (sub-)section, then the sort expression becomes: =[M] in ([D], section1; section2, …etc.), where section1, section2, etc. are the expressions of the sections containing the data block. This is the sort expression which has been fixed and which now gives a correct behavior:

Good sort in section.png

The behavior modification can be found into the following versions:

  • In BI 4.0, starting from SP11
  • In BI 4.1, since SP03 Patch 9, SP04 Patch 7, SP05 Patch 2 and later


Note that there is no behavior modification when there is no ranked by dimension defined for the ranking.


Hiding a Report Element when a Formula is True

This section is about the “Hide when following formula is true” setting. This setting can be found in the Format dialog box of any Web Intelligence report element: tables, charts, forms, sections and free cells.


Formula Evaluation when the Data is Null

Null is not 0. It is neither greater nor lower than 0. Indeed, Null is not a numeric value and will not return any result in a numerical expression. As a consequence, hiding a report element on a formula condition will not give any result when the evaluated data is Null.


Example:

Hiding a table when the formula “[Data] >= 0” is true, will not hide this table if [Data] is Null.

To hide this table, the formula should be: “[Data] >= 0 Or IsNull([Data])”.

A defect in previous versions of Web Intelligence would evaluate Null as a numeric value greater than 0. This issue has been corrected, which may explain why some report elements are no longer hidden in migrated documents.

Versions where the wrong behavior can be found:

  • 4.1 SP3 from patch 2 to 6
  • 4.1 SP4 from patch 1 to 3

Versions where the correct behavior can be found:

  • 4.0
  • 4.1 up to SP2
  • 4.1 SP3 and SP4, outside the above patches
  • 4.1 since SP5
  • 4.2


Formula Evaluation with a Filter on the Data

When a filter is applied to an object assigned to a report element and that filter removes all values of the object, then this object no longer exists in the calculation context of the report element (i.e. at the most detailed level). As a consequence, hiding the report element on a formula condition based on the filtered object is no longer possible: the evaluation returns no result and the report element is not hidden.


In previous versions of Web Intelligence, the filtered object would sometimes remain in the calculation context and be evaluated as null or zero, depending on the evaluated formula. As a consequence, the report element would get hidden.


Example:

We have the following table:

Year

Quantity sold

2009

53,107

2010

79,855

2011

90,305

1) Format the table as follows:

“Hide when following formula is true:” = [Year] <> “2016”

Result: the table is hidden as expected


2) Now, filter out [Year], in the table:

For instance, define a filter such as: [Year] equals to “2012”

Result: the table is no longer hidden. Since [Year] is completely filtered out, it is removed from the calculation context of the table. As a consequence, the “hide on formula” condition cannot be evaluated.


In this situation, hiding the report element can be achieved in two ways:

  • Use the “Hide when Empty” format setting on the report element.
  • Alternatively, the object can be evaluated at the report level in the “Hide when following formula is true” condition, with the “In Report” context modifier. For example: “Hide when following formula is true:” = [Year] <> “2016” In Report will hide the above table even when [Year] no longer exists in the context of that table.

Versions where the wrong behavior can be found:

  • 4.1 SP3 from patch 2 to 6
  • 4.1 SP4 from patch 1 to 3

Versions where the correct behavior can be found:

  • 4.0
  • 4.1 up to SP2
  • 4.1 SP3 and SP4, outside the above patches
  • 4.1 from SP5
  • 4.2


Other Functions and Calculation Changes

Previous() in a cross-table no longer returns values for the first column.

In prior versions, the Previous() function carried the last value in a row over to the first value of the next row in a cross-table.  This behavior was confusing because there was often no link between the last column of one row and the first column of the next.


In the following example, using XI 3.0, the first column in the second row returns the last column in the first row, even though there is no link between France and US.

/wp-content/uploads/2013/04/3_2_a_202366.png

In XI 3.1, Web Intelligence no longer returns a previous revenue for US in 2004 (since there is none available for that report).

/wp-content/uploads/2013/04/3_2_b_202367.png

This change is also applicable when you use Previous with the COL keyword. In this case the last value in a column is not carried over as the first value of the next column.

Measures will ignore incompatible dimensions

Prior to XI R2 SP03, a measure in a table returned an empty value when the table contained an invalid dimension present in the section header.


In the example below, Year and Country are incompatible:

  /wp-content/uploads/2013/04/4_1_a_202368.png

After XI R2 SP03, Web Intelligence returns the measure value calculated using the compatible dimensions. In the example below, Revenue is calculated by Country:

/wp-content/uploads/2013/04/4_1_b_202369.png


“If” expressions return the same values for formulas and variables referencing formulas

The sum of a formula containing an “If” expression will now return the same result as a variable referring to an identical formula.


As shown in the following table, in XI R2, the sum for the formula if([Year]=”2002”;1;0) returns the sum of the visible values, whereas the sum of the variable referring to the same formula (MyVarIf) returns the sum of the multiple occurrences of the underlying data (which are hidden).

  /wp-content/uploads/2013/04/2_2_a_202361.png

If you deselect the “Avoid duplicate row aggregation” option, you can see the duplicated data.

/wp-content/uploads/2013/04/2_2_b_202362.png

In XI R3 and subsequent releases, the system returns the same result for the variable and the formula.

  /wp-content/uploads/2013/04/2_2_c_202363.png


UNV vs. UNX Count projection function


When creating a universe in Information Design Tool (IDT) or Universe Design Tool (UDT), each measure object can have its own projection function. The projection function is the default aggregation used by the Web Intelligence calculation engine when consuming a measure in a block. The projection function can be a sum (by default), a count, a min, a max, or it can be delegated to the data source. The projection function can also be set to “none”, in which case the Web Intelligence calculation engine will process the measure as a dimension (aggregation by identical values).


The “Count” projection function counts the occurrences of each unique value in the list of values of a measure. But it is processed differently in the Web Intelligence calculation engine, depending on whether the measure comes from a UNV or a UNX universe:

  • If the measure comes from a UNV universe, the count aggregation will not take into account the empty values of that measure
  • If the measure comes from a UNX universe, the count aggregation will take into account its empty values


As a result, if a UNV universe is exported as a UNX universe, a Web Intelligence document built with that universe as a data source might show different results before and after the export operation, if one of its measure objects is using a count projection function.


In a future version of Web Intelligence and IDT, it will be possible to choose between the two count projection functions: count with or without empty values.

Versions where this behavior is observed:

  • Since 4.0 (when UNX universes were released for the first time)



Automatic formula rewrite mechanism

Web Intelligence provides an Automatic Formula Rewrite mechanism that automatically modifies a selection of formulas (see list below) in a document. The formulas that follow a certain pattern are modified when you open a document migrated from a previous version (see above for a list of the applicable versions). After modification the formula returns the same result than before the calculation change.

We then recommend that you save the document so that the modifications are stored in the document, thus completing the formula rewrite mechanism.

The Automatic Formula Rewrite mechanism is available by default for documents migrated to BI 4.1 SP03 for the following formula pattern:

  • “where with dim as parameter in condition”
  • “running calculation reset on section”

BI 4.1 SP03 (patch2 required)

  • “merged object in aggregation function”

BI 4.1SP03 patch3:

  • “running calculation in column”

The releases that apply for this solution are specified above in the sections.

Automatic formula rewrite mechanism rules

The rules to automatically modify the formulas are stored in an XML file called “Formula_migration_rules.xml”, located in the [installation directory]\[SAP BusinessObjects Version]\[OS]_[PLATEFORM]\config folder.

For example, on Microsoft Windows:

  •       Web Intelligence server: (64bits): C:\Program Files (x86)\SAP  BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\config
  •       Web Intelligence Rich Client (32 bits): C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI4.0\win32_x86\config

BEWARE!!!


Modifying this file may have an unexpected impact on all of your Web Intelligence documents. In particular if you enable the “force” attribute, the formulas in yourdocuments may be rewritten and introduce behaviors and results that you did not expect.


You should never use the “force” attribute for all of your documents.  Use it only for specific documents. In order to do this you should enable the “force” attribute, open the document, save it and then disable the “force” attribute immediately afterwards.

Note: If you modify the XML file, then you need to restart the server or the application to apply the changes.

The XML file has the following content:

<Rules>

  <Rule name=”ExtractPlainDimFromWhereCond” enable=”true” force=”false”>

    <!–List of version where the behavior changed–>

    <Version value=”12.3.6.1006″/>  <!– Titan XI3.1 SP3 FP06 –>

    <Version value=”12.4.1.1188″/>  <!– Titan XI3.1 SP4 FP01 –>

    <Version value=”12.5.1.1357″/>  <!– Titan XI3.1 SP5 FP01 –>

    <Version value=”14.0.5.882″/>    <!– 4.0 SP5 RTM –>

  </Rule>

  <Rule name=”ResetOnSectionForCumulative” enable=”true” force=”false”>

    <!–List of version where the behavior changed–>

    <Version value=”11.5.10.0″/>

  </Rule>

  <Rule name=”UseMergeDimInAgg” enable=”true” force=”false”>

    <!–List of version where the behavior changed–>

    <Version value=”12.3.2.0″/>

  </Rule>

  <Rule name=”UseColForCumulativeOnXTabBody” enable=”true” force=”false”>

    <!–List of version where the behavior changed–>

    <Version value=”12.x.x.x”/> <!– All XI3.x versions –>

    <Version minvalue=”14.0.2.798″ maxvalue=”14.0.2.846″/>

    <Version minvalue=”14.0.5.882″ maxvalue=”14.0.5.1249″/>

    <Version minvalue=”14.0.6.1036″ maxvalue=”14.0.6.1145″/>

    <Version minvalue=”14.0.7.1147″ maxvalue=”14.0.7.1147″/>

    <Version minvalue=”14.1.0.896″ maxvalue=”14.1.0.896″/>

    <Version minvalue=”14.1.1.1036″ maxvalue=”14.1.1.1072″/>

  </Rule>

</Rules>

Where:

  • enable=“true” means that the rule is applied, depending on the document version.
  • force=”true” means that the rule is applied, regardless of the document version.
To report this post you need to login first.

60 Comments

You must be Logged on to comment or reply to a post.

  1. Campbell Skene

    Thanks Gregory – great article.  Are there also changes in the WebI calculation engine between 4.0 and 4.1?  If so, I’m wondering if a similar article or other documentation exists describing these changes, or if you could briefly outline what they are.

    Much appreciated,

    Campbell

    (0) 
  2. Mikhail Budilov

    Hope in 4.1 will be better with

    Unfortunately my team still haven’t any workarounds on missed basic functionality – prompts default values.

    Ideas ad details about this:

    Calculated default values – ideas

    https://ideas.sap.com/ct/ct_a_view_idea.bix?c=1DA84A30-1E5A-43FA-95C5-857A8B99D197&idea_id=161A4002-5B4C-4823-943B-B447CF052F3E

    https://ideas.sap.com/ct/ct_a_view_idea.bix?c=E445BDF0-DEF5-4D6A-8A1D-519A4D301682&idea_id=1918147C-6E1A-4DDD-939A-E9182391E508

    Ability to using functions/calculations in @prompt default value parameter on Semantic Layer (IDT)

    @Prompt(‘Enter value(s) for Something:’, ‘A’, ‘Class\Something’, Mono, Free, Persistent, {sysdate – 14} )

    https://ideas.sap.com/ct/ct_a_view_idea.bix?c=E445BDF0-DEF5-4D6A-8A1D-519A4D301682&idea_id=8D91ED5D-99CA-44C8-B49A-B107E723361D

    (0) 
  3. Sarah Kinmond

    Great article – many thanks Gregory/Pierre.

    One quick question: if making any modification to the XML file, will this be retained (a ‘one-time’ fix)?

    I have a customer already questioning if applying future SP’s or Patches will revert/overwrite any changes they might have already made to the XML file?

    (0) 
    1. Gregory BOTTICCHIO Post author

      Hi Sarah,
      I checked internally and the assumption is that the XML file will be overwritten once the SPx will be installed.

      So, Best Practice is to back it up.

      GB

      (0) 
  4. consultant sap

    Hi,

    Before migration I had as formula ( in a cell of the webi ): Nombre([agents non formes].[Matricule Agent])

    Now I have this :

    UseMerged(Nombre([agents non formes].[Matricule Agent]))

     

    The problem is that the values of my cells are differents Before and After migration.

    But the automatic formula rewrite solution is already desactivated by default .

    So what’s wrong ??? why the rule appears !!

    Any help plz

    (0) 
    1. Pierre SAUREL

      Hi Marina, it looks to be a bug so I suggest that you contact the support:

      indicating: which version are you running?

      which version was the report from? (did you save it after migration on the new system?)

      provide the report with the formula.

      are you running a server or rich client ? did you restart after desactivating formula rewrite?

      provide the xml file

      regards

      (0) 
      1. consultant sap

        Hi Pierre ,

        Thank u for ur answer , here is the content of the file , make sure the option is desactivated please :

        <Rules enable=”true” force=”false”>
                 <Rule name=”ExtractPlainDimFromWhereCond” enable=”true” force=”false”/>
                 <Rule name=”ResetOnSectionForCumulative” enable=”true” force=”false”/>
                 <Rule name=”UseMergeDimInAgg” enable=”false” force=”false”/>
        </Rule>
        </Rules>

        We migrated from BOXI 3.1 SP4 TO BI 4.1 SP3

        I have hunddreds of webi files , so I don’t know why I’m seeing the usemerge in some cells into 2/30 reports checked yesterday. Besides some values are not the same !

        I remember saving one report which duplicated the usemerge in the cell like this :

        =(UseMerged(UseMerged(Nombre([agents non formes].[Matricule Agent]))))/((UseMerged(UseMerged(Nombre([agents formes].[Matricule Agent]))))+(UseMerged(UseMerged(Nombre([agents non formes].[Matricule Agent])))))

        I tried to save the other report to see if the usemerge will be duplicated but not !!

        Do u have any idea ???

        I’m not running a server on rich client but it’s used to build some reports with client posts.

        (0) 
        1. Pierre SAUREL

          this XML is fine, do you have the opportunity to run patch 2 (recheck the XML after upgrade) ? the issue should not occur with this version.

          In case there is a rewrite on top of a rewrite or receive the incorrect result, I suggest that you contact our support team.

          regards

          (0) 
          1. consultant sap

            Hi Pierre ,

            Thank u for ur answer , I tried to change the value “true” in the first line to “false” and I it removes the function UseMerge in my formula ! but the problem is that my report takes a lot of time to be opened !

            <Rules enable=”false” force=”false”>
                     <Rule name=”ExtractPlainDimFromWhereCond” enable=”true” force=”false”/>
                     <Rule name=”ResetOnSectionForCumulative” enable=”true” force=”false”/>
                     <Rule name=”UseMergeDimInAgg” enable=”false” force=”false”/>
            </Rule>
            </Rules>

            I’m confused …

            (0) 
              1. consultant sap

                Hi Pierre,

                Can you tell me what will happen for the two other rules “ExtractPlainDimFromWhereCond” and “ResetOnSectionForCumulative” if I make the enable true or false in the first line of the file  <Rules enable=”false” force=”false”> ??

                I didn’t understand the function of these rules and what can they change ?

                Thank you

                (0) 
                1. Pierre SAUREL

                  Hi Marina

                  You have to install patch 2 so to have the rule UseMergeDimInAgg working.

                  For any rule, when “enable=true” it, the system cheks the version of the report and depending of this version rewrites the formula that match a certain pattern and for which the result has changed (and so returns the old result).

                  when “force=true”, the system does not consider the version of the report, and rewrites the formula that match the pattern.

                  Once rewrited there is no rewrite, but to avoid a check we recommand to save the report so that the system will not try to evaluate a rewrite.

                  Each rule corresponds to a change explained in the doc here:

                  For ExtractPlainDimFromWhereCond see section ““Where” operator on measure with a condition on a formula based on a dimension”. the system rewrites certain formual using “where” operator adding a list of dimension as second parameter of the “where” operator.

                  For ResetOnSectionForCumulative see section “Running calculations will not reset” , the system adds the keyword “section” as parameter of the function running calculation so to force the calculation to reset on section.

                  Regards

                  (0) 
                  1. consultant sap

                    Thank you so much Pierre for the explanation , by the way , I don’t want to have the useMerge working , because it gives wrong values. So if I put false in the first line of rules here : <Rules enable=”false” force=”false”> .  Will that desactivate all the rules ( “ExtractPlainDimFromWhereCond” and “ResetOnSectionForCumulative” )

                    (0) 
                    1. Pierre SAUREL

                      you need first to install patch 2, then you can deactivate the rule for “usemerge”.

                      The rules works independantly, so the other will still continue to work.

                      (I would like to get the report that is not working with usemerge is this possible? )

                      (0) 
                        1. Pierre SAUREL

                          I rechecked (see doc above, section merge) the version for which the rewrite should apply and I figure out that it should not apply for report from XI 3.1 SP04 : the behavior for the object participating to a merge is correct and the same like it is with version 4.1 (that’s why you can see different result with formula rewrite).

                          (0) 
                          1. consultant sap

                            Ok , I didn’t succeed to send u the reports , so is there a way to avoid this rewrite ? I have a hundred of reports that contains merged objects !!! I can’t do it manually !! and I can’t install Patch 2 for so many reasons !

                            (0) 
                            1. Pierre SAUREL

                              Hi Marina, there is an issue with the rule “UseMergeDimInAgg” in SP03, that has been fixed in patch 2.

                              If you cannnot install the patch (recommanded), you can:

                              1. Disable the formula re-write completely by renaming the
                                Formula_migration_rules.xml file to a different name.
                              2. Comment out the Rule tag in the XML file for “UseMergeDimInAgg”

                              (0) 
  5. Pascal GAULIN

    All,

    We have added a paragraph on ranking data within sections, where there is a behavior change (bug correction) on the sorting of the data.

    Regards,

    Pascal.

    (0) 
  6. Raj k

    Good document.

    We are on BO 3.1Sp7 and migrating it to BI 4.1 Sp5.

    Do we need to concern about these changes ?

    Thanks!

    (0) 
    1. Pascal GAULIN

      Hello,

      Actually, yes. Some of these behavior changes may impact documents migrated from XI 3.1 SP7. However, these changes occur in very specific calculation contexts and, most likely, your documents will not be impacted.

      To be sure, you should go through the list of changes to check in which versions each of them has occured.

      Regards,

          Pascal.

      (0) 
  7. Navjot Sidhu

    Hi everyone,

    I have migrated few reports from BO 3.1 SP2  to BI 4.1 SP5 and the reports are appended with UseMerge now.

    I have encountered some issue as mentioned below:

    1. If I disable automatic formula rewrite from formula_migration_rules.xml and open the report, I see the correct results.

    2. If automatic formula rewrite is enabled, I see incorrect results. To fix it I have to change the location of a bracket in the formula.

    (which changes the calculation context).

    If UseMerge is enabled, there should not be a need to change the brackets manually  to get the correct output.

    Any inputs on it?

    Regards

    Navjot Kaur

    (0) 
    1. Pascal GAULIN

      Hello Navjot Kaur,

      Could you please give us the exact formula used in your document, as well as its location (free-standing cell, table body, table footer, break or section)?

      Thank you,

      Pascal.

      (0) 
      1. Navjot Sidhu

        Hi Pascal,

        Default formula after conversion of report in BI 4.1 is:

        =UseMerged(Count([Totoro Lifecycle].[New Version Id]))  Where
        ([TotoroLifecycle].[Change Group Modified]=”Amend-Economic”;[Totoro Lifecycle].[Change Group Modified]).

        Result given is incorrect. (bracket is after (New Version Id])) )

        Correct formula should have been
        =UseMerged(Count([Totoro Lifecycle].[New Version Id]) Where ([Totoro Lifecycle].[Change Group Modified]=”Amend-Economic”;[Totoro Lifecycle].[Change Group Modified]) )

        (bracket should have been at the end of the formula)

        Regards

        Navjot

        (0) 
        1. Pascal GAULIN

          Hi Navjot,

          Sorry, I should have been clearer:

          – What is the original formula?

          – Where is this formula located?

          Thank you,

              Pascal.

          (0) 
          1. Navjot Sidhu

            Pascal,

            This is within the report query.

            The original formula is the one I have mentioned in point 1 above. (that is after the migration.)

            The brackets are placed incorrectly as a result of which the report output differs.

            (0) 
  8. Pascal GAULIN

    All,

    We have updated the document with a behavior modification regarding the combination of merged dimensions and dimension objects in a same report block.

    Regards,

        Pascal.

    (0) 
  9. Pascal GAULIN

    Hello everyone,

    We have again updated the document with a behavior modification when using filters on object details with multiple values.

    Regards,

        Pascal.

    (0) 
    1. Chaiyod Sae-khow

      Hi Pascal,


      I have some question about “Ranked by” option using a dimension which is not in the table.

      Step to reproduce (on SAP BI 4.1 SP5)

      1. Create a sample WebI report in SAP BI 4.1 SP5. Add object [Year], [State], [Store name] and [Quantity sold] in query panel.

      2. Drag object [Year], [State], [Store name] and [Quantity sold] to show as table.

      3. Apply Ranking the top 2 [Quantity sold] by [Store name].

      4. Check result.

      5. Delete column [Store name].

      6. Nothing changed.

      Please advise me about it.

      Chaiyod.

      (0) 
      1. Pascal GAULIN

        Hello Chaiyod,

        Which 4.1 SP5 version?

        If this is the RTM version, then you will get the “intermediary” behavior, where the ranked by dimension is ignored, if it is not part of the block.

        If this is SP5 patch1 and newer, then you will get the “original” behavior, where the ranked by dimension is taken into account, even when it is not part of the block. Hence, nothing will change if you remove [Store name] in your example.

        Regards,

           Pascal.

        (0) 
        1. Chaiyod Sae-khow

          Hi Pascal,

          How to check my version is SAP BI 4.1 SP5 RTM version?

          By the way, I try to reproduce with SAP BI 4.1 SP4 and found same behavior.

          Could you tell me about SAP BI 4.1 version that you found behavior changed.

          Best Regards,

          Chaiyod.

          (0) 
          1. Pascal GAULIN

            Hello Chaiyod,

            “RTM” means that it is the first release on that version, i.e. prior to any patch. You can find this information in the “About’ dialog box of Web Intelligence.

            Check the article for the list of versions where the behavior has changed. 4.1 SP4 was impacted.

            Regards,

                Pascal.

            (0) 
  10. Keith Fisher

    Hi Pascal,

    I have recently migrated a 3.1 system to 4.1 and am seeing some odd results in relation to the usemerged() function.

    The 3.1 source system is at 3.1 SP3 FP 4. The FP was installed in March 2011 according to the installation history.

    A batch of 4 reports with created dates of November 2013 have been imported – when we open 3 of them in 4.1 SP6 the usemerged() function has been added to the count variables, but not on the fourth.

    From my reading of your blog this seems incorrect behaviour as it states that from FP3.3 onwards the calc engine looks at individual query references as not being part of the merged dimension, and so these variables ought not to be updated. Also, it is odd that 1 of the 4, which was built at the same time does not have the changes applied.

    I have tried adjusting the formula_migration_rules.xml and re-opening the reports, but this doesn’t seem to affect any of them – even after a webi processing server restart.

    Have you got any suggestions for troubleshooting this further, and can you tell me where I might be going wrong with the formula_migration_rules.xml?

    thanks very much

    Keith

    (0) 
    1. Pascal GAULIN

      Hello Keith,

      The automatic formula migration mechanism is based on the information saved in the WebI document, i.e. the version used to create the document and the version with which it has been saved. Both these dates can be found in a file called “version”, at the root of the WID file (which you can open like a ZIP archive).

      When you open a document, the saved version of the document is checked against the formula migration rules. If any or the rules applies, then the pattern of that rule is searched in the document. If the pattern is found, then the formula is changed according to that rule.

      If the document is saved, then the formula modification is persisted. Obviously, the saved version of a document is also persisted each time a document is saved.

      In recent versions, the “useMerged()” rule only applies to documents migrated from  XI 3.1 SP3 patch 2 (= version 12.3.2.0) so I wonder why it has been applied in your case. Could you please check the versions of the XI 3.1 document?

      Thanks,

          Pascal.

      (0) 
      1. Keith Fisher

        Hi Pascal,

        thanks very much for the swift reply.

        I’ve opened up one of the reports that had the usemerged() function added, and found this in the version file:

        6     –  –   12.1.0.0-   Web Intelligence-    12.3.0.601

        I opened the report in the same folder that I believe to have been created at roughly the same time but which didn’t have the function added and found this:

        6     –  –   12.1.0.0-   Web Intelligence-    12.3.0.601

        Odd that neither file mentions the FP3.4 which is mentioned in the software inventory tool in March 2011 (3.1 LAFix 3.4.12 Update 12.3.4.12000). Also odd that both files have the same data but one was affected and the other not. These two reports are very similar, basically the same report with different query filters, so the variables are exactly the same before the automatic rewrite.

        The created date for both reports as recorded in the document properties is November 5th 2013, after the FP3.4 application.

        So can see what the auto rewrite was applied, as the version file for the report says 12.3.0.601, but then why wasn’t it applied to all 4 reports at the same time, not just three of them?

        Appreciate your help on this – and I have now managed to disable the auto-rewrite function by setting the rule to false and restarting the SIA, but still interested in the version numbering in the version file.

        thanks

        Keith

        (0) 
        1. Pascal GAULIN

          Hi Keith,

          The first version number (12.1.0.0) is the creation version: XI 3.1, while the other (12.3.0.601) is the last modified version: XI 3.1 SP3. I don’t know why it doesn’t say XI 3.1 FP3.4, even without the LAFix…

          Now, the UseMergeDimInAgg rule is applied on aggregation functions only. For example, if you have Count([Lines]) and [Lines] is participating to a merged object, then this formula will be transformed into UseMerged(Count([Lines])).

          So, maybe the formulas are not exactly the same in the 4 documents, hence the reason why the rule did not apply on all of them?

          Regards,

          Pascal.

          (0) 
  11. Pascal GAULIN

    Hello everyone,

    We have added a paragraph on a behavior change concerning running sums with a reset dimension, in cross-table footers.

    Regards,

        Pascal.

    (0) 
  12. Pascal GAULIN

    Hello everyone,

    We have added yet another paragraph on a behavior change concerning the Count projection function in UNV vs. UNX universes, when the measure is consumed in Web Intelligence.

     

    Regards,

        Pascal.

    (0) 
  13. Pascal GAULIN

    Hello everyone,

    We have added a new section on the “Hide when the following formula is true” report element setting. These behavior changes are due to a couple of defect corrections in 4.1 SP3 and SP4.

    Regards,

        Pascal.

    (0) 

Leave a Reply