The problem that I have is with a report based on a cube, the report has a tree prompt based on a hierarchy which has 5 levels to it. I am trying to write a query that counts the members returned on the 4th level of the hierarchy that the tree prompt is filtering on.
I then need to create a variable so that when more than 10 members are returned in the 4th level I can render certain objects in the report.
Any help would be great as it's driving me round the bend!
Chris
I'm not sure if anyone else is having a problem with this but I eventually cracked it with the below macro
count( 1 within set descendants (#promptmany(''Param_name,'memberuniquename','[Cube hierarchy to filter on]','set(','',')')#,[level in cube you wish to count members for after filter]))
This by default gave me the auto generated prompt which I replaced with a tree prompt on the prompt page.