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 open..click 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 ..click 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