none
TopCount in Cube not the same as TopCount in Excel RRS feed

  • Question

  •  I was reading a post on https://sqldusty.wordpress.com/tag/dynamic-named-set/ and my issue is I don't get the same results if I build a TopCount in the Cube compare to users doing filtering on the Top in MS Excel using pivot table connecting to the same cube.
    Quick sample adventure works
    In Adventure works cube you will find this dynamic set

    Create Dynamic Set CurrentCube.[Top 25 Selling Products]

    As TopCount

        (

           [Product].[Product].[Product].Members,

           25,

           [Measures].[Sales Amount]

        )

    ,

    Display_Folder = 'Sets'

    and if you connect to the cube via Excel and drop Sales amount and product on the pivot table then do a filter of Top 25 on sale amount you get the same exact data sets, which is what you would expect.

    But if you add say "Sales Territory Country" from  Sales Territory dimension to the Top 25 Selling Products measure doesn't have 25 products for Australia while doing the filter on the excel side via pivot table does have 25 products for Australia.

    So this beg the question how can you someone accomplish a Top X Client but still be able to add more Dimensions on top via Excel? So if I want to see Top 10 Client by sales amount I can write that in MDX in the CUbe side. But then the user wants to slice it by say territory so they want to know the top 10 clients by sales amount for each territory. How can that be done?

    Kind of the same in adventure works it has the Top 25 selling Products which is the Top 25 products by sales amount. What if users wanted to see the Top 25 selling products for each territory. Does that mean a new Dynamic set is needed? cause having the Dynamic set of "Top 25 Selling Products" and dropping Sales territory on the pivot doesn't work as above.

    Basically I would like to know how you can create a TOP X measure in the Cube but be able to change the slicers or dimensions dynamically as users slice by different dimensions via Excel pivot. Kind of in a way mimic what excel does when you create a filter for Top X for what ever dimension then a user adds another dimension by simply dropping it on top of the pivot.
    Wednesday, December 10, 2014 1:58 PM

All replies

  • Hi Anony,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, December 12, 2014 8:31 AM
    Moderator