Auto Increment Row Number in a Report Table

To add auto increment column we can use "RunningValue" function in SSRS.

To do this add below expression to the cell in the group you want to add auto increment column.

=RunningValue(<Value Field (Must be the grouped field)>,CountDistinct,"<DataSet>")

Example:-
=RunningValue(Fields!Name.Value,CountDistinct,"DataSet")

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"