How to detect subselect inside MDX calculations (aka Multiselect in Excel 2007)
The subject of multiselect friendly calculations is a popular one in forums. I have written about it before here, here and here. In February 2008 I will write a blog which will give a definite answer on how AS2008 will deal with it (which is follow up on this post). But in the meantime, questions about AS2005 keep coming. By now, most people realize that it is possible to detect presence of set in WHERE clause by using EXISTING operator, which takes care of multiselect queries generated by Excel 2003 and other tools. However, Excel 2007 for multiselect uses subselects which are stealthier, their presence cannot be detected neither by EXISTING nor by any other MDX function. The standard answer for detecting subselects always was to use query scoped named sets, because they are subject of implicit autoexist with subselects. However, this approach isn't applicable to Excel 2007, since it is up to Excel 2007 to generate MDX queries, and the user can only control the expression for the calculation inside MDX Script.
Tags: mdx, subselect, multiselect