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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s