Friday, 20 November 2015


Dynamics AX CUBES, SSRS and SSAS

Open SQL Server Business Intelligence Development Studio from SQL server


Make a new project



In new  project  select  business  intelligence projects in project type and using Analysis Service Project Template make new project




In the solution Explorer make a new datasource as given in the above screen shot.
Datasource wizard will b on the next button.

Clcikc new and specify the servername and the database name

Type server name and database name .and check the connection by clicking test connection and then press ok.

Select the inherit option and click on next button.

click on the finish button



.now in the solution explorer create new data source view as above.


 one wizard will b opened . click on the next button.

.no change and just click on the next button.

select the table of which you want data in cube.

clcik finish button .and your view will b generated automatically..



 now again go to the solution explorer and using right click on the cube folder make new cube as shown in the above screen shots…


 one wizard will b opened .click on the next button..

select the first option USE existing tables.

select the dimension which is showing in the above screen.


 select all measure which you want to show in cube and click on the next button.

.Select all the dimension table which you want to use and click on the next button..


click on the next button.

now in the solution explorer double click on the dimension for adding dimension from that dimension table.

 above screen will appear .


Now drag and drop the fileds which you want as a dimension from the dimension table

 now go to the project button and select the project name properties.

 now select the server name and then click the ok button.

 in the solution explorer select the project name .right click and then process the project.

one window will b open . click the run button without changing anything.

you will find the process succeeded message on the screen .then click on the close button.

now click on the cube and then right click on it and first process and then browse it..


here is the window where you can drag and drop the measures and dimension on the screen as you want. .measure should be drop on the red symbol place and dimension on the dimensions like X and Y

SSRS using Cubes


In BI tools open new project


Select server project wizard in BI projects

Click the next button


Enter the server name and select Microsoft sql server analysis service and click edit


Enter the server name again and select the cube name in the database name

Check if connection is succeeded or not and click ok


Now copy the connection string and save it in notepad file and click next

The query builder wizard will open , click on the query builder

Now you will find the different measures and dimensions, select that dimensions and measures which you want to show in the report and drag it in the report area

Copy the query in the notepad file and click next and finish wizard

Now goto new and select new project option


Select visual c# and then dynamics AX Reporting Project

The project will open now , in solution explorer right click on the project and select add new item

One window will open , select report data source from the window, in the properties of data source paste the saved connection string in the conncetion string field

Select the name of the project in the name field in the properties of the data source

Select the report from the project


In the report option select the dataset and add new dataset


In the properties of the dataset select the query field and paste the saved query from notepad file

In the dataset you will find the values as you can see in the screenshot

Goto the design and add autodesign


In the autodesign add one table or any design which you want to add



Drag the field from the dataset in to the table dnt drag the fields which are in the red box, that fields are for label purpose only.

Right click on the report and click on preview


Deploy on EP

Right click on the project name solution explorer and click on save to AOD


Open the AX and in report libraries u will find report which we deploy in BI.

We can edit report by right click on report name and thn select Edit in Visual Studio

After doing changes in visual studio click on the restore in AOT


Now in menuitem add one menu item


In the properties of menuitem set the properties Name , label , objectype, object and runon as below

Object type   =    SQLReportLibraryReport

Run On    =   Called from


Goto the administration.


In the internet ->EP click web site..

U will get administration of web sites , in this click on view in browser..


In the browser home page will appear.

Enter edit page in the site action ..


U will find add a web part option on the screen on add a web part..

Click on the Dynamics Report Server Report..


Click on the given arrow of Dynamics Report Server Report  and u will find one window in that click on Modify shared web part..


Now u will find one window in that enter report name

The report will come on EP








No comments:

Post a Comment