Passing column values as LOV of @prompt in the derived table

[Former Member]

Hi,

I need to use derived table where i have a sub query.

I need to filter values in the subquery based on the user input and based on that value i need to process the main query.

But i need to show the LOV in the prompt of the subquery.

How can i do this. Is there any way to pass column values as lov?

Pls help me out

thanks

[Former Member · Accepted solution]

Hi,

In the sub query, in the required place create a prompt either by using @prompt() as below

reqtab.col = or in @prompt('Enter Req table col value','A/N/D','Classname/Obj name','Mono/multi','Free/Constarined','Persistent/Not persistent')

Cheers,

Suresh Aluri.

[MariannevL]

To add on to Suresh,

The sql from class name/object name will be replaced with whatever is in the select box of that object.

If you already have an object you can use to produce the LOV, do so.

If not, you can create what we refer to as a 'technical' object, that is only there for the purpose of holding the sql you need in the select box.

Now you do not want the user to see or use this object, it might not be 'compatible' or usable with other objects in the universe.

(It could be on a standalone table or derived table, solely there to generate the right LOV).

As a best practise create a separate class to hold objects like that and hide the entire class before you publish the universe.

Good luck,

Marianne

[Former Member]

Hi! I'm trying to implement this and a little stuck. Hoping you can point out my error. I am using BOXI 3.1.

Here is my derived table, named Wubert.

SELECT

BANK_ACCT_CD,

BANK_NM,

ACCT_DSCR AS BANK_ACCT_DSCR

FROM DIM_BANK

WHERE

BANK_ACCT_CD IN

@prompt('Enter value(s) for Bank Account or * for ALL:','A','Wubert/LOV',Multi,Free,Persistent,{'78'})

I have created a class Wubert with three objects, one for each of the above.

I added a fourth object in this same class for the sole purpose of generating the LOV. That dimension object is defined as DIM_BANK.BANK_ACCT_CD. I have checked Associate a List of Values, Automatic refresh before use and Export with universe. When I click Edit to go take a look at the SQL that will generate the LOV, I have it as:

SELECT DISTINCT

  DIM_BANK.BANK_ACCT_CD

FROM

  DIM_BANK

ORDER BY

  1

The derived table Parses without a problem.

I go into WebI and try to create a new report using this derived table. I pull the three objects above (Bank Acct Cd, Bank Nm and Bank Acct Dscr). When I click SQL, I get the error message "Invalid Prompt definition. See your Business Objects administrator. (Error: WIS 00005)"

Suggestions?

Thank you very much,

Rob