none
How to use Dynamic Set to fix Multiselect Issue in Excel RRS feed

  • Question

  • Hi,

    I have a calculation that fails within Excel 2010 when I select more than one date from my dimension . I have been told (by Deepak) that this is because Excel uses a subselect instead of a where clause (which Excel 2003 & earlier use). I have tested this by saving a new file as an .xls then open it in compatability mode and create my pivot table as an older version; in this instance, my calculation is (mostly) okay. I was refered to this article for help: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

    Please see my next post for more info (as I have had formatting issues in the past with my first posts).

    Thanks,

    Tuesday, October 5, 2010 11:57 PM

Answers

  • try to create your dynamic set as follows (your current definitions uses a dynamic set to reference a static set):

    CREATE DYNAMIC SET CURRENTCUBE.[Days]

     AS {EXISTING [Status Date].[Calendar].[Day]} ;

    hth,
    gerhard


    - www.pmOne.com -
    Wednesday, October 6, 2010 7:49 AM
    Answerer

All replies

  • This is how my cube is set up:

     

    Measure Groups:

    Dimensions:

    Injury

    Injury Status

    Date (Injury Date)

    Regular: Date

    Referenced: Injury

    Recordable Injury

    Many-To-Many: Injury Status

    Regular: Recordable Injury

    Injury

    Fact: Injury ID

    Regular: Injury ID

    Date (Status Date)

    Many-To-Many: Injury Status

    Regular: Date (StatusDate)

    Date (Status Start)

    Many-To-Many: Injury Status

    Regular: Date (StartDate)

    Date (Status End)

    Many-To-Many: Injury Status

    Regular: Date (EndDate)

     

    Note that I am using SSAS 2008 R2 with Excel 2010 as my front end. My SSAS is just a testing database at this point so it has a limited number of dimensions.

    Here is the calculation I was testing between Excel 2010 as-is & then again within a pivot table in compatibility mode.

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Recordable Injuries]

     AS Aggregate( --Crossjoin

     (

      NULL:LinkMember(Tail(existing [Status Date].[Calendar].[Day].Members).item(0),[Status Start].[Calendar]),

      LinkMember(Tail(existing [Status Date].[Calendar].[Day].Members).item(0),[Status End].[Calendar]):NULL

    --  ,Root([Status Date])

     ), [Measures].[Status Count]),

    VISIBLE = 1;

    [Measures].[Status Count] is a distinct count of InjuryID on the [Injury Status] measure group.

    [Status Date].[Calendar] is a date hierarchy of Year->Quarter->Month->Day. [Status Date] also contains the following members that are not part of the hierarchy:

    ·         [Months] – the Year & Month number are the keys, with the Name like [June 2010]

    ·         [Month Name] – the key is the month number and the name is the month name

    The calculation is supposed to gather distinct Injury counts, so that when there are multiple Recordable  Injury status records, only the latest one in the selected time period will appear. However, within Excel 2010, the data does not roll up properly (if at all) when more than one [Status Data].[Calendar] member is selected.

    I originally had created the cube within SSAS 2005. I recreated the cube (did not upgrade but created new) within SSAS 2008 R2. As per Deepak’s advice, I attempted to use a Dynamic Set to fix my multi-select problem. (See this post which was my original: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/69ceb3c5-8b4e-46aa-9584-ad5a9dfee8f1/).

    This is my new Dynamic Set:

    CREATE DYNAMIC SET CURRENTCUBE.[Days]

     AS [Status Date].[Calendar].[Day] ;

    And this is my new calc that uses the dynamic set:

    CREATE MEMBER CURRENTCUBE.[MEASURES].[Recordable Injuries TEST]

     AS Aggregate( Crossjoin

     (

      NULL:LinkMember(Tail([Days]).item(0),[Status Start].[Calendar]),

      LinkMember(Tail([Days]).item(0),[Status End].[Calendar]):NULL

    ), [Measures].[Status Count]),

    VISIBLE = 1;

    The new calculation does not work. It gives a proper final total, but if I then try to group the data within Excel by [Status Date].[Months], the monthly totals are off in June, but okay in July.

    Any ideas what I am doing wrong?

    Thanks,

    Tina

     

    Wednesday, October 6, 2010 12:34 AM
  • try to create your dynamic set as follows (your current definitions uses a dynamic set to reference a static set):

    CREATE DYNAMIC SET CURRENTCUBE.[Days]

     AS {EXISTING [Status Date].[Calendar].[Day]} ;

    hth,
    gerhard


    - www.pmOne.com -
    Wednesday, October 6, 2010 7:49 AM
    Answerer
  • Hello,

    Thanks for your post. It helps me a lot.
    But it works only, when i am using one dimension for filtering. Can you please give me a hint what i can do when i want to filter on more than one dimension?

    Thanks!

    Wednesday, October 27, 2010 9:48 AM
  • I don't understand why this works (though I can see that it does in my own case). What difference does layering the dynamic set actually make?

    In my case I still put the EXISTING in the calculated member, not the set, and it works, which just confuses me massively. eg:

    CREATE DYNAMIC SET [Selected Days] as (
        [Date].[Year-Month-Date].[Date]
    )
    CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
        count(existing(
    [Selected Days]))
    )

    works, but

     

    CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
        count(existing([Date].[Year-Month-Date].[Date]))
    )

     

    fails.

    Friday, May 13, 2011 4:07 AM
  • ok I answered my own question, or at least Mosha did.

    You don't need 'existing' *at all* in the dynamic named set solution, and the reason why this works is by design in the implementation of dynamic named sets:

    " Dynamic sets are not calculated once. They are calculated before each query, and, very important, in the context of that's query WHERE clause and subselects"

    http://sqlblog.com/blogs/mosha/archive/2007/08/26/mdx-in-katmai-dynamic-named-sets.aspx

    Makes a lot more sense now. But that does mean the original answer above is incorrect: adding the 'existing' to the dynamic named set shouldn't have changed anything...

    Friday, May 13, 2011 7:06 AM