Unicode characters in Business Objects

I came across an issue today when trying to run a Web Intelligence report that used prompts and the prompt value contained a unicode character.

When the propmt value that contained the unicode character was selected the report did not return any results even though I know that there is data in the underlying database for that value.

The problem was that my universe was not configured to recognise unicode characters.

The SQL that was generated to be run on my SQL Server database did not contain the syntax to tell SQL Server look for unicode characters. ANSI92 SQL syntax denotes that the constant that you are searching for in your string must be preceeded by a capital N.

For example:
SELECT *
FROM    Table1
WHERE  Col1 = ('á')

will not return any data if Table1.Col1 does contain value 'á'

However:

SELECT *
FROM     Table1
WHERE Col1 = (N'á')will return data if Table1.Col1 does contain value 'á'

To ensure that Business Objects reports that use a Universe will correctly return data that contains unicode values in the prompts you must change the universe parameter called UNICODE_STRINGS to value 'Yes' from 'No'. Remember to click on 'Replace' when changing universe parameter values for them to take affect.