BusinessObjects Board

Automatic Display the Values for Other Prompts

building semantic layer
#1

Topic: Automatic Display the Values for Other Prompts Based on the Value User Chooses for the First Prompt.

Usually, there are multiple prompts for one report. When a user selects a value from one of the prompts, could all other prompt values be automatically showing the corresponding values based on what user selected from the first prompt?

For example, if a user runs a report and selects ProvinceA from the Province prompt, then selects a Program from the Program prompt list, the values in that list should be filtered to show corresponding programs only based on ProvinceA. The same case is true for other prompts like Lender/Agency/Program/Branch, etc.

I wonder if this could be done in universe—by choosing one value from one prompt, then other prompts will be automatically showing the list of values based on the first prompt value selected.

What is the best way to do this? Can this be done in Designer itself, instead of writing VB code? Or suggest creating cascading prompts?

Your help and insight are greatly appreciated.

Lilly

Lilly J (BOB member since 2002-08-22)

#2

In universe, you should overwrite the SQL for LOVs of the other prompts to restrict the values based on first prompt by using @Prompt(‘ProvinceA’,) in teh where clause of teh sql.

saki (BOB member since 2004-01-22)

#3

I would go with Cascading Prompts as outlined in this FAQ Designer entry.

Andreas :de: (BOB member since 2002-06-20)

#4

Hi, Andreas:

I think you are right. I tried this, it is great.

Thanks very much.

Lilly

Lilly J (BOB member since 2002-08-22)

#5

Hi Andreas,
I did exactly what you guys said about cascading prompts it’s working fine,when i try to do this…
[b]Overriding the LOV SQL

  1. Press the SQL button in the query panel of the LOV
  2. Add the phrase UNION SELECT ‘ALL’ FROM DUAL or similar dummy table for your particular database.
  3. Click the “Do not generate SQL before running” checkbox. [/b]

I used this code in Resort and Service Line in beach universe it’s showing ‘ALL’ but when i click ‘ALL’ in those two prompts it’s saying “No data to fetch”

When i enter Country=US;Resort=Bahamas Beach and Service Line=Food & Drinks.It’s showing data.

Thanks
Matt.

matt_man2004 (BOB member since 2004-06-12)

#6

But, you can’t just use this LOV in a regular prompt.

You have to build your own universe-based condition object that does something like:

column=@prompt(your prompt stuff) OR
'ALL' = @prompt(your prompt stuff)

Steve Krandel :us: (BOB member since 2002-06-25)

#7

Hello Steve,

I have prompts for COUNTRY,RESORT and SERVICE LINE. When a user selects a value from one of the prompts, could all other prompt values be automatically showing the corresponding values based on what user selected from the first prompt?

For example, if a user runs a report and selects “US” from the Country prompt, then selects a Resort from the Resort prompt list, the values in that list should be filtered to show corresponding resorts only based on country.and also same for service line

If i use this code i can’t restrict values,it’s showing all valuse in resort and service line.
column=@prompt(your prompt stuff) OR
‘ALL’ = @prompt(your prompt stuff)

Thanks,
Matt

matt_man2004 (BOB member since 2004-06-12)