How to implement Cascading Drill Filters(not in Prompts)

building reports webintelligence
1

I need some help designing better (like Cascading) Drill filters.

I have a situation, where i need to pull Dimensions like State Code, Division and District onto Drill filters area in the drill mode.
What i want is, when i select particular State like CA in the State filter then Division should list only corresponding to CA, non other. So the District. I need Division should filter on State and District should filter on Division and so on.

Can any body help me, how can i achieve this one?

Env: BOXIR2SP5, DB2

MightyBO :us: (BOB member since 2007-06-28)

2

Hello kevvu,
check this Thread

Arjun (BOB member since 2008-07-28)

3

Thanks Arjun.

But i am not looking for Cascading Prompts. What i am referring here, in the Edit report report mode, we can drag objects as Drillable Objects onto Drill filter pane(Enable Drill Mode). There we have LOVs as drop downs, if we select the value in the drop down correspondingly report will change.

There we can add objects as Hierarchical wise, so i need 2nd level object should display only corresponding to 1st level object.

Suppose we have: State …Division

If i select CA as state, my drop down list in Division should display only CA divisions.

MightyBO :us: (BOB member since 2007-06-28)

4

That’s how it works already. If it’s not working that way, then you probably don’t have proper data. Here’s a screen shot showing same from Island Resorts. I have selected the “West” Region, and note that the pull-down for City only shows appropriate cities. I did not have to do anything special to make this work, it’s the natural behavior.
drill_dropdowns.jpg

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

5

Hi Dave,

Does it work going from Right to Left as well. For instance, I select Los Angeles and the under State selection, I should only see CA.

In my report I select CA and I see Cities under CA. (working as expected). But when I select LA as my City, eveything gets populated Under State.

Please suggest.

Thanks!

OranjeBoy (BOB member since 2009-08-11)

6

Dave,

Thank you very much for your response.

I observed that the scenario you mentioned, above for West and LA, is working only when we are pulling objects from Same table(i.e single table).

Suppose i pull Region from one table and State from another table, it’s not working as expected although they are connected by FK.

Any idea?

MightyBO :us: (BOB member since 2007-06-28)

7

In Island Resorts region is already from a different table than state, which is the example I mentioned above. It works fine.

It doesn’t work going ‘up’ because there might be more than one city with the same name. Suppose I selected Portland. Do I mean Portland Maine? Oregon? Texas?

Rumor has it that the reason the TV show “The Simpsons” takes place in Springfield is because every state has a city named Springfield.

In any case, when you select only from the lower level then “all” values from the upper level are displayed because it doesn’t know which might be appropriate.

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