AllMembers vs Children

Both do what their name implies .
AllMembers will return All Members on the current level and all levels below.
SELECT
    {       
        ([Date].[Calendar Year].&[2013], [Measures].[Sales Amount])
    }
ON COLUMNS
,
    {
        ([Product].[Product Model Lines].AllMembers)
    } ON ROWS
FROM
   [Adventure Works]

More dimensions on rows

Let’s add a second dimension to our query:

SELECT
    {
     [Measures].[Sales Amount]   
    } ON COLUMNS
    ,   
    NON EMPTY
    {
      [Date].[Calendar Year].Children
    , [Product].[Product Model Lines].Children
    }                    
    ON ROWS
FROM
    [Adventure Works]

Hmmmm, we get an error:

“Members, tuples or sets must use the same hierarchies in the  function.”

More Measure Columns

Adding more measure columns is simple, but you have to be aware of the extra Curly Brackets “{ }” which enclose your row or columns definition. Let’s try:
SELECT
    {
     [Measures].[Sales Amount]
    ,[Measures].[Tax Amount]
    ,[Measures].[Total Product Cost]    
    } ON COLUMNS
    ,   
    NON EMPTY
    {
    [Date].[Calendar Year].Children
    }                    
    ON ROWS
FROM
    [Adventure Works]

My first MDX query

Open up your SSMS (Sequel Server Management Studio) and make a connection to your “Adventure Works” OLAP database.

Click on the MDX query button: .