Friday, November 1, 2013

TopCount and BottomCount in SSAS

In some scenarios we may want to get the lowest or highest dimension attributes using SSAS Cube. As an example we may need to get the 10 items with lowest unit price and the highest unit price.

In such kind of a scenario we can use the TopCount and BottomCount functions.

To get the 10 items with highest unit price, below expression can be used.

   1: TopCount
   2:     (
   3:     [Item].[Item].[Item].Members,
   4:     10,
   5:     [Measures].[Unit Price]
   6:     )

Likewise I used the BottomCount in the same way to get the 10 items with lowest unit price.
   1: BottomCount
   2:     (
   3:     [Item].[Item].[Item].Members,
   4:     10,
   5:     [Measures].[Unit Price]
   6:     )
But it I filter data for the Month, it only shows the values for the last month. For others no rows were displayed.
Actually this is due to the NULL values and to avoid it, NONEMPTY function was used.

   1: BottomCount
   2:    (NONEMPTY([Item].[Item].[Item].Members,{[Measures].[Unit Price]})
   3:    , 10
   4:    , [Measures].[Unit Price]
   5:    )

This shows the 10 items with lowest unit price for the selected filtering criteria.

No comments:

Post a Comment