David Lai's Business Intelligence Blog

While creating a universe off an Informix database, I discovered a bug that SAP never took care of or provided any documentation on fixing, which was quite of surprising.

Basically using a date object in the filter will generate an incorrect WHERE clause.

In the following example, we create a simple universe off an Informix database via an ODBC connection

Using Information Design Tool, we will create a simple universe consisting of 1 table containing a date field. Now to show the WHERE CLAUSE date bug, we will open up the query panel and drag a date field in both the result objects and filter window. Enter a constant such as Nov 17, 2017, and then view the query script. You will see in the WHERE clause instead of having ‘2017-11-17′, it would have ’11/DD/YYYY’ instead.

Publish the universe and try it out in Web Intelligence. Using the same example as above, we will drag the date field into the result object and filter pane. Open the query script viewer and you will see the same error in the WHERE clause.

Solution

To fix the issue with the WHERE clause, we must modify the Informix.prm file belonging to the connection.

Please ensure that you modify the Informix.prm in ALL of the servers containing a WEBI processing server. The Informix.prmfile is found in the directory

<BOBJ INSTALL DIR>\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc

In the Configuration tag, add the following parameter entry as shown in the screenshot below

Test to ensure that the query WHERE clause is now generated correctly in Information Design Tool and Web Intelligence.

You can find a copy of the corrected Informix.prm file HERE