Simple MDX

Execute the following MDX statement:-

   
   select  {[Measures].[Units]} on columns,
      {[CostCenters].[Engineers]} on rows
   from TimesheetReport
   where ([WorkDates].[2002].[Quarter 2].[August])

An MDX statement is made up of the following components:-

The Select Clause

The select clause can contain one or more Axes specifications. In this example there are two Axes specifications but an MDX statement can contain up to 128. The values from the column axes dimensions are displayed in the columns at the top of the page while the values from the row access dimension are displayed at the beginning of the rows. At the intersection of a row and column header is a value.

Note that each Axes specification contains a set made up of 1 tuple. Because there is only one member in each tuple there is no need to surround it in rounded brackets.

The first five axes in an MDX statement have aliases - COLUMNS, ROWS, PAGES, SECTIONS and CHAPTERS. This statement uses the first three:-

   select {([Measures].[Units])} on columns,
      {[CostCenters].[Engineers]} on rows,
      {[Shifts].[A], [Shifts].[B], [Shifts].[C]} on pages
   from TimesheetReport
   where ([WorkDates].[2002].[Quarter 2].[August])

Attempt to execute this statement in the MDX Sample Application and it will fail. It is only able to display two dimensional MDX statements. A pivot table would display the results of this statement by putting the Shifts in a drop down box at the top of the pivot table. Once again, note that the PAGES axis contains three tuples. Because each tuple contains only one member there is no need to surround them in rounded brackets.

It is possible to write the same statement using the AXIS key word rather than the axes aliases. For example:-

   select {([Measures].[Units])} on Axis(0),
      {[CostCenters].[Engineers]} on Axis(1),
      {[Shifts].[A], [Shifts].[B], [Shifts].[C]} on Axis(2)
   from TimesheetReport
   where ([WorkDates].[2002].[Quarter 2].[August])

Note, you must specify the Axes in order without a gap. In other words you cannot have a row access without first having a column access. Attempt to execute the following example. It will fail:-

   select {[Measures].[Units]} on rows,
      {[CostCenters].[Engineers]} on pages 
   from TimesheetReport
   where ([WorkDates].[2002].[Quarter 2].[August])

The From Clause

The From clause specifies the cube where data is selected from.

The Where Clause

The Where Clause specifies a tuple that defines a slice from the cube that is applied to the axes in the select clause to limit the data returned. In the example above the [WorkDates].[2002].[Quarter 2].[August] member limits the values returned by the MDX to those that belong to this month. Because the where clause is a tuple, it can include members from multiple dimensions.

Further Issues

You cannot put members from the same dimension in more than one Axes, or in an Axes and the Where clause. For example, try and execute the following statement. It will fail because it includes members from the CostCenters dimension in both the ROWS Axis and the Where tuple:-

   select {[Measures].[Units]} on columns,
      {[CostCenters].[Engineers]} on rows
   from TimesheetReport
   where ([WorkDates].[2002].[Quarter 2].[August], [CostCenters].[Personnel])