Casecading Prompt

[Gyana]

Hi,
  I have 2 value promps(p1 & p2).

p1         p2
---------------
A           1
             2
             3
B           4
C           5
             6
My requiremant is if i select p1 it ll shows (None,A,B,C) for 'None' I used static choise.
When i select ' A' it shows (1,2) But when i select None the p2 not showing anything. It should show all p2 values.
Please guide me.

Thanks,
Bapee

[wyconian]

Hi

The problem is you have an unbalanced tree i.e. there are values for p2 that don't have a corresponding p1 value.  So when you have a cascading prompt from p1 to p2 and you select p2 = none there are no corresponding p2 values.

Using static choices allows you to select p1 = none but it doesn't create the relationship between p1 and p2 values.

If there is a hierarchical relationship in your warehouse between p1 and p2 values you can add a 'static member' for p1 called 'none' in the lookup you use for surrogate key substitution.  This will establish the relationship between p1 and p2 values where p1 = none and the cascade prompt should work OK.

The other thing to do remove the cascade prompt and just have 2 simple prompts instead.

Hope that helps

[PRIT AMRIT]

I agree with Wyconian.

There is another work around to get it done though. Use Union to do it.

Create another Query with a Dummy 'PI' query item([P1]='None'. And have your other query items as you have in your existing query. Then do a UNION. Do your cascading on your final query and hence the report.

Hope this should help you.

Thanks
P

[wyconian]

Prit

That sounds like a nice quick work around :)

[Gyana]

Thanks wyconian and PRIT