Prompt on Unionized Alternate Dimensions (MDX)

[gagnonp]

Hello all, I need to find a solution that addresses this requirement of SINGLE Value Prompt and report filter. No Cascading.

Data Source = Dimensional (transformer cube)

Project Dimension (H1=Dimension, H2 and H3 =Alternate Paths):

----------------
| H1 | H2 | H3 |
----------------
| L3 | L3 |Comp|
| L2 | L2 |    |
| L1 | L1 |    |
| ____________ |
| Project Code |
----------------

Data:

H1_L3  H1_L2  H1_L1  H2_L3  H2_L2  H2_L1  H3_Comp    Project
X900   X910   X911   Y100   Y110   Y111   Completed     A001
X800   X810   X811   Y200   Y210   Y211   Completed     A002   
X700   X710   X711   Y200   Y210   Y212   Non-Completed A003
X900   X910   X911   Y300   Y310   Y311   Non-Completed A004
X900   X910   X911   Y300   Y310   Y311   Completed     A005
X900   X910   X911   Y300   Y310   Y312   Non-Completed A006

Requirements:

1. H1 and H2 values are unique across H1 and H2 levels

2. PROMPT: SINGLE Value Prompt on all values of H1 AND H2 (UNION)
   Prompt Values will be:  X700, X710, X711, X800, X810, X811, X900, X910, X911, Y100, Y110, Y111. Y200, Y210, Y211, Y300, Y310, Y311, Y312
   
3. FILTER: Display in a crosstab row the Non-Completed (H3) Projects.

For example, picking X900 retrieves: A004, A006 ; Picking Y210 retrieves: A003