MDX Head Function

The MDX Head Function is used to select the required number of records from the source data. For example, If you want to find the Top 10 performing products. Or you want to find the top 10 regions with the highest sales or to calculate the Highest Salary. We can use this MDX Head function along with the Order Function.

Similarities between MDX HEAD and SQL TOP Clause

The MDX HEAD Function is similar to the SQL TOP Clause.

  • Like TOP Clause, the Head function extracts the required number of records in the order they were initially stored.
  • To extract the Top 10 performing records, we have to use the Order By Clause in SQL.
  • To extract the Top 10 performing records, we have to use the MDX Order Function with appropriate Measure or Numeric Value.

MDX Head Function Syntax

The Head Function allows only two arguments. The basic syntax of this HEAD function in Multidimensional Expression is as shown below:

HEAD (Set_Expression, Count)
  • Set_Expression: Any Multidimensional Expression or attributes on which you want to check.
  • Count: Number of records you want to retrieve.

How to write MDX HEAD Function to extract Top 7 States from Geography dimension (present in the Adventure Works Cube) whose Reseller Sales amount is greater than others with example?. For this, we are going to use the below-shown data.

Source Table

MDX Head Function Example

In this example, we will show you how to write the Head function. The following query will return the first seven records from the above source in the order they appear above.

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
 HEAD (
	 [Geography].[Geography].[State-Province] 
        ,7 
       ) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, the Head function will select the Top 7 records irrespective of their measured values.

MDX HEAD FUNCTION 1

No one will accept the above result as the top 7 performing states around the world. They were just the first seven records.

Using Order Function along with MDX Head Function

In this example, we will show you, What happens when we add the MDX Order Function.

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
 ORDER (
    HEAD ([Geography].[Geography].[State-Province], 7)--Already Picked Data 
   ,[Measures].[Reseller Sales Amount], DESC
       ) ON ROWS
FROM [Adventure Works]

MDX HEAD FUNCTION 2

First, the Head function will select the First 7 records present in the source data using the below statement.

 HEAD ([Geography].[Geography].[State-Province], 7)

Next, the Order function outside the MDX Head function will sort the already picked data in descending order using their Reseller Sales Amount.

 ORDER (
    HEAD ([Geography].[Geography].[State-Province], 7) 
   ,[Measures].[Reseller Sales Amount], DESC
       ) ON ROWS

It means we are getting the First 7 records from the source data in Descending order.

Let us change the above code:

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
  HEAD (
    ORDER (
          [Geography].[Geography].[State-Province]
         ,[Measures].[Reseller Sales Amount]
         ,BDESC
           )
     ,7 
      ) ON ROWS
FROM [Adventure Works]
MDX HEAD FUNCTION 3

First, the Order function will sort the State Province data in the Descending order using their Reseller Sales Amount. We used BDESC to break the hierarchy while sorting the data.

ORDER (
        [Geography].[Geography].[State-Province]
       ,[Measures].[Reseller Sales Amount]
       ,BDESC
      )

Next, the MDX Head function will pick the First 7 records from the already sorted data.

HEAD (
    ORDER (
          [Geography].[Geography].[State-Province]
         ,[Measures].[Reseller Sales Amount]
         ,BDESC
           )
     ,7 
      ) ON ROWS

Now, We got our Top 7 performing states.

NOTE: Please use the appropriate Measure as the second argument in the ORDER function. Otherwise, You will end up with the wrong results.

Categories MDX