We have a request whereby the User wants the change the table source of a Universe based on a user response. Table structure will remain the same, however, new tables could get generated either 3 * per day or 1 * per week depending on week. User would answer a prompt in Webi whereby they would answer for “table source” and they would know the correct information.
For example:
On 20090824 there might be two tables loaded: Table_200908240800 and Table_200908241300.
On 20090901 there might be one table loaded: Table_200909010900.
When running a Webi Report, there would be a prompt asking “Input Data Source name” and the end user is technical enough to know the exact name to input.
Is Universe Designer flexible enough to allow user to dynamically upon user input allow for changing source names as long as table structures and Universe Objects aren’t changing?
Thanks Marek for the quick response, however, reviewing the topic assumes that table names are known, but in our scenario over time we will have many tables to choose from that don’t exist currently. New Table Names will be different based on data and timestamp naming convention, therefore the Union method won’t work for us as we’d have to continuously update the Union and performance from selecting against Unions are not as good as a physical table.
We need a solution that will scalably allow user to repoint from Webi to new source tables that don’t currently exist and we wouldn’t know the exact name of now or even in the future.
The Dynamic Universe PDF file is definitely what I am looking for. The example is Oracle and I was implementing based on Teradata version 12.0. I have implemented all of the steps (Insert table, hidden LOV, custom SQL for LOV, and @Prompt in table name). When executing in Webintelligence I get the below error message:
“Database error: [NCR][ODBC Teradata Driver][Teradata Database] Object ‘‘Tablename’’ does not exist. . Contact your Business Objects administrator or database supplier for more information. (Error: WIS 10901)”
When I do a “select * from tablename” from our SQL editor results return fine. The LOV refresh appears to be working perfectly, just the final step seems to fail.
The Webi SQL statement is below but won’t provide the resulting SQL statement due to error:
SELECT
Table__1.Col_Nm
FROM
“@Prompt(‘Which Table?’,‘A’,‘Hidden\Table’,mono,free)” Table__1
I am adverse to turning on tracing because in the past it prevented everyone from running their reports, at least those having Teradata connection.
Any ideas? Will this concept only work for Oracle and not Teradata? Is the BOBJ Teradata middleware preventing our use of this solution?
If you check the linked PDF one more time you will see that the @prompt() function is defined with the 2nd parameter as ‘N’ while you have ‘A’. That’s probably that one small detail that needs to be fixed.
It does not work for me with ‘A’ either - because the @prompt() function is then replaced with a string so the final query looks something like:
select *
from 'table_a'
And this is not a valid query.
However, it works with ‘N’ parameter for me. When I change the parameter from ‘A’ to ‘N’ then the table name (the value of the prompt) is passed without the quotes so the final query is valid and has a form
select *
from table_a
I am not sure if it does not work for you because you use Teradata and I use Oracle or because of any other problem.
Do you have an Oracle database where you could try it with ‘N’ parameter?
I have included FY in the prompt values and ran the webi report and report ran fine.
The reason for using FY is because the table names starts with FY, which means Year.
Ex: FY08, FY09
Users are used to entering FY prompt values as 08 or 09.
I’ve tried a few things, the issue I’m facing is that the table name seems to be encased in two ` which is the character to the left of the number 1 on your keyboard.
The SQL itself contains the character ` around the table name both in the select clause and in the from.
I have included the owner when I rename the table, ie dbo
My table name is @PROMPT(‘Data?’,‘N’,‘LOVS\Provider’,mono,constrained)
The objects referencing the table contain the ` that surround the table name (prompt) as well.
What should the SQL syntax look like? Should the SQL syntax contain the table name as shown above with the .column_name? Or should the SQL syntax contain an alias , ie Table1.column and the Alias is referencing the @Prompt table name in the From clause. Either way the is showing up no matter what I do. I tried to edit the SQL and remove the and I still get the error shown at the top even though there are no ` in the SQL syntax that I’ve edited.
(BOB member since 2003-11-27)
(BOB member since 2003-11-27)
(BOB member since 2005-06-19)
(BOB member since 2002-10-04)
(BOB member since 2003-05-28)
(BOB member since 2004-01-29)
.
(BOB member since 2005-01-06)
(BOB member since 2004-01-29)