How to control memberset when using drill down
I'm hoping this one is just a case of me missing something obvious and its a case of using the correct syntax.
I want to control the dim members displayed once a drill down has reached the basmember level.
For example I have an account dim for which I initially have a memberset of DEP when the the current view for that dim is set to ALL_Accounts.
The report expands to initially show, say, two lines, Income and Expenditure according to the hierarchy I've set up.
The user drills, say from Expenditure to Staff Salaries, then to basmember level where 6 accounts are shown eg for salaries, NI, Pension etc.
I want to control which of the 6 basmembers are shown when the drill reaches basmember level by using a memberset filter based on a dim attribute.
Lets say the company is multi-national and has salary accounts that only apply to specific countires, Country being an attribute on the Account dim. I want to use some sort of IF statement in the memberset cell which says, if I am currently not at basmember level then memberset = DEP and continue drill, else expand using the member set filter Country='UK'.
I'm currently trying to use evpro to retrieve hlevel within the memberset cell but the statement can only retrieve hlevel for a specific cell containing the member. That cell ref will obviously need to change depending on where the user is drilling.
Or is the answer vba?