BusinessObjects Board

Dynamic SQL in LOV

Folks,

I have a situation as follows: I need to first show the user a prompt like ‘Select AI or FT’ holding only 2 values, ie ‘AI’ and ‘FT’. If the user selects ‘AI’, then the SQL generated to show the LOV for the next lower level prompt will be

SELECT DISTINCT PRODUCT_NAME FROM PRODUCT;

However, if the user selects ‘FT’, then the SQL generated to show the next lower level prompt will be

SELECT DISTINCT PRODUCT_NAME FROM PRODUCT
WHERE hasFactorTerm = ‘Y’;

Can this be achieved in BO? Based on user-selected value, can one have a different SQL for the LOV generated? :blue:

If this is not possible, can anyone suggest a work-around? :idea:

Thanks much,

SR


dwglobal (BOB member since 2005-12-19)

You actually need cascading prompts
Please read Designer FAQ on Cascading prompts

.


haider :es: (BOB member since 2005-07-18)

Hi –

Thank you for the response. I have set up cascading prompts in BO before. However, note the dynamic aspect of the LOV here this time. Once the cascading prompt is set up (In this case, the level goes from ‘Calculation type’ to ‘Product List’)…Based on what you select in Calculation type, the Product List must be populated. Thus,

[Calculation Type] ------------> [Product List]

Product list is populated based on what the user selects in Calculation Type.

However, the SQL for the lower level is very different once the upper level is chosen. If the user chooses ‘FT’ as a value in Calculation type, the SQL generated to populate the LOV for Product list is SELECT distinct PRODUCT_NAME FROM PRODUCT WHERE HasFactorTerm=’Y’;

If the user selects ‘AI’ as a value in the Calculation Type, the SQL for the next lower level LOV is SELECT distinct PRODUCT_NAME FROM PRODUCT; --No ‘where’ clause this time

Thanks,

SR


dwglobal (BOB member since 2005-12-19)

What you can do is create a union LOV query for step two. The query would be:

SELECT DISTINCT PRODUCT_NAME FROM PRODUCT
WHERE @Prompt(...) = 'AI'
UNION
SELECT DISTINCT PRODUCT_NAME FROM PRODUCT 
WHERE hasFactorTerm = 'Y'
AND @Prompt(...) = 'FT';

The trick is… you cannot “branch” your LOV selection, it has to be linear. But by including the prompts as shown above only one half of the union will ever return rows. Does this make sense?


Dave Rathbun :us: (BOB member since 2002-06-06)

Thank you, Dave,

Yes, it does make sense. The only change I had to make in the code was to make that last where statement into an OR from an AND to accommodate my logic.

It works, thank you again for the approach! :smiley:

Cheers,

SR


dwglobal (BOB member since 2005-12-19)