lowest Level of Hierarchy Expression

[davephall]

Hi there,

I'm using a powercube in 8.3, and I've got a report that uses a hierarchy of Directorate, with levels of Directorate (top level), Directorate1 and Region

My first crosstab has a slicer on the hierarchy ( [Finance Cube].[Directorate] -> ?Directorate? ) to filter at whatever level I want using a tree prompt. This is passed through as a parameter to drill through to a second report that breaks the measures down by the directorate1 level.

I then want to be able to pass this data item through to a second drill through, to a report that breaks down by region (the reports are different, so drill-down won't do the job).

If I've filtered at the directorate (top level) level on my first report, the whole thing works fine, passing directorate1 through to the third report, but if I've filtered at the region level, obviously this gets overwritten by the directorate1 data item in the second report.

I've been trying all day to get something to work (I thought I may be able to do something with ordinals, but can't seem to find the ordinal of the parameter) so any pointers in the right direction would be gratefully received.

Thanks,

Dave.

[davephall]

Another though I've had that may put a different slant on what to do. I could pass the directorate1 data item to the third report as directorate1 parameter, and then pass the directorate parameter to the third report as directorate parameter.

So now I've got two parameters (holding the two different MUNs) in the third report, is there any way of creating a data item that picks the parameter at the lowest level, something like:

if( ordinal( ?directorate? ) < ordinal( ?directorate1? ) )
Then ( ?directorate? ) Else ( ?Directorate1? )

Except for the fact that this gives me an invalid prompt use error...

Thanks in advance,

Dave.