MDX Tail Function

In Multidimensional Expression, Tail Function is used to select the required number of records from bottom of the source data.

For example, If you want to find the Bottom 10 performing products or  you want to find the Bottom 10 regions with Lowest sales or to calculate Lowest Salary we can use this Head function along with MDX Order Function.

MDX Tail Function Syntax

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

1	TAIL (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 TAIL Function to extract Bottom 7 States from Geography dimension (present in the Adventure Works Cube) whose Reseller Sales amount is lower than others with example. For this, we are going to use below shown data.

MDX Tail Function Example

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

CODE

1 2 3 4 5 6

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, TAIL ( [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, Tail function will select the Bottom 7 records irrespective of their measure values.

OUTPUT

No one will accept the above result as Least performing states around the world. They we just Bottom 7 records.

Using Order Function along with MDX Tail Function

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

CODE

1 2 3 4 5 6

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

OUTPUT

Analysis:

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

1	TAIL ([Geography].[Geography].[State-Province], 7)

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

1 2 3 4

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

It means, We are getting Last 7 records from the source data in Descending order

Let us change the above code:

CODE

1 2 3 4 5 6 7 8 9 10

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,   TAIL (     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.

1 2 3 4 5

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

Next, Tail function will pick the Last 7 records from the already sorted data.

1 2 3 4 5 6 7 8

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

Now, We got our Bottom 7 records or Least performing states.

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

Using NON EMPTY in MDX Tail Function

In this example we are going to use Non Empty keyword to remove the NULL values.

CODE

1 2 3 4 5 6 7 8 9 10 11

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

OUTPUT

If you observe the above screenshot, MDX Non Empty keyword we used in the above query removed those Null records. Now, it looks Nice and Easy 

Thank You for Visiting Our Blog