Creating Sample SSRS Report in Dynamics ax 2012

Create SSRS Report in Dynamics AX 2012

SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Reporting Services is a server-based reporting platform that includes a complete set of tools to create, manage, and deliver reports, and APIs that enable you to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

In a Visual Studio reporting project for Microsoft Dynamics AX, you can define a report in a report model. A report consists of a collection of items, such as datasets, parameters, images, and report designs. A model can contain more than one report.

Also, for this post I am assuming that all report services are configured in the system.

Let us create a query in AOT as a dataset source for our report.

Create a new query by name – “SR_InventQuery” and add InventTable as datasource and add ItemId range to it. We will let you know the significance of adding the range.

So, your new query should like below :

Please note: we can use already existing queries which are in AOT for report as datasource,

for better understanding I have created new query above.

Open visual studio 2010 and lets us create a new Dynamics AX project.

Once visual studio is launched >> click on file menu >> New project as shown below

Select Microsoft Dynamics AX from the installed templates >> report model and name the model as SR_InventReportProject as shown below

Now let us add a new report to the newly created report Model as shown below. Right click on the SR_InventReportProject  from the solution explorer, Add >> Report

 

Rename the report to SR_InventReport by right click and rename option on the newly added report.

Then we need to add the dataset to the newly created report. Right click on the datasets node and chose the option New dataset. Rename it to SR_InventDS  and go to query property and click on the ellipsis (…) button to select the query which we have created before  name as SR_InventQuery(usr)

 

Now , you can select the list of fields and display methods you want to see on your report.
I have selected few fields form the fields and also couple of display methods like site Id and location Id as shown below and click on Ok Button.

 

we are done with the datasets part and lets work on the design part real quick now…

Its very simple.. Select the InventDS dataset and drag and drop on to your designs node as shown below. It will create autodesign for you. Rename it as SR_InventDesign

 

when we expand the designs node, we can see our  fields and the data methods added in the data nodes. we will look in to other nodes in detail .

Well there are now some important [not mandatory] properties but beautification properties which make your report look good with style. Once you expand the designs node, you will find InventDesign(Rename) with the dataset name.

Right click Design and go to properties and set the style template to “TableStyleTemplate” and On to SR_InventDesign , we also need to set an important property called Layout Template – set it to ReportLayoutStyleTemplate as shown below

Now, lets switch to parameters node in the report. If you expand the parameters node , you will find some parameters. Lets work on AX_CompanyName parameter. By default it visibility parameter is hidden. Lets make it visible it as we want to display the items based on the company [dataarea id] selection by the user.

See the screen shot below

we are getting closer. Now we can preview the data by right clicking the SR_inventDesign and by choosing option preview as shown below

Note: you can select the company parameter and click on the report tab to view the report. But our main aim is to deploy back this report model to AX.

To deploy the report to AOT, we have a very simple option. Right click on the SR_ReportNew Model  from the solution explorer and select option Add  SR_InventReprotProject to AOT as shown below.

We are done with visual studio development part. Now lets us switch to AX and see whether the report model has been saved to AOT or not. To do so, open your AX client and goto to AOT >> Visual studio projects >> Dynamics AX Model projects . you should see SR_InventReprotProject project.

Also, In AOT >> SSRS Reports >> Reports >> you should see SR_InventReport report.

Now we are left out with final thing, creating menu item for this report. This can be easily done by following the below process.

Go to AOT >> Menu items >> Output >> Right click and Select New Menu item and set the following properties as shown below.

 

we are done and hope you are excited to view the report now. Well you can add this menu item to relevant menu and I hope you know this process.Now , lets open the report, Right click on the newly created menu item and select open.[You should see the following as shown below]

Since we have made the company parameter visible- we have option of generating the report based on the data area id and since we have added range ItemId to the query – we get two ranges as shown above.

I have selected ceu as my company and I am leaving ItemId as blank to view all the items in the report. Now lets run the report and see how it renders the data. [Below is the report]

Note: AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration.