Intersection at different levels

[davephall]

Hi there,

I've got a recurring issue that I can't find mention of anywhere, and I was hoping someone had a solution. My dimensional report in report studio has two parameters, 'row directorate' and 'prompt directorate', as the report can either be parameterised at runtime, or this can come from a drill through in a previous report. I can't combine these into a nested crosstab, as sometimes the row directorate is at a higher level than the prompt directorate and vice-versa.

Now to get round this issue, I've sliced using 'row directorate' and used a filter expression to get 'prompt directorate' but this causes a fair few problems, not least when I try to drill through to a relational report with the item not mentioned in the crosstab.

I thought that the intersect function may do what I want it to, something like:

Code Select Expand

intersect( set( [Directorate] -> ?Prompt Directorate? ) , set( [Directorate] -> ?Row Directorate?) )

Which works fine when the two parameters are at the same hierarchy level, but when they're at different levels (E.G. All Directorates + Some Directorates should return Some Directorates) nothing is returned. Does anyone know of a way to get this behavior?

Thanks,

Dave.