Get Top N Values

To get top n values we have to edit the data set MDX query.

Data set query before edit:-

SELECT NON EMPTY { [Measures].[Alteration Count] } ON COLUMNS, NON EMPTY { ( [DimEmployee].[Employee].[Employee].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HNBA BIDW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Data set query after replacing the highlighted area:-

With set [Top 10 Alterations] As Topcount([DimEmployee].[Employee].[Employee].ALLMEMBERS,10,[Measures].[Alteration Count]) SELECT NON EMPTY { [Measures].[Alteration Count] } ON COLUMNS, NON EMPTY { ( [Top 10 Alterations] ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HNBA BIDW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Get Last N Months Data

In order to get last N months data we have to edit dataset's code. To do this we use Lag function in MDX.

Here is the code before being edited:-

SELECT NON EMPTY { [Measures].[Loan Amount] } ON COLUMNS, NON EMPTY { ( [DimDate].[YearMonth].[YearMonth].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HNBA BIDW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Here is the code after replacing the highlighted area:- (Selected month of a parameter pass to the code)

="SELECT NON EMPTY { [Measures].[Loan Amount] } ON COLUMNS, NON EMPTY { ( ((ClosingPeriod([DimDate].[YearMonth].[YearMonth],"+ Parameters!DimDateYearMonth.Value +").Lag(11):ClosingPeriod([DimDate].[YearMonth].[YearMonth],"+ Parameters!DimDateYearMonth.Value +"))) ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [HNBA BIDW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"