Bug: Inconsistent results in WebI report

Today I had an issue with a WebI report. It was a small, simple report that acted strangely: Whenever the report was refreshed the result showing in the report was different each time. Some dimensions and measures kept appearing/disappearing in WebI for each refresh.

Background:
WebI report, unx universe. Business Objects 4.2 SP 3
* No limitation on universe in rows returned or time run
* Small, limited sql on only one table
* Sql server query works ok – returns same results every time
* Query window in webi works ok – returns same results every time
* Refresh in webi reports does not work – returns different measures and dimensions appearing/reappearing with refresh

The problem was not the universe or the sql, it was the report filter.
We filtered out a list of values, example “Customer” with a similar filter:
“Customer IN LIST CustomerA;CustomerB;CustomerB ;CustomerC;Customer C ”
This filter was manually selected from a list of values.

Note the spaces at the end of some of the values? This seems to mess up how WebI handles the returned results.
Ok, so what if we change the filter?
“Customer IN LIST CustomerA;CustomerB;CustomerC”
With the new list of values, both “CustomerB” and “CustomerB ” STILL shows up – inconsistently! Sometimes, “CustomerB ” will be missing on refresh and sometimes it will be there.

Solution:
The solution was to trim the dimension from whitespace in universe: rtrim(Customer)

This removed the inconsistencies completely.

I’ve worked with BO for a few years and this is the first time I’ve seen this kind of behaviour from a dimension so unfortunately I’m not sure what triggers it. If anyone can shed some light on why it happens I’d be grateful.

Advertisements