excel web access question RRS feed

  • Question




       I have an excel web access web part connected to an Analysis Services 2005 cube. It is possible to expand an entire level of an hierarchy, not only a member?


    Thank you

    Wednesday, May 2, 2007 10:54 AM


  • The short answer for whether this functionality exists OOB (Out Of the Box) is no, however here are a few options you can try:


    • If the cube hierarcy/data set at and above the level you want expanded is fairly static, you can set the initial expansion in the worksheet.  A manual/automatic refresh could however cause a different expansion.
    • Check out the Analysis Services (AS) product/forums to see if there is a hierarchy webpart.  We have Part to Part (P2P) filtering between AS/Excel Web Access (EWA), but I don't think there is hierarchy expansion/control webpart.
    • Look for drill images with the [+] (ewr078.gif) that you want to expand and trigger their onclick programatically in custom javascript.  Unfortunately this will be a postback/refresh per level that you expand and is dependent on the Office 2007 implementaion (may not work in subsequent versions).
    • Scrape the sessionId from the web part and use the public webservice to expand the pivot (assuming that this is possible).  Perhaps Shahar or others more intimate with the Excel Services Public API can comment on its applicability (or a custom UDF to expand the pivot to a desired state).


    Unfortunately programmatic access/extensibility in the browser is somewhat limited in Office 2007... and I can't comment on how that may change in future versions of the product (since I like my job and want to keep it ).  Nonetheless we are always listening to how people are using the product in order to improve.

    Thursday, June 14, 2007 11:14 PM