Basic MDX Queries

The general syntax for an MDX query is as follows:

As my mention in my previous post, the terms Columns and Rows are simply aliases for the true names of the axes: Axis(0) and Axis(1) respectively. Technically, an MDX query can have up to 128 axes, with aliases for the first five:

1.                   Columns

2.                   Rows

3.                   Pages

4.                   Sections

5.                   Chapters

The following all three queries are same:

SELECT

Measures.[Internet Sales Amount] ON COLUMNS,

[Customer].[Country].MEMBERS ON ROWS,

[Product].[Product Line].MEMBERS ON PAGES

FROM [Adventure Works]

SELECT

Measures.[Internet Sales Amount] ON 0,

[Customer].[Country].MEMBERS ON 1,

[Product].[Product Line].MEMBERS ON 2

FROM [Adventure Works]

SELECT

Measures.[Internet Sales Amount] ON AXIS(0),

[Customer].[Country].MEMBERS ON AXIS(1),

[Product].[Product Line].MEMBERS ON AXIS(2)

FROM [Adventure Works]

Note: In MDX always lower axes is must, without COLUMNS can’t have ROWS or PAGES.

Run the below query, you will find the error: 

Here [M] is mentioning [Mountain]

The above query is return error, because we can use the same dimension in ROWS axis, so we can filter there.

For slicing, we can use other dimensions. For example if you want to filter for year 2007 sales amount.

The WITH Clause and Calculated Members

The MDX with clause provides you with the ability to create calculations and use them within the context of the query. In addition, you can also retrieve data from outside the context of the current cube using LookupCube MDX function.

Named Sets

This query is not too lengthy, but you can imagine a query that would contain a lot of members and functions being applied to this specific set several times within the query. Instead of specifying the complete set every time it ’ s used in the query, you can create a named set and then use it in the query as follows:

Calculated Members

Calculated members are calculations specified by MDX expressions. They are resolved as a result of MDX expression evaluation rather than just by the retrieval of the original fact data.

One other example: Ordering inside the name set

Cheers!