none
Filter a parent, show members

    Question

  • Hello =)

    I got a product dimension, with several levels of hierarchy... something like:

    [Dept] -> [Group] -> [Type] -> [SKU]

     

    What i want to do is to filter the [Dept], and show all its [Group]s, as lines.

     

    I tried something like:

     

    SELECT
     {[Product].[Group]} ON ROWS,
     {[Measures].[Sales]} ON COLUMNS
    FROM [myCube]
    WHERE ([Product].[Product Hierarchy].[05 - Some Dept])
    

     

    But it raises the error:

    The Product Hierarchy hierarchy already appears in the Axis1 axis.

     

    How can i achieve that WITHOUT using a subquery?

    Friday, January 7, 2011 2:42 PM

Answers

  • Hi,

    You can also check the function Exists:

    http://msdn.microsoft.com/en-us/library/ms144936.aspx

     

    For example:

     

    SELECT [Measures].[Internet Sales Amount] ON 0,

    [Product].[Product Categories].[Subcategory].MEMBERS

    ON 1

    FROM [Adventure Works]

    WHERE  [Product].[Product Categories].[Category].&[3];

    ---OUTPUT

    ---The Product Categories hierarchy already appears in the Axis1 axis.

     

    SELECT [Measures].[Internet Sales Amount] ON 0,

    EXISTS(

    [Product].[Product Categories].[Subcategory].MEMBERS

    , [Product].[Product Categories].[Category].&[3]

    ) ON 1

    FROM [Adventure Works];

    -----WORKS

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Tuesday, January 11, 2011 2:50 AM
    Moderator

All replies

  • Hi

    there are several ways to achieve that:

    with
    set Set_1 as
    [Product].[Product Categories].[Category].&[1].CHILDREN
    
    set Set_2 as
    Descendants([Product].[Product Categories].[Category].&[1],1)
    
    SELECT 
    [Measures].[Internet Sales Amount] ON 0,
    Set_1 -- Set_2 
    ON 1
    FROM [Adventure Works]
    

    Or you can use a similar syntax to yours - but you have to use a member from different hierarchy:

    SELECT 
    [Measures].[Internet Sales Amount] ON 0,
    [Product].[Product Categories].[Subcategory] ON 1
    FROM [Adventure Works]
    where
    [Product].[Category].&[1]
    

    Note above that in the where part we have a category from Product.Category attribute hierarchy, while on rows we have Subcategories from [Product].[Product Categories].[Subcategory] level that exists in [Product].[Product Categories] user hierarchy in the same Product dimension.

     

    HTH,

    Hrvoje Piasevoli

    • Proposed as answer by Javier Guillen Friday, January 7, 2011 10:11 PM
    Friday, January 7, 2011 4:49 PM
  • Hi,

    You can also check the function Exists:

    http://msdn.microsoft.com/en-us/library/ms144936.aspx

     

    For example:

     

    SELECT [Measures].[Internet Sales Amount] ON 0,

    [Product].[Product Categories].[Subcategory].MEMBERS

    ON 1

    FROM [Adventure Works]

    WHERE  [Product].[Product Categories].[Category].&[3];

    ---OUTPUT

    ---The Product Categories hierarchy already appears in the Axis1 axis.

     

    SELECT [Measures].[Internet Sales Amount] ON 0,

    EXISTS(

    [Product].[Product Categories].[Subcategory].MEMBERS

    , [Product].[Product Categories].[Category].&[3]

    ) ON 1

    FROM [Adventure Works];

    -----WORKS

     

    Hope this helps,

    Raymond


    Raymond Li - MSFT
    Tuesday, January 11, 2011 2:50 AM
    Moderator