MDX Head Function

In Multidimensional Expression, 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 highest sales or to calculate Highest Salary we can use this Head function along with MDX Order Function.
Similarities between MDX HEAD and SQL TOP Clause


MDX HEAD Function is similar to SQL TOP Clause.


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


MDX Head Function allows only two arguments. The basic syntax of this function is:

HEAD (Set_Expression, Count)


Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Count: Please provide, How many number of records you want to retrieve.


In this article we will show you, How to write 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 below shown data.

MDX Head Function Example

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


CODE

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, Head function will select the Top 7 records irrespective of their measure values.


OUTPUT





No one will accept the above result as top 7 performing states around the world. They we just first 7 records.
Using Order Function along with MDX Head Function


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


CODE


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]


OUTPUT





Analysis:

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

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

Next, Order function outside the Head function will sort the already picked data in the 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 First 7 records from the source data in Descending order

Let us change the above code:


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]


OUTPUT





Analysis

First, 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, 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 second argument in the ORDER function otherwise, You will end up with wrong results.