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"

Creating a SSAS project

Lets we go to Sql Server Business Intelligence Development Studio. Then go to File -> New -> Project then select Analysis Service Project’ under ‘Business Intelligence Projects’. Then give a name, location and click ok. 

Now we want to add Data Source into the project. To do so right click ‘Data Source’ in the Solution Explorer and click ‘New Data Source’.  

Then we get ‘Data Source Wizard’, click next. Now we have to create a connection to the data source that we are going to retrieve data. Click ‘New’ button. 

Then we get ‘Connection Manager’ window. Give the server Name and the Database and click ok.  

And Click Next. And then enter the username and password and click next. 

And then give a name to the data source and click finish.  

It will finish the Data Source creation process. Then we want to create a ‘Data Source View’. Data Source Views let us define a subset of the data that came from the large data warehouse. Data Source View is an isolated schema. So we can add any required annotations without affecting the schemas of underline data source.

To create a Data Source View first right clicks the ‘Data Source Views’ in the Solution Explorer and select ‘New Data Source View’.  

After that we get ‘Data Source View Wizard’, click next. Then we get a window to select the Data Source, select the Data Source that we create before and click next. 

Then we can see another window. In here we want to select tables we are going to use. Select tables and click the ‘>’ button to move them to the right. We can move all the tables at ones by clicking ‘>>’ button.  

After select the tables click next. From the next window give a name to the Data Source View and click finish. Then we can see the tables with their relationships. 

Next we want to create a ‘Cube’. Cube is a set of related measures and dimensions that is used to analyze data. 

To do so, right click the ‘Cubes’ in Solution Explorer and choose ‘New Cube’.

Then we get the ‘Cube Wizard’, click next. Then we forced to select the creation method. Cubes can be created by using existing tables, creating an empty cube, or generating tables in the data source. Here I use the method ‘Use Existing Tables’. Select the method and click next. 

 Now we can see another window and in there we can select the tables we want to create the Cube. Select the tables and click next.  

 

Then the next two steps we want to select the Measures and Dimensions and finally give a suitable name and click finish.  

Finally we can assign access roles to the project to access the project. To do so right click the ‘Roles’ in Solution Explorer and click the ‘New Role’. 

  

In the coming window there is a tab called ‘General’, here select the permissions and the go to the ‘Membership’ tab and add users who want to access the project. 

  

Now we finished creating our SSAS project.