BottomCount in MDX

February 11, 2012

9138

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
From [Adventure Works]

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
From [Adventure Works]

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
From [Adventure Works]

In the above query no Numeric expression to ask server to SORT the set in an order before returning 10 records. The output is as shown in below screenshot –

The above output is entirely different from the previous one as the last one is not sorting the data before selecting the records. If the see the products names you can see that the products are ordered alphabetically and then returning last 10.

So, in simple BottomCount with numeric_Exp is Order(ASC) + Tail and BottomCount without numeric_Exp acts like TAIL function.

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V