# BottomCount in MDX

4878

Friends,

This function is very much similarly to the functions BottomSum and BottomPercent. Please find the Major Difference between the all three functions –

1) BottomCount – Orders the set and then returns specified number of tuples in the specified set with the lowest values will be pulled.

2) BottomPercent – Orders the set and then returns tuples from the bottom of the set with the lowest values whose cumulative total is less than or equal to the supplied percentage.

3) BottomSum – Orders the set and then returns the summed value of a numeric expression across a supplied set.

In this post we are gonna discuss about BottomCount Function. This function will do TWO jobs

1) Sorts the set based on numeric parameter in Ascending order.

2) Returns specified number of tuples in the specified set with the lowest values.

PFB the syntax of the same.

BottomCount(Set_Exp,Count,Numeric_Exp).

Set_Exp – Valid MDX Set Expression

Count – It specifies the NUMBER of records to be returned.

Numeric_Exp – NOT A MANDATORY PARAMETER and it specifies on what column basis the set needs to be ordered.

If the numeric expression is specified then BottomCount is nothing by the combination of TWO MDX functions i.e ORDER(Asc) + TAIL and if the numeric expression is not specified then it is JUST like TAIL function in MDX.

If the numeric expression is specified then the set specified in the function will get sorted according to the value of the specified numeric expression in ascending order and then returns the specified number of tuples from BOTTOM.

Now lets see the difference between two cases –

In the below case I specified Numeric Expression and the below query returns 10 “Products” that have the lowest “Sales Amount”.

select [Measures].[Sales Amount] on Columns,
BottomCount([Dim Product].[Products].[Products],10,[Measures].[Sales Amount]) on Rows

If you see the screenshot given below, it is returning all NULLS as in most of the cases you will have products with NO SALES and as we are requesting for BottomCount it will return Products with LEAST values and obviously NULLS.

The above one is correct but none of the client wants to see the least valued products which has NO SALES at all instead they wish to see least valued products whose sales is NON ZERO. PFB the query which will return the bottom products with NON ZERO sales.

select [Measures].[Sales Amount] on Columns,
BottomCount(FILTER([Dim Product].[Products].[Products], NOT ISEMPTY([Measures].[Sales Amount])),
10,[Measures].[Sales Amount]) on Rows

In the above query I am filtering EMPTY records and pfb the screenshot of the output.

The above output looks perfect and for sure client will be happy. he will be even more happy if you format the output .. 😉 Now let’s see the other scenario i.e with out giving numeric expression and pfb sample query of the same –

select [Measures].[Sales Amount] on Columns,
BottomCount([Dim Product].[Products].[Products],10) on Rows