@Prompt function at Table Owner level

building semantic layer
1

Hi All,
While attempting to use @Prompt function at Table Owner level to access different Schemas (owner’s) in the same database – I was stumped. The scenario goes like this:
My database is SQL server; I started experimenting with default database PUBS to make this logic works. In the current situation, single database (PUBS) with two schemas (dbo and guest). I created a universe with three tables.
Table 1 Jobs from dbo
Table 2 Jobs from guest
Table 3 employee from dbo
Joins:
dbo.employee.job_id = dbo.jobs.job_id
dbo.employee.job_id=guest.jobs.job_id

I used @Prompt function in the Owner box of job table1 from dbo schema:

Syntax:
@Prompt(‘Select Schema’,‘n’,{‘dbo’,‘guest’},mono,constrained).
Job Id objects syntax changed to :
“pubs.@Prompt(‘SelectSchema’,‘n’{‘dbo’,‘guest’},mono,constrained).jobs”.job_id

parse failed: Invalid object name ‘pubs.100.jobs error. Later I removed LOV parameter empty, still same error. To avoid the double quote around entire table owner name I changed BACK_QUOTE_SUPPORTED=N in the prm file. Now the syntax changed to: pubs.@Prompt(‘Select Schema’,‘n’,mono,constrained).jobs.job_id . Still parse failed: Invalid definition (UNV0023).
From there I can’t go further. Please note I followed the following threads.
A B C
Somewhere some thing I was doing wrong, can any one point me the direction.

murali :new_zealand: (BOB member since 2002-08-16)

2

Your parse failed on integrity-checking, right? How about trying the universe out – does it work when actual SQL gets generated?

I would expect that a parse would fail – SQL Server knows nothing about this @prompt stuff – that’s all inside BusinessObjects. At run time, though – when one answers the prompt and the “real” SQL gets generated – that’s when the database will evaluate the “resolved” owner/table names, and if you’ve done it correctly, all should then be fine.

Anita Craig :us: (BOB member since 2002-06-17)

3

Hi Anati,
Thanks for your reply,

 I would expect that a parse would fail -- SQL Server knows nothing about this @prompt stuff -- that's all inside BusinessObjects.

OK I agree with you. If so how to tell SQL server about @prompt.

 At run time, though -- when one answers the prompt and the "real" SQL gets generated -- that's when the database will evaluate the "resolved" owner/table names, and if you've done it correctly, all should then be fine.

We can only fill the @Prompt when BO allows doing so. In my case, BO generating the this SQL:
SELECT
pubs.@Prompt(‘Select Schema’,‘n’,mono,constrained).jobs.job_id,
dbo.employee.fname
FROM
pubs.@Prompt(‘Select Schema’,‘n’,mono,constrained).jobs,
dbo.employee
WHERE
( dbo.employee.job_id=pubs.@Prompt(‘Select Schema’,‘n’,mono,constrained).jobs.job_id )
when I try to RUN the query from Query panel just going back to Query panel again.
What I have to do now? could you please explain me ?

murali :new_zealand: (BOB member since 2002-08-16)

4

You don’t “tell” SQL server. When you run the query, BusinessObjects “prompts” you and substitutes the answer into the SQL.

You hit the Run button – and get prompted? What do you answer to the prompt?

Anita Craig :us: (BOB member since 2002-06-17)

5

Hi Anita,

 You hit the Run button -- and get prompted? What do you answer to the prompt?

Probably you missed one point

 when I try to RUN the query from Query panel just going back to Query panel again. 
What I have to do now? Could you please explain me?

what I already mentioned in my previously reply. Any suggestions

murali :new_zealand: (BOB member since 2002-08-16)

6

Hi Anita,
What I’m trying to say is: when I try to run a query by hitting RUN button from Query panel, but the query panel reappears without executing the SQL statement. Even though I’m using synchronous Connection mode. Generally this can happen if you are using asynchronous connection. :confused:

murali :new_zealand: (BOB member since 2002-08-16)

7

murali – I haven’t made a post because I have no idea what’s happening – I’ve never seen the query panel just come back like that. So – I didn’t want to make a useless post. Perhaps someone else can have an insight and chime in.

Anita Craig :us: (BOB member since 2002-06-17)

8

Try

@Prompt('Select Schema','A',{'dbo','guest'},mono,constrained).

instead of

@Prompt('Select Schema','n',{'dbo','guest'},mono,constrained).

mkumar (BOB member since 2002-08-26)

9

that has done the trick. :yesnod: Thanks mkumar

murali :new_zealand: (BOB member since 2002-08-16)