BusinessObjects-Webi Runs Fine in Modify Mode But Retrieves No Data in View Mode

Another strange situation from Business Objects.

Just create a report with a date prompt. Check the options "with list of values" and choose "Equal" as operator. In Modify mode (Java Report Panel) your report works fine. But if you try to refresh your report in View mode you will see the "No Data To Retrieve" message.

If you analyze the SQL Scripts of both mode's, you will see that in View mode BI adds some hours (eg.4 or 8) to your current selected date prompt. Therefore no data matches to your query.

This is addressed to ADAPT01710199
See note: http://service.sap.com/sap/support/notes/1930605

For a workaround change the time setting under Tomcat as follow:



Environment: SAP Business Objects BI 4.1 SP2 Patch2

BusinessObjects-The specified DSN contains an architecture mismatch between the Driver and Application

If you have already google this error message and still not satisfied with the 64 bit driver solution you are on the right place.

With SAP BI 4.0 the new BO infrastructure is based on 64 bit technology BUT only on the server side. The client tools (universe designer, webi rich client etc.) are still 32 bit applications.

Many forums offers to create both 32 and 64 bit odbc for your file with the same name. One for client tools one for server.

OK, that's fine but did you realize that e-fashion has only one odbc and it is 32 bit, and it is still running on the server???

If e-fashion can do that why can't you? Here is the answer. I assume that you are using an Excel file as source. You created an odbc (32 bit odbc) for your excel file (on the server). Now go to the universe designer choose new connection. On the drop down box select "MS Excel 2007 ODBC Driver"

click next next finish. Enjoy it. Yes, that's all. Nothing to do else.

Tweet

BusinessObjects-Merged Dimension Incompatible Object

Merging dimension is always a need by creating webi documents. But by default you can use only dimensions from one query, merged dimensions and the measures. If you want to use dimensions from other query and you can't merge them, you got an error message "Cannot drop here - the object is incompatible". See pic below.

To avoid this problem you have to create a detail variable object and associated it to the merged dimension. In the example above you want to use the phone numbers from Query2. Select the type as "detail" and associated object.

Now you can use this variable with your Query1 dimensions.

To understand why this works with detail please refer to this document:
http://michaelwelter.wordpress.com/2011/04/18/tips-for-merging-dimensions/

Tweet

BuisnessObjects-Order Prompts in Web Intelligence

If you use prompts which you have defined in your universe, you can't order them through Query panel in your web intelligence document.

To order your prompts you have to use the {user:n} parameter in your filter definition. n is the order number of the prompt and starts from 0.

Here is an example:

On the query panel you see that the order of the prompts is ProductNumber, ProductName, ProductId.

After running the query you will see that the prompt order is like ProductId, ProductName, ProductNumber.

Take a look to the SQL script and you can see the definition of the prompts. They are like;

   ( SalesLT.Product.ProductNumber  IN  @Prompt('Enter values for Productnumber:','A','Productheader\Productnumber',Multi,Free,Persistent,,User:2)  )
   AND
   ( SalesLT.Product.Name  IN  @Prompt('Enter values for Product Name:','A','Productheader\Product Name',Multi,Free,Persistent,,User:1)  )
   AND
   ( SalesLT.Product.ProductID  IN  @Prompt('Enter values for Productid:','N','Productheader\Productid',Multi,Free,Persistent,,User:0)  )

So, all you have to do is change the numbers of the end of your prompts.

Tip: If you parse your filter in universe you will get an error message like this:

 Just ignore it.

Tweet

BusinessObjects-How to find which report is running from database session

Sometimes you see very large SQL expressions which are killing your database systems. With audit reports you can find which reports have run after they finished. But with this little code you can directly find the user, the report and the universe from your database directly on demand.

http://www.dallasmarks.org/blog/2011/10/tips-and-tricks-identifying-business-objects-queries-using-end_sql/

Just put this lines to your universe parameters "END_SQL" line:
/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

Thats it. Your query will look like this:

Your dba will appreciate you.

BusinessObjects-Webi Ignores Prompt

Today i faced a new property(!) of BI4. I create a query. Put my dimensions and my prompts. After running the query i saw that my query returns result which schould filter after my prompt.

I checked the SQL script and there it is. The query stays without the prompt. See the picture below.

As you seen on the screenshot, i have two prompts but the script shows me only one of them. After a little search i found that the properties are different of the dimensions.

Aftermath: Webi prompt doesn't support "Hierarchical Display" dimensions.

Tweet