try to use NONEMPTYSUBSET()
"This function can help optimize queries that are based on a large set for which the set of nonempty combinations is known to be small. NonEmptySubset reduces the size of the set in the presense of a metric; for example, you might request the nonempty subset of descendants for specific Units.
NonEmptySubset is used to reduce the size of a set before a subsequent analytical retrieval."
The thing is I once had that message and used NONEMPTYSUBSET and the warning didn't go away so I wound up just ignoring it. Some problems are just complicated.
I know thats not the right way but due to requiremnet i have to do the same.
Now when i retriev the member in excel , my excel is dying up and it is not giving result.
As KKT says, as far as the warning (it's not an 'error') goes, sometimes you just have to live with it.
Why does your requirement force you to do this the 'wrong way'?
As you probably know, this method for forcing roll-up of dynamic members (an IF switch on level with a SUM function at non-level-zero) is notoriously slow. Multiple layers of complex MDX (formula members calling formula members) likewise.
Assuming you're interested in changing the technique if it gives the same results, can you explain the requirement in more detail?
I agree with TimG and others. ASO is meant for aggregations and you are making it slower by enforcing it to aggregate explicity by using a formula...
Will 22.214.171.124, you can always use procedural calcs to calculate at base level and then leave it to aggregate at higher level..