Dimensional Reporting

[AJ123]

Hi guys Urgent need

Trying to develop a report on top of a dynamic cube source where in we have a hierarchy which has level 1to level 7

i need to have a prompt on the levels,
that is when i select level 1 then it should show me level1 when i select level 2 then show me level 2 and level 1

for example i have 3 levels country, state, city

so i have a prompt on the levels when i select country then it should in the cross tab country, state, city when i select state then it should show me state and city and when i select city then it should show me city
that is when i select the highest level(parent) then it should show me the lower levels of the parent

prompt select country          Country             xxxxx
                                                State          xxxxx
                                                   City         xxxxx

prompt select city                 City                xxxxx

prompt select state               State              xxxxx
                                               City            xxxxx


Thanks in advance!

[tjohnson3050]

Check out the Ancestor function