none
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:

    Bikes

    Bike 1

    Bike 2

    Bike 3

    Bike 4

    Bike 5

    Accesoires

    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

    Gerhard

     

     

     

    Wednesday, February 27, 2008 2:57 PM
    Answerer

Answers

  • Hi Gerhard,

     

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

     

    Code Snippet

    select

    {[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
    Moderator