BusinessObjects Board

One prompt depends on second

We have a two tables, for example, Country and City.
Tables are linked by Country_ID.

Also we have a two prompts in report: “Select a country” and “Select a city”

User want click on “Values” button, select a country, then go to “Select a city” field, click on “Values” and see ONLY cities from selected country.

Any suggestions?..


relgames (BOB member since 2004-04-26)

Please, try a search on BOB, suggested keywords: casc*

Or look at this FAQ Designer entry.


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

Sorry for misunderstanding: fields CountryName and CityName are in single table

SQL:
select country_id,
dbo.GetCountryName(country_ID),
dbo.GetCityName(country_ID)
from country

Physically there are two tables, but we need to use user functions to retreive city caption, so we can’t write SQL like this:
select t1.countryName, t2.cityName
from country t1, city t2
where t1.country_id=t2.country_id

We need exactly this structure of universe (city names are selected by some criteria in our GetCityName function)

FAQ’s method does not work with this report…

May be VBA? Is VBA worked under webi?


relgames (BOB member since 2004-04-26)

Hi Relgames,

I have a similar situation in my BU. I utilize “Cascading Prompts” which should work for you as well.

Our structure is Business Units -> Market Units -> Locations. I built these prompts to be used in conjunction with an “ALL” option. However, I will just cover cascading prompts. Here’s how to do Cascading Prompts:

[list]In the second level of your hierarchy build a SQL-driven LOV in Designer. In my case, MU would have a condition of BU. Here’s the SQL:

SELECT DISTINCT FLGL.FLGL_PBG_STRUCT5D.FLEX_VALUE_MU, FROM FLGL.FLGL_PBG_STRUCT5D WHERE ( FLGL.FLGL_PBG_STRUCT5D.FLEX_VALUE_BU = @variable('Enter BU') AND (( FLGL.FLGL_PBG_STRUCT5D.FLEX_VALUE_5 In ('USAT','UNID','USA1') )) ) )[/list]

[list]For you next level, you would do the same but this time add the BU and the MU as prompted conditions, BU and MU.[/list]

To an end-user it may appear a little screwy because they would have to reselect BU for for a list of MU’s and MU for a list of locations, but with a little education, my user base was able to deal with it.

Hope this helps.[/code][/list]


vonwolf :us: (BOB member since 2002-10-21)