Excel 2007 Doesn't Show Column Totals for Named Sets


  • I came across a weird issue with Excel 2007. The cube script has a named set defined that returns specific members of the Date dimension, such as the months in the current quarter. When the cube is browsed in the Cube Browser, the browser shows a column total as it should. However, Excel doesn't show it a total at the end of the column list. Does anyone know if this is a known issue? Here is the Excel 2007 query:

    SELECT NON EMPTY {Hierarchize(Distinct({[Current QTD]}))} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers(DrilldownMember({{DrilldownLevel({[Financial Account].[Financial Account Hierarchy].[All]})}}, {[Financial Account].[Financial Account Hierarchy].&[-7168254622765392115]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Financial Account].[Financial Account Hierarchy].[Account Number],[Financial Account].[Financial Account Hierarchy].[Category Type],[Financial Account].[Financial Account Hierarchy].[Financial Account Hierarchy],[Financial Account].[Financial Account Hierarchy].[Sort] ON ROWS  FROM (SELECT (Filter([Current QTD], Intersect([Current QTD], Descendants([Current QTD].Current, , AFTER)).Count = 0)) ON COLUMNS  FROM [Viking]) WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

    Home page and blog:
    Thursday, January 22, 2009 3:35 PM

All replies

  • Hi Teo,

    can you describe what have you placed where in Excel? I'm having difficulty interpreting what Excel ment with this query. In particular, that subselect.

    Then I can try simulate it and play with it on AW 2008 example. Be sure to tell all the relevant options you made on that pivot. The query doesn't look like it's generated from simple drag & drop of members/sets.


    Tomislav Piasevoli
    Business Intelligence Specialist

    Saturday, January 24, 2009 3:15 AM
  • Hi Tomislav,

    You can request any of the AW sets, such as Top 50 Customers. You will notice that there isn't a subtotal in the Excel 2007 pivot table report. We need a set subtotal because the total value is derived in the cube script with a custom formula. We are submitting this to MS support for further investigation.

    Home page and blog:
    Monday, January 26, 2009 3:27 PM
  • I think one workaround will be to explicitly request the attribute All member like so:

    CREATE SET CURRENTCUBE.[Rolling 12 Months] AS {

    [Date].[Date Hierarchy].CurrentMember.Lag(11):[Date].[Date Hierarchy].CurrentMember

    ,[Date].[Date Hierarchy].[All]}

    but it will be nice if Excel would support the same behaviour as the cube browser, where you could add the set as a filter and bring the attribute on rows and columns so you could see the All member total. Currently, Excel doesn't support sets as filters.

    Home page and blog:
    Monday, January 26, 2009 4:44 PM
  • True.

    Well, I played with it for a while and I must say that this might be a feature, you know.

    Sets don't have total. Excel on the other hand creates such queries that the total is always included in generated MDX. Other apps might return members without totals and then use grid features to get the same. Which is better? I guess both approaches have downsides. Grid doesn't handle well totals for some formats. Query generated totals might be hard or even impossible to make in some cases.

    Take for example this typical MDX construct made by Excel for Product Categories on rows:

    NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[Product Categories].[All Products]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

    The blue part orders hierarchy members, the orange includes calculated members if any, the green part is standard placeholder that includes future total and all the members from a level below that survive subselect, if any. It's a way of approaching the data, a philosophy on how to get it and how much of it. Excel uses that particular way as I said before.

    When you compare this to another Excel construct, this time for a set on rows:

    NON EMPTY {Hierarchize(Distinct({[Long Lead Products]}))} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS 

    you see that there's no total inside it for set (although they prepared your set for it, maybe to manually add it, by including Distinct function in order to avoid potential multiple members in it. And no wonder, set are flat, no total above them (although it might be a good feature in the next version of SSAS to include it somehow, we would avoid many troubles :-).

    You might think that the total could have been there, it isn't and that's a bug for sure, rigth? Well, if Excel generated it using WITH MEMBER using Aggregate of that set, that would be fine (only partially, later why), but what about multidimensional sets? The total for them couldn't be defined as only one member (on which hierarchy/dimension?). It should be defined for all of them, and then that tuple (of calc members built in the same order - dimensionality) should be placed after the set, to simulate total. Too complicated if you ask me. Also, I don't know whether the current SSAS can give you the exact order of hierarchies in a set or just a list of them.

    Now, more about that second thing - partially fine. Run this query:

    WITH MEMBER [Product].[Model Name].Total AS  
    Aggregate([Long Lead Products])  

    SELECT {[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
     NON EMPTY Union({Hierarchize(Distinct({[Long Lead Products]}))},
      FROM (SELECT (Filter([Long Lead Products],
     Intersect([Long Lead Products], Descendants([Long Lead Products].Current, , AFTER)).Count = 0)) ON COLUMNS
      FROM (SELECT ({[Product].[Product Model Lines].[Product Line].&[R],
    [Product].[Product Model Lines].[Product Line].&[T]}) ON COLUMNS

    Sorry for not formatting it, but it's not important in this case, there's nothing to learn new in it, I just created a calc member as aggregate of set and added it using Union to rows to roleplay a grand total. If you copy-paste results to Excel (a common ritual that works without problems :-)), you'll see that the calc member shows much more than what the sum of the set is. Why? Because I sliced it in subselect on Road and Touring bikes. Now, you might thought that adding EXISTING keyword in Aggregate will help. Nope, doesn't listen. Then maybe adding complex Exists? Yup.

    MEMBER [Product].[Model Name].Total AS  
    Aggregate( Exists( [Long Lead Products],  
    {[Product].[Product Model Lines].[Product Line].&[R],  
        [Product].[Product Model Lines].[Product Line].&[T]}))   

    Will Excel generate one or more of such calc members where it should aggregate the set but first slicing it by all relevant subcubes? And create a tuple in the end of those members, placing it just at the right place in query?

    In my opinion there is no easy way for sets with subselects as an approach. Or maybe with any approach using just MDX query. But then again, those guys are smart, maybe they'll think of something :-).

    Just before posting this I saw our reply. I like your idea very much! It's elegant and it works, I tested it on my sample. Moreover, it could be implemented relatively easy:

    NON EMPTY {Hierarchize( Union( Distinct({[Long Lead Products]}), {[Product].[Model Name].[All Products]} )} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS 

    or using generic default member, referring only to set name

    [Long Lead Products].Item(0).Hierarchy.DefaultMember

    Still, the problem of multidimensional sets resides, I believe ...

    Tomislav Piasevoli
    Business Intelligence Specialist

    Monday, January 26, 2009 6:14 PM