Excel 2007, MDX Compatibility and connection string (for ragged dimensions)


  • I'm trying to open a simple SSAS 2005 cube with a ragged hierarchy (it has a level that contains member that should be hidden if the member name is the same as the parent).

    I read about the necessity to use MDX Compatibility=2 into the connection string to see the HideMemberIf property in action. The dimension browser in BIDS does not display a member into a level only if it has no visible childs. May be this is a browser issue, as I read somewhere...

    With Excel 2007, it should be possible to change the connection string. Unfortunately, even if I change it, Excel 2007 force the "MDX Compatibility=1" into my connection string. Moreover, it adds a "Safety Options=2" and "MDX Missing Member Mode=Error" and even if I try to change them, Excel still recreate the same connection string.

    Is there someone that:

    - has been successful using ragged hierarchies with Analysis Services 2005?

    - has been able to change the connection string into Excel 2007?

    Thank you

    Marco Russo

    Wednesday, January 10, 2007 9:44 AM

All replies

  • Not tried them with Excel 2007, I'm afraid - it's always seemed pretty dumb to me that the default was that ragged hierarchies weren't displayed properly anyway. And what's worse is that in AS2005 you can't force the MDX compatibility property on the server either:

    Can we get this fixed please?


    Wednesday, January 10, 2007 11:09 AM
  • Hi Chris,

    I just posted in Excel 2007 forum another annoying issue.

    By default, Excel 2007 display calculated measures but not calculated members on other dimensions.
    You have to change this pivottable option settings each time. For each Pivot Table you create.

    I asked it there is a way (eventually with a registry hack) to change this default...

    Marco Russo


    Wednesday, January 10, 2007 11:17 AM
  • Hi there,


    Has anyone seen any updated information on the ragged hierarchy issue? I am having problems with setting MDX Compatibility = 2. Excel resets it back to 1.




    Wednesday, September 26, 2007 10:01 PM
  • Has anybody got any new information on this topic?
    Wednesday, November 12, 2008 8:59 AM
  • Just a note, I am using SageBI (formerly IntelligentApps) and it does offer setting MDX Compatibility to 0,1, and 2. This tool is written on Excel. I am sure other tools do offer the functionality but it's really stupid not to have the facility in native excel. If anyone successfully put a bit of VB code to switch this damn things on, please share with us.

    Microsoft deliberately made Excel slightly "less" than other commercial reporting products. Considering Microsoft invested billions in last 7/8 years in the BI space, who would have thought they could not eventually put a bit of code to fix Excel?

    Monday, January 5, 2009 11:29 PM
  • If you change the connection string in an Excel 2007 PivotTable and add the following it appears to let you override the MDX Compatibility.

    Extended Properties="MDX Compatibility=2";MDX Compatibility=2;

    That being said, the user experience isn't too great once you switch that connection string. Expanding the tree by clicking the plus signs won't let you get to the children below a hidden member. But if you right click in certain contexts and choose "Expand/Collapse... Expand Entire Field" then you can get to those children under a hidden member, but the entire tree seems to be disoriented and buggy. So I'm not sure the above trick will actually help anything.
    Friday, January 30, 2009 7:57 PM
  • Thanks furmangg,
    It worked up to a certain level then crashed with that typical annoying "Send Error to Microsoft" process.
    My project dimension is as follows:

    Parent Project-->Child Project-->Parent Task-->Child Task-->Sub Task Lvl1-->Sub Task Lvl2-->Sub Task Lvl3-->Expense Item

    Not a parent child dimension but level based.
    I am able to drill down in Excel up until Sub Task Lvl3 then it's crashing.
    Quite strange. Works fine on SageBI but not on Excel 2007.

    Wednesday, February 11, 2009 1:04 AM