BusinessObjects Board

using @prompt in derived table

building semantic layer
#1

Hi All,

I have searched about this but still could not get the problem fix.

I am using BO XI 3.1 and the DB is MS SQL Server 2005.

I have created a derived table “test” using the following code:


select ADD_DATE from
IDS_WORK_CONTROL_FACT
where ADD_DATE >=CAST(@Prompt('END_DATE','D',,mono,free) AS DATETIME)

It gave me an error when i was trying to save the table in the unvierse:

Exception: DBD, [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.State: 42000

anyway, i saved it and created an objects using test.ADD_DATE. When testing it using an webi report, the prompt displayed but the report query is not executed due to the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared… (WIS 10901)

Can anyone help me?

dongtao :cn: (BOB member since 2007-10-09)

query
#2

I might be wrong here as I don’t use prompts much at universe level, but isn’t @prompt Bob syntax? If you’re writing SQL in a derived table, do you maybe need to use the syntax appropriate for your database?

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#3

Prompts work fine in derived tables.
Just tried in SQL Server (with XIR2)
Prompt syntax was ok to on my own table.

-Can you make sure you have access to the table?
-Can you insert the actual table in the universe, and try with a self referencing join using the prompt?
-Do you get rows back?
-Can you post the whole sql generated from webi?

hope this helps!

ottoman

Ottoman :uk: (BOB member since 2002-10-04)

#4

Thanks Ottoman.

I do have access to the database, and i am able to insert actual tables into the universe.

I can also create dimensions using prompt and referencing to actual tables. its working fine.

I am not able to get the webi query scripts because of the erorr message that SQL statements is not prepared.

I am not very sure whether i need to change any universe or server configuration. Anyone get other idea?

dongtao :cn: (BOB member since 2007-10-09)

#5

this is really a painful experience until I found the last missing piece.
Just share with you and dont make the same mistake.

my ODBC has a default database which is database 1, and in my derived table i am using table B in database 2 on the same server. Without the database name in front of the table, SQL server will always give error because there is no table B in database 1.

I just give the full name (database, schema, and table name) in the derived table and its working now.

Thanks guys for your help.

dongtao :cn: (BOB member since 2007-10-09)