Top 5 Children of HierarchyLevel RRS feed

  • Question

  • Hi,


    i need to build a query the returns me a set containing a whole hierarchy-level an the top 5 children of each element in that hierarchy-level


    lets take Adventure Works as an example

    we have 4 members in Product Category (Bikes, Accesoires, Components and Clothing)

    and i want to get the top 5 products of each of these Categories

    like this:


    Bike 1

    Bike 2

    Bike 3

    Bike 4

    Bike 5


    Accesoires 1

    Accesoires 2

    Accesoires 3

    Accesoires 4

    Accesoires 5



    its NOT the problem to get the top 5 of one member like

    topcount([Product].[Product Categories].[Category].&[1].children, 5, [Measures].[Sales Amount])

    but to build the query to return the top 5 for each member in the Product Category


    my first approach was to crossjoin the topcount-set with my Product Categories:

    [Product].[Product Categories].[Category].members


    topcount([Product].[Product Categories].currentmember.children, 5, [Measures].[Sales Amount])

    but the AS gives me the error that the Product.Product Category is used more than once in the crossjoin


    anyone has an idea how to solve this problem?


    kind regards





    Wednesday, February 27, 2008 2:57 PM


  • Hi Gerhard,


    How about this query - does it return the results you want?


    Code Snippet


    {[Measures].[Sales Amount]} on 0,

    Generate([Product].[Product Categories].[Category],

    {[Product].[Product Categories].CurrentMember,

    TopCount(existing [Product].[Product Categories].[Product],

    5, [Measures].[Sales Amount])}) on 1

    from [Adventure Works]


    Wednesday, February 27, 2008 4:35 PM