Dimensions using Children vs Members

The following 2 queries use the Measures of Sales Amount & Reseller Sales Amount for axis 0 (or columns) and the Product Category Name on axis 1 (or rows). The first one uses the children of the dimension.

select { Measures.[Sales Amount],
[Measures].[Sales Amount - Fact Reseller Sales] } on 0,
[Dim Product Category].[Category Name].children on 1
FROM AdventureWorksProductsDW2012;

The second query is the same as the first, but uses Members of the Product Category and now includes "All" as one of the rows displayed.

select { Measures.[Sales Amount],
[Measures].[Sales Amount - Fact Reseller Sales] } on 0,
[Dim Product Category].[Category Name].members on 1
FROM AdventureWorksProductsDW2012;

Slicer Axis

The next query is similar to the query above except that it uses the Country Canada in the slicer axis in the Where clause to provide only sales figures for that country.

select { Measures.[Sales Amount] } on 0,
[Dim Product Category].[Category Name].members on 1
FROM AdventureWorksProductsDW2012
where [Dim Customer].[English Country Region Name].&[Canada];

This next query uses a Measures dimension in the slicer axis and includes the Order Date Years in the Columns and State Province names in the Rows.

SELECT [Order Date].[Calendar Year].children on 0,
NON EMPTY [Dim Geography].[State Province Name].Members ON 1
FROM [AdventureWorksProductsDW2012]
where {[Measures].[Sales Amount]};

Cross Join in Query

This query uses a Cross Join between the Country United States and the States in columns and the Measure Sales Amount in the slicer axis to provide all sales amounts in the row by State.

SELECT [Dim Geography].[English Country Region Name].[United States] *
[Dim Geography].[State Province Name].Members ON 0
FROM [AdventureWorksProductsDW2012]
WHERE Measures.[Sales Amount];

This next query uses the Sales Amount in the Columns and the Cross Join of the United States and States Members in the rows

select Measures.[Sales Amount] on 0,
[Dim Geography].[English Country Region Name].[United States] *
[Dim Geography].[State Province Name].Members on 1
FROM [AdventureWorksProductsDW2012];

This Cross Join example of Subcategories and Sales Amounts over Order Years uses a tuple of subcategories which displays all Subcategories but only Amounts for the Product Subcategories of Road Bikes and Mountain Bikes.

SELECT {[Measures].[Sales Amount]} *
[Dim Product Subcategory].[English Product Subcategory Name].members ON COLUMNS,
{[Order Date].[Calendar Year].MEMBERS} ON ROWS
FROM [AdventureWorksProductsDW2012]
WHERE({[Dim Product].[Product Subcategory Key].&[1],
[Dim Product].[Product Subcategory Key].&[2]});

Here we are using NON EMPTY to remove other empty members from results - both empty years and columns from data returned.

SELECT Non Empty {[Measures].[Sales Amount]}
* [Dim Product Subcategory].[English Product Subcategory Name].members ON COLUMNS,
Non Empty {[Order Date].[Calendar Year].MEMBERS} ON ROWS
FROM [AdventureWorksProductsDW2012]
WHERE({[Dim Product].[Product Subcategory Key].&[1],
[Dim Product].[Product Subcategory Key].&[2]});

And finally, we add a Having stmt to limit the output to only sales Amounts > $5,000,000.

SELECT non empty {[Measures].[Sales Amount]} *
[Dim Product Subcategory].[English Product Subcategory Name].members ON COLUMNS,
non empty {[Order Date].[Calendar Year].MEMBERS}
HAVING Measures.[Sales Amount] > 5000000 ON ROWS
FROM [AdventureWorksProductsDW2012]
WHERE({[Dim Product].[Product Subcategory Key].&[1],
[Dim Product].[Product Subcategory Key].&[2]});

Calculated Members

In the Connection Dialog, click on Options and add Additional Connection String information to allow Subqueries to be executed by adding Subqueries=1 or Subqueries=2. If this is not done, the default of Subqueries=0 is automatically chosen and an error is generated when attempting to execute the query on the Calculated Member

Below is the result that you will see in the Messages window in SSMS if you do not add the Subqueries text to the Connection String when opening the SSAS connection.

Executing the query ...
A set has been encountered that cannot contain calculated members.
Execution complete

Create the Member for the query. In this case, the member includes All Subcategories of Bikes.

CREATE MEMBER [AdventureWorksProductsDW2012].[Dim Product Subcategory].[English Product Subcategory Name].[All Bikes]
AS AGGREGATE(
{
[Dim Product Subcategory].[English Product Subcategory Name].&[Mountain Bikes],
[Dim Product Subcategory].[English Product Subcategory Name].&[Road Bikes],
[Dim Product Subcategory].[English Product Subcategory Name].&[Touring Bikes]
}
)

Execute the query for the calculated member and see the sales amounts across each year for All Bikes sold.

  Select [Order Date].[Calendar Year].members on 0,
  [Dim Product Subcategory].[English Product Subcategory Name].allmembers on 1
  from (Select {[Dim Product Subcategory].[English Product Subcategory Name].[All Bikes]} on 0
   from [AdventureWorksProductsDW2012] )
  Where [Measures].[Sales Amount];